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
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
[CHARACTER_SET[:]character-set-descriptor-area-name]}
BY :embedded-variable[ROWS] |
Operands
- {SQL-statement-identifier | extended-statement-name}
- 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.
- USING :array-of-embedded-variables[:array-of-indicator-variables][, :array-of-embedded-variables[:array-of-indicator-variables]]...
- 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.
- USING DESCRIPTOR [:]SQL-descriptor-area-name
[CHARACTER_SET [:]character-set-descriptor-area-name]
- SQL-descriptor-area-name
- When using the SQL descriptor area to assign a value to the input ? parameter in the SQL preprocessed by the PREPARE statement, specifies the name of the SQL descriptor area that is to be used to store the variable being passed to the input ? parameter.
- character-set-descriptor-area-name
- When using the SQL descriptor area to assign a value to the input ? parameter in the SQL preprocessed by the PREPARE statement, specifies the name of the character set descriptor area that specifies the character set name used for the variable being passed to the input ? parameter.
- For details about character set descriptor areas, see the HiRDB Version 9 UAP Development Guide.
- BY :embedded-variable[ROWS]
- 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
- The SQL statement executed by the EXECUTE statement must be preprocessed in advance using the PREPARE statement.
- 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 [authorization-identifier.] table-identifier (ROW)
{VALUES(:embedded-variable-array [:indicator-variable-array])
| query-expression-body }
[WITH ROLLBACK]
- UPDATE [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 [authorization-identifier.] table-identifier [ [AS] correlation-name]
SET ROW = row-update-value
[used-index-SQL-optimization-specification]
[WHERE search-condition]
[WITH ROLLBACK]
- DELETE FROM [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.
- The PREPARE statement and the EXECUTE statement that executes the SQL statement preprocessed by the PREPARE statement should be executed in the same transaction.
- 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.
- 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].
- 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 9 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.
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];
For other data types, assign the value 0.
- The BLOB type, BINARY with a maximum length of 32,001 bytes or greater, and the abstract data type cannot be specified.
- If the array of embedded variables specified in the USING clause are character data type that use the default character set, and if a different character set is used for the input ? parameter, it is automatically converted to the character set used for the input ? parameter.
Notes
- The EXECUTE statement can be executed any number of times per SQL statement that is preprocessed by the PREPARE statement.
- 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
- 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;
- Update the values of the product code (PCODE) and quantity in stock (SQTY) specified in the array variable written in C to the values listed in the following tables:
Table 4-4 Product code and quantity in stock stored in table (before updating)
Product code | New quantity in stock |
---|
'101M' | 40 |
'101L' | 70 |
'201M' | 15 |
'202M' | 28 |
'302S' | 7 |
Table 4-5 Product code and quantity in stock subject to updating (assigned to an embedded variable array)
Product code | New quantity in stock |
---|
'101M' | 35 |
'101L' | 62 |
'201M' | 13 |
'202M' | 10 |
'302S' | 6 |
Table 4-6 Product code and quantity in stock stored in table (after updating)
Product code | New 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