functions and varchar

[Ivan Smahin]

Hello Danny,

Friday, July 18, 2008, 11:15:21 AM, you wrote:

> Hi List,

> I spent some time making this function work :

> CREATE OR REPLACE FUNCTION sp_max_GetTypelistId( tl_name VARCHAR )
> RETURNS ULONG
> BEGIN
> DECLARE test ulong;
> SET test = SELECT RecID FROM tbl_typelists WHERE LOWER(typelistname) =
> tl_name;
> RETURN test;
> END;

> It always return NULL. If I run the query in sql editor it returns a  
> value...
> I tried several things, and at one point, I replaced the varchar also
> by a long and then I got a result...


> CREATE OR REPLACE FUNCTION sp_max_GetTypelistId( tl_name LONG )
> RETURNS ULONG
> BEGIN
> DECLARE test ulong;
> SET test = SELECT RecID FROM tbl_typelists WHERE RecID = tl_name;
> RETURN test;
> END;


> Totally ridiculous function of course, but this time, it returned the
> correct value!

> Changing it to a third version :

> CREATE OR REPLACE FUNCTION sp_max_GetTypelistId(tl_name LONG)
> RETURNS VARCHAR
> BEGIN
> DECLARE test VARCHAR;
> SET test = SELECT TYPELISTNAME FROM tbl_typelists WHERE RecID = tl_name;
> RETURN test;
> END;

> it again returned NULL...
> So I'm guessing that a function can't handle varchars very well..??
> Any idea's?

Please report it to the Mantis.
Meanwhile you can use usual SP with IO argument for result.

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com