calling SP from UDF

**Serge Rielau** · Jul 24 '08, 03:35 AM

Re: calling SP from UDF

lenygold via DBMonster.com wrote:

Hello All.
I developed a Stored Procedure for generic paging from any table. It is
working fine:
>
--#SET TERMINATOR !
CREATE PROCEDURE TAB_SEL_ANYPAGE (TNAME CHAR(20),PREDIC ATE VARCHAR(1000),
>
N INTEGER,PAGE_NO INTEGER)
LANGUAGE SQL
READS SQL DATA
RESULT SETS 1
>
S1 : BEGIN
>
DECLARE SQL_CURS1 CHAR(200) ;
DECLARE STMT_CURS1 CHAR(200) ;
DECLARE CURS2 CURSOR WITH RETURN FOR STMT_CURS1 ;
>
SET SQL_CURS1 = 'SELECT * FROM '||TNAME ||
'WHERE ' || PREDICATE || ' ' || CHAR(N * (PAGE_NO - 1))
|| CASE WHEN N 0 THEN 'FETCH FIRST '|| CHAR(N) ||' ROWS ONLY'
WHEN N = 0 THEN ' '
ELSE
RAISE_ERROR ('77000', 'INVALID FETCH, VALID N - INTEGER 0')
END ;
PREPARE STMT_CURS1 FROM SQL_CURS1 ;
>
OPEN CURS2 ;
END S1!
>
Example of use: Get 2nd page 12 line per screen from table QUERY_LOG based on
the key
invoice#:
>
CALL TAB_SEL_ANYPAGE ('QUERY_LOG','I NVOICE#' ,12,2);
>
>
INVOICE# CREATE_DATE QUERY_ID
----------- ----------- ----------
13 05/10/2008 ALL01 13
14 05/10/2008 ALL01 14
15 05/10/2008 ALL01 15
16 05/10/2008 ALL01 16
17 05/10/2008 ALL01 17
18 05/10/2008 ALL01 18
19 05/10/2008 ALL01 19
20 05/10/2008 ALL01 20
21 05/10/2008 ALL01 21
22 05/10/2008 ALL01 22
23 05/10/2008 ALL01 23
24 05/10/2008 ALL01 24
>
12 record(s) selected.
>
>
But i a got an error when i try to develop UDF to call this SP:
>
--#SET TERMINATOR!
CREATE FUNCTION TAB_SEL_ANYPAGE (TNAME CHAR(20),PREDIC ATE VARCHAR(1000),N
INTEGER,PAGE_NO INTEGER)
RETURNS TABLE (RESULT VARCHAR(1000))
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE RESULT VARCHAR(1000);
CALL TAB_SEL_ANYPAGE (TNAME CHAR(20),PREDIC ATE VARCHAR(1000),N INTEGER,
PAGE_NO INTEGER,RESULT) ;
RETURN VALUES RESULT;
END!
>
SQL0104N An unexpected token "VALUES" was found following "RETURNS ".
Expected tokens may include: "JOIN <joined_table >
".
Any idea what is wrong?
>

Your example... When I try thsi I get a syntax error on the CALL because
you added parameter types to your arguments...
Fixing this I get through:
db2 =CREATE FUNCTION TAB_SEL_ANYPAGE (TNAME CHAR(20),PREDIC ATE
VARCHAR(1000),N
db2 (cont.) =INTEGER,PAGE_N O INTEGER)
db2 (cont.) =RETURNS TABLE (RESULT VARCHAR(1000))
db2 (cont.) =MODIFIES SQL DATA
db2 (cont.) =DETERMINISTIC
db2 (cont.) =NO EXTERNAL ACTION
db2 (cont.) =BEGIN ATOMIC
db2 (cont.) =DECLARE RESULT VARCHAR(1000);
db2 (cont.) =CALL TAB_SEL_ANYPAGE (TNAME,PREDICAT E,N ,PAGE_NO,RESULT );
db2 (cont.) =RETURN VALUES RESULT;
db2 (cont.) =END!
DB20000I The SQL command completed successfully

Which version/platform of DB2 is this anyway?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**lenygold via DBMonster.com** · Jul 24 '08, 08:15 AM

Re: calling SP from UDF

