ILE RPG and SQL are both powerful and useful languages. I’m sure many of you use one or both of these languages, but did you know that they can be used together? By using the SQL precompiler—the Create Structured Query Language ILE RPG Object (CRTSQLRPGI) command—you can embed SQL statements right in your RPG source. You can also call your RPG source from an SQL statement by creating SQL external functions and procedures. This blog will focus on embedding SQL statements in your RPG source.
SQL statements can be written in both fixed and free format RPG. When you compile your source, you use the CRTSQLRPGI command. The precompiler finds the SQL statements and converts them to RPG source code. The precompiler generates a new source member, which is used as input to the RPG compiler.
In fixed format, the SQL statement starts with EXEC SQL and ends with END-EXEC.
C/EXEC SQL UPDATE DEPARTMENT
C+ SET MGRNO = :MGR_NUM
C+ WHERE DEPTNO = :INT_DEP
C/END-EXEC
In free format, the SQL statement starts with EXEC SQL and ends with a semicolon.
EXEC SQL UPDATE DEPARTMENT SET MGRNO = :MGR_NUM
WHERE DEPTNO = :INT_DEP;
The free format support was added in IBM i 5.3. Since the same precompiler gets called no matter what value is specified on the target release parameter (TGTRLS), SQL statements in free format will be allowed with any valid value specified on the TGTRLS parameter.
RPG has the control-specification (H-Spec) that tells the compiler how to deal with devices and how certain types of information will be represented, as well as including keywords that will override parameters specified on the command. The SQL precompiler has a similar feature—the SQL SET OPTION statement.
EXEC SQL SET OPTION
NAMING = *SQL,
CLOSQLCSR = *ENDMOD,
COMMIT = *NONE,
DATFMT = *ISO,
COMPILEOPT = 'DBGVIEW(*LIST)';
The SET OPTION statement must be the first SQL statement in the source. I really like this statement since it lets me not have to remember to specify certain parameters on the CRTSQLRPGI command. But the coolest part of the statement is that you can specify compiler parameters that aren’t allowed in the H spec. Yeah, you read that right. You just need to specify the parameters in the COMPILEOPT string. This string is added to the compiler command that’s built by the precompiler. The only thing you need to remember is to not use any of the parameters that the precompiler already passes to the compiler. This will cause the compiler command to fail because of duplicate parameters. There’s also a compiler options (COMPILEOPT) parameter on the CRTSQLRPGI command. COMPILEOPT was added in IBM i 5.3 and is allowed to be specified with any valid value specified on the TGTRLS parameter.
Speaking of parameters, the RPG preprocessor options (RPGPPOPT) parameter on the CRTSQLRPGI command is one that you really want to know about. The precompiler only handles the /COPY directive and is restricted to only one level, so you can’t have nested /COPY. Since the other directives are not handled, the precompiler scans the source that’s in both the /IF and /ELSE. Specifying *LVL1 or *LVL2 on the RPGPPOPT parameter will tell the precompiler to call the compiler to have it preprocess the source. The compiler will handle all the directives and generate a new source member for the precompiler to use as input. The only difference between *LVL1 and *LVL2 is that *LVL1 doesn’t expand the /INCLUDE directive. The /INCLUDE directive was originally created as a way to hide code from the precompiler. The RPGPPOPT parameter is valid on IBM i 5.3, but not for previous releases.
The precompiler now has variable scoping. This enhancement is in the base of IBM i 6.1 and PTFed in IBM i 5.4 (product 5722SS1: SI34160 and SI34161 and product 5722ST1: SI34384 or database group PTF SF99504, #20). Before this support was added, all variables in the entire source needed to be unique to be used by SQL. Now the precompiler recognizes procedures and scopes the variables to the procedure they’re defined in.
In IBM i 6.1, the precompiler added the support to take an IFS source stream file as input. The source stream file (SRCSTMF) parameter is used to specify the path name of the IFS source stream file to be compiled. The SQL include directory (INCDIR) parameter is used for the SQL include statement. If you want to have an INCDIR parameter on the compiler command, you’ll need to use the COMPILEOPT parameter. Another thing to remember when your source is in IFS is that the precompiler won’t expand /COPY statements. If you have SQL statements in your /COPY files, you’ll need to specify a value other than *NONE on the RPGPPOPT parameter. One benefit of having your source in IFS is that it gives you more flexibility with your source code management than the traditional file system.
Other IBM i 6.1 enhancements to the SQL precompiler include: the capability to do a LIKE on the SQLCA variables, increased LOB variable limits and the new file enhancements (EXTNAME enhancement, LIKEFILE, EXTDESC and QUALIFIED).
Using the SQL precompiler lets you bring two powerful languages together. As you can see, there have been a lot of enhancements to make it even easier. Here’s a quick review:
| Enhancement | Release | Can TGTRLS(*PRV) be specified? |
| SQL in free format | 5.3 | Yes |
| COMPILEOPT | 5.3 | Yes |
| RPGPPOPT | 5.3 | No |
| Variable scoping | 5.4 | Yes |
| Source in IFS | 6.1 | Yes |
| LIKE on the SQLCA variables | 6.1 | Yes |
| Increased LOB variable limit | 6.1 | No |
| New file enhancements | 6.1 | No |
Two great places to go for help with embedding SQL in RPG are the RPG Café and the RPG400-L mailing list. The RPG Cafe is the place for RPG developers and IBM developers to mingle and discuss RPG. Subscribing to RPG400-L lets you use the list to e-mail other RPG developers. The e-mails are archived so you have the capability to search past posts.
There is also a precompiler manual, Embedded SQL programming, in the IBM i Knowledge center.
Gina Whitney wrote this week’s blog. Gina is a member of the SQL development team for Db2 for IBM i in Rochester, Minn. She’s responsible for the SQL precompilers. Thanks, Gina!
This blog post was edited to update links on January 26, 2020.
This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.