Your Browser is not longer supported

Please use Google Chrome, Mozilla Firefox or Microsoft Edge to view the page correctly
Loading...

{{viewport.spaceProperty.prod}}

PREPARE - Prepare dynamic statement

You use PREPARE to prepare a dynamic statement or the cursor description of a dynamic cursor for execution at a later time.

You execute a statement prepared with PREPARE with the EXECUTE statement.

The statement identifier used in PREPARE for a cursor description is used to declare a dynamic cursor with DECLARE CURSOR. You open the dynamic cursor with OPEN.


PREPARE statement_id FROM statement_variable

statement_variable ::= : host_variable


statement_id

Name of the dynamic statement or cursor description. You can use this name to reference the statement or cursor description in the compilation unit.


statement_variable

Alphanumeric host variable containing the statement text. The host variable can also be of the type CHAR(n), where 256 <= n <= 32000.

The following conditions must be satisfied:

    • The statement text cannot include any host variables. Question marks are specified as placeholders for unknown values (see "Rules for placeholders"). The placeholders are supplied with values in the USING clause of an EXECUTE or OPEN statement.

    • The statement text may not contain comments in the host language. Pragmas (--%PRAGMA) are exceptions.

    • A SELECT statement cannot include an INTO clause.

    • The RETURN INTO clause cannot be specified in an INSERT statement. The CLI call SQL_DIAG SEQ_GET is available to allow you to use the function you probably know from static INSERT statements. It enables you to simulate RETURN INTO (see "SQL_DIAG_SEQ_GET - SQLdsg").

If statement_id is defined for a dynamic cursor, but the statement is not a cursor description, an error is reported. The statement is prepared successfully despite this fact and can be executed with EXECUTE.

Rules for placeholders

A placeholder for an input value in a dynamic statement is represented by a question mark. You can specify a placeholder if the operands and operators associated with the placeholder uniquely define the data type of the placeholder.

Below you will find a summary of the positions permitted or not permitted for placeholders grouped according to whether a monadic or dyadic operator, a range or element query or a pattern comparison is involved, as well as the positions permitted or not permitted for CASE expressions, CAST expressions, numeric functions, string functions, SELECT list, INSERT, UPDATE and MERGE. The data type of a placeholder is also specified for permitted placeholders.

If a placeholder is not permitted at a certain position, this also applies even if the placeholder is enclosed in parentheses.


Example

not permitted: (?)+(?)

Monadic operators

No placeholders are permitted for monadic operators. The following cases are therefore not permitted:

    • The operand of a monadic operator cannot be a placeholder (e.g. -?).

    • The operand for IS [NOT] NULL cannot be a placeholder (e.g.? IS NULL).

    • The argument of an aggregate function cannot be a placeholder (e.g. AVG(?)).

Dyadic operators

In the case of dyadic operators, only one of the operands can be a placeholder.


Example

permitted: ?+1, ?<100, p=?

not permitted: ?=?


Data type of the placeholder

If one of the operands for concatenation is a placeholder (?||“...” or “...”||?), the data type of the placeholder is VARCHAR(32000) or NVARCHAR(16000).

For all other dyadic operators, the data type of the placeholder is the same as the data type of the other operand.

Range query

If the first operand in a range query is a placeholder, neither of the other two operands can be a placeholder.


Example

permitted: ? BETWEEN 100 AND 500
        50 BETWEEN ? AND ?
not permitted: ? BETWEEN 100 AND ?


Data type of the placeholder

The data type of the placeholder is derived from the data types of the values of the other operands which are not placeholders (see "Data type of the placeholder in CASE, BETWEEN and IN").

Element query

In an element query, neither the first operand nor any of the elements in the list may be placeholders.


Example

permitted: ? IN ('Frankfurt','Munich','Hamburg')
           x IN (?,'Munich','Hamburg')
           ? IN ('Frankfurt',?,?)
           x IN (?,?,?)
           ? NOT IN (SELECT order_num FROM service WHERE order_text='Training')
not permitted: ? IN (?,?,?)


Data type of the placeholder

    • If the first operand is a placeholder and the second operand is a subquery, the data type of the placeholder is the same as the data type of the derived column.

    • If the first operand is a placeholder and the second operand is a list of expressions, the data type of the placeholder is derived from the data types of the elements in the list that are not placeholders (see "Data type of the placeholder in CASE, BETWEEN and IN").

    • If an element in the list is a placeholder and the first element is not a placeholder, the data type of the placeholder is the same as the data type of the first operand.

Pattern comparison

In a pattern comparison, the second and third operand may be placeholders.


Example

permitted: x LIKE ? ESCAPE ?

not permitted: ? LIKE y ESCAPE ?


Data type of the placeholder

