background image
<< Explanation and Example | The non-dynamic parameters >>

Dynamic parameters

<< Explanation and Example | The non-dynamic parameters >>
Derby Reference Manual
99
Operator
Explanation and Example
Syntax
>=, <>) with ALL or ANY or SOME
applied.
Operates on table subqueries, which
can return multiple rows but must
return a single column.
If ALL is used, the comparison must
be true for all values returned by the
table subquery. If ANY or SOME
is used, the comparison must be
true for at least one value of the
table subquery. ANY and SOME are
equivalent.
WHERE normal_rate < ALL
(SELECT budget/550 FROM Groups)
{
ALL |
ANY |
SOME
}
TableSubquery
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, they are always IN parameters.
New: Derby supports the interface ParameterMetaData, new in JDBC 3.0. This interface
describes the number, type, and properties of prepared statement parameters. See the
Derby Developer's Guide for more information.
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 DATE