This idea for this post comes from a Comment posted on Creating a SQL table on the fly. Anonymous posed the question "Now how do you use that table in your RPG program?".
The post I describe how it is possible to use the SQL CREATE TABLE to build a SQL table extracting data from another file. I have mainly used this in processes that extract data and then send the extracted data to the requester via email. But there is no reason why I cannot read the table created in a RPG program.
The SQL statement I am going to use is very similar to the one in the aforementioned article:
CREATE TABLE QTEMP/WORKFILE AS (SELECT A.ORDNO,A.ORQTY,A.JOBNO,A.DUEDT A.ITEM,B.ITDSC,A.UFLG1 FROM ORDHDR A, ITMMST B WHERE A.ITEM = B.ITEM AND A.DUEDT > 1150115) WITH DATA RCDFMT WORKFILER
The SELECT defines the columns (fields) that are in the table, and allow me to only select the records with a due date of greater than January 15, 2015. WITH DATA is needed so that the table is generated containing data. I always use the RCDFMT so that the table will have a record format name just in case I need to use it in a RPG program.
As the table is generated in QTEMP I cannot add a key to the file. I am going to create a unique index with the key I want. I could just as well just create an index, but I want to ensure the keys in this index are unique:
CREATE UNIQUE INDEX QTEMP/WORKFILE1 ON QTEMP/WORKFILE (DUEDT,ORDNO)
In this example I am going to create the table using embedded SQL statements in a RPG program, and then read the table using RPG.
01 ctl-opt alwnull(*inputonly) ; 02 dcl-f WORKFILE1 usage(*input:*output:*update) keyed extfile('QTEMP/WORKFILE1') usropn ; 03 exec sql SET OPTION COMMIT = *NONE ; 04 exec sql DROP TABLE QTEMP/WORKFILE ; 05 exec sql CREATE TABLE QTEMP/WORKFILE AS (SELECT A.ORDNO,A.ORQTY,A.JOBNO,A.DUEDT A.ITEM,B.ITDSC,A.UFLG1 FROM ORDHDR A, ITMMST B WHERE A.ITEM = B.ITEM AND A.DUEDT > 1150115) WITH DATA RCDFMT WORKFILER ; 06 exec sql CREATE UNIQUE INDEX QTEMP/WORKFILE1 ON QTEMP/WORKFILE (DUEDT,ORDNO) ; 07 open WORKFILE1 ; 08 dow (1 = 1) ; 09 read WORKFILER ; 10 if (%eof) ; 11 leave ; 12 endif ; 13 if (%subst(JOBNO:1:1) = 'A') ; 14 UFLG1 = '1' ; 15 update WORKFILER %fields(UFLG1) ; 16 endif ; 16 enddo ; 18 *inlr = *on ; 19 clear WORKFILER ; 20 ORDNO = '*END' ; 21 write WORKFILER ; 22 close WORKFILE1 ;
Those of you who are regular readers of this site know that I code everything using the latest all free RPG. Line 2 is a file definition statement defining the file WORKFILE1 will be used for input, update and output, I am going to read it in key sequence, I am "hard coding" the location of the file using the EXTFILE keyword (see Useful keywords for your F-specs), USROPN means I will open the file using the OPEN and CLOSE operation codes. I want to open the file rather than let the program open it as program initialization as I have to build it first using the SQL CREATE TABLE.
If I want to define the file using the fixed format File specification it would look like:
FWORKFILE1 UF A E K DISK extfile('QTEMP/WORKFILE1') F usropn
I do not want to use commitment control as this is a work file, therefore, I set the SQL option to not use commitment control on line 3.
The DROP TABLE on line 4 is like the DLTF command but better. When I DROP TABLE any indexes that are built over this file are also deleted.
I create the table on line 5, and create the unique index on line 6.
As the index now exists I can open it on line 7. From then on I can treat it like any file. I can read it with a RPG READ operation, line 9, and the %EOF indicator will come on when the end of the index is reached.
I can also update the SQL table using the RPG UPDATE operation code, see line 15.
After all the rows (records) are read and I exit the DO and seton LR, line 18, to flag that the program will end.
To show that I can write to a SQL index using the RPG WRITE operation I write an ending record, line 21.
I always prefer the close the file, line 22, after setting on LR. You can do line 19 - 22 before setting on LR if you prefer.
To be able to compile this program I have to first create the table. I did this by creating another program that just contained the CREATE TABLE and CREATE UNQUEI INDEX. That way I can run it before compiling the RPG program.
If the RCDFMT had been omitted from the CREATE TABLE then the record format name name of the index would be the same as the record format name of the table, which is the name of the table. If I did not want to use WORKFILE as the record format name I could rename it in the file definition using the RENAME keyword:
dcl-f WORKFILE1 usage(*input:*output:*update) keyed extfile('QTEMP/WORKFILE1') rename(WORKFILE:INPUT) usropn ; FWORKFILE1 UF A E K DISK extfile('QTEMP/WORKFILE1') F rename(WORKFILE:INPUT) F usropn
This is a very simple example to show that it is possible to use a SQL table like a file in a RPG program. I am sure you can come up with more complicated scenarios for your own situations.
Also see Reading a SQL table in SQL for how to do the same using SQL.
This article was written for IBM i 7.2, and it should work with earlier releases too.