wood burning stoves*
The moose likes JDBC and the fly likes is that a bug with PreparedStatement ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "is that a bug with PreparedStatement ?" Watch "is that a bug with PreparedStatement ?" New topic
Author

is that a bug with PreparedStatement ?

sinasi susam
Ranch Hand

Joined: Jul 15, 2005
Posts: 67
I wanted to execute this query,



it works properly with all strings except one thing,
However when i set "null" to that value,it returns nothing.
But i know there are null values in table.Because i inserted them myself.

Probably you will say me use setNull method instead setString i did too but nothing changes.


it returns nothing if i dont correct the sql string to

"select * from my_table where name is null"

Can anyboy explain me something why is that?I am trying to develop my own persistence tools but i am stucked here...
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
The latter should work. May be the driver problem.
What database and driver are you using?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

And the former won't. In SQL NULL shouldn't be used with comparison operators. The effect will differ from database to database, but in Oracle, the result of:

is UNKNOWN (regardless of the value in "field"), not FALSE or TRUE (though UNKNOWN behaves very much like FALSE).
[ December 05, 2005: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
sinasi susam
Ranch Hand

Joined: Jul 15, 2005
Posts: 67
DB is "ORacle database".
Driver is "thin" driver.

What did you mean by "UNKNOWN"?
A kind of an exception?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

No, its the documented behaviour when using NULL in SQL statements with comparison operators. For example:

Even if the value in "field" is NULL, the where condition will never return true (i.e. you will never return rows where the field has the value NULL). Have a read of the Orcale SQL Reference documentation, in particular the section called "Nulls with Comparison Conditions"
sinasi susam
Ranch Hand

Joined: Jul 15, 2005
Posts: 67
I think i can find the documentation in oracle's own site.

Thank you Paul
sinasi susam
Ranch Hand

Joined: Jul 15, 2005
Posts: 67
I took a look at documentation.TRUE ,FALSE and UNKNOWN evaluated.

I am curious about how do they solved this problem ,in most used persistent tools such as hibernate or ibatis?
I dont know of them..
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I can't tell you off the top of my head. The source for both if available for download. You could have a look if you are very curious.
Virag Saksena
Ranch Hand

Joined: Nov 27, 2005
Posts: 71
It is a good design practice to avoid NULLs when possible.
If you are using Oracle you can use the Oracle specific function NVL
So


will return TRUE. Obviously this means you need a functional index on nvl(name) to optimize access.

Of course this means your application won't work with other databases.
There are other ways to get around this like using the CASE statement, but that kills the possibility of a functional index.

Regards,

Virag


<a href="http://www.auptyma.com" target="_blank" rel="nofollow">The Peak of Performance</a>
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: is that a bug with PreparedStatement ?