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.
Thanks again for this. How do you update a select set of columns from a subselect? Say column3 and column4 alone.
ReplyDeleteShould this statement
ReplyDelete04 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"
?
Yes, it is my mistake. Thank you fro bringing that to my attention. It has been corrected/
DeleteLove it, Data sets are the way to work. Release the old record at a time mentality....BIG DATA Sets is today!
ReplyDeleteHi Simon,
ReplyDeleteis 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) ;
Not without doing what is described here.
Deletethanks for replying.
DeleteI 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?
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.
DeleteAs far as will it work I say give it a try and see if it does.
it works...
ReplyDeletebut 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 :)
sorry... a few mistakes in the "insert" loop:
ReplyDeletefor i = 1 to %elem(data) ;
sqlStmt = 'INSERT INTO ' + nameFile + 'values(' + data(i) + ')' ;
exec sql execute immediate :sqlStmt ;
endfor