Cursor for update in fixed list select dynamic sql

manjuraveendran · New User Joined: 09 Feb 2007 Posts: 2 Location: ernakulam

Hi all,

Can we use cursor for update in fixed list select dynamic sql?
thanks
Manju

dick scherrer · Posted: Mon Aug 13, 2007 9:17 pm

Hello,

Please post example code of what you are askiing about.

manjuraveendran · New User Joined: 09 Feb 2007 Posts: 2 Location: ernakulam

Hai my code is as follows,

In this code EMP is the table name,from which am trying to select the salary field for updation .

PROCEDURE DIVISION.
P1.
MOVE "SELECT SAL FROM EMP WHERE NAME = ? FOR UPDATE OF SAL"
TO X-TEXT.
MOVE 70 TO X-LEN.
EXEC SQL DECLARE CURSOR1 CURSOR FOR OB1
END-EXEC.
EXEC SQL PREPARE OB1 FROM :X END-EXEC.
MOVE "REN" TO NAME-TEXT.
MOVE 3 TO NAME-LEN.
EXEC SQL OPEN CURSOR1 USING :NAME END-EXEC.
PERFORM P2 UNTIL SQLCODE = 100.
EXEC SQL CLOSE CURSOR1 END-EXEC.
STOP RUN.
P2.
EXEC SQL FETCH CURSOR1 INTO :SAL END-EXEC.
IF SQLCODE = 0
COMPUTE SAL = SAL + 20
EXEC SQL UPDATE EMP SET SAL = :SAL WHERE CURRENT OF
CURSOR1 END-EXEC
DISPLAY "UPDATED"
ELSE
DISPLAY "NOT UPDATED".

Thanks
Manju

dbzTHEdinosauer · Posted: Tue Aug 14, 2007 4:30 pm

have you tried executing this?

if not and this is just a general question, I suggest that you go check out the manual.

this looks like a mish-mash of dynamic sql and imbedded sql. doubt if you could get it preprocessed or bound.

This is the v7 appl guide publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/FRAMESET/DSNAPH13/CCONTENTS?DT=20030508165437
and the v7 sql reference publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/CCONTENTS?DT=20010718164132

here is the v8 and v9 documentation: publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.inst/bjnnmstr392.htm