Hi,
Im a DB2 newbie. I'm trying to declare a cursor inside a compound statement in a UDF, but the compiler always fails on the "declare cursor" line with err msg: "SQL0104N An unexpected token "cursor for..."

create function xxx.xxx()
returns integer
begin atomic
declare x cursor for select * from xxx.table1
end

I've read that only a subset of SQL commands is permitted within an atomic compound statement, but which ones??? The UDF will not accept a non-atomic BEGIN-END

I'm using DB2 v8.1 on Windows XP.

Thanks in advance...

Look in the V 8.1 SQL Reference manual Volume 2 (in my manual it is on page 123) - Section "Compound SQL (Dynamic)". It tells you everything you need to know.

Andy

Thanks Andy,
unfortunately, I don't have this book. Is it available online, or could you post the relevant bit?

regards,
Deji

Deji,
Online documentation is located here:
http://publib.boulder.ibm.com/infoce...help/index.jsp

To find what you are looking for: under Contents:
REFERENCE -> SQL -> SQL Statements -> Compound SQL (Dynamic)

You can download the manuals at:
http://www-306.ibm.com/cgi-bin/db2ww...bs.d2w/en_main

Andy

Thanks again Andy. It appears I can't declare a cursor within a UDF. Is this consistent with your experience?, and if so, how would you run a dynamic SQL select within a UDF?

Regards,
Deji

Deji,
No, you cannot use a cursor in a UDF. Can you post what you are trying to do, and maybe I can help you?

Andy

Andy, I wanted to write a function which would accept a lookup-value, then use this value to lookup against a column in another table, and return a column in the 2nd table. (eg lookup id 45 in the staff table, and return "SMITH"). I wrote this succesfully as a SP, but I would now like to convert it to a UDF. The SP code is as follows:

CREATE procedure ARENA_SCHEMA.LookupByInteger(in p_intLookupValue integer, in p_strLookupTable varchar(50),
in p_strLookupColumn varchar(50), in p_strReturnColumn varchar(50),
out p_strReturnValue varchar(255))
reads sql data
result sets 0
/* looks up on value <p_intLookupValue> into table <p_strLookupTable>.<p_strLookupColumn>
and returns value of <p_strLookupTable>.<p_strReturnValue> in <p_strReturnValue> */
/* returns 0 if the lookup succeeds, -1 if the lookups fails, -2 if duplicate matches found */
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
BEGIN

declare intReturnValue integer;
declare strSQL varchar(250);
declare intRowNotFound int default 0;
declare intRowCount integer;
declare strReturnValue varchar(250);
declare objStatement statement;

declare objCursor cursor for objStatement;
-- select p_strReturnColumn from p_strLookupTable where
-- p_strLookupColumn = p_intLookupValue;

declare CONTINUE handler for NOT FOUND
set intRowNotFound = 1;

set p_strLookupTable = ltrim(rtrim(p_strLookupTable));
set p_strLookupColumn = ltrim(rtrim(p_strLookupColumn));
set p_strReturnColumn = ltrim(rtrim(p_strReturnColumn));

set strSQL = 'select ' || p_strReturnColumn ||
' from ' || p_strLookupTable ||
' where ' || p_strLookupColumn || ' = ' || char(p_intLookupValue);

if (p_strLookupTable = '' or p_strLookupColumn = '' or p_strReturnColumn = '') then
/* all parameters must be supplied */
set intReturnValue = -1;
else

prepare objStatement from strSQL;
open objCursor;

set intRowCount = 0;
set intRowNotFound = 0;
while (intRowCount < 2 and intRowNotFound = 0) do
fetch objCursor into strReturnValue;
if (intRowNotFound = 1) then
if (intRowCount = 0) then
set intReturnValue = -1;
end if;
else
set intReturnValue = 1;
set intRowCount = intRowCount+1;
if (intRowCount > 1) then
set intReturnValue = -2;
end if;
end if;
end while;

close objCursor;

if (intReturnValue >= 0) then
set p_strReturnValue = strReturnValue;
else
set p_strReturnValue = null;
end if;

end if;

return intReturnValue;
end


Thanks,
Deji

Deji,
Your problem in the UDF is that you cannot build the select statement. You could use the FOR statement if the SELECT statement is known. If you have a finite set of values for lookuptable.lookupcolumn then you could write it as a series of IF-THEN-ELSE. Otherwise you will need to stay with the SP.

Andy

Thanks for taking the time to look at this Andy. You've saved me from further fruitless searching. Coming from a Microsoft (SQL Server/VB) background, the biggest problem I'm finding is that information on DB2 or J2EE is not as available as with MS products, even if the technology is better...

By the way, if I wrote the UDF in C, could that get around the dynamic SQL problem?

Regards,
Deji

Deji,
C or Java should allow you to do what you want.
Andy