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.

This topic describes the following methods that you can use to execute a data change 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
Dynamically executing a data change statement by using host variable arrays:
To dynamically execute a data change statement by using host variable arrays, perform the following actions in your program:
  1. 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)))
  2. Assign any attributes for the SQL statement to a host variable.
  3. Include a PREPARE statement for the SQL statement.
  4. 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.

Example: The following code prepares and executes an INSERT statement:
/* 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;
Dynamically executing a data change statement by using descriptors:

You can use an SQLDA structure to specify data types and other information about the host variable arrays that contain the values to insert.

To dynamically execute a data change statement by using descriptors, perform the following actions in your program:
  1. 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: 
    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);
    The SQLDA structure has the following fields:
    • 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.
  2. 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 (?, ?, ?)
  3. Assign any attributes for the SQL statement to a host variable.
  4. Include a PREPARE statement for the SQL statement.
  5. 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.
Example: The following code prepares and executes an 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;
Parent topic: Including dynamic SQL in your program
Related concepts:
Programming assembler applications that issue SQL statements
Programming C and C++ applications that issue SQL statements
Programming COBOL applications that issue SQL statements
Programming Fortran applications that issue SQL statements
Programming PL/I applications that issue SQL statements
Related tasks:
Including dynamic SQL for varying-list SELECT statements in your program
Related reference:
SQLTYPE and SQLLEN