• 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

PreparedStatement and 'null' value in WHERE clause

 
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,



Why above code does not update column AGE for all records which NAME is null? In normal query I would rather use "...WHERE NAME IS NULL" for this case but I want to have general solution (handling both NULL and not NULL values in WHERE clause). Guys, do you have any idea how to do it?
 
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 Radoslaw Sztando:
Why above code does not update column AGE for all records which NAME is null?


Because you aren't using "is null". Null will never match on =.


but I want to have general solution (handling both NULL and not NULL values in WHERE clause). Guys, do you have any idea how to do it?


You are on the right track with your if statement. You just need to add a bit more logic:

 
Greenhorn
Posts: 3
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jeanne Boyarsky:



In this case, it's not necessary to use PreparedStatement, why not use Statement directly?

 
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 Xiangning Liu:
In this case, it's not necessary to use PreparedStatement, why not use Statement directly?


Because prepared statements protect you from SQL injection. I'd much rather the JDBC driver handle that than my having to write a (possibly incorrect) validator.
 
Ranch Hand
Posts: 169
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In your query your are checking

name = null or name = passed value

in oracle name = null is different from name is null.

Change your code accordingly.

HTH
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic