Wednesday, January 17, 2018

Using RPG data structures with SQL insert and update

rpg data structures with sql inserts and updates

As I can fetch rows using SQL from a table, or file, into a data structure. I was asked:

Can I insert or update rows using a RPG data structure?

The answer is a definite "Yes".

In these examples I am going to be inserting and updating the following table:

01  CREATE TABLE QTEMP.TABLE1 (
02    COLUMN1 CHAR(10) NOT NULL,
03    COLUMN2 DECIMAL(11,2),
04    COLUMN3 DATE,
05    COLUMN4 TIMESTAMP
06  ) ;

Defining the data structure for this table, or any other table or file, is incredibly simple as I can use the file itself as the definition of the data structure's subfields. Like this:

01  dcl-ds Data extname('QTEMP/TABLE1') qualified ;
02  end-ds ;

I always like to qualify my data structure so that there can never be any confusion as to where the subfields comes from.

I can move values to my subfields and then insert a row using the data structure:

01  Data.Column1 = 'DS #1' ;
02  Data.Column2 = 26.22 ;
03  Data.Column3 = %date() ;
04  Data.Column4 = %timestamp() ;

05  exec sql INSERT INTO QTEMP.TABLE2 VALUES(:Data) ;

I can look in the table and see the row:

COLUMN1    COLUMN2   COLUMN3     COLUMN4
DS #1        26.22   2018-01-17  2018-01-17-04.37.28.037000

The only way I could find to insert multiple rows into a file from a data structure was to do this:

01  dcl-ds Data1 extname('QTEMP/TABLE1') qualified dim(10) ;
02  end-ds ;

03  dcl-ds Data2 likeds(Data1) ;

04  dcl-s RowsFetched packed(5) ;
05  dcl-s i like(RowsFetched) ;

06  for i = 1 to RowsFetched ;
07    Data2 = Data1(i) ;
08    exec sql INSERT INTO QTEMP.TABLE1 VALUES(:Data2) ;
09  endfor ;

Line 1 and 2: I have used the external definition of the table for this data structure, and as it has a DIM keyword it is a data structure array.

Line 3: As I mentioned above I cannot use a data structure array to insert rows, I need just a normal data structure. The LIKEDS ensures that Data2's subfields are identical to those in Data1.

Line 4: I have included this variable as in this scenario I have pretended that the data in Data1 came from a multiple row fetch. When I perform a multiple row fetch I can retrieve the number of rows fetched. This is what the number contained within represents.

Line 5: This variable will be used to retrieve data from the data structure array.

Lines 6 – 9: This For group is where all the action happens! The For group is performed the number of times as the value in RowsFetched, which is the number of elements in the data structure array.

Line 7: I move the element from the data structure array into the plain data structure.

Line 8: I use the plain data structure in the insert statement. As Data2 is a RPG variable it must start with a colon ( : ).

Update follows an insert. To update I have to use SET ROW in the update statement.

exec sql UPDATE QTEMP.TABLE2 SET ROW = :<data structure>
          WHERE <column name> = <comparison value> ;

The WHERE clause is needed to find the row that should be updated.

If I wanted to update the last row I inserted it would look like:

01  Data2.Column2 = 22.22 ;
02  Data2.Column3 = %date() + %days(10) ;
03  Data2.Column4 = %timestamp() + %hours(12) ;

04  exec sql UPDATE QTEMP.TABLE1 SET ROW = :Data2
              WHERE COLUMN1 = :Data2.Column1 ;

if you notice I did not change the value in the subfield Data2.Column1. I am using this value as the "key" to find the row in the table to be updated.

The SET ROW is on line 4. The data structure must contain subfields that are the equivalent to the columns in the table.

The row is changed from this:

COLUMN1    COLUMN2   COLUMN3     COLUMN4
DS #13       44.48   2018-01-17  2018-01-17-05.00.48.101000

To this:

COLUMN1    COLUMN2   COLUMN3     COLUMN4
DS #13       46.11   2018-01-27  2018-01-17-17.04.28.902000

I can use the SET ROW with a subselect statement.

01  exec sql UPDATE QTEMP.TABLE2
02              SET ROW = (SELECT * FROM QTEMP.TABLE1
03                       WHERE TABLE2.COLUMN1 = TABLE1.COLUMN1) ;

This example can use the "select all", SELECT *, as the columns in the two tables have the same names.

If they do not:

01  exec sql UPDATE QTEMP.TABLE1
02              SET ROW = (SELECT COLUMN5,COLUMN6,
03                                CURRENT_DATE,CURRENT_TIMESTAMP 
04                           FROM QTEMP.TABLE3
05                          WHERE COLUMN5 = 'TWO')
06            WHERE COLUMN1 = 'SECOND' ;

In both of these examples the values in all of the columns in TABLE1 are replaced with the values from the other table.

 

