Re: select * from mytable where myfield=null;

Hi,

I understand that this behavior is a standard but agree with Bruno that it's nonsense. I have trouble with it in my own application. I use sql command like "select * from A where A.a=? and A.b=? and A.c=? and A.d=? and A.e=?". There are five parameters in my query and each of them can be null! I can not create SQL command string for all possible combinations of null values so I have to build the query dynamically. Why then use prepared statement?

I doubt that anybody ever needed x=null in SQL command but it seems that my example is very common. There SHOULD be some workaround for that. But I agree that this is not topic for this list.

Hoanui

--- On Mon 05/13, Barry Lind wrote:
> Bruno,
>
> No, the driver should never alter your SQL statement. If you said '...
> = ? ' it would be wrong to change this to ' ... is ? '. How does the
> driver know that you wanted this behavior. (I know for example that
> code I have written would break if the driver suddenly decided to try to
> out guess what I intended). This is the responsibility of the client
> code to handle nulls appropriately for their application. You will not
> find the jdbc drivers for Oracle or MSSQL doing anything like this
> either.
>
> --Barry
>
> Bruno Lamouret wrote:
> > Hi, I agree with you when you say that '= null' isn't ANSI Standard.
> > But the trouble is that the jdbc driver does this error.
> > A query such as "select * from mytable where date=?"
> becomes "select *
> > from mytable where date=null"
> > with the jdbc driver when we put a null value in the
> preparedStatement
> > while it should become
> > "select * from mytable where date is null".
> >
> > am I right ?
> >
> > Thanks Bruno
> >
> >
> > Barry Lind wrote:
> >
> >> This isn't really a jdbc question and would probably better be
> >> addressed to pgsql-general. The behavior you are now seeing is
> ANSI
> >> Standard behavior. '= null' should always return false according
> to
> >> the standard. In 7.2 this non-standard behavior was fixed. You
> will
> >> see this behavior in all of the interfaces to postgres, not just
> jdbc.
> >>
> >> There is a parameter in the postgresql.conf file that will revert
> back
> >> to the old buggy behavior (transform_null_equals = true).
> >>
> >> thanks,
> >> --Barry
> >>
> >>
> >> blamouret wrote:
> >>
> >>> Hi,
> >>> I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4
> >>>
> >>> Here is my table :
> >>> id | date | value
> >>> -----+-------------------------+---------
> >>> 1 | 2002-05-10 10:00:00:+02 | 5
> >>> 2 | (null) | 10
> >>> ...
> >>>
> >>> With the jdbc driver, this query doesn't any result :
> >>> Stmt = con.prepareStatement(select * from mytable where
> date=?)
> >>> Stmt.setNull(1,java.sql.Types.TIMESTAMP);
> >>> Stmt.executeQuery();
> >>>
> >>> I think the jdbc driver transform the query on "select *
> from mytable
> >>> where date=null" and not "select * from mytable
> where date is null".
> >>>
> >>> Il seems that select * from mytable where date=null was
> supported by
> >>> postgres before 7.0 version, but not with the 7.2.1
> >>>
> >>> How can i do ?
> >>> thanks
> >>> Bruno.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> ---------------------------(end of
> broadcast)---------------------------
> >>> TIP 4: Don't 'kill -9' the postmaster
> >>>
> >>
> >>
> >
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

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