Re: select * from mytable where myfield=null;

The "proper" and portable approach would to solve it on the client (as
Dave previously mentioned) - i.e. convert the statement into something
like

select * from mytable where date=? or ((coalesce(?,'x')='x' and date is
null)

or using Oracle syntax

select * from mytable where date=? or ((nvl(?,'x')='x' and date is null)

Well, you have another parameter, but there is not that much for free in
this world - only important things ;-)

Cheers,

Jorg Janke (203) 445-9503 http://www.compiere.org
Smart ERP & CRM Business Solution for Distribution and Service globally
------------------------------------------------------------------------
Porting to PostgreSQL: http://www.compiere.org/technology/pg/index.html
General questions/issues: http://sourceforge.net/forum/?group_id=29057
Support via: http://sourceforge.net/tracker/?group_id=29057&atid=410216
------------------------------------------------------------------------

-----Original Message-----
From: Dave Cramer [mailto:Dave(at)micro-automation(dot)net]
Sent: 14 May, 2002 05:57
To: hoanui(at)excite(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] select * from mytable where myfield=null;

The solution is pretty straight forward here. For those who like the
behavior then just set the transform_null_equals = true option in the
postgresql.conf

The jdbc driver should never change the sql

Dave
On Tue, 2002-05-14 at 05:50, Hoanui Morangie wrote:
> 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!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org