Re: Bug: Cannot pass null in Parameter in Query for ISNULL

Hi Oliver,

Your response is not entirely unexpected however perplexing in light
of the fact that other JDBC drivers don't have this bug. I have tested
them.

You are basically describing to me the nature of the bug from the
perspective of the driver's internals.

We can really only be interested in the resolution of it not in a
workaround. That is because I had already included the workaround in
my testcase posted to the mailing list - for illustration purposes.

The reason for not being able to use workarounds is that we are using
JPA which is a layer that is not accessible for modification.

It would be nice if you could take on board the obvious fact that it
is nonsense to test for the "type" of null that -
1) is only used in a parameter
2) the database does not have a problem processing natively
3) is correctly coded with ISNULL.

Don't you think that it would be worth the trouble spending some extra
driver coding, to detect and allow this scenario and pass the
perfectly valid and correct query to the database?

Kind Regards,

Bernard

On Wed, 30 Nov 2011 20:04:43 +1300, you wrote:

>> Hi,
>>
>> Native PostgreSQL has no problem with queries like:
>>
>> select id from author a where null is null or a.name = null
>>
>> However the JDBC driver fails to process such a query with a
>> parameter:
>>
>> ERROR: could not determine data type of parameter $1

>This is specific to calling PreparedStatement.setObject(index, null).
>There is no type information provided when you call that, so it's not
>entirely surprising you can get that error.
>(Try a native PREPARE with a parameter type of "unknown" and you'll
>see the same thing - it's not only JDBC)

> This is specific to calling PreparedStatement.setObject(index, null).

> To avoid this, use one of these instead:
>
>  * PreparedStatement.setObject(index, null, type)
>  * PreparedStatement.set<type>(index, null)
>  * PreparedStatement.setNull(index, type)
>
> all of which provide type information that the driver needs.

>I'd also refer you to the JDBC javadoc for setObject(int,Object) which says:

>==
>Note: Not all databases allow for a non-typed Null to be sent to the
>backend. For maximum portability, the setNull or the setObject(int
>parameterIndex, Object x, int sqlType) method should be used instead
>of setObject(int parameterIndex, Object x).
>==

>Oliver