Embedding SQL in RPG - Revision history

PoC: Reworked

2021-03-25T09:56:08Z

Reworked

← Older revision		Revision as of 11:56, 25 March 2021
Line 3:		Line 3:
	In such situations, utilizing the flexibility of SQL can speed up development considerably. This comes at a price, though:		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 that creates a binary object ~~that~~ 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,		* 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.<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.		* the mentioned precompiler isn't very smart.<ref>At least not with V4R5.</ref> 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.<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 15:		Line 15:

	Programmers on other Platforms may recognize certain similarities of the needed steps.		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 ===		=== Prepare the SQL query ===
Line 68:		Line 58:
	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.

	===~~= Handling EOF without GOTO =~~===		=== Check for end-of-results ===
	C/EXEC SQL		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+ FETCH NEXT FROM SLT1 INTO :RCDEXP
	C/END-EXEC		C/END-EXEC
	C SQLCOD IFEQ 100		C* Handle EOF.
	C SQLSTT ANDEQ '02000'		C SQLCOD IFEQ 100
			C SQLSTT ANDEQ '02000'
	C LEAVE		C LEAVE
	C ENDIF		C ENDIF
Line 84:		Line 86:

	== Alternate forms ==		== Alternate forms ==
	~~{{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 in a fast manner.<ref>This feature is also exposed via ODBC.</ref>
	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')
Line 111:		Line 112:
	C/END-EXEC		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~~.		Providing a real-world example with INSERT or UPDATE would probably more helpful.

	== Weblinks ==		== Weblinks ==
	* [http://www.astradyne.net/manuals/im025510.pdf DB2 Universal Database for iSeries SQL Programming with Host Languages], PDF		* [http://www.astradyne.net/manuals/im025510.pdf DB2 Universal Database for iSeries SQL Programming with Host Languages], PDF
	* [http://www.astradyne.net/manuals/im026510.pdf DB2 Universal Database for iSeries SQL Reference], PDF		* [http://www.astradyne.net/manuals/im026510.pdf DB2 Universal Database for iSeries SQL Reference], PDF
			* [https://code400.com/forum/forum/iseries-programming-languages/rpg-rpgle/12263-sql0312-error SQL0312 Error], code400.com. See comments from Scott Klement and kitvb1.
			* [https://www.ibm.com/support/pages/sql-precompiler-sql0312-and-sql5011-message-enhancement SQL Precompiler SQL0312 and SQL5011 message enhancement], finally with the latest incarnation of the OS, helpful messages have been implemented!

	== Footnotes ==		== Footnotes ==

PoC: De-whichified

2020-03-12T21:57:37Z

De-whichified

← Older revision		Revision as of 23:57, 12 March 2020
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 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:		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 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,		* embedded SQL will be processed by a precompiler that creates a binary object that 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 stuff in proper order.		* 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.

Line 63:		Line 63:
	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.		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 />		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.<br />
	At the moment, I can't think of a better way than to declare secondary tables' variables by hand as described above.		At the moment, I can't think of a better way than to declare secondary tables' variables by hand as described above.

PoC: Rework

2019-02-23T19:08:27Z

Rework

← Older revision		Revision as of 21:08, 23 February 2019
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 ~~power~~ of SQL can speed up development considerably. This comes at a price, though:		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 ~~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,		* 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~~ proper order ~~done~~.		* 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 ~~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\|Weblinks]]'' section below.		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.

	* Create an externally described DS.
	DO_TABLE1 E DS EXTNAME(TABLE1:TBL1RECFMT)		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''.		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 ~~to think~~ about reflecting changes.		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')

PoC: /* Alternate forms */

2019-02-09T11:38:29Z

Alternate forms

← Older revision		Revision as of 13:38, 9 February 2019
Line 83:		Line 83:

	== Alternate forms ==		== 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 ~~SELECT~~ statement.		{{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.
	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')
Line 109:		Line 110:
	C/END-EXEC		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. ~~I'm not yet sure how~~ to ~~circumvent this~~.		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 ==		== Weblinks ==

PoC: addition

2019-02-08T19:00:03Z

addition

@@ Line 23: / Line 23: @@
 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.
 === Prepare the SQL query ===
@@ Line 64: / Line 66: @@
 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.
 === End SQL handling ===
@@ Line 70: / Line 81: @@
   C+ CLOSE SLT1
   C/END-EXEC
 == Weblinks ==

PoC: /* Automatic variable creation */ Wrong spacing

2019-02-08T14:33:41Z

Automatic variable creation: Wrong spacing

← Older revision		Revision as of 16:33, 8 February 2019
Line 54:		Line 54:

	* Create an externally described DS.		* Create an externally described DS.
	DO_TABLE1 E DS EXTNAME(TABLE1:TBL1RECFMT)		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''.		* 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''.

PoC: Test SQL

2019-02-08T14:21:07Z

Test SQL

← Older revision		Revision as of 16:21, 8 February 2019
Line 5:		Line 5:
	* 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,		* 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.<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 proper order done.		* 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 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.<br />
			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.		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.

PoC: /* Automatic variable creation */ Better

2019-02-08T13:11:49Z

Automatic variable creation: Better

← Older revision		Revision as of 15:11, 8 February 2019
Line 50:		Line 50:
	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.

	* Create an externally described DS~~-Overlay~~.		* Create an externally described DS.
	DO_TABLE1 E DS EXTNAME(TABLE1:TBL1RECFMT)		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 DS~~-Overlay~~.		* 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/EXEC SQL
	C+ FETCH NEXT FROM SLT1 INTO :O_TABLE1		C+ FETCH NEXT FROM SLT1 INTO :O_TABLE1

PoC: /* Automatic variable creation */ Cleanup: File not needed

2019-02-08T12:51:17Z

Automatic variable creation: Cleanup: File not needed

← Older revision		Revision as of 14:51, 8 February 2019
Line 50:		Line 50:
	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.

	* ~~First, reference your file(s) containing the fields you're referencing in your ''SELECT''. We don't need the file descriptor(s) at run time, so flag them ''USROPN'' but never open.~~		* Create an externally described DS-Overlay.
	~~FTABLE1 IF E DISK USROPN~~

	* Next, create a DS-Overlay.
	DO_TABLE1 E DS EXTNAME(TABLE1:TBL1RECFMT)		DO_TABLE1 E DS EXTNAME(TABLE1:TBL1RECFMT)

PoC: /* Automatic variable creation */ Addition

2019-02-08T12:37:49Z

Automatic variable creation: Addition

← Older revision		Revision as of 14:37, 8 February 2019
Line 50:		Line 50:
	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.

	* First, reference your file(s) containing the fields you're referencing in your ''SELECT''.		* First, reference your file(s) containing the fields you're referencing in your ''SELECT''. We don't need the file descriptor(s) at run time, so flag them ''USROPN'' but never open.
	FTABLE1 IF E DISK		FTABLE1 IF E DISK USROPN

	* Next, create a DS-Overlay.		* Next, create a DS-Overlay.