DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
You can use structure arrays for FETCH INTO, INSERT, UPDATE, and DELETE statements that are non-dynamic, when you set the precompiler option COMPATIBILITY_MODE to ORA.
You can use structure arrays to store multiple column data in a structure form.
For a structure array that is declared for an INSERT, UPDATE, or DELETE statement, you must ensure that all array elements are initialized with a value. Otherwise, unexpected data can get introduced or removed from the table.
The total number of rows that are successfully processed is stored in the sqlca.sqlerrd[3] field. However, the sqlca.sqlerrd[3] field does not represent the number of rows that are committed successfully in the case of INSERT, UPDATE, or DELETE operations.
The total number of rows that are impacted by the INSERT, UPDATE, or DELETE operation is stored in the sqlca.sqlerrd[2] field.
In one FETCH INTO statement, the maximum number of records that can be retrieved is the cardinality of the array that is declared. If more rows are available after the first fetch, you can repeat the FETCH INTO statement to obtain the next set of rows.
// Declare structure array with cardinality of 3.
EXEC SQL BEGIN DECLARE SECTION;
struct MyStruct
{
int c1;
char c2[11];
} MyStructVar[3];
EXEC SQL DECLARE cur CURSOR FOR
SELECT empno, lastname FROM employee;
EXEC SQL END DECLARE SECTION;
...
// MyStrutVar is a structure array for host variables
EXEC SQL FETCH cur INTO :MyStructVar;// Declare structure array with cardinality of 3.
EXEC SQL BEGIN DECLARE SECTION;
typedef struct _st_type {
int id;
char name[21];
} st_type;
st_type st[3];
EXEC SQL END DECLARE SECTION;
...
// Populating the array.
for( i=0; i<3; i++)
{
memset( &st[i], 0x00, sizeof(st_type));
if( i==0) { st[i].id = 100; strcpy(st[i].name, "hello1");}
if( i==1) { st[i].id = 101; strcpy(st[i].name, "hello2");}
if( i==2) { st[i].id = 102; strcpy(st[i].name, "hello3");}
}
// The structure elements must be in
// the same order as that of the table elements.
//
EXEC SQL INSERT INTO tbl values (:st);
// Check for SQLCODE.
printf(“sqlca.sqlcode = %d\n”, sqlca.sqlcode ); // 0
// The INSERT operation inserted 3 rows without encounting an error
printf(“sqlca.sqlerrd[3] = %d\n”, sqlca.sqlerrd[3] ); // 3
// The INSERT operation was successful and 3 rows has been stored in database.
printf(“sqlca.sqlerrd[2] = %d\n”, sqlca.sqlerrd[2] ); // 3
// The tb11 table now contains the following rows:
// C1 C2
// ----------- -----------
// 100 hello1
// 101 hello2
// 102 hello3