Re: select * from mytable where myfield=null; - Mailing list pgsql-jdbc

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
ownapplication. 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
fiveparameters in my query and each of them can be null! I can not create SQL command string for all possible
combinationsof 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
someworkaround 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!