Dynamically executing a data change statement
Dynamically executing data change statements with host variable arrays is useful if you want to enter rows of data into different tables. It is also useful if you want to enter a different number of rows. The process is similar for both INSERT and MERGE statements.
About this task
For example, suppose that you want to repeatedly execute a multiple-row INSERT statement with a list of activity IDs, activity keywords, and activity descriptions that are provided by the user. You can use the following static SQL INSERT statement to insert multiple rows of data into the activity table:
EXEC SQL
INSERT INTO DSN8A10.ACT
VALUES (:hva_actno, :hva_actkwd, :hva_actdesc)
FOR :num_rows ROWS;
However, if you want to enter the rows of data into different tables or enter different numbers of rows, you can construct the INSERT statement dynamically.
- By using host variable arrays that contain the data to be inserted
- By using a descriptor to describe the host variable arrays that contain the data
- Assign the appropriate INSERT or MERGE statement to a host variable.
If needed, use the CAST specification to explicitly assign types to
parameter markers that represent host variable arrays.Example: For the activity table, the following string contains an INSERT statement that is to be prepared:
INSERT INTO DSN8A10.ACT VALUES (CAST(? AS SMALLINT), CAST(? AS CHAR(6)), CAST(? AS VARCHAR(20)))
- Assign any attributes for the SQL statement to a host variable.
- Include a PREPARE statement for the SQL statement.
- Include an EXECUTE statement with the FOR n ROWS
clause.
Each host variable in the USING clause of the EXECUTE statement represents an array of values for the corresponding column of the target of the SQL statement. You can vary the number of rows without needing to prepare the SQL statement again.
/* Copy the INSERT string into the host variable sqlstmt */
strcpy(sqlstmt, "INSERT INTO DSN8A10.ACT VALUES (CAST(? AS SMALLINT),");
strcat(sqlstmt, " CAST(? AS CHAR(6)), CAST(? AS VARCHAR(20)))");
/* Copy the INSERT attributes into the host variable attrvar */
strcpy(attrvar, "FOR MULTIPLE ROWS");
/* Prepare and execute my_insert using the host variable arrays */
EXEC SQL PREPARE my_insert ATTRIBUTES :attrvar FROM :sqlstmt;
EXEC SQL EXECUTE my_insert USING :hva1, :hva2, :hva3 FOR :num_rows ROWS;
You can use an SQLDA structure to specify data types and other information about the host variable arrays that contain the values to insert.
- Set the following fields in the SQLDA structure for your INSERT
statement.
- SQLN
- SQLABC
- SQLD
- SQLVAR
- SQLNAME
Example: Assume that your program includes the standard SQLDA structure declaration and declarations for the program variables that point to the SQLDA structure. For C application programs, the following example code sets the SQLDA fields:
The SQLDA structure has the following fields:strcpy(sqldaptr->sqldaid,"SQLDA"); sqldaptr->sqldabc = 192; /* number of bytes of storage allocated for the SQLDA */ sqldaptr->sqln = 4; /* number of SQLVAR occurrences */ sqldaptr->sqld = 4; varptr = (struct sqlvar *) (&(sqldaptr->sqlvar[0])); /* Point to first SQLVAR */ varptr->sqltype = 500; /* data type SMALLINT */ varptr->sqllen = 2; varptr->sqldata = (char *) hva1; varptr->sqlname.length = 8; memcpy(varptr->sqlname.data, "\x00\x00\x00\x00\x00\x01\x00\x14",varptr->sqlname.length); varptr = (struct sqlvar *) (&(sqldaptr->sqlvar[0]) + 1); /* Point to next SQLVAR */ varptr->sqltype = 452; /* data type CHAR(6) */ varptr->sqllen = 6; varptr->sqldata = (char *) hva2; varptr->sqlname.length = 8; memcpy(varptr->sqlname.data, "\x00\x00\x00\x00\x00\x01\x00\x14",varptr->sqlname.length); varptr = (struct sqlvar *) (&(sqldaptr->sqlvar[0]) + 2); /* Point to next SQLVAR */ varptr->sqltype = 448; /* data type VARCHAR(20) */ varptr->sqllen = 20; varptr->sqldata = (char *) hva3; varptr->sqlname.length = 8; memcpy(varptr->sqlname.data, "\x00\x00\x00\x00\x00\x01\x00\x14",varptr->sqlname.length);
- SQLDABC indicates the number of bytes of storage that are allocated for the SQLDA. The storage includes a 16-byte header and 44 bytes for each SQLVAR field. The value is SQLN x 44 + 16, or 192 for this example.
- SQLN is the number of SQLVAR occurrences, plus one for use by DB2® for the host variable that contains the number n in the FOR n ROWS clause.
- SQLD is the number of variables in the SQLDA that are used by DB2 when processing the INSERT statement.
- An SQLVAR occurrence specifies the attributes of an element of
a host variable array that corresponds to a value provided for a target
column of the INSERT. Within each SQLVAR:
- SQLTYPE indicates the data type of the elements of the host variable array.
- SQLLEN indicates the length of a single element of the host variable array.
- SQLDATA points to the corresponding host variable array. Assume that your program allocates the dynamic variable arrays hva1, hva2, and hva3.
- SQLNAME has two parts: the LENGTH and the DATA. The LENGTH is 8. The first two bytes of the DATA field is X'0000'. Bytes 5 and 6 of the DATA field are a flag indicating whether the variable is an array or a FOR n ROWS value. Bytes 7 and 8 are a two-byte binary integer representation of the dimension of the array.
- Assign the appropriate INSERT or MERGE statement to a host variable.Example: The following string contains an INSERT statement that is to be prepared:
INSERT INTO DSN8A10.ACT VALUES (?, ?, ?)
- Assign any attributes for the SQL statement to a host variable.
- Include a PREPARE statement for the SQL statement.
- Include an EXECUTE statement with the FOR n ROWS clause. The host variable in the USING clause of the EXECUTE statement names the SQLDA that describes the parameter markers in the INSERT statement.
/* Copy the INSERT string into the host variable sqlstmt */
strcpy(sqlstmt, "INSERT INTO DSN8A10.ACT VALUES (?, ?, ?)");
/* Copy the INSERT attributes into the host variable attrvar */
strcpy(attrvar, "FOR MULTIPLE ROWS");
/* Prepare and execute my_insert using the descriptor */
EXEC SQL PREPARE my_insert ATTRIBUTES :attrvar FROM :sqlstmt;
EXEC SQL EXECUTE my_insert USING DESCRIPTOR :*sqldaptr FOR :num_rows ROWS;