Re: Question on embedded SQL in RPG with Update

Hi Tom,

On your declare cursor/select statement, you need "for update of XXXPY70P"... for example:

C/EXEC sql
C+ DECLARE A CURSOR FOR
C+ SELECT *
C+ FROM XXXPY70P
C+ WHERE SUBSTR(DEDSC1, 68, 3) > ' '
C+ AND SUBSTR(DEDSC1, 70, 1) <> 'N'
C+ FOR UPDATE OF XXXPY70P
C/end-exec

And on the update, you need to use the filename -- NOT the record format name. (You never use the record format name in SQL)

C/exec sql
C+ update xxxpy70p
C+ set dedsc1= substr(dedsc1,1,67)
C+ WHERE CURRENT OF A
C/end-exec

Though, I wonder if you really need to declare a cursor and all of that. Do you need the fields in your RPG logic? If not, you can just do this:

C/exec sql
C+ update xxxpy70p
C+ set dedsc1= substr(dedsc1,1,67)
C+ WHERE SUBSTR(DEDSC1, 68, 3) > ' '
C+ AND SUBSTR(DEDSC1, 70, 1) <> 'N'
C/end-exec

Good luck


Tom Deskevich wrote:

I am trying to do an update while in a loop with embedded SQL in RPG.

When I enable the update, SQLSTATE returns 24501 and nothing seems to be put
in the buffer.

When disable the update, it runs fine.

I cannot find any examples of updating using embedded SQL.
I have the file format referenced in the UPDATE. I had the name of the
physical file.
Worked the same. I also tried using the name of the external data structure,
same results.

Here is my code:

FQsysprt O F 120 PRINTER OFLIND(*InOF) DPRODOCKE E DS EXTNAME(XXXPY70P) C/EXEC sql C+ DECLARE A CURSOR FOR C+ SELECT * C+ FROM XXXPY70P C+ WHERE SUBSTR(DEDSC1, 68, 3) > ' ' C+ AND SUBSTR(DEDSC1, 70, 1) <> 'N' C/end-exec C/EXEC sql C+ OPEN A C/end-exec C SQLSTATE Downe '02000' C/EXEC sql C+ FETCH A INTO :PRODOCKE C/end-exec C If sqlstate='02000' C leave C Endif C/exec sql C+ update xxxpy70f <<< when enabled, no recs
end up in buffer C+ set dedsc1= substr(dedsc1,1,67) CONCAT ' ' <<< disabled, prints all
recs that qualify
C+ WHERE CURRENT OF A C/end-exec C** except print C Enddo C/EXEC sql C+ CLOSE A C/end-exec C eval *inlr=*on OQsysprt E print 2 O deccy


Tom Deskevich
Infocon Corporation
Phone 814-472-6066
Fax 814-472-5019