Function(UDF) Problem

[Praveen]

Hi All,

I have a small function below, when i try to create this, it says the
following error..

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "VARYING" was found following
"RESULT_SET_LOCATOR". Expected tokens may include: "<space>". LINE
NUMBER=26. SQLSTATE=42601.

As this function i have to use in select statement(like select
empno,call_accountlist...), so i cant create this as procedure. Can
anyone help me out in this? where is the problem?
CREATE function CALL_AccountList ()
RETURNS INTEGER
LANGUAGE SQL

BEGIN ATOMIC

DECLARE sqlcode INT DEFAULT 0;
DECLARE v_empId INT DEFAULT 0;
DECLARE v_empNum INT DEFAULT 0;
DECLARE v_empCnt INT DEFAULT 0;
DECLARE loc1 RESULT_SET_LOCATOR VARYING;
SET v_empNum =20;
CALL AccountList('BA',307);
ASSOCIATE RESULT SET LOCATOR(loc1)WITH
PROCEDURE AccountPackage.AccountList;
ALLOCATE c1 CURSOR FOR RESULT SET loc1;
L1:LOOP
FETCH FROM c1 INTO v_empID;
IF (sqlcode =100)or (v_empCnt >v_empNum)
THEN LEAVE L1;
ELSE
SET v_empCnt =v_empCnt +1;
INSERT INTO temp_table (num_col,char_col)
VALUES (v_empId,'IN DEPARTMENT ');
END IF;
END LOOP L1;
RETURN v_empCnt;
END@

Thanks in Advance,
Praveen

[Serge Rielau]

Praveen,

SQL Functions use inline SQL PL as described in Compound SQL (Dynamic).
The SQL PL you used there is well beyond it's capabilities.
You have four possible routes of action:
1. Use an SQL Procedure instead of an SQL Function
2. Same as 1. but in addition write an C Function which CALLs the stored
procedure.
3. Write a C or Java function instead of an SQL Function
4. Same as 1. but in addition dowload the DB2 Stinger Technology Preview
and write an SQL Function that CALLs the stored procedure.
DB2 Stinger supports CALL in inline SQL PL, but it does not support
resultset support in inline SQL PL.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab