.st0{fill:#FFFFFF;}

SQL RPG and the annoying compile COMMIT *NONE thing 

 November 12, 2013

By  NickLitten

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.

COMPILE TIME PARAMETER

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.

SQL SET OPTION

USE RPG CODE

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:

RPG3/400

C/EXEC SQL SET OPTION COMMIT = *NONE
C/END-EXEC

RPGLE:

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;

 

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad 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 remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

  • My create table was in a subroutine. Adding the commit=*none wouldn’t allow a compile. I finally found your website where the *none belongs at the beginning of the program. Worked like a charm. Thanks for your help!

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

    Get In Touch

    I’m always looking for awesome input, feedback and critique!

    >