For many releases of IBM i we have been able to put keyword in the H-spec/Control options that are the same as the parameters in RPG's compile options, see here. The same is also available in SQL for RPG objects with embedded SQL, SQLRPGLE.
I have given some examples in the past of using the SET OPTION statement in SQL to turn off commitment control. There are a multitude of other options that mirror options in the SQLRPGLE compile commands. I am not going to list all of the options in this post as IBM provides them all on their web site, there is a link at the bottom of this article to the page. I am only going to show what I think are the most useful.
Below are a few of them I added to a program I created:
exec sql SET OPTION COMMIT = *NONE, NAMING = *SQL, CLOSQLCSR = *ENDMOD, DATFMT = *USA, TIMFMT = *USA ;
Notice how each one is separated by a comma (,).
- COMMIT = *NONE - Turn off commitment control
- NAMING = *SQL - The SQL naming conventions will be used
- CLOSQLCSR = *ENDMOD - SQL cursor is closed when the module completes
- DATFMT = *USA - The format of the date used for output and on input to determine if the date is valid
- TIMFMT = *USA - The format of the time
There is an additional option that can only be used in SQL functions, procedures and triggers: DBGVIEW. If you add this to a program it will not compile giving you the following message, which in my opinion tells you that DBGVIEW is valid.
SQL0104 30 10 Position 30 Token DBGVIEW was not valid. Valid tokens: COMMIT NAMING DECMPT DATFMT TIMFMT DATSEP TIMSEP SRTSEQ LANGID DFTRDBCOL RDBCNNMTH ALWBLK ALWCPYDTA DLYPRP DYNUSRPRF USRPRF CLOSQLCSR OPTLOB DYNDFTCOL SQLPATH SQLCURRULE COMPILEOPT DECRESULT CNULRQD CNULIGN OUTPUT DBGVIEW TGTRLS EVENTF.
I have found that I need to wary of differing date formats in the H-spec/Control options and in the SQL Set options. For example:
01 ctl-opt datfmt(*jul) ; 02 dcl-s wkDate date ; 03 exec sql SET OPTION DATFMT = *USA ; 04 exec sql SET :wkDate = CURDATE() ; 05 *inlr = *on ;
In the Control options, line 1, I have set the default Date format to Julian (YYDDD), DATFMT(*JUL).
I have defined the variable wkDate as a date variable, but without a date format keyword, therefore, it will use the format specified in the Control options.
In the Set options, line 3, I have chosen the Date format to be in USA format (MMDDYYYY), DATFMT = *USA.
On line 4 I move, in SQL, the Current date into the variable wkDate.
I added a debug break point at line 5. When I display the value of wkDate I get the date in Julian format: WKDATE = '15/201'
I recommend that you consider adding the SQL Set options to all of your SQLRPGLE programs. You might want to put it in its own source file and then use /COPY to include it into your other source members.
..... *. 1 ...+... 2 ...+... 3 ...+... 4 /copy mylib/mysrc,sqlsetopts
I know that /COPY and /INCLUDE are suppose to function the same way, but I could not get my test SQLRPGLE program to compile with the /INCOUDE, when it would with the /COPY. I also had to place the slash (/) in the seventh position of the source member, as I had to do in fixed format RPG . In free format RPG members, not SQLRPGLE, you can place the slash in any place on the line greater than the seventh position.
September 11, 2015
Make sure to read the comments posted by Birgitta Hauser here.
You can learn more about the Set options available on the IBM web site here.
This article was written for IBM i 7.2, and should work for earlier releases too.