Re: select * from mytable where myfield=null;

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
>>>
>>
>>
>
>
>
>