Instead of CREATE TABLE QTEMP.X ... you should use DECLARE GLOBAL TEMPORARY TABLE X.
If a temporary table is generated with DECLARE GLOBAL TEMPORARY TABLE, it is possible to INSERT, UPDATE, DELETE rows without specifying WITH NC.
... and DECLARE GLOBAL TEMPORARY TABLE, INSERT, UPDATE and DELETE are executed under commitment control, i.e. ROLLBACK will reset all modification within this table since the last COMMIT.
GET_CLOB_FROM_FILE must be executed under commitment control, so you NEED to add an SET OPTION Statement with COMMIT=*CHG.
Since you are working under commitment control now, you should execute COMMIT before you leave the program!
Attention:
If you do not start commitment control manually in your job (i.e. execute the STRCMTCTL command before calling your program), commitment control is started automatically (i.e. STRCMTCTL with default options is executed automatically)
Keep in mind the default option for commitment scope is *ACTGRP, i.e. commit and rollback work only within the same activation group (when using multiple activation groups a rollback may not reset everything as you expected!)
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!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Booth Martin
Sent: Sonntag, 15. Dezember 2019 04:29
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: SQLRPGLE commit=^none and commit=*chg
Program has 2 sql steps; first, create a file in QTEMP then later a json_table(get_CLOB_from_file). One fails if option is not commit=*none, the other fails if option commit=*none. I can not figure out how to change the commit in between the two sql activities.
--
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
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
