Dynamic Parameters

Values and Value Expressions › Dynamic Parameters

Dynamic Parameters

A dynamic parameter is a value supplied during the execution of a dynamic SQL statement. It allows a statement, such as an UPDATE or INSERT statement, to be prepared once but executed multiple times with different input values for each execution. It also allows a SELECT statement to be prepared once but be used with different selection criteria to retrieve different rows.

Using Dynamic Parameters

You indicate the presence of a dynamic parameter by specifying a dynamic parameter marker within the text of the SQL statement being prepared. A dynamic parameter marker is the question mark ("?") symbol. It can be specified anywhere that an input host variable can be specified, except as noted below.

When executing an SQL statement that contains one or more dynamic parameter markers, you supply values to be substituted in place of the markers through the USING clause on the EXECUTE statement. If the prepared SQL statement is a SELECT, the substitution values are supplied through the USING clause on the OPEN statement.

Parameter Data Types

When a statement containing a dynamic parameter marker is prepared, CA IDMS infers the data type of the substitution value by examining the context where the dynamic parameter marker appears. You may use the DESCRIBE statement (or the DESCRIBE option on the PREPARE statement) to determine the assumptions that CA IDMS has made about the data types of the dynamic parameters.

The data types of the actual substitution values do not need to be the same as those assumed by CA IDMS. However, they must be compatible with respect to the assignment operator. That is, the value passed at the time the statement is executed must be capable of being assigned to a variable of the data type assumed by CA IDMS.

Note: For more information about the assignment operation, see Comparison, Assignment, Arithmetic, and Concatenation Operations.

The following table outlines how CA IDMS infers the data type of a dynamic parameter from the context in which it is used.

Context	data type of dynamic parameter

Date-time value expressions
? + date or date + ?	Date duration (DECIMAL(8,0))
? + time or time + ?	Time duration (DECIMAL(6,0))
? + timestamp or timestamp + ?	Time duration (DECIMAL(6,0))
date - ?	Date duration (DECIMAL(8,0))
time - ?	Time duration (DECIMAL(6,0))
timestamp - ?	Time duration (DECIMAL(6,0))
? - date	DATE
? - time	TIME
? + labeled duration or ? - labeled duration	DATE if duration is DAY, MONTH, YEAR; TIME if duration is HOUR, MINUTE, SECOND
v + ? DAY/MONTH/YEAR/ HOUR/MINUTE/SECOND	DECIMAL(31,6)
Other value expressions
? arithmetic-operator v or v arithmetic-operator ?	Same as v
? \|\| v or v \|\| ?	VARCHAR (256)
Scalar functions
CAST (? AS data-type)	data-type
CHAR_LENGTH (?)	VARCHAR (256)
CHARACTER_LENGTH (?)	VARCHAR (256)
COALESCE (v,...?,...)	Same as v (The first entry in the list cannot be a dynamic parameter)
CONCAT (?,?)	First and second VARCHAR (256)
CONVERT(?, data-type)	data-type
FLOAT (?)	DOUBLE PRECISION
HEX (?)	VARCHAR (256)
IFNULL(v,?)	Same as v (The first entry in the list cannot be a dynamic parameter)
INTEGER (?)	INTEGER
LCASE(?)	VARCHAR (256)
LEFT (?, ?)	First is VARCHAR (256); second is INTEGER
LENGTH (?)	VARCHAR (256)
LOCATE (?, ?, ?)	First and second are VARCHAR (256); third is INTEGER
LOWER (?)	VARCHAR (256)
LTRIM (?)	VARCHAR (256)
POSITION (? IN ?)	Both are VARCHAR (256)
PROFILE (?)	VARCHAR (256)
RTRIM (?)	VARCHAR (256)
SUBSTR (?, ?, ?) or SUBSTRING (? FROM ? FOR ?)	First is VARCHAR (256); second and third are INTEGER
TRIM (? FROM ?)	Both are VARCHAR (256)
UCASE (?)	VARCHAR (256)
UPPER (?)	VARCHAR (256)
VALUE (v,...,?,...)	Same as v (The first entry in the list cannot be a dynamic parameter)
VARGRAPHIC (?)	VARCHAR (256)
Predicates
? comparison-operator v or v comparison-operator ?	Same as v
? LIKE ? ESCAPE ?	All are VARCHAR (256)
? BETWEEN v1 AND v2	Same as v1
v BETWEEN ? AND ?	Both same as v
v1 IN (v2,...,?,...)	Same as v1
? IN ( v1, v2, ...)	Same as v1
? comparison-operator ANY/ALL (subquery)	Same data type as result of subquery.
? comparison-operator (subquery)	Same data type as result of subquery.
Update values
UPDATE ... SET column = ?	Same as column.
INSERT ... VALUES (...,?,...)	Same as target column.

Note: Dynamic parameters are always nullable.

Data Type Conversion Considerations

CA IDMS uses the rules in the previous table to infer a data type for a dynamic parameter. The actual value of the parameter may have a different data type provided the two are compatible with regard to the assignment operator. However, in certain cases, compatibility may not be sufficient. For example, if you wish to supply a very long character string as an input value and CA IDMS has inferred a data type of VARCHAR(256), the input value is truncated to a length of 256. To circumvent this, you can use the CAST function to override the default data type, as in the next example:

UPDATE MY.TEXT
  SET STRING =
    CHAR(CURRENT TIMESTAMP) || '**' || CAST (? AS VARCHAR(1000))
  WHERE ...

As an operand of a concatenate symbol, CA IDMS would normally assign VARCHAR (256) as the data type for the dynamic parameter. However, by using a CAST function, the parameter is instead assigned a data type of VARCHAR (1000).

Restrictions in the Use of Dynamic Parameters

Dynamic parameter markers may not be used in the following contexts:

Following a unary + or - operator
By itself as an entry in the select-list of a query expression
As both operands of a dyadic operator (except for the concatenation operator)
As the first entry in the operand list of the COALESCE and VALUE functions
As both the first and second or first and third operands of a BETWEEN predicate
As both the first operand and any entry in the second operand of the IN predicate
As the operand in the following functions: CHAR, DATE, DAY, DAYS, DECIMAL, DIGITS, MINUTE, MONTH, MICROSECOND, OCTET_LENGTH, SECOND, TIME, TIMESTAMP, YEAR

Note: The CAST function may be used to assign a data type to a parameter that otherwise would not be allowed within the desired context. For example, if you want to use a dynamic parameter as the first operand in the VALUE function, you may embed the parameter in a CAST function to assign a default data type.

Statement Options

For more information, refer to the options that make use of dynamic parameters on the following statements:

DESCRIBE
EXECUTE
OPEN
PREPARE