passing array to embedded sql

[leialeia]

I'd like to know if it's possible to insert multiple records with a single query. The values comes from an array of values.

let say, i have two arrays:
NAME[10]
AGE[10]

how do i loop 10 times to ADD the values to a DB2 table named TABLE2?

i have a code on how to add a single NAME & AGE.
SUBMIT SQL CONTINUE;
CONNECT TO DB2(SSID=&DB2ID);
EXECUTE(INSERT INTO &CREATOR.&TABLE2 (NAME, AGE)
VALUES('&NAME','&AGE')) BY DB2;
%PUT SQLXRC=&SQLXRC, SQLXMSG=&SQLXMSG;
%LET SQLDBRC=&SYSDBRC;
EXECUTE(COMMIT) BY DB2;
DISCONNECT FROM DB2;
QUIT;
ENDSUBMIT;

thanks!

[jeffro]

No you really can't do it with a single query. You can string multiple queries together and execute them consecutively, then the commit will make the changes to the database. So you could do this:

Query
Query
Query
Query
Commit

Rather than this:

Query
Commit
Query
Commit
Query
Commit

[leialeia]

thanks jeffro!

could you elaborate further? a sample code would be a great help!

in addition this, i also have to implement ROLLBACK.
let say, i'm inserting values of NAME[5] & AGE[5], and then an error happened.
i have to ROLLBACK, so that values of NAME[1] to NAME[5] and AGE[1] to AGE[5] won't be added to the TABLE.

thanks so much!

[DJMaze]

in mysql/postgresql:

query: BEGIN;
your queries here
query: COMMIT;
on failure:
query: ROLLBACK;

in ms sql:

query: BEGIN TRANSACTION;
your queries here
query: COMMIT;
on failure:
query: ROLLBACK;

I don't know the exact command for ODBC/Access though