From: | Dave Cramer <Dave(at)micro-automation(dot)net> |
---|---|
To: | hoanui(at)excite(dot)com |
Cc: | "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: select * from mytable where myfield=null; |
Date: | 2002-05-14 09:56:35 |
Message-ID: | 1021370195.2628.46.camel@inspiron.cramers |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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!
From | Date | Subject | |
---|---|---|---|
Next Message | Marin Dimitrov | 2002-05-14 10:03:00 | Re: select * from mytable where myfield=null; |
Previous Message | Hoanui Morangie | 2002-05-14 09:50:14 | Re: select * from mytable where myfield=null; |