By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,223 Members | 1,371 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,223 IT Pros & Developers. It's quick & easy.

SQL Stored Procedure Question

P: n/a
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:er**@herber-consulting.de>
Mobile: +49 177 2276895
***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
I'm guessing: Try the prepare after the open.

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

Nov 12 '05 #2

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

Thank you.

Serge Rielau wrote:
I'm guessing: Try the prepare after the open.

Cheers
Serge


--
IT-Consulting Herber
Email: <mailto:er**@herber-consulting.de>
Mobile: +49 177 2276895
***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************
Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
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#Antispam@mapsitnA#it4profit.com wrote:
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


--
IT-Consulting Herber
Mobile: +49 177 2276895
***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************
Nov 12 '05 #5

P: n/a
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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.