File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SetNull() in a select PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SetNull() in a select PreparedStatement" Watch "SetNull() in a select PreparedStatement" New topic
Author

SetNull() in a select PreparedStatement

Andrea Gazzarini
Ranch Hand

Joined: Sep 09, 2002
Posts: 125
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


Regards,<br />Andrea<br /> <br /><a href="http://www.linkedin.com/in/andreagazzarini" target="_blank" rel="nofollow">Andrea Gazzarini</a><br />Skype ID : gazzax72<br />Google Talk ID : a.gazzarini@gmail.com<br /><a href="http://www.andreagazzarini.blogspot.com" target="_blank" rel="nofollow">My Blog</a>
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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).


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Ed zhang
Greenhorn

Joined: Jan 06, 2005
Posts: 4
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

Joined: Aug 07, 2003
Posts: 1646
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29237
    
139

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!


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Ed zhang
Greenhorn

Joined: Jan 06, 2005
Posts: 4
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

Joined: Jan 06, 2005
Posts: 4
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29237
    
139

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SetNull() in a select PreparedStatement
 
Similar Threads
PreparedStatement X DateTime Field
is that a bug with PreparedStatement ?
Prepared statement null setting
SetNull in a PreparedStatement
MySQL Prepared Statement & NULL