Using RPG data structures with SQL insert and update

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.