Matching NULL column entries

Thread view:

James Greenfield - 10 May 2004 09:34 GMT

Having a bit of a running argument with someone. As far as I can tell
this is a bit of a hole in JDBC (assuming they have the right of it)
or ANSI SQL. Can anyone provide any clarification for me on this?

The scenario is essentially this. I have a table with a nullable
column. I'd like to retrieve, using a JDBC PreparedStatement, the
subset of rows for which that column is NULL. Preparing and executing

"SELECT * FROM table WHERE column=?"

returns no rows. This, I'm told, is because the ANSI SQL spec states
that <column> = NULL never evaluates to true. I know that on most
database platforms (certainly those I've played with) they make a
syntax distinction so you can use <column> IS NULL. I'm told this is
not part of the ANSI SQL spec. If that's the case then how is this
kind of check supposed to be performed? If it *isn't* the case, then
I'd expect the JDBC driver to render the appropriate underlying SQL
call in the case where I bind a NULL value to the parameter marker
above.

What am I missing? How is one "supposed" to do this?

Thanks
James Greenfield

Reply to this Message

Paul Thomas - 10 May 2004 10:29 GMT

> Having a bit of a running argument with someone. As far as I can tell
> this is a bit of a hole in JDBC (assuming they have the right of it) or
[quoted text clipped - 19 lines]
> Thanks
> James Greenfield

The ANSI standard is WHERE <column> IS NULL. I think your expectation of
a JDBC driver automagically converting invalid SQL into valid SQL is
outside of the JDBC specs.

Signature

Paul Thomas
+------------------------------+---------------------------------------------+

+------------------------------+---------------------------------------------+

Reply to this Message

James Greenfield - 10 May 2004 17:17 GMT

Thanks Paul, appreciate the quick response. My question then becomes
how is one expected to do this?

It strikes me as a valid requirement to be able to filter out all rows
where that particular column is non-null. In at least one case on
Oracle the nullability of a row as an exclusion mechanism allows for a
very hefty optimization.

James

> > Having a bit of a running argument with someone. As far as I can tell
> > this is a bit of a hole in JDBC (assuming they have the right of it) or
[quoted text clipped - 23 lines]
> a JDBC driver automagically converting invalid SQL into valid SQL is
> outside of the JDBC specs.

Reply to this Message

Tobias Besch - 11 May 2004 06:04 GMT

AFAIK, you nee to prepare two statements.
I. e. one with "... where column_x is null"
and another one with ".. where column_x = ?".
Next you have to choose programmatically which statement to use.

Tobias

Reply to this Message

nioTo - 10 May 2004 12:21 GMT

Le 10/05/2004 10:34, James Greenfield a écrit :

> The scenario is essentially this. I have a table with a nullable
> column. I'd like to retrieve, using a JDBC PreparedStatement, the
[quoted text clipped - 13 lines]
>
> What am I missing? How is one "supposed" to do this?

try th method

public void setNull(int parameterIndex,
int sqlType)
throws SQLException
from PreparedStatement class

nioTo

Reply to this Message

James Greenfield - 11 May 2004 07:07 GMT

Thanks nioTo,

Unfortunately I've already tried that. The question here isn't how to
bind a NULL value, that's easy to accomplish, but rather how to
*match* a NULL value in a where clause.

James

> Le 10/05/2004 10:34, James Greenfield a écrit :
> > The scenario is essentially this. I have a table with a nullable
[quoted text clipped - 23 lines]
>
> nioTo

Reply to this Message

Matching NULL column entries

Free Magazines