Wednesday, February 11, 2015

Reading a SQL table in SQL

sql fetch update insert to red update write to sql table

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:

 

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

9 comments:

  1. Now to really take full advantage of SQL you would do block fetches, grabbing all the rows you want in one IO

    ReplyDelete
  2. if (SQLCOD <> 0) ;
    is kind of foolish

    ... s.th like this should be better ...
    EOF(1) = 02000
    EOF(2) = 02001
    if (%lookup(sqlstt:EOF) > 0;
    leave;
    elseif (sqlstt <> *zeros);
    log(sqlstt:pgmname:"CRY FOR HELP":"SEND A MAIL":"DO A SYSOPR": "GO MSGW":"DO A TICKET ON HELPDESK");
    *inlr = *on;
    return;
    endif;

    ReplyDelete
    Replies
    1. What is log()?
      Is it something you have developed?
      The only time I have encountered log() was for calculating logarithms.

      Delete
    2. og like logging ... its not a real function of rpgle it's just do S.th. if you got an internal Error .... I've often got told "there are no Internal Error's, I've tried it ...." and i just open STRSQL, and disconnect the DB connection to the current DB and call his PGM .... ;) and OOOOPS ;)

      also:
      creating a temporary table with the result of an SQL statement and read this Temp Table is really inefficient. Also you did not mentioned how to read a result into a ArrayDs and also you did not mention that you can open a cursor for update to update the current row you've selected.

      Delete
    3. Fetching multiple rows from a SQL table into an array is he subject of a future post. It is waiting in "queue" to be published.

      Delete
  3. When fetching data using SQL I always check SQLCODE for:
    - negative values => processing error occured
    - positive values => warnings (value of 100 means "no data found", aka %eof)
    - zero value => success

    ReplyDelete
  4. I check SQLSTATE instead of SQLCODE. I believe IBM deprecated SQLCODE many years ago.

    http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rbafzmst66.htm

    http://www.mcpressonline.com/tips-techniques/sql/techtip-sqlcod-end-of-file-gotcha.html

    Chris Ringer

    ReplyDelete
  5. good effort

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.