AW: Question on embedded SQL in RPG with Update

Hi,

I don't think FOR UPDATE OF is really necessary in your example. FOR UPDATE
OF is only mandatory if the SQL-Statement is not updateable (for example if
an ORDER BY Clause is used).

Please check the following:
1. Is your file registered in a journal?
2. If it is not registered make sure your program was compiled with compile
option COMMIT *NONE (or that your member contains a SET OPTION Statement
with Commit = *NONE).

I assume your file is not journaled, but you compile your program with
COMMIT *CHG (which is the default value).

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Tom Deskevich
Gesendet: Monday, 23. March 2009 17:57
An: RPG programming on the IBM i / System i
Betreff: RE: Question on embedded SQL in RPG with Update

yes, it does contain that field.

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 Pat Landrum
Sent: Monday, March 23, 2009 10:43 AM
To: RPG programming on the IBM i / System i
Subject: RE: Question on embedded SQL in RPG with Update


Tom,

Your select references file XXXPY70P, where the update references file
xxxpy70f . Does
File xxxpy70f contain a field dedsc1? If not, this is why your update is
not working.

Regards,
Pat Landrum
Senior Programmer/Analyst
Hanover County Public Schools
200 Berkley Street
Ashland, VA 23005
Email: plandrum@xxxxxxx
Phone: 804-365-4658 Fax: 804-365-4628
Never trust a computer you can't throw out a window - Steve Wozniak

Notice: This message or any accompanying documents may contain
confidential or privileged information of Hanover County Public Schools.
If you are not the intended recipient, disclosure, copying or
distribution is strictly prohibited by state and federal law. If you
received this message in error, please notify the sender as soon as
possible.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom Deskevich
Sent: Monday, March 23, 2009 11:58 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Question on embedded SQL in RPG with Update

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