Moving to SQL (from native file IO) has been a major game changer in the RPG programming world. SQL can be confusing to learn, horrible to use if it needs lots of compile time parameters but using Exec SQL SET OPTION something answers my prayers.
I first played with SQL back the golden days of RPG3 and RPG400 when we were all wrapped up in using Commitment Control. Journalling files and making changes to data in our RPGSQL programs before issuing a COMMIT or ROLLBACK was exciting and new. Roll forward a few years and database access times have massively increased and the entire concept of commitment control is not something we focus on so much in the RPG programming world.
Because of this history, in modern RPG4 SQL programs, we still have to tell the program not to use commitment if we don’t want to use it. This also means you can use embedded SQL in RPG4 programs without journaling your files.
The easiest way is to enter the create parameter of COMMIT(*NONE) thing so it turns it off after the first commit without the isolation level nonsense.
This is my preferred technique. Adding a line of code to the program and then know that you never need to specify additional compilation parameters makes me sleep easy at night. Just specify COMMIT *NONE in the compile command or add a SET OPTION statement at the beginning of the program:
C/EXEC SQL SET OPTION COMMIT = *NONE C/END-EXEC
Exec SQL SET OPTION COMMIT = *NONE;
Easy Peasy, Lemon Squeezy.
Now we can get quite fancy with SQL SET OPTION and use something like this:
EXEC SQL Set Option Naming = *Sys, Commit = *None, UsrPrf = *Owner, DynUsrPrf = *Owner, CloSqlCsr = *EndMod;
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
Simple email validation SQL RPG ILE program
Cleaning messy IBM i Integrated File System (IFS) file names
How to encrypt or hide CL/RPG Source Code in ILE Debug Views
‘device file does not contain an entry for screen size’
Set off all RPG Indicators in a program
DSPLY Sucks. QUILNGTX Rocks.