An SQLDA should be defined whenever you use dynamic SQL.
statement_name cannot exceed 18 characters.
Whether specified as a string expression or as a host variable, the dynamic SQL statement text can be as long as 32 kbytes (including SQL text, USING data, and parcel overhead).
If specified as a host variable, the statement string must follow the rules for SQL strings for the client programming language.
| IN this language … | statement_string is a … |
|---|---|
| COBOL | non-numeric literal. |
| C | |
| PL/I | character string expression. |
- Be a single statement or a multistatement request
- Incorporate an EXEC statement
- Incorporate a data returning statement
If the statement string variable is varchar, the statement text cannot be longer than 64K.
- Any of the following specific SQL statements:
ABORT EXECUTE IMMEDIATE BEGIN TRANSACTION FETCH CHECKPOINT LOGOFF CLOSE LOGON COMMIT OPEN CONNECT POSITION DESCRIBE PREPARE ECHO REWIND END TRANSACTION ROLLBACK EXECUTE - A Preprocessor2 declarative.
The dynamic SQL statement can include parameter markers, or placeholder tokens (the question mark), where any literal, particularly a host variable, reference is legal.
Values are supplied to the statement by means of the USING clause of the OPEN and EXECUTE statements.
- A typed placeholder has its data type explicitly cast. For example, part_no is a typed placeholder in the following UPDATE statement.
UPDATE parts SET part_no = (CAST(? AS INTEGER)) WHERE vendor_no = ?;
This action establishes that the data type of the variable at runtime will either be the type cast for the placeholder or one that is convertible to that type.
- An untyped placeholder is one for which the data type is determined by its context.
For example, in the following statement, the type of the untyped placeholder in the WHERE clause is the same as that of vendor_no.
UPDATE parts SET part_no = (CAST(? AS INTEGER) WHERE vendor_no = ?;
Using placeholders within a CASE expression as the result of a THEN/ELSE clause is valid only when at least one other result in the CASE expression is not a placeholder or the NULL keyword.
- As the operand of a monadic operator.
For example, + ? is not valid.
- As both operands of a dyadic operator.
For example, ? + ? is not valid.
- As both operands of a comparison operator.
For example, the following SELECT statement is not valid.
SELECT * FROM table_name WHERE ? = ?
If you were to replace either placeholder with 0+, the comparison becomes valid because such a value provides enough context to determine that the data type is numeric.
By extension, it is also true that placeholders cannot be used to denote corresponding fields in a comparison.
For example, the following comparison is not valid because the first value in each pair has an unknown type that cannot be determined from the context at PREPARE time:
(?,X) > (?,Y)
At the same time, the following comparison is valid because the types can be determined from the context at PREPARE time.
(?,X) > (Y,?)
- As both operands in a POSITION function.
- As the only operand in an UPPER function.
- As the only operand in a LOWER function.
- As either the second or third operand in a TRIM function.
- As the FROM operand in an EXTRACT function.
- As the first operand in a TRANSLATE function.
- As the argument for any aggregate function.
- As any component of the left hand operand of IS [NOT] NULL.
- As the second component of either operand of an OVERLAPS comparison.
- As solitary select item expressions.
For example, the following SELECT is not valid.
SELECT ? AS alias_name FROM table_name;
The following SELECT statement is valid because the placeholder is used as part of an expression and not as the entire expression in the SELECT list.
SELECT 0 + ? AS alias_name FROM table_name;
- Executing the PREPARE statement without the INTO and USING clauses.
- Executing a DESCRIBE statement for the prepared statement using the INTO and USING clauses.
- PREPARE cannot be executed as a dynamic SQL statement.