VADELIA |
SADELIA |
WADELIA |
EADELIA |
|
(I/B) (C/S) |
(B) (C/S) |
(I/B) (C/S) |
(B) (C/S) |
Important: this instruction is not compatible with AS/400 server parts.
Section for use
DECLARATION
Syntax
SQL_STATEMENT SQLStatementName StatementType
StatementType |
→ |
*CLONE | *REFERENCE | *VALUE | Null |
Description
This instruction declares a SQL statement which will be built via the BUILD_SQL_STMT instruction and used by the following instructions:
- CURSOR to define a dynamic cursor,
- EXEC_SQL to define a dynamic EXEC_SQL,
- LOADING to define a *SQL_D load and
- LOAD_SQL_LST to define a dynamic condition.
The *CLONE instruction specifies that the variables used in the BUILD_SQL_STMT instruction will be cloned, replaced by a ? in the SQL statement, and that it is the cloned variable (which will contain the value the variable had at the time of BUILD_SQL_STMT) which will be used as a host variable when executing the SQL query (execution of OPEN_SQL_C, EXEC_SQL, LOADING_LST or LOAD_SQL_LST).
The *REFERENCE instruction specifies that the variables used in the BUILD_SQL_STMT instruction will be replaced by a ? in the SQL statement, and it is these variables which will be used as host variables when executing the SQL query (execution of OPEN_SQL_C, EXEC_SQL, LOADING_LST or LOAD_SQL_LST), with the value they will have when they are executed (which may be different from the value they had at the time of BUILD_SQL_STMT).
The *VALUE statement specifies that the variables used in the BUILD_SQL_STMT instruction will be replaced by their sanitized value (in the SQL sense) in the SQL statement.
Not specifying a statement type is equivalent to the *CLONE type.
Examples
Example 1:
SQL_STATEMENT stmt1 *CLONE
...
W1 = 'abc'
BUILD_SQL_STMT stmt1 *INIT 'DELETE FROM MY_TABLE WHERE MyCol = :W1'
* the SQL statement has the "DELETE FROM MY_TABLE WHERE MyCol = ?" value
W1 = 'xyz'
EXEC_SQL (:stmt1)
* deletion of records with column MyCol = 'abc'
Example 2:
SQL_STATEMENT stmt1 *REFERENCE
...
W1 = 'abc'
BUILD_SQL_STMT stmt1 *INIT 'DELETE FROM MY_TABLE WHERE MyCol = :W1'
* the SQL statement has the "DELETE FROM MY_TABLE WHERE MyCol = ?" value
W1 = 'xyz'
EXEC_SQL (:stmt1)
* deletion of records with column MyCol = 'xyz'
Example 3:
SQL_STATEMENT stmt1 *VALUE
...
W1 = 'abc' ↑ Top of page
BUILD_SQL_STMT stmt1 *INIT 'DELETE FROM MY_TABLE WHERE MyCol = :W1'
* the SQL statement has the "DELETE FROM MY_TABLE WHERE MyCol = 'abc' " value
W1 = 'xyz'
EXEC_SQL (:stmt1)
* deletion of records with column MyCol = 'abc'