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 PreparedStatement - NOT IN (?) is mis-behaving Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement - NOT IN (?) is mis-behaving" Watch "PreparedStatement - NOT IN (?) is mis-behaving" New topic
Author

PreparedStatement - NOT IN (?) is mis-behaving

Babji Reddy
Ranch Hand

Joined: Jan 24, 2006
Posts: 106
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

Joined: May 26, 2003
Posts: 30965
    
158

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(?, ?, ?)


[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
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333



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

Joined: Jan 24, 2006
Posts: 106
Thanks for the clarification.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: PreparedStatement - NOT IN (?) is mis-behaving