Hi Serge!
My DB2 version is DB2 9.5C EXPRESS.
I fixed but still it is not working.
--#SET TERMINATOR!
CREATE FUNCTION TAB_SEL_ANYPAGE (TNAME CHAR(20),PREDIC ATE VARCHAR(1000),N
INTEGER,PAGE_NO INTEGER)
RETURNS TABLE (RESULT VARCHAR(1000))
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE RESULT VARCHAR(1000);
CALL TAB_SEL_ANYPAGE (TNAME,PREDICAT E,N,PAGE_NO,RES ULT);
RETURN VALUES RESULT;
END!

SQL0104N An unexpected token "VALUES" was found following "RETURN ".

When i run identical TONKUMA UDF i am perfectly fine:
--#SET TERMINATOR !
CREATE FUNCTION Calculate (inStr VARCHAR(100))
RETURNS TABLE (Result FLOAT)
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE Result FLOAT;
CALL Calculate(inStr , Result);
RETURN VALUES Result;
END!

Thank's for your help!

Serge Rielau wrote:

>Hello All.
>I developed a Stored Procedure for generic paging from any table. It is

>[quoted text clipped - 69 lines]

>".
>Any idea what is wrong?

>
>Your example... When I try thsi I get a syntax error on the CALL because
>you added parameter types to your arguments...
>Fixing this I get through:
>db2 =CREATE FUNCTION TAB_SEL_ANYPAGE (TNAME CHAR(20),PREDIC ATE
>VARCHAR(1000), N
>db2 (cont.) =INTEGER,PAGE_N O INTEGER)
>db2 (cont.) =RETURNS TABLE (RESULT VARCHAR(1000))
>db2 (cont.) =MODIFIES SQL DATA
>db2 (cont.) =DETERMINISTIC
>db2 (cont.) =NO EXTERNAL ACTION
>db2 (cont.) =BEGIN ATOMIC
>db2 (cont.) =DECLARE RESULT VARCHAR(1000);
>db2 (cont.) =CALL TAB_SEL_ANYPAGE (TNAME,PREDICAT E,N ,PAGE_NO,RESULT );
>db2 (cont.) =RETURN VALUES RESULT;
>db2 (cont.) =END!
>DB20000I The SQL command completed successfully
>
>Which version/platform of DB2 is this anyway?
>
>Cheers
>Serge
>

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200807/1

**Serge Rielau** · Jul 24 '08, 10:55 AM

Re: calling SP from UDF

Hmm...
--#SET TERMINATOR !
vs
--#SET TERMINATOR!

I think DB2 didn't recognize your terminator directive.

Chances are you actually got two errors because DB2 thinks you gave it
two statements. CREATE ...; and RETURN...;

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**lenygold via DBMonster.com** · Jul 24 '08, 09:35 PM

Re: calling SP from UDF

Thank you Serge. I passed this step.
I am using Tonkuma example as a template to learn how to call SP from Udf:
--#SET TERMINATOR !
CREATE FUNCTION Calculate (inStr VARCHAR(100))
RETURNS TABLE (Result FLOAT)
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE Result FLOAT;
CALL Calculate(inStr , Result);
RETURN VALUES Result;
END!

--#SET TERMINATOR !
CREATE PROCEDURE Calculate (IN inStr VARCHAR(100),OU T Result FLOAT)
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN NOT ATOMIC
DECLARE CalcSelect VARCHAR(130) DEFAULT '';
DECLARE PrepSelect STATEMENT;
DECLARE C1 CURSOR FOR PrepSelect;
SET CalcSelect = 'SELECT '||COALESCE(inS tr,'CAST(NULL AS FLOAT)')
||' FROM SYSIBM.SYSDUMMY 1';
PREPARE PrepSelect FROM CalcSelect;
OPEN C1;
FETCH C1 INTO Result;
CLOSE C1;
END!

EXAMPLE OF USE:
SELECT deptno
, mgrno
, INT(C.Result) AS INT_Result
FROM Department D
, TABLE( Calculate(mgrno ||'*10+5' ) ) C;

My SP final:

