Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SetNull in a PreparedStatement

 
Andrea Gazzarini
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,I have an amazing problem with the prepared statement...
Here's my code...
String sql="SELECT COLUMN FROM TABLE WHERE COL_CRITERIA=?";
Connection conn =...get the connection
PreparedStatement st = conn.prepareStatement();
// Suppose that the value to be assigned to the prepared statement is NULL..
st.setNull(1,Types.INTEGER);
ResultSet rs = st.executeQuery();
...now the result set is empty even if there are many rows in the TABLE table with the COL_CRITERIA=NULL...
why don't the setNull doesn't replace the =? with IS NULL? In this way
the statement will be
SELECT COLUMN FROM TABLE WHERE COL_CRITERIA IS NULL
and not
SELECT COLUMN FROM TABLE WHERE COL_CRITERIA=NULL (wrong)
I'm using a DB2 7.1
Thanks Thanks Thanks...
 
Leslie Chaim
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
why don't the setNull doesn't replace the =? with IS NULL?
Simply because that's not what the setNull method does
This has noting to do with the flavor of your database. This is just the general design of relational databases. Since NULL is an unknown value even NULL itself is unknown, the following never returns anything:

BTW, if you would change your sql text to WHERE COL_CRITERIA is ? I think it should work... at least for the NULL case
I can think of two ways to get around this. One is to have a separate query for WHERE COL_CRITERIA is NULL, work with that resultSet then continue with the next query. Second, simply add to the existing query or COL_CRITERIA is NULL and do the stipulations yourself.
 
Andrea Gazzarini
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your answer.but...look at this:

Look: is obviously an example...
 
Ed zhang
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I meet the same problem now.
it happens in mssql server 2000.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ed,
Please don't cross-post the same question. As I suggested in your other post, it's ok to link to the thread for more information. (We have a URL button in the UBB code area right below the edit box.)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic