EXECUTE statement Format 2 (Execute an SQL statement using an array)

Function

The EXECUTE statement executes the SQL statements, preprocessed by the PREPARE statement, in multiple rows in batch using an array.

Privileges

None.

Format 2: Batch execution of multiple rows or multiple times of preprocessed (INSERT, UPDATE, or DELETE) statement

EXECUTE {SQL-statement-identifier|extended-statement-name}
    {USING :array-of-embedded-variables[:array-of-indicator-variables]
          [, :array-of-embedded-variables[:array-of-indicator-variables]]...
     |USING DESCRIPTOR [:]SQL-descriptor-area-name}
    BY :embedded-variable[ROWS]

Operands

SQL-statement-identifier
Specifies the SQL identifier assigned to the SQL statement that was preprocessed by the PREPARE statement.
extended-statement-name
Specifies the extended statement name that identifies the SQL statement preprocessed by the PREPARE statement.
For extended statement names, see 2.26 Extended statement name.
array-of-embedded-variables
When assigning the value of an input ? parameter included in the SQL statement that was preprocessed by the PREPARE statement, specifies the embedded variable having the value to be assigned to the input ? parameter in an array format.
Make the number of embedded variable arrays specified in the USING clause equal to the number of input ? parameters that are included in the SQL statement executed by the EXECUTE statement. The array of embedded variables and the input ? parameters are put in correspondence from the beginning in their respective collating sequences.
The data type of the array of embedded variables specified in the USING clause should be the data type allowed for the corresponding input ? parameter.
array-of-indicator-variables
Specifies an indicator variable in an array format, indicating whether the value of embedded-variable is the null value.
The array of indicator variables should be declared in the embedded SQL declare section as an embedded variable in an array format having the SMALLINT data type.
If the array-of-indicator-variables operand is omitted, HiRDB assumes that the value of array-of-indicator-variables is a non-null value.

When assigning the value of the input ? parameter included in the SQL statement and preprocessed by the PREPARE statement that is to be given in the SQL descriptor area, specifies the name of the SQL descriptor area storing information on the input ? parameter.

embedded-variable
If the SQL statement preprocessed by the PREPARE statement is an INSERT statement, assigns the number of rows to be processed (inserted). If the SQL statement preprocessed by the PREPARE statement is either an UPDATE or DELETE statement, specifies an embedded variable in which the number of operations (updating or deleting) to be performed is assigned. Specify a SMALLINT type embedded variable.
The following range of values can be specified:
  • Specifying USING :array-of-embedded-variables[:array-of-indicator-variables][, :array-of-embedded-variables[:array-of-indicator-variables]] in the operand:
    The range of values is 1 to 4,096.
  • Specifying USING DESCRIPTOR [:]SQL-descriptor-area-name in the operand:
    The range of values is 1 to 30,000.
Zero and negative values cannot be assigned. Assigning an out-of-range value may cause a runtime error.
The word ROW can be omitted without changing the meaning.

Common rules

  1. The SQL statement executed by the EXECUTE statement must be preprocessed in advance using the PREPARE statement.
  2. The EXECUTE statement Format 2 can be used only if the SQL statement preprocessed by the PREPARE statement is the INSERT, UPDATE, or DELETE statement shown below:
    • INSERT INTO [authorization-identifier.] table-identifier [ (column-value [, column-value]...) ]
      {VALUES (insertion-value [, insertion-value]...)
      | query-expression-body }
      [WITH ROLLBACK]
    • INSERT INTO [[RD-node-name.]authorization-identifier.] table-identifier (ROW)
      {VALUES(:embedded-variable-array [:indicator-variable-array])
      | query-expression-body }
      [WITH ROLLBACK]
    • UPDATE [[RD-node-name.]authorization-identifier.] table-identifier [ [AS] correlation-name]
      [used-index-SQL-optimization-specification]
      SET {update-object = update-value
      | (update-object, update-object [, update-object]) = row-subquery}
      [, {update-object = update-value
      | (update-object, update-object [, update-object]...) = row-subquery} ]...
      [WHERE search-condition]
      [WITH ROLLBACK]
    • UPDATE [[RD-node-name.]authorization-identifier.] table-identifier [ [AS] correlation-name]
      SET ROW = row-update-value
      [used-index-SQL-optimization-specification]
      [WHERE search-condition]
      [WITH ROLLBACK]
    • DELETE FROM [[RD-node-name.]authorization-identifier.] table-identifier
      [ [AS] correlation-name]
      [used-index-SQL-optimization-specification]
      [WHERE search-condition]
      [WITH ROLLBACK]
    If the SQL statement preprocessed by the PREPARE statement is not in the above format, a runtime error may occur.
  3. The PREPARE statement and the EXECUTE statement that executes the SQL statement preprocessed by the PREPARE statement should be executed in the same transaction.
  4. The SQL statement identifier, similar to the embedded variable, is a name that is effective in the file in the compile unit. More than one SQL statement associated with the same SQL statement identifier cannot be used across multiple modules.
  5. Specifying USING :array-of-embedded-variables[:array-of-indicator-variables][, :array-of-embedded-variables[:array-of-indicator-variables]]... in the operands is subject to the following rules:
    • The number of elements in array-of-embedded-variables and array-of-indicator-variables should be in the 1 to 4,096 range. An out-of-range value may cause an error.
    • The number of elements in array-of-embedded-variables and array-of-indicator-variables should be greater than or equal to the maximum number of rows specified in BY :embedded-variable[ROWS].
  6. Specifying USING DESCRIPTOR [:]SQL-descriptor-area-name in the operands is subject to the following rules:
    • For assigning the value of an input ? parameter to the area indicated by SQLDATA in the SQL descriptor area specified in SQL-descriptor-area-name, see the HiRDB Version 8 UAP Development Guide. The number of elements in the array should be greater than or equal to the maximum number of elements specified in BY :embedded-variable[ROWS].
    • Assign a value consistent with the data type to the SQLSYS area of the SQL descriptor area specified in SQL-descriptor-area-name.
      [Figure]For a variable-length character string type (VARCHAR, NVARCHAR, or MVARCHAR), assign a length equal to one element, including the area for storing the length of the character string and any gap between elements that arises from a boundary alignment.
      Example:
      In the C language, the following array variable of the VARCHAR type requires the value sizeof(vchr[0]) to be assigned to SQLSYS:
      struct {
      short len;
      char str[257];
      } vchr[128];
      [Figure] For other data types, the value 0 should be assigned.
  7. The BLOB type, BINARY with a maximum length of 32,001 bytes or greater, and the abstract data type cannot be specified.

Notes

  1. The EXECUTE statement can be executed any number of times per SQL statement that is preprocessed by the PREPARE statement.
  2. The EXECUTE statement Format 2 processes the number of rows specified in BY :embedded-variable. Therefore, a value equal to the specified number of rows should be assigned to the area pointed to by SQLDATA to which the value of array-of-embedded-variables, array-of-indicator-variables, or input-?-parameter is assigned.

Examples

  1. Insert 50 rows of data into the inventory table in batch, assigned to the array variable in the C language:

     EXEC SQL BEGIN DECLARE SECTION;
       short   XINSERT_NUM;
       long    XPCODE[50];
       short   IPCODE[50];
       char    XPNAME[50][17];
       short   IPNAME[50];
     EXEC SQL END DECLARE SECTION;

     EXEC SQL
       PREPARE PRESQL FROM
           'INSERT INTO STOCK(PCODE, PNAME) VALUES (?, ?)';

                    :
       Assign a value to each element of the array variable.
                    :

     XINSERT_NUM = 50;
     EXEC SQL
       EXECUTE PRESQL USING :XPCODE:IPCODE, :XPNAME:IPNAME
           BY :XINSERT_NUM ROWS;

  2. Update the quantity in stock (SQTY) by the value of the product code (PCODE) and the quantity in stock (SQTY) assigned to array variables in the C language to the following values:

    Table 4-5 Product code and quantity in stock stored in table (before updating)

    Product codeNew quantity in stock
    '101M'40
    '101L'70
    '201M'15
    '202M'28
    '302S'7

    Table 4-6 Product code and quantity in stock subject to updating (assigned to an embedded variable array)

    Product codeNew quantity in stock
    '101M'35
    '101L'62
    '201M'13
    '202M'10
    '302S'6

    Table 4-7 Product code and quantity in stock stored in table (after updating)

    Product codeNew quantity in stock
    '101M'35
    '101L'62
    '201M'13
    '202M'10
    '302S'6

    EXEC SQL BEGIN DECLARE SECTION;
         short   XUPDATE_NUM;
         char    XPCODE[5][5];
         short   IPCODE[5];
         long    XSQTY[5];
         short   ISQTY[5];
    EXEC SQL END DECLARE SECTION;

EXEC SQL
PREPARE PRESQL FROM
     'UPDATE STOCK SET SQTY = ? WHERE PCODE = ?';
   . . . Assign value to elements of variable array . . .
       Assign {'101M','101L','201M','202M','302S'} to XPCODE
       Assign {35,62,13,10,6} to XSQTY
XUPDATE_NUM = 5;
EXEC SQL
EXECUTE PRESQL USING :XSQTY:ISQTY, :XPCODE:IPCODE
BY :XUPDATE_NUM