CREATE PROCEDURE TAB_SEL_ANYPAGE (TNAME CHAR(20),PREDIC ATE VARCHAR(1000),N
INTEGER,PAGE_NO INTEGER,RESULT VARCHAR(2000))
MODIFIES SQL DATA
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN NOT ATOMIC
DECLARE SQL_CURS1 CHAR(200) ;
DECLARE STMT_CURS1 CHAR(200) ;
DECLARE CURS2 CURSOR WITH RETURN FOR STMT_CURS1 ;
SET SQL_CURS1 = 'SELECT * FROM '||TNAME ||
'WHERE ' || PREDICATE || ' ' || CHAR(N * (PAGE_NO - 1))
|| CASE WHEN N 0 THEN 'FETCH FIRST '|| CHAR(N) ||' ROWS ONLY'
WHEN N = 0 THEN ' '
ELSE
RAISE_ERROR ('77000', 'INVALID FETCH, VALID N - INTEGER 0')
END ;
PREPARE STMT_CURS1 FROM SQL_CURS1 ;
OPEN CURS2 ;
FETCH CURS2 INTO Result;
CLOSE CURS2;
END
DB20000I The SQL command completed successfully.

My UDF final:

CREATE FUNCTION SEL_ANYPAGE_SP (TNAME CHAR(20),PREDIC ATE VARCHAR(1000),N
INTEGER,PAGE_NO INTEGER)
RETURNS TABLE (RESULT VARCHAR(2000))
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE RESULT VARCHAR(2000);
CALL TAB_SEL_ANYPAGE (TNAME,PREDICAT E,N,PAGE_NO,RES ULT);
RETURN VALUES RESULT;
END!
DB20000I The SQL command completed successfully.

But when i test it is not working:
select 1 FROM SYSIBM.SYSDUMMY 1,
TABLE(SEL_ANYPA GE_SP('QUERY_LO G','INVOICE#' ,12,2)) TP;

SQL0440N No authorized routine named "SEL_ANYPAGE_SP " of type "FUNCTION "
having compatible argument.

Is is possible in DB2 to retrieve a table fetched in SP thru UDF??
Thank's In advance Leny G.

P.S I learned alot on this board and very happy that i find it.

Serge Rielau wrote:

>Hmm...
>--#SET TERMINATOR !
>vs
>--#SET TERMINATOR!
>
>I think DB2 didn't recognize your terminator directive.
>
>Chances are you actually got two errors because DB2 thinks you gave it
>two statements. CREATE ...; and RETURN...;
>
>Cheers
>Serge
>

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200807/1

**Serge Rielau** · Jul 25 '08, 01:45 AM

Re: calling SP from UDF

lenygold via DBMonster.com wrote:

Thank you Serge. I passed this step.
I am using Tonkuma example as a template to learn how to call SP from Udf:
--#SET TERMINATOR !
CREATE FUNCTION Calculate (inStr VARCHAR(100))
RETURNS TABLE (Result FLOAT)
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE Result FLOAT;
CALL Calculate(inStr , Result);
RETURN VALUES Result;
END!
>
--#SET TERMINATOR !
CREATE PROCEDURE Calculate (IN inStr VARCHAR(100),OU T Result FLOAT)
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN NOT ATOMIC
DECLARE CalcSelect VARCHAR(130) DEFAULT '';
DECLARE PrepSelect STATEMENT;
DECLARE C1 CURSOR FOR PrepSelect;
SET CalcSelect = 'SELECT '||COALESCE(inS tr,'CAST(NULL AS FLOAT)')
||' FROM SYSIBM.SYSDUMMY 1';
PREPARE PrepSelect FROM CalcSelect;
OPEN C1;
FETCH C1 INTO Result;
CLOSE C1;
END!
>
EXAMPLE OF USE:
SELECT deptno
, mgrno
, INT(C.Result) AS INT_Result
FROM Department D
, TABLE( Calculate(mgrno ||'*10+5' ) ) C;
>
>
>
My SP final:
>
CREATE PROCEDURE TAB_SEL_ANYPAGE (TNAME CHAR(20),PREDIC ATE VARCHAR(1000),N
INTEGER,PAGE_NO INTEGER,RESULT VARCHAR(2000))
MODIFIES SQL DATA
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN NOT ATOMIC
DECLARE SQL_CURS1 CHAR(200) ;
DECLARE STMT_CURS1 CHAR(200) ;
DECLARE CURS2 CURSOR WITH RETURN FOR STMT_CURS1 ;
SET SQL_CURS1 = 'SELECT * FROM '||TNAME ||
'WHERE ' || PREDICATE || ' ' || CHAR(N * (PAGE_NO - 1))
|| CASE WHEN N 0 THEN 'FETCH FIRST '|| CHAR(N) ||' ROWS ONLY'
WHEN N = 0 THEN ' '
ELSE
RAISE_ERROR ('77000', 'INVALID FETCH, VALID N - INTEGER 0')
END ;
PREPARE STMT_CURS1 FROM SQL_CURS1 ;
OPEN CURS2 ;
FETCH CURS2 INTO Result;
CLOSE CURS2;
END
DB20000I The SQL command completed successfully.
>
My UDF final:
>
CREATE FUNCTION SEL_ANYPAGE_SP (TNAME CHAR(20),PREDIC ATE VARCHAR(1000),N
INTEGER,PAGE_NO INTEGER)
RETURNS TABLE (RESULT VARCHAR(2000))
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE RESULT VARCHAR(2000);
CALL TAB_SEL_ANYPAGE (TNAME,PREDICAT E,N,PAGE_NO,RES ULT);
RETURN VALUES RESULT;
END!
DB20000I The SQL command completed successfully.
>
But when i test it is not working:
select 1 FROM SYSIBM.SYSDUMMY 1,
TABLE(SEL_ANYPA GE_SP('QUERY_LO G','INVOICE#' ,12,2)) TP;
>
SQL0440N No authorized routine named "SEL_ANYPAGE_SP " of type "FUNCTION "
having compatible argument.

