On Thu, 13 Apr 2006, Jeff Hubbach wrote:
> In the process of upgrading an app, I came across a change in behavior
> of PreparedStatement.setNull(). The behavior of the driver for 7.3 and
> 7.4 is consistent, a call to:
> stmt.setNull(1,java.sql.Types.NULL);
> succeeds. However, in 8.0 and up (including the 8.2 dev driver), this
> call fails with a "Could not determine data type" error.
>
> PreparedStatement st = conn.prepareStatement("select count(*) from
> test_null where ? is null");
> st.setNull(1,java.sql.Types.NULL); // Fails
> //st.setNull(1,java.sql.Types.INTEGER); // Works
>
This is an expected change when the driver was modified to use server side
prepared statements instead of just interpolating text values into the
query string. When given a null value, the driver cannot try to infer any
type information about it and must let the server determine what the type
is. Your example is a situation where the server cannot possible do so.
If you had written "WHERE intcol = ?", then it could infer that the
parameter should be an integer. For a situation like "? is null" you must
provide the server with the parameter type (integer is a real type,
Types.NULL, Types.OTHER are not). The driver cannot pick an arbitrary
type because if the server infers a different type then an
appropriate cast must exist or the query will bail out.
The server generally does a reasonable job of inferring types, the example
you've shown is an awfully contrived one, why would you need the server to
tell you if a value was null?
Kris Jurka