Update

I have Lyle Larsen Jr. to thank for the code to perform a multiple row insert using a data structure.

01  dcl-ds Data extname('QTEMP/TABLE4') qualified dim(99)
02  end-ds ;

03  dcl-s NbrOfRows packed(3) ;

04  Data(1).Column1 = 'ONE' ;
05  Data(1).Column2 = '1' ;

06  Data(2).Column1 = 'TWO' ;
07  Data(2).Column2 = '2' ;

08  NbrofRows = 2 ;

09  exec sql INSERT INTO QTEMP.TABLE4 :NbrOfRows ROWS
                    VALUES(:Data) ;

By using the ROWS in the insert statement, line 9, will insert the first two elements from the data structure array into the table.

Do remember that your data structure array must have the same number, or more, elements than the rows you intend to insert.

 

This article was written for IBM i 7.3, and should work for earlier releases too.

15 comments:

  1. Thanks again for this. How do you update a select set of columns from a subselect? Say column3 and column4 alone.

    ReplyDelete
  2. Should this statement
    04 exec sql UPDATE QTEMP.TABLE1 SET ROW = :Data
    WHERE COLUMN1 = :Data2.Column1 ;

    be amended to be
    04 exec sql UPDATE QTEMP.TABLE1 SET ROW = :Data2
    WHERE COLUMN1 = :Data2.Column1 ;

    with the word ":Data" is changed to ":Data2"
    ?

    ReplyDelete
    Replies
    1. Yes, it is my mistake. Thank you fro bringing that to my attention. It has been corrected/

      Delete
  3. Love it, Data sets are the way to work. Release the old record at a time mentality....BIG DATA Sets is today!

    ReplyDelete
  4. Hi Simon,
    is there a way to use the same logic but with a file name contained into a variable field nameFile?

    Something like:
    09 exec sql INSERT INTO nameFile :NbrOfRows ROWS
    VALUES(:Data) ;

    ReplyDelete
    Replies
    1. Not without doing what is described here.

      Delete
    2. thanks for replying.

      I know how to use cursors, my doubt was just about the INSERT which will take values from a multiple DS (as the one defined in your article.

      From your answer, it's like I have to define a variable containing the same sql string:

      sqlStmt = 'INSERT INTO ' + nameFile + NbrOfRows + ' ROWS values(' + data + ')' ;

      Will this work?

      Delete
    3. I must admit I would not "soft code" an Insert statement. I would hard code so that others can understand which file is being updated.

      As far as will it work I say give it a try and see if it does.

      Delete
  5. it works...

    but only if you:

    Add one more DS element containing quotes ('''') before and after every character one (so can't use extname but can only define the ds internally)

    d myds ds
    d quote1 1 inz('''')
    d charField 10
    d quote2 1 inz('''')
    ..
    ..

    put the insert into a loop
    for i = 1 to %elem(data) ;
    DATA(i)
    sqlStmt = 'INSERT INTO ' + nameFile + NbrOfRows + ' ROWS values(' + data(i) + ')' ;
    endfor


    hope it's clear and sorry for the not free format ds definition :)

    ReplyDelete
  6. sorry... a few mistakes in the "insert" loop:

    for i = 1 to %elem(data) ;

    sqlStmt = 'INSERT INTO ' + nameFile + 'values(' + data(i) + ')' ;
    exec sql execute immediate :sqlStmt ;
    endfor

    ReplyDelete
  7. Wish there was a way to insert multiple rows from a MODS.

    ReplyDelete
    Replies
    1. I think this is another example of why you should not use MODS in any new programs you write. Data structure arrays are, in my opinion, so much easier to use.

      Delete
  8. Unless something has changed, the INSERTs will fail in the future when a new column is added to the table, with a column count mismatch error. The way to solve this is to list the (column1,column2,etc) names before the values(). Any column not included gets the default value.

    Ringer

    ReplyDelete
  9. Hey, thanks for the article! I’m looking for a way to insert a data structure array into a table.

    exec sql INSERT INTO QTEMP.TABLE4 :NbrOfRows ROWS
    VALUES(:Data) ;

    looks nice, but my table has a generated identity column.
    So I tried this:

    exec sql INSERT INTO QTEMP.TABLE4 :NbrOfRows ROWS
    (field1, field2, field3)
    VALUES(:Data.field1, :Data.field2, :Data.field3) ;

    It doesn’t work. The SQL precompiler won’t let me compile the program.

    From the complile listing:
    SQL0104 30 1386 Position 28 Token ( invalid. Valid Token: VALUES.

    So, is it possible to insert a data structure array in one go when only specific fields are to be inserted? Thanks.

    ReplyDelete
    Replies
    1. Create a data structure array that does not include the identity column.
      Rather than list the individual columns in the VALUES, just give the data structure name.

      Delete

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.