Re: Question on embedded SQL in RPG with Update

Here is some example code. I don't think you are telling SQL what you want
to update. See line 6.

Exec Sql Declare C2 Cursor For
Select STREC#
From STOPOFF
Where STORD# = :InTripNumber
Order By STSEQ#
For Update Of STREC#;

Exec Sql Open C2;
If SqlState <> cSqlStateOk;
SendMessageToAudit('Open of C2 failed! Sql State = ' + SqlState);
ERRH_Throw('CPF9898' :
cCPFMessageFileName :
'Open of Cursor C2 failed! Sql State = ' + SqlState);
EndIf;

Record# = cMaximumStopNumber - StopCount;
x = 0;
DoU 0 = 1;
Exec Sql Fetch Next
From C2
Into :RecordNumber;
Select;
When SqlState = cSqlStateNoRow;
Leave;
When SqlState <> cSqlStateOk;
SendMessageToAudit('Fetch failed C2! Sql State = ' + SqlState);
ERRH_Throw('CPF9898' :
cCPFMessageFileName :
'Fetch From C2 Failed! Sql State = ' + SqlState);
Other;
x += 1;
Record# += 1;
Exec Sql Update STOPOFF
Set STREC# = :Record#
Where Current Of C2;
Select;
When SqlState = cSqlStateNoRow;
When SqlState <> cSqlStateOk;
ERRH_Throw('CPF9898' :
cCPFMessageFileName :
'Update of C2 Failed! Sql State = ' + SqlState);
EndSl;
dsStopArray(x).NewRecord# = Record#;
EndSl;
EndDo;


On Mon, Mar 23, 2009 at 9:58 AM, Tom Deskevich <
thomas.l.deskevich@xxxxxxxxxxxxx> 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.