BUILD_SQL_STMT

Important: this instruction is not compatible with AS/400 server parts.

Section for use

All

Syntax

BUILD_SQL_STMT  SQLStatementName  InitOrValue

BUILD_SQL_STMT  SQLStatementName   GetMode StmtVarId 

Description 

This instruction builds a SQL statement. SQL statements built by this instruction are exempt from SQL injection.

The *INIT parameter reinitializes the SQL statement.

The AlphaConstant constant is added to the current value of the SQL statement. If this constant contains :variableId elements, these elements are replaced by a ? in the case of a *CLONE or REFERENCE-type SQL statement, or by their sanitized value (in the SQL sense) in the case of a *VALUE-type SQL statement.

Using the *GET_STATEMENT parameter places the content of the built statement in the StmtVarId variable.

Using the *GET_STMT_VALUE parameter places the content of the statement built with the value of the parameters in place of the ? character in the StmtVarId variable for *CLONE and *REFERENCE types.

Examples

Example 1

SQL_STATEMENT stmt1 *CLONE

CURSOR cur1 :Stmt1

W1 = 'ABC'
W2 = 45
BUILD_SQL_STMT stmt1 *INIT 'Select COL_1 FROM ENTITY1 WHERE COL1 <> :W1'
BUILD_SQL_STMT stmt1 ' AND COL_2 = :W2 '
BUILD_SQL_STMT stmt1 *GET_STATEMENT MyQuery
* the MyQuery variable has the 'Select COL_1 FROM ENTITY1 WHERE COL1 <> ? AND COL_2 = ?' value '
W1 = *BLANK
W2 = 0
OPEN_SQL_C cur1
* the cursor is open with substitution host variables having the value 'ABC' and 45 (value of the variables contained in the BUILD_SQL_STMT instruction when it is run)
READ_NX_SQL_C cur1 :WRESULT1
...
CLOSE_SQL_C cur1

Example 2:

SQL_STATEMENT stmt2 *REFERENCE

LIST mylist *REF_LDM(ENTITY1)

BUILD_SQL_STMT stmt2 *INIT
BUILD_SQL_STMT stmt2 'COL_1 = :W1 AND COL_2 = :W2'

W1 = 'ABC'

W2 = 45

LOAD_SQL_LST mylist ENTITY1 *COND(:stmt2)

* The internal cursor used to load mylist will be a cursor with substitution host values with the values 'ABC' and 45 (value of variables when LOAD_SQL_LST is executed).

Example 3:

SQL_STATEMENT stmt3 *REFERENCE

BUILD_SQL_STMT inst3 *INIT 'COL_1 <> :W1 AND COL_2 = :W2'

W1 = 'ABC'

W2 = 45

BUILD_SQL_STMT stmt3 *GET_STMT_VALUE MyQuery
* the variable MyQuery has the value 'Select COL_1 FROM ENTITY1 WHERE COL1 <> 'ABC' AND COL_2 = 45 '

Example 4 :

INSTRUCTION_SQL stmt4 *VALUE

BUILD_SQL_STMT inst4 *INIT 'COL_1 <> :W1 AND COL_2 = :W2'

W1 = 'ABC'

W2 = 45

BUILD_SQL_STMT stmt4 *RECUP_INSTR MaRequete
* the variable MyQuery has the value 'Select COL_1 FROM ENTITY1 WHERE COL1 <> 'ABC' AND COL_2 = 45 '

BUILD_SQL_STMT stmt4 *GET_STMT_VALUE MaRequete
* the variable MyQuery has the value 'Select COL_1 FROM ENTITY1 WHERE COL1 <> 'ABC' AND COL_2 = 45 '

*GET_STATEMENT and *GET_STMT_VALUE are identical in the case of a SQL_STATEMENT with the type *VALUE

↑ Top of page