Embedding SQL in RPG

Sometimes, getting a desired task done with the standard database API calls can be extremely tedious. That possibly involves creating intermediate results in a temporary storage location that will add up to complexity.

In such situations, utilizing the flexibility of SQL can speed up development considerably. This comes at a price, though:

• SQL is inherently slower than the standard API calls,
• embedded SQL will be processed by a precompiler that creates a binary object which in turn is automatically linked with the other application modules in the final compilation stage. It creates (invisible) hooks and variables in the RPG source, you'll encounter only in the compiler log and while debugging. Compilation-debugging-cycles will be slower,
• the mentioned precompiler isn't very smart. The SQL statement blocks must come in proper order within the source. The precompiler seems not to be aware of subroutines and possibly functions, so you may need to move routines around and possibly create new ones just for getting stuff in proper order.

It is strongly advisable that you actually test your SQL query via the interactive SQL query tool STRSQL or remotely via ODBC.
When running your query, you may encounter a brief display that a temporary access path is being created. How to optimize tables so this step isn't necessary for each invocation of the SQL query itself will be subject of a separate article.

It is also important to notice that SQL-Invocations within a program retain state. So you can declare stuff in an earlier call and embed cursor fetches in a loop.

Example

Utilizing SQL for getting records back involves the following, sometimes optional steps. Especially the loop handling and how to recognize that there are no more records can be accomplished in multiple ways. See the PDF docs in the Weblinks section below.

Programmers on other Platforms may recognize certain similarities of the needed steps.

Prepare the SQL query

C/EXEC SQL DECLARE SLT1 CURSOR FOR
C+ SELECT FIELD1, FIELD2 FROM TABLE1
C+  ORDER BY FIELD1
C/END-EXEC

The cursor name (SLT1 in this example) should be very short. Longer names won't compile.

Execute the Query

C/EXEC SQL
C+ OPEN SLT1
C/END-EXEC

Fetch result(s)

This statement must be embedded in an RPG DO-loop. It maybe infinite, since the first SQL call forces a GOTO to a yet to be defined label in the RPGLE code.

C/EXEC SQL
C+ FETCH NEXT FROM SLT1 INTO :RCDEXP
C/END-EXEC

• Variables from RPGLE are available in SQL when prefixed with a colon, like :RCDEXP in this example.
• RCDEXP is a(n unqualified) data structure, defined in the D-section (global variables) of your code, to actually hold the data for every row fetched. The members of this DS must be of compatible types and same length as the fields in the queried database file(s).
  • You need to define a DS when fetching records with multiple fields. If you 're after a single field, this isn't necessary.

DRCDEXP           DS
DFIELD1                         20A
DFIELD2                         20A

Automatic variable creation

When you make changes to the underlying PF, you need to manually reflect that in your DS. This is error-prone and tedious, since you need to remember this fact. Fortunately, this can be automated by utilizing an externally described DS.

DO_TABLE1       E DS                  EXTNAME(TABLE1:TBL1RECFMT)

You can now delete the manual DS-Definition in your code, and you need to update your loop: Change the destination variable to the newly defined DS.

C/EXEC SQL
C+ FETCH NEXT FROM SLT1 INTO :O_TABLE1
C/END-EXEC

Now, for every compile run, the definitions of the table will be statically embedded within the resulting binary and you are freed from thinking about reflecting changes.

If you utilize multiple tables to do a JOINed SQL query and SELECT fields from both tables, you'll have a hard time with automatic variable creation. If you simply utilize two externally described DS', compilation will fail because of a name clash (from the common field in both tables used for the JOIN). You could circumvent that by PREFIXing the DS' fields but then all fields are renamed that raises the necessity to move variable contents around in code.
At the moment, I can't think of a better way than to declare secondary tables' variables by hand as described above.

Since the database physical file is usually referenced in display and printer files for field reference purposes, there's no need for static definitions. They can be all derived from the PFs.

Check for end-of-results

You should define what to do when all records of an SQL set have been fetched from the cursor.

C/EXEC SQL
C+ WHENEVER NOT FOUND GOTO ENDLOOP1
C/END-EXEC

You must define a TAG in your code with the same name, so the generated SQL binary blob will have a destination to jump to. Personally, I'm appalled to use this GOTO-like behavior.

An alternative approach is to check implicit SQL-variables within a loop. That allows to specify other stuff to do before finally leaving the loop. Example:

C* Read loop start.
C     *ZERO         DOWEQ     *ZERO
C/EXEC SQL
C+ FETCH NEXT FROM SLT1 INTO :RCDEXP
C/END-EXEC
C* Handle EOF.
C     SQLCOD        IFEQ      100
C     SQLSTT        ANDEQ     '02000'
C                   LEAVE
C                   ENDIF

End SQL handling

End the current SQL query by closing the cursor. It can be re-opened and executes the SQL from the second step.

C/EXEC SQL
C+ CLOSE SLT1
C/END-EXEC

Alternate forms

IBM allows a so called dynamic SQL. That is, a statement is precompiled to be run only once at runtime, while variables could be substituted. This speeds up further usage of the same statement and is thus especially helpful for running many INSERT or UPDATE statements in a loop in a fast manner.

DQRYSTRING        S             40A   INZ('SELECT FIELD1, FIELD2 -
D                                     FROM TABLE1 ORDER BY FIELD1')
D*
C/EXEC SQL
C+ PREPARE STM1 FROM :QRYSTRING
C/END-EXEC
C*
C/EXEC SQL
C+ DECLARE SLT1 CURSOR FOR STM1
C/END-EXEC
C*
C/EXEC SQL
C+ OPEN SLT1
C/END-EXEC
C*
C* Loop around here
C/EXEC SQL
C+ FETCH NEXT FROM SLT1 INTO :RCDEXP
C/END-EXEC
C* End loop
C*
C/EXEC SQL
C+ CLOSE SLT1
C/END-EXEC

Providing a real-world example with INSERT or UPDATE would probably more helpful.

Weblinks

• DB2 Universal Database for iSeries SQL Programming with Host Languages, PDF
• DB2 Universal Database for iSeries SQL Reference, PDF
• SQL0312 Error, code400.com. See comments from Scott Klement and kitvb1.
• SQL Precompiler SQL0312 and SQL5011 message enhancement, finally with the latest incarnation of the OS, helpful messages have been implemented!