Embedding SQL in RPG: Difference between revisions
(Rework) |
|||
Line 1: | Line 1: | ||
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. | 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 | 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, | * 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 | * embedded SQL will be processed by a precompiler which creates a binary object which 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 in the compiler log and while debugging. Compilation-debugging-cycles will be slower, | ||
* the mentioned precompiler isn't very smart.<ref>At least not with V4R5.</ref> 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 mentioned precompiler isn't very smart.<ref>At least not with V4R5.</ref> 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 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.<br /> | It is strongly advisable that you actually test your SQL query via the interactive SQL query tool ''STRSQL'' or remotely via ODBC.<br /> | ||
Line 12: | Line 12: | ||
== Example == | == Example == | ||
Utilizing SQL for getting records back involves | 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|Weblinks]]'' section below. | ||
Programmers on other Platforms may recognize certain similarities of the needed steps. | Programmers on other Platforms may recognize certain similarities of the needed steps. | ||
Line 24: | Line 24: | ||
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. | 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. | 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. | ||
=== Prepare the SQL query === | === Prepare the SQL query === | ||
Line 53: | Line 53: | ||
==== Automatic variable creation ==== | ==== 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 | 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) | 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/EXEC SQL | ||
C+ FETCH NEXT FROM SLT1 INTO :O_TABLE1 | C+ FETCH NEXT FROM SLT1 INTO :O_TABLE1 | ||
C/END-EXEC | 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 | 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 which raises the necessity to move variable contents around in code.<br /> | |||
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. | 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. | ||
Line 84: | Line 85: | ||
== Alternate forms == | == Alternate forms == | ||
{{FIXME|Write about SQLDS}} | {{FIXME|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. | 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 - | DQRYSTRING S 40A INZ('SELECT FIELD1, FIELD2 - | ||
D FROM TABLE1 ORDER BY FIELD1') | D FROM TABLE1 ORDER BY FIELD1') |
Revision as of 20:08, 23 February 2019
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 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 which creates a binary object which 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 in the compiler log and while debugging. Compilation-debugging-cycles will be slower,
- the mentioned precompiler isn't very smart.[1] 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 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.
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 to do 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 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 which 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.
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 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
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
Footnotes
- ↑ At least not with V4R5.