• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SetNull() in a select PreparedStatement

 
Andrea Gazzarini
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everyone..here's my problem...
I've a PreparedStatement with these query:
"SELECT NAME FROM PERSON WHERE ID_COMPARTMENT=?"
ID_COMPARTMENT is a INTEGER column.
How I can set null value to the preparedStatement?
I've tried ps.setNull(1,java.sql.Types.INTEGER), I haven't any error but the query does't return the correct result.
The correct query should be
"SELECT NAME FROM PERSON WHERE ID_COMPARTMENT IS NULL" and I think the setNull method doesn't replace the =? and place instead "IS NULL".
Did anyone can help me to solve this problem?
Thanks,
Andrea
 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're correct -- the SQL should say "IS NULL". I haven't heard of any other solution to this but to use a different SQL string template for the query (one which has "IS NULL" hardcoded).
 
Ed zhang
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I meet the same problem using mssql server 2000 recently.

But it really works well in sybase. I wonder if it's a bug of sqlserver.

What's your db ?
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ed zhang:
But it really works well in sybase. I wonder if it's a bug of sqlserver.
If Sybase considers "NULL = NULL" to be true, it's a bug in Sybase. It's been a while since I've used it, but I can't imagine Sybase has changed to violate the standard rules of relational databases.

Of course, I suppose the Sybase JDBC driver could be really clever and do automatic string replacements, but that seems like it would be a big hole to introduce other bugs.
[ January 06, 2005: Message edited by: David Harkness ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34669
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ed,
I agree with David. null = null is prohibited in most major databases including Oracle and db2.

Also, note that the original post is a year and a half old. It's ok to start a new thread (and link to the old one if you want.)

And welcome to JavaRanch!
 
Ed zhang
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If Sybase considers "NULL = NULL" to be true, it's a bug in Sybase.


I think it's a good merit of Sybase in my opinion. I think SUN should issue the standards of PreparedStatement implements.
 
Ed zhang
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne,

Thanks for your suggestion. I am so keen to know the answer for the question. I just saw it and had trouble with it .
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34669
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ed zhang:


I think it's a good merit of Sybase in my opinion. I think SUN should issue the standards of PreparedStatement implements.

It would actually be a SQL/database standard. The PreparedStatement passes through SQL to the database. So Sun wouldn't be in a position to issue standards on it. They could issue standards on setting parameters in a PreparedStatement. In fact they do.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic