calling SP from UDF

lenygold via DBMonster.com

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),PREDICATE 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','INVOICE#' ,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),PREDICATE 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),PREDICATE 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?

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

Jul 23 '08 #1

Subscribe Post Reply

2966

Serge Rielau

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),PREDICATE 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','INVOICE#' ,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),PREDICATE 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),PREDICATE 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),PREDICATE
VARCHAR(1000),N
db2 (cont.) =INTEGER,PAGE_NO 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,PREDICATE,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

Jul 24 '08 #2

lenygold via DBMonster.com

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),PREDICATE 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,PREDICATE,N,PAGE_NO,RESULT);
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),PREDICATE
VARCHAR(1000),N
db2 (cont.) =INTEGER,PAGE_NO 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,PREDICATE,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....m-db2/200807/1

Jul 24 '08 #3

Serge Rielau

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

Jul 24 '08 #4

lenygold via DBMonster.com

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),OUT 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(inStr,'CAST(NULL AS FLOAT)')
||' FROM SYSIBM.SYSDUMMY1';
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),PREDICATE 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),PREDICATE 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,PREDICATE,N,PAGE_NO,RESULT);
RETURN VALUES RESULT;
END!
DB20000I The SQL command completed successfully.

But when i test it is not working:
select 1 FROM SYSIBM.SYSDUMMY1,
TABLE(SEL_ANYPAGE_SP('QUERY_LOG','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....m-db2/200807/1

Jul 24 '08 #5

Serge Rielau

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),OUT 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(inStr,'CAST(NULL AS FLOAT)')
||' FROM SYSIBM.SYSDUMMY1';
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),PREDICATE 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),PREDICATE 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,PREDICATE,N,PAGE_NO,RESULT);
RETURN VALUES RESULT;
END!
DB20000I The SQL command completed successfully.

But when i test it is not working:
select 1 FROM SYSIBM.SYSDUMMY1,
TABLE(SEL_ANYPAGE_SP('QUERY_LOG','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

Jul 25 '08 #6

lenygold via DBMonster.com

Hi Serge.
I replaced Char with Varchar and it helped. But i got another error:
SELECT 1 FROM SYSIBM.SYSDUMMY1,
TABLE(SEL_ANYPAGE_SP('QUERY_LOG','INVOICE#',12,2)) TP;
sqlcode: -20267

sqlstate: 429BL
the function "SEL_ANYPAGE_SP" (specific "SQL080724223118400") 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....m-db2/200807/1

Jul 25 '08 #7

Serge Rielau

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','INVOICE#' ,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

Jul 26 '08 #8

Similar topics