SQL-J Language Reference
Page 115 of 121

Dynamic Parameters

You can prepare statements that are allowed to have parameters for which the value is not specified when the statement is prepared using PreparedStatement methods in the JDBC API. These parameters are called dynamic parameters and are represented by a ?.

The JDBC API documents refer to dynamic parameters as IN, INOUT, or OUT parameters. In SQL-J, they are always IN parameters.

You must specify values for them before executing the statement. The values specified must match the types expected.

Dynamic Parameters Example

PreparedStatement ps2 = conn.prepareStatement(
    "UPDATE HotelAvailability SET rooms_available = " +
    "(rooms_available - ?) WHERE hotel_id = ? " +     "AND booking_date BETWEEN ? AND ?"); -- this sample code sets the values of dynamic parameters -- to be the values of program variables ps2.setInt(1, numberRooms); ps2.setInt(2, theHotel.hotelId); ps2.setDate(3, arrival); ps2.setDate(4, departure); updateCount = ps2.executeUpdate();

Where Dynamic Parameters Are Allowed

You can use dynamic parameters anywhere in an expression where their data type can be easily deduced.

  1. 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 DATES

  2. 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 DATES

  3. 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

  4. 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 rule 3. 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

  5. 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

  6. Use in a CAST is always permitted. This gives the dynamic parameter a type.

    CALL (CLASS java.lang.Integer).valueOf(CAST (? AS VARCHAR(10)))

  7. 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 and "LIKE" in Table 1-10 for more information.)

    WHERE ? LIKE 'Santi%'
    -- type assumed to be CHAR with a length of
    -- java.lang.Integer.MAX_VALUE

  8. 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 BIT or BIT VARYING, the type of the parameter is BIT VARYING with the maximum allowed length for the type.

    SELECT BITcolumn || ?
    FROM UserTable
    -- Type assumed to be BIT of length specified for BITcolumn

  9. 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). (For more information about conditional expressions, see Conditional (?:).)

    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 :

  10. INSTANCEOF does not permit use of dynamic parameters.
  11. Dynamic parameters are not permitted as receivers in a method invocation (see Method Invocation).
  12. 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. A ? parameter is not allowed by itself in any select list, including the select list of a subquery, unless there is a corresponding column in a UNION (see no. 23, below) that is not dynamic.

    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

  13. 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.

  14. 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

  15. A dynamic parameter is allowed as the sole item in a WHERE clause. Its type is assumed to be boolean.

    WHERE ?

  16. A dynamic parameter is not allowed as the operand of the unary operators - or +.
  17. A dynamic parameter is allowed as the operand of a NOT operator. Its type is assumed to be boolean.

    WHERE NOT ?

  18. A dynamic parameter as a method parameter is allowed if there is only one method with a matching name and number of parameters and that parameter type is not a Java primitive type.

    VALUES (CLASS java.lang.Integer).toString(?)
    -- not VALID. You need to use CAST (? as INT) instead of ?.

  19. BIT_LENGTH and OCTET_LENGTH allow a dynamic parameter operand. It is assumed to be a maximum-length BIT VARYING type.

    SELECT BIT_LENGTH(?)

  20. CHAR_LENGTH and CHARACTER_LENGTH allow a dynamic parameter. The type is assumed to be a maximum length VARCHAR type.

    SELECT CHAR_LENGTH(?)

  21. EXTRACT does not allow any dynamic parameters.
  22. Quantified comparisons.

    ? = SOME (SELECT 1 FROM t)
    -- is valid. Dynamic parameter assumed to be INT type

    1 = SOME (SELECT ? FROM t)
    -- is valid. Dynamic parameter assumed to be INT type.

  23. A dynamic parameter is allowed to represent a column if it appears in a UNION expression; Cloudscape can infer the data type from the corresponding column in the UNION.

    SELECT ?
    FROM t
    UNION SELECT 1
    FROM t
    -- dynamic parameter assumed to be INT

    VALUES 1 UNION VALUES ?
    -- dynamic parameter assumed to be INT

  24. 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. For example, above we said that a dynamic parameter cannot be used as the operand of a unary -. It can, however, appear within an expression that is the operand of a unary minus, such as:

- (1+?)

The dynamic parameter is assumed to be an INT (because the binary operator +'s other operand is of the type INT). Because we know its type, it is allowed as the operand of a unary -.

The use of a ? in a publication definition (for a Cloudscape synchronization system) has a different form. For information, see the Cloudscape Synchronization Guide.

Java Expressions

This section discusses some of the Java extensions to SQL-J. Defining a column to hold a Java data type is discussed in CREATE TABLE statement. This section covers: