• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SetNull() in a select PreparedStatement

 
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author
Posts: 11962
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ?
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic