This is an example of a SQL cursor loop. Transaction control would be put around update/insert actions, if desired. In order to use transaction control, your tables must be journaled.
Exec SQL
Declare ${Cursor_Name} Cursor With Hold For
Select Day
From TradMrgnP
Where TradNo = :cTradNo
Order by TradNo
Fetch First 10 Rows Only
For Read Only
Optimize For All Rows;
Exec SQL Open ${Cursor_Name};
If SqlState = '24502';
Exec SQL Close ${Cursor_Name};
Exec SQL Open ${Cursor_Name};
EndIf;
If SqlState = '00000';
Exec SQL Fetch Next From ${Cursor_Name} Into :mRec;
DoW SqlState >= '00000' and SqlState < '02000';
If SqlState <> '00000';
LogParms.PgmAction = '${Cursor_Name} Record processed: ' + mRec;
ExSR LogSqlErr;
EndIf;
Exec SQL Fetch Next From ${Cursor_Name} Into :mRec;
EndDo;
If SqlState > '02000';
LogParms.PgmAction = 'Fetch error on cursor ${Cursor_Name}';
ExSR LogSqlErr;
EndIf;
Else;
LogParms.PgmAction = 'Could not open cursor ${Cursor_Name}';
ExSR LogSqlErr;
EndIf;
Exec SQL Close ${Cursor_Name};
Chris
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Mohan Eashver <mohankva@xxxxxxxxx>
Date: Friday, June 30, 2023 at 8:12 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>, RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL Embedded in RPG with Commitment Control
Happy Thursday to you
I am looking for a complete code sample of an RPG that embed SQL and uses
Commitment control along with Open Fetch and Close of the Cursor.
I do understand the basic concept of Open Fetch and Close of Cursors. But I
never had to code SQLRPGLE for over 20 years.
----
Regards,
Mohan Eashver
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l<
https://lists.midrange.com/mailman/listinfo/rpg400-l>
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/rpg400-l<
https://archive.midrange.com/rpg400-l>.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
