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
***********************************************
