SQL Stored Procedure Question

[Eric Herber]

I've a question regarding DB2 SQL Stored Procedures
(DB2 V8.1.4 on Suse Linux).

It seems not to be possible to use the 'where current of <cursor>'
syntax in conjunction with dynamically prepared SQL statements.

When I execute the simple stored procedure (sourcecode see below),
DB2 raises the following error message at the 2nd PREPARE statement:

SQL0504N The cursor "C_STAFF" is not defined. SQLSTATE=34000

The stored procedure provided below is only an example to reproduce
the error. The real stored procedure needs dynamic SQL, so changing
to static SQL is not a solution.

I did not find anything in the documentation about that. To be honest,
the DB2 documentation is horrible, at least regarding SQL stored
procedures.

Any input is greatly appreciated.
Thank you.


CREATE PROCEDURE tmp3()

SPECIFIC tmp3
LANGUAGE SQL

t3: BEGIN

DECLARE v_sql VARCHAR(256);
DECLARE v_id SMALLINT;
DECLARE v_name VARCHAR(9);
DECLARE v_not_found SMALLINT DEFAULT 0;
DECLARE v_stmt1 STATEMENT;
DECLARE v_stmt2 STATEMENT;
DECLARE c_staff CURSOR FOR v_stmt1;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_not_found = 1;

SET v_sql = 'SELECT id,name FROM db2inst1.staff FOR UPDATE';
PREPARE v_stmt1 FROM v_sql;

SET v_sql = 'DELETE FROM db2inst1.staff WHERE CURRENT OF c_staff';
--The next PREPARE raises SQL error SQL0504N
PREPARE v_stmt2 FROM v_sql;

OPEN c_staff;
FETCH FROM c_staff INTO v_id, v_name;

WHILE (v_not_found = 0)
DO

EXECUTE v_stmt2;
FETCH FROM c_staff INTO v_id, v_name;

END WHILE;

END t3@



--
IT-Consulting Herber
Email: <mailto:eric@he rber-consulting.de>
Mobile: +49 177 2276895
*************** *************** *************** **
Download the IFMX Database-Monitor for free at:

http://www.herber-consulting.de/BusyBee

*************** *************** *************** **

[Serge Rielau]

Re: SQL Stored Procedure Question

I'm guessing: Try the prepare after the open.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Eric Herber]

Re: SQL Stored Procedure Question

I already tried this, but it doesn't help.
The error message is the same.
Any other ideas ?

Thank you.

Serge Rielau wrote:[color=blue]
> I'm guessing: Try the prepare after the open.
>
> Cheers
> Serge[/color]

--
IT-Consulting Herber
Email: <mailto:eric@he rber-consulting.de>
Mobile: +49 177 2276895
*************** *************** *************** **
Download the IFMX Database-Monitor for free at:

http://www.herber-consulting.de/BusyBee

*************** *************** *************** **

[andreyp#Antispam@mapsitnA#it4profit.com]

Re: SQL Stored Procedure Question

I can't say about version 8, but in ver 7.2 sql-sp converter into C not
used declared cursor name. It's simply assign names CURSx, where x started
from 2.
In you case try change to '... CURRENT OF CURS2'

Andy

[Eric Herber]

Re: SQL Stored Procedure Question

Yes, indeed I checked the generated Esql/C File (*.sqc)
and the cursor is named 'curs4'.

If I use that cursor name, it works.
However I'm not sure how reliable this method is and
from my point of view the stored procedure behaviour
should be considered a bug.

Anyway, thank you very much for this hint.


andreyp#Antispa m@mapsitnA#it4p rofit.com wrote:[color=blue]
> I can't say about version 8, but in ver 7.2 sql-sp converter into C not
> used declared cursor name. It's simply assign names CURSx, where x started
> from 2.
> In you case try change to '... CURRENT OF CURS2'
>
> Andy[/color]

--
IT-Consulting Herber
Mobile: +49 177 2276895
*************** *************** *************** **
Download the IFMX Database-Monitor for free at:

http://www.herber-consulting.de/BusyBee

*************** *************** *************** **

[Serge Rielau]

Re: SQL Stored Procedure Question

Eric,

I forwarded your example to the SQL PL developers.
The way it works is definitely not the way it should work.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab