Re: Possible regression: setNull() usage changed from 7.4 to

On 4/13/06 at 7:07 PM, books(at)ejurka(dot)com (Kris Jurka) wrote:

>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

Kris,

Thanks for your reply. Yes, the example I posted is a simplified test
case to duplicate the error I was receiving in some other query. The
query is checking for duplicate usernames, and we use the same query
whether a user is being edited or not. We pass in a String username for
parameter 1 and an Integer userid (that could be null, in the case of an
Add instead of an Edit) for parameters 2 and 3.

select count(*) from users where usernam = ? and (? is null or userid !=
?)

If it's an Add, and we pass in 'blah' and null, the error message I
posted about is returned. If it's an edit, then the userid of the user
being edited is excluded from the check by virtue of the (? is null or
userid != ?) section.

It makes complete sense what you're saying. I'm assuming that it was the
8.0 JDBC driver that changed the behavior of PreparedStatement to use
server-side prepared statements instead of string replacement?

Something I successfully tried on the server was:
PREPARE test_null (anyelement) AS SELECT COUNT(*) FROM test_null WHERE
$1 IS NULL;

However I couldn't find, and don't think there is, a java.sql.Types
value that corresponds to the postgresql anyelement type.

The whole application was written around the idea that a query like this
works. If there is any way to work around this, I'd love to hear it. Is
there any way to force a PreparedStatement to use the old 7.4 string
replacement method instead of the server-side prepared statement?

Or, alternately, do you see anything wrong with the following:

select count(*) from users where usernam = ? and
(?::integer is null or userid != ?)

stmt.setString(1,'blah');
stmt.setNull(2,java.sql.Types.NULL);
stmt.setNull(3,java.sql.Types.NULL);

This code gives the server the type information it needs by doing an
explicit cast. It seems to work, but was wondering if there are any
problems you see with it (or if you have a better/cleaner way to go
about it).

Thanks again,
-- Jeff Hubbach