RE: Question on embedded SQL in RPG with Update

I needed a COMMIT(*NONE)


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


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Scott Klement
Sent: Monday, March 23, 2009 10:49 AM
To: RPG programming on the IBM i / System i
Subject: 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

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.