This week's book giveaway is in the Design forum.
We're giving away four copies of Design for the Mind and have Victor S. Yocco on-line!
See this thread for details.
Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

is that a bug with PreparedStatement ?

 
sinasi susam
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The latter should work. May be the driver problem.
What database and driver are you using?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
sinasi susam
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DB is "ORacle database".
Driver is "thin" driver.

What did you mean by "UNKNOWN"?
A kind of an exception?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think i can find the documentation in oracle's own site.

Thank you Paul
 
sinasi susam
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic