• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement - NOT IN (?) is mis-behaving

 
Babji Reddy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • 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
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
378
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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(?, ?, ?)
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
Thanks for the clarification.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic