Re: SQL Embedded in RPG with Commitment Control -- RPG400-L

Well, it looked good when I typed the E-Mail. Let’s try the formatting again.

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 McNierney_Chris via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>
Date: Friday, June 30, 2023 at 9:15 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>, Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: McNierney_Chris <Chris.McNierney@xxxxxxxx>
Subject: Re: SQL Embedded in RPG with Commitment Control
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><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><https://archive.midrange.com/rpg400-l<https://archive.midrange.com/rpg400-l>>.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
--
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.