Thread: Trouble with NULL

Hi,

could anybody help me please with the following problem: I am using Postgres 7.2 on Mandrake Linux 8.2 with JDK 1.4. My table:


create table data
(
id text primary key,
...
variant_id text null,
...
);


Query: select * from data where variant_id is null;
works fine - selects one row.

In my java code:


...
String query = "select * from data where variant_id = ?";
...
PreparedStatement pstmt = con.PrepareStatement(query);
if (parameter == null)
{
pstmt.setNull(0, java.sql.Types.VARCHAR);
}
else
{
pstmt.setObject(0, parameter);
}
...


It works if the parameter is not null. If the parameter is null query returns no row (should return one). What's wrong?

Thank you

Hoanui

---

Join Excite! - http://www.excite.com
The most personalized portal on the Web!

Hi Hoanui,

= NULL and IS NULL are not the same thing :) Don't you see that you are
using completely different SQL statements in the java example and in
the query above it? You should always use the IS NULL version when you
want to see if something is null, comparing to NULL doesn't work.

Also, the parameterIndex in the PreparedStatement.set() methods below
should be numbered from 1. 0 is out of range.

Tom.

On Mon, May 06, 2002 at 04:55:55AM -0400, Hoanui Morangie wrote:
>  Hi,
>
>   could anybody help me please with the following problem: I am using Postgres 7.2 on Mandrake Linux 8.2 with JDK
1.4.My table: 
>
>
> create table data
> (
>    id                text     primary key,
>    ...
>    variant_id        text     null,
>    ...
> );
>
>
> Query: select * from data where variant_id is null;
> works fine - selects one row.
>
> In my java code:
>
>
> ...
> String query = "select * from data where variant_id = ?";
> ...
> PreparedStatement pstmt = con.PrepareStatement(query);
> if (parameter == null)
> {
>   pstmt.setNull(0, java.sql.Types.VARCHAR);
> }
> else
> {
>   pstmt.setObject(0, parameter);
> }
> ...
>
>
> It works if the parameter is not null. If the parameter is null query returns no row (should return one). What's
wrong?
>
> Thank you
>
>   Hoanui
>
> ------------------------------------------------
> Join Excite! - http://www.excite.com
> The most personalized portal on the Web!

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs

Hi Tom,

thank you for your answer. I know that "x is null" is not "x = null". So how shuld I build my query? The example was simplificated. There are five conditions in the "where" clausule and each of them can be null. If I would have to build my query dynamically it would make no sense to use prepared statement!

Thank you

Hoanui


--- On Mon 05/06, Thomas O'Dowd wrote:
> Hi Hoanui,
>
> = NULL and IS NULL are not the same thing :) Don't you see that you are
> using completely different SQL statements in the java example and in
> the query above it? You should always use the IS NULL version when you
> want to see if something is null, comparing to NULL doesn't work.
>
> Also, the parameterIndex in the PreparedStatement.set() methods below
> should be numbered from 1. 0 is out of range.
>
> Tom.
>
> On Mon, May 06, 2002 at 04:55:55AM -0400, Hoanui Morangie wrote:
> > Hi,
> >
> > could anybody help me please with the following problem: I am using
> Postgres 7.2 on Mandrake Linux 8.2 with JDK 1.4. My table:
> >
> >
> > create table data
> > (
> > id text primary key,
> > ...
> > variant_id text null,
> > ...
> > );
> >
> >
> > Query: select * from data where variant_id is null;
> > works fine - selects one row.
> >
> > In my java code:
> >
> >
> > ...
> > String query = "select * from data where variant_id = ?";
> > ...
> > PreparedStatement pstmt = con.PrepareStatement(query);
> > if (parameter == null)
> > {
> > pstmt.setNull(0, java.sql.Types.VARCHAR);
> > }
> > else
> > {
> > pstmt.setObject(0, parameter);
> > }
> > ...
> >
> >
> > It works if the parameter is not null. If the parameter is null query
> returns no row (should return one). What's wrong?
> >
> > Thank you
> >
> > Hoanui
> >
> > ------------------------------------------------
> > Join Excite! - http://www.excite.com
> > The most personalized portal on the Web!
>
> --
> Thomas O'Dowd. - Nooping - http://nooper.com
> tom@nooper.com - Testing - http://nooper.co.jp/labs
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

---

Join Excite! - http://www.excite.com
The most personalized portal on the Web!