The data type of the placeholder is VARCHAR(32000) or NVARCHAR(16000).

CASE expression

Not all the operands in a CASE expression may be placeholders. If the CASE expression contains one or more THEN or ELSE clauses, not all the operands in these clauses can be placeholders. The following cases are therefore not permitted:

    • In a simple CASE expression, the first operand (expression after CASE) is a placeholder and the operand in the WHEN clause is a placeholder or - if there are several WHEN clauses - all the operands in the WHEN clauses are placeholders.

    • In a simple CASE expression, all the THEN clauses and the ELSE clause contain placeholders.

      Example

      permitted: CASE ?
          WHEN 1 THEN 10
          WHEN 2 THEN 20
          WHEN ? THEN 30
          WHEN ? THEN 30
          ELSE 50 END
not permitted: CASE ?
                WHEN ? THEN 10
                WHEN ? THEN 20
                WHEN ? THEN 30
                WHEN ? THEN 30
                ELSE 50 END
not permitted: CASE x
                WHEN 1 THEN ?
                WHEN 2 THEN ?
                ELSE ? END

    • In a CASE expression with a search condition, all the THEN clauses and the ELSE clause contain placeholders.

      Example

      permitted: CASE
         WHEN ord_stat_num= 1 THEN ?
         WHEN ord_stat_num= 2 THEN ?
         WHEN ord_stat_num > 2 AND ord_stat_num < 5 THEN ?
         ELSE 50 END
not permitted: CASE
               WHEN ord_stat_num= 1 THEN ?
               WHEN ord_stat_num= 2 THEN ?
               WHEN ord_stat_num > 2 AND ord_stat_num < 5 THEN ?
               ELSE ? END

    • In a CASE expression with NULLIF, both operands are placeholders (e.g. NULLIF (?,?))

    • In a CASE expression with COALESCE, all the operands are placeholders (e.g. COALESCE (?,?,?))


Data type of the placeholder

The data type of the placeholder in a CASE expression depends on the data types of the other operands which are not placeholders.

If an operand of a CASE expression with NULLIF is a placeholder, its data type corresponds to the data type of the other operand.

If several of the other operands are without placeholders, the following rules apply:

    • If the first operand of a simple CASE expression is a placeholder and/or if the CASE expression contains one or more placeholders as operands in its WHEN clause or clauses, its data type is derived from the data types of the other operands which are not placeholders and not operands of the THEN or ELSE clause(s).

    • If a CASE expression with a search condition or a simple CASE expression contains placeholders in the THEN clause(s) and/or the ELSE clause, its data type is derived from that of the other THEN or ELSE clause operands which are not placeholders.

    • If an operand of a CASE expression with COALESCE is a placeholder, its data type is derived from the data types of the other operands which are not placeholders.

The rules described in "Data type of the placeholder in CASE, BETWEEN and IN" apply to the calculation of the placeholder data type.

CAST expression

No restrictions

Data type of the placeholder

The data type of the placeholder in a CAST expression corresponds to the data type of the result value of the CAST expression.

Numeric functions

In the numeric function POSITION, both operands cannot be placeholders (e.g. POSITION (? IN ?)).

Data type of the placeholder

The data type of the placeholders in the numeric functions POSITION, OCTET_LENGTH and CHAR_LENGTH is VARCHAR(32000) or NVARCHAR(16000).

For the numeric function JULIAN_DAY_OF_DATE, the data type of the placeholder is DATE.

String functions

The following are not permitted in string functions:

    • In the string functions LOWER and UPPER, the operands cannot be placeholders.

    • In the string function TRIM, the first operand (character) and/or the second operand (expression) cannot be placeholders (e.g. TRIM (TRAILING FROM ?)).

    • In the string function SUBSTRING, the first operand cannot be a placeholder (e.g. SUBSTRING ? FROM 1 FOR 5)).


Data type of the placeholder

In the string function SUBSTRING, the data type of the placeholder is
NUMERIC(31,0).

Time functions

No restrictions


Data type of the placeholder

For the time function DATE_OF_JULIAN_DAY the data type of the placeholder is INTEGER.

SELECT (list)

In a SELECT expression, an element in the SELECT list may not consist of only one placeholder.


Example

permitted: SELECT 3+? FROM ...

not permitted: SELECT ?,x,p FROM ...

INSERT, UPDATE, MERGE

You can specify a placeholder as the column value of an atomic column and for an element in a multiple column.


Example

permitted: INSERT INTO tab (x, ...) VALUES (?, ...)
           INSERT INTO t (x) VALUES <..., ?, ...>
           UPDATE tab SET x=?
           UPDATE t SET x=<..., ?, ...>


Data type of the placeholder

The data type of the placeholder is the data type of the column. In the case of a multiple column with a dimension > 1, the placeholder is also multiple with the same dimension. Otherwise, the placeholder is atomic.

Data type of the placeholder in CASE, BETWEEN and IN

In CASE expressions, area queries and element queries, the data type of the placeholder is derived in some cases from the data types of the other operands or elements which are not placeholders. In these cases, the following rules apply:

    • All the values of the other operands have the data type NCHAR:
      The value of the placeholder has the data type NCHAR with the greatest length.

    • At least one value of the other operands has the data type VARCHAR: The value of the placeholder is that with the data type VARCHAR and the greatest or greatest maximum length.

    • All the values of the other operands have the data type NCHAR:
      The value of the placeholder has the data type NCHAR with the greatest length.

    • At least one value of the other operands has the data type NVARCHAR: The value of the placeholder is that with the data type NVARCHAR and the greatest or greatest maximum length.

    • All values of the other operands are an integer or fixed-point type (INT, SMALLINT, NUMERIC, DEC):
      The value of the placeholder has the data type integer or fixed-point number.

      • The number of decimal places is the greatest number of decimal places among the different values of the other operands.

      • The total number of places is the greatest number of places before the decimal point plus the greatest number of decimal places among the different values of the other operands, but not more than 31.

    • At least one value of the other operands is of the type floating-point number (REAL, DOUBLE PRECISION, FLOAT); the others have any other numeric data type: The value of the placeholder has the data type DOUBLE PRECISION.

    • All the values of the other operands have the time data type:
      The value of the placeholder also has this data type.


Converting the placeholder data type using CAST

The rules for placeholders sometimes result in an undesired data type for a particular placeholder. You can avoid undesired data types by using the CAST expression (see section "CAST expression").


Example

In the following dynamic UPDATE statement, the placeholder represents a single-digit integer:

UPDATE t SET x=?+1

If, in the USING clause of the EXECUTE statement, the value 10 is specified for the placeholder, execution is not successful.

An UPDATE statement can be formulated to avoid this data type assignment:

UPDATE t SET x=CAST(? AS DEC(5,0))

Procedures

A procedure can be called using a dynamic CALL statement. If a procedure contains parameters of the type OUT or INOUT, the corresponding arguments must be specified in a dynamic CALL statement in the form of placeholders.

Assignments for PREPARE

The following SQL statement can be prepared with PREPARE:

ALTER SPACE

ALTER STOGROUP

ALTER TABLE

CALL

COMMIT

CREATE INDEX

CREATE FUNCTION

CREATE PROCEDURE

CREATE SCHEMA

CREATE SPACE

CREATE STOGROUP

CREATE SYSTEM_USER

CREATE TABLE

CREATE USER

CREATE VIEW

DELETE

DROP FUNCTION

DROP INDEX

DROP PROCEDURE

DROP SCHEMA

DROP SPACE

DROP STOGROUP

DROP SYSTEM_USER

DROP TABLE

DROP USER

DROP VIEW

GRANT

INSERT (without RETURN INTO clause)

MERGE

PERMIT

REORG STATISTICS

REVOKE

ROLLBACK

SELECT (without INTO clause)

SET CATALOG

SET SCHEMA

SET SESSION AUTHORIZATION

SET TRANSACTION

UPDATE


Additionally to these SQL statements, dynamic cursor descriptions and all the utility statements can also be prepared with PREPARE (see the “ SQL Reference Manual Part 2: Utilities”).


The following statements cannot be prepared with PREPARE:

ALLOCATE DESCRIPTOR

CLOSE

DEALLOCATE DESCRIPTOR

DECLARE CURSOR

DESCRIBE

EXECUTE

EXECUTE IMMEDIATE

FETCH

GET DESCRIPTOR

INCLUDE

OPEN

PREPARE

RESTORE

SET DESCRIPTOR

STORE

WHENEVER

Validity period of a prepared statement

An SQL statement prepared with PREPARE remains prepared for execution at least until the end of the current transaction. After the end of the transaction, you should prepare the statement again. If the plan buffer of the DBH still contains the access plan of the SQL statement contained in statement_variable, SESAM/SQL uses the existing access plan.

A statement prepared with PREPARE is lost if PREPARE is executed using the same statement_id in the same compilation unit and the same SQL session.

The prepared statement is also lost if the statement contains a reference to a dynamic cursor and the prepared cursor description for this cursor is lost.

Example

Prepare a description of the dynamic cursor CUR_SERVICE1 for subsequent execution. The contents of the host variable DESCRIPTION are defined using actions of the ESQL program host language.

DECLARE cur_service1 CURSOR FOR cur_description

PREPARE cur_description FROM :DESCRIPTION

See also

DECLARE CURSOR, EXECUTE, FETCH, OPEN

Powered by Atlassian Confluence and Scroll Viewport.