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 which will add up to complexity.

In such situations, utilizing the power 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 which creates a binary object which is automatically linked with in the final compilation stage. It also creates (invisible) hooks and variables in the RPG source, you'll encounter in the compiler log and while debugging. Compilation-debugging-cycles will be slower,
• the mentioned precompiler isn't very smart. The SQL statements must come in proper order. 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 the proper order done.

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 these, sometimes optional steps. An example follows. 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.

General statements to steer SQL behavior

You can (and 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.

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

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.

• Create an externally described DS.

DO_TABLE1       E DS                  EXTNAME(TABLE1:TBL1RECFMT)

• Clean up. You can 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 to think about reflecting changes.

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.

Handling EOF without GOTO

C/EXEC SQL                            
C+ FETCH NEXT FROM SLT1 INTO :RCDEXP
C/END-EXEC                            
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

This article isn't finished yet or needs to be revised. Please keep in mind that thus it may be incomplete. Reason: Write about SQLDS

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.

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

Beware that if you restrict record selection by a WHERE condition in combination with a LIKE match on a string, you'll get a SQL0312 compilation error. For such truly dynamic behavior, a separate data structure has to be created and filled. Details follow.

Weblinks

• DB2 Universal Database for iSeries SQL Programming with Host Languages, PDF
• DB2 Universal Database for iSeries SQL Reference, PDF