The first parameter is a CHAR, while the first argument is a VARCHAR.
VARCHAR is higher in the promotion chain than CHAR. So DB2 cannot see
the function. In general I avoid using CHAR and SMALLINT as function
parameters for that reason because literals are VARCHAR/INTEGER.

Is is possible in DB2 to retrieve a table fetched in SP thru UDF??

Not with SQL Functions.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**lenygold via DBMonster.com** · Jul 25 '08, 02:45 AM

Re: calling SP from UDF

Hi Serge.
I replaced Char with Varchar and it helped. But i got another error:
SELECT 1 FROM SYSIBM.SYSDUMMY 1,
TABLE(SEL_ANYPA GE_SP('QUERY_LO G','INVOICE#',1 2,2)) TP;
sqlcode: -20267

sqlstate: 429BL
the function "SEL_ANYPAGE_SP " (specific "SQL08072422311 8400") modifies SQL
data and is invoked in an illegal context. Reason code = "3
".

3. The table function is preceded by a table reference which is not
referenced by a function argument.
Thank's again for your help.

Serge Rielau wrote:

>Thank you Serge. I passed this step.
>I am using Tonkuma example as a template to learn how to call SP from Udf:

>[quoted text clipped - 81 lines]

>SQL0440N No authorized routine named "SEL_ANYPAGE_SP " of type "FUNCTION "
>having compatible argument.

>The first parameter is a CHAR, while the first argument is a VARCHAR.
>VARCHAR is higher in the promotion chain than CHAR. So DB2 cannot see
>the function. In general I avoid using CHAR and SMALLINT as function
>parameters for that reason because literals are VARCHAR/INTEGER.
>

>Is is possible in DB2 to retrieve a table fetched in SP thru UDF??

>Not with SQL Functions.
>
>Cheers
>Serge

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200807/1

**Serge Rielau** · Jul 26 '08, 12:15 PM

Re: calling SP from UDF

lenygold via DBMonster.com wrote:

Thank's Serge for promt reply.
>
Does it mean if have SP like this:
CALL TAB_SEL_ANYPAGE ('QUERY_LOG','I NVOICE#' ,12,2);
>
INVOICE# CREATE_DATE QUERY_ID
----------- ----------- ----------
13 05/10/2008 ALL01 13
14 05/10/2008 ALL01 14
15 05/10/2008 ALL01 15
16 05/10/2008 ALL01 16
17 05/10/2008 ALL01 17
18 05/10/2008 ALL01 18
19 05/10/2008 ALL01 19
20 05/10/2008 ALL01 20
21 05/10/2008 ALL01 21
22 05/10/2008 ALL01 22
23 05/10/2008 ALL01 23
24 05/10/2008 ALL01 24
I can't pass it otput in application programm using UDF and db2 function
TABLE.
This SP is LANGUAGE SDL.
I would like to avoid writing SP LANGUAGE COBOL.
So i have a working SP, and i have UDF. How to combine them like
TONKUMA did it his example?

Exactly. The result set is a side-effect. You cannot pick it up past teh
SQL or within an SQL Function. The later is a restriction. Teh first by
design.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

calling SP from UDF

calling SP from UDF

Comment

Comment

Comment

Comment

Comment

Comment

Comment