|
Where dynamic parameters are allowed
You can use dynamic parameters anywhere in an expression where
their data type can be easily deduced.
- Use as the first operand of BETWEEN is allowed if one of the second and
third operands is not also a dynamic parameter. The type of the first operand
is assumed to be the type of the non-dynamic parameter, or the union result
of their types if both are not dynamic parameters.
WHERE ? BETWEEN DATE('1996-01-01') AND ?
-- types assumed to be DATE
- Use as the second or third operand of BETWEEN is allowed. Type is assumed
to be the type of the left operand.
WHERE DATE('1996-01-01') BETWEEN ? AND ?
-- types assumed to be DATE
- Use as the left operand of an IN list is allowed if at
least one item in the list is not itself a dynamic parameter. Type for the
left operand is assumed to be the union result of the types of the non-dynamic
parameters in the list.
WHERE ? NOT IN (?, ?, 'Santiago')
-- types assumed to be CHAR
- Use in the values list in an IN predicate is allowed if the first operand
is not a dynamic parameter or its type was determined in the previous rule.
Type of the dynamic parameters appearing in the values list is assumed to
be the type of the left operand.
WHERE FloatColumn IN (?, ?, ?)
-- types assumed to be FLOAT
- For the binary operators +, -, *, /, AND, OR, <, >,
=, <>, <=, and >=, use of a dynamic parameter as one operand but
not both is permitted. Its type is taken from the other side.
WHERE ? < CURRENT_TIMESTAMP
-- type assumed to be a TIMESTAMP
- Use in a CAST is always permitted. This gives the dynamic parameter a
type.
CALL valueOf(CAST (? AS VARCHAR(10)))
- Use on either or both sides of LIKE operator is permitted. When used on
the left, the type of the dynamic parameter is set to the type of the right
operand, but with the maximum allowed length for the type. When used on the
right, the type is assumed to be of the same length and type as the left operand.
(LIKE is permitted on CHAR and VARCHAR types; see Concatenation operator for
more information.)
WHERE ? LIKE 'Santi%'
-- type assumed to be CHAR with a length of
-- java.lang.Integer.MAX_VALUE
- A ? parameter is allowed by itself on only one side of the || operator.
That is, "? || ?" is not allowed. The type of a ? parameter on one side of
a || operator is determined by the type of the expression on the other side
of the || operator. If the expression on the other side is a CHAR or VARCHAR,
the type of the parameter is VARCHAR with the maximum allowed length for the
type. If the expression on the other side is a CHAR FOR BIT DATA or VARCHAR
FOR BIT DATA type, the type of the parameter is VARCHAR FOR BIT DATA with
the maximum allowed length for the type.
SELECT BITcolumn || ?
FROM UserTable
-- Type assumed to be CHAR FOR BIT DATA of length specified for BITcolumn
- In
a conditional expression, which uses a ?, use of a dynamic parameter (which
is also represented as a ?) is allowed. The type of a dynamic parameter as
the first operand is assumed to be boolean. Only one of the second and third
operands can be a dynamic parameter, and its type will be assumed to be the
same as that of the other (that is, the third and second operand, respectively).
SELECT c1 IS NULL ? ? : c1
-- allows you to specify a "default" value at execution time
-- dynamic parameter assumed to be the type of c1
-- you cannot have dynamic parameters on both sides
-- of the :
- A dynamic parameter is allowed as an item in the values list or select
list of an INSERT statement. The type of the dynamic parameter is assumed
to be the type of the target column.
INSERT INTO t VALUES (?)
-- dynamic parameter assumed to be the type
-- of the only column in table t
INSERT INTO t SELECT ?
FROM t2
-- not allowed
- A ? parameter in a comparison with a subquery takes its type from the
expression being selected by the subquery. For example:
SELECT *
FROM tab1
WHERE ? = (SELECT x FROM tab2)
SELECT *
FROM tab1
WHERE ? = ANY (SELECT x FROM tab2)
-- In both cases, the type of the dynamic parameter is
-- assumed to be the same as the type of tab2.x.
- A dynamic parameter is allowed as the value in an UPDATE statement. The
type of the dynamic parameter is assumed to be the type of the column in the
target table.
UPDATE t2 SET c2 =? -- type is assumed to be type of c2
- Dynamic parameters are allowed as the operand of the unary operators -
or +. For example:
CREATE TABLE t1 (c11 INT, c12 SMALLINT, c13 DOUBLE, c14 CHAR(3))
SELECT * FROM t1 WHERE c11 BETWEEN -? AND +?
-- The type of both of the unary operators is INT
-- based on the context in which they are used (that is,
-- because c11 is INT, the unary parameters also get the
-- type INT.
- LENGTH allow a dynamic parameter. The type is assumed to be a maximum
length VARCHAR type.
SELECT LENGTH(?)
- Qualified comparisons.
? = SOME (SELECT 1 FROM t)
-- is valid. Dynamic parameter assumed to be INTEGER type
1 = SOME (SELECT ? FROM t)
-- is valid. Dynamic parameter assumed to be INTEGER type.
- A dynamic parameter is allowed as the left operand of an IS expression
and is assumed to be a boolean.
Once the type of a dynamic parameter is determined based on the
expression it is in, that expression is allowed anywhere it would normally
be allowed if it did not include a dynamic parameter.
|
