using data structure in INSERT sql

[LEILOON]

Hi
who knows the syntax of using a data structure in VALUES part of INSERT sql command in RPG.
Actually my program inserts record in a table which has a lot of fields. I have a data structure(CUSTINFOREC) exactly as table record format in my program and fill it during the runtime. and now I prefer to replace my static INSERT by a dynamic one and use this data structure directly in INSERT command.
because this way :

DCUSTINFOREC DS INZ
DCIR_CUSNO 6
DCIR_CUSNM 35
DCIR_OD_LCD 5
DCIR_OD_DESC 35
.
.
.


EVAL INSRTSQL='INSERT INTO BAYAT/CUSTINFO'
EVAL INSRTSQL=%TRIM(INSRTSQL)+' VALUES('
EVAL INSRTSQL=%TRIM(INSRTSQL)+''''+CIR_CUSNO
EVAL INSRTSQL=%TRIM(INSRTSQL)+''','+''''
EVAL INSRTSQL=%TRIM(INSRTSQL)+CIR_CUSNM
EVAL INSRTSQL=%TRIM(INSRTSQL)+''','+''''
EVAL INSRTSQL=%TRIM(INSRTSQL)+CIR_OD_LCD
.
.
.
is very long (as the table has many fields) and I believe is not technically beautiful.

thank you

[B.Hauser]

Re: using data structure in INSERT sql

If all fields in the data structure are in the same sequence with the same (or at least compatible) data types, you can use the datastructure as follows:

Exec SQL Insert into MySchema/MyTable
           Values (:DataStructure); 

To insert multiple rows you can use the following statement:

Exec SQL Insert into MySchema/Mytable
          x Rows Values(:ArrayDatastructure); 

where x the number of elements in the ArrayDatastructure or an numeric variable without decimal positions that holds the number of elements of the ArrayDatastructure.

Both solution will only work for static SQL. With dynamic SQL you cannot use a datastructure, you need to list each column.
As far as I see in your example, dynamic SQL is not necessary.

For more information check the following books in the online library:
SQL Reference
Embedded SQL Programming
SQL Programming
Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone


Birgitta