• 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 - NOT IN (?) is mis-behaving

 
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In the following piece of code


The results are ignoring the the first clause and bringing the results.

Instead When I replaced the first ? with ' ','XX', and second ? with 1 and used stmt = connection.createStatement(). Then I got results as expected?

Is it a wrong usage of Prepared statement? ( Database is Oracle 9i)

Thanks
 
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 Babji Reddy:
Is it a wrong usage of Prepared statement?


Unfortunately so. A "?" can only be replaced by a single string literal. It can't include any SQL syntax (like the commas in your example.)

You need to write:
not in(?, ?, ?)
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator




Is it a wrong usage of Prepared statement? ( Database is Oracle 9i)



Yes. You can't do what you're trying to do. There is no way, no workaround, nada, zip, nothing. (There are a very very few DBs/drivers where this will actually work, but Oracle has never been one of them and it's really not supposed to work.)

The ? fields in a SQL statement are for binding values; the binding occurs quite late, long after the SQL has been parsed from a text string into some sort of internal representation. An expression list, like ('a','b','c'), is not a value, but as its name implies, is a list of expressions that will be resolved to a list of values. Most of that resolution normally occurs during parse, long before binding. In other words, the ? fields are not set via string relacement...
 
Babji Reddy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the clarification.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic