Reading a SQL table in SQL

In a previous post I explained how to handle a SQL table using RPG operation codes to perform the file access. The post was just to illustrate that it was possible. I received messages expressing surprise that I had chosen to use RPG and not SQL. I think those who sent me those messages missed the point of the post. Anyway in this post I am going to show the same example, but using SQL for accessing the SQL table.

If I was doing this myself I would probably replace the entire DO loop in the RPG program with the following SQL statement:

UPDATE QTEMP/WORKFILE SET UFLG1 = '1' WHERE SUBSTRING(JOBNO,1,1) = 'A'

But I am going to do this replacing each of the RPG file access operation code I used with an equivalent SQL statement. These being:

RPG operation	SQL equivalent
READ	FETCH
UPDATE	UPDATE
WRITE	INSERT

Here is modified version of the original program with the SQL equivalents:

01 dcl-ds WorkFile_ds extname('QTEMP/WORKFILE') 02 qualified ; 03 end-ds ; 04 exec sql SET OPTION COMMIT = *NONE ; 05 exec sql DROP TABLE QTEMP/WORKFILE ; 06 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 ; 07 exec sql DECLARE C0 CURSOR FOR SELECT * FROM QTEMP/WORKFILE ORDER BY DUEDT,ORDNO ; 08 exec sql OPEN C0 ; 09 dow (1 = 1) ; 10 exec sql FETCH NEXT FROM C0 INTO :WorkFile_Ds ; 11 if (SQLCOD <> 0) ; 12 leave ; 13 endif ; 14 if (%subst(WorkFile_ds.JOBNO:1:1) = 'A') ; 15 WorkFile_ds.UFLG1 = '1' ; 16 endif ; 17 exec sql UPDATE QTEMP/WORKFILE SET UFLG1 = :WorkFile_ds.UFLG1 WHERE CURRENT OF C0 ; 18 enddo ; 19 *inlr = *on ; 20 exec sql CLOSE C0 ; 21 exec sql INSERT INTO QTEMP/WORKFILE (ORDNO,ORQTY,JOBNO,DUEDT,ITEM, ITDSC,UFLG1) VALUES ('*END',0,'',0,'','','') ;

On line 1 - 3 I have defined a data structure based on WORKFILE, the file that will be built in this program. I use this to define all of the columns in the table for the RPG program, and to make the FETCH statement easier to read as I do not have to include all of WORKFILE's columns in it. If I had to use a fixed column definition for this data structure it would look like:

D WorkFile_ds E DS extname('QTEMP/WORKFILE') D qualified

Lines 4 – 6 remain unchanged from the original.

In the original program I defined an index so that I could read the data in the key order I wanted. I do not have to so this using SQL statements. Declaring the cursor, line 7, is almost the equivalent as defining the file that will be used for input. I give the cursor a name, C0 (I know, no marks for originality for the cursor's name) and then the SELECT statement I want to define what columns, rows, order of them, etc I desire. In this example I want all columns, which is indicated by *, and the input to be ordered (sorted) in Due Date (DUEDT) and Order Number (ORDNO). I could have replaced the * with the just the columns I wanted to use, for example:

DECLARE C0 CURSOR FOR SELECT DUEDT,ORDNO,JOBNO,UFLG1 FROM QTEMP/WORKFILE ORDER BY DUEDT,ORDNO

Once the cursor has been defined it has to be opened, line 8, as you would opening when a file in RPG.

Then my program enters the DO loop, line 9.

I do the FETCH NEXT of the cursor, on line 10, which is the equivalent of RPG's read. I do need to list the variables that the input buffer from the table is moved to. This is why I have used a data structure, defined on line 1, rather than the individual fields. If I chose to define the variables it would have to match what was defined in the cursor declaration, so using the alternate example I gave this FETCH NEXT would look like:

FETCH NEXT FROMC0 INTO :WorkFile_ds.DUEDT, :WorkFile_ds.ORDNO, :WorkFile_ds.JOBNO, :WorkFile_ds.UFLG1

Line 11 shows what I use as the equivalent of RPG's %EOF indicator. The SQL Code, SQLCOD, is zero when there is no error, if it is not zero then an error was encountered when fetching the data from the table. When end of table is encountered the SQL Code will not be zero. I do not have to define SQLCOD, it is defined by the compiler when I compile the program or module.

The next change was to replace RPG's UPDATE operation code with SQL's UPDATE. I find it interesting that I have to give the name of the file and the cursor in the SQL statement. The WHERE CURRENT OR C0 indicates that only the current row (record) is updated.

After the logic exits the DO loop the cursor has to be closed, line 20.

Finally I used an INSERT to add a row to the end of the table, line 21.

 

You can learn more about these on the IBM website:

• SQL Declare cursor
• SQL FETCH
• SQL UPDATE
• SQL INSERT

 

This article was written for IBM i 7.2, and it should work with earlier releases too.