This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes cannot bind variables to upsert query using preparedstatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "cannot bind variables to upsert query using preparedstatement" Watch "cannot bind variables to upsert query using preparedstatement" New topic

cannot bind variables to upsert query using preparedstatement

ishara gunathilake

Joined: Jul 04, 2012
Posts: 3
here is my code. I cant bind variables to this upsert statment. it gives me an error "ORA-01008: not all variables bound" even it has bound all variables. i use ojdbc14 driver.

i hard coded the values. then it works fine. i think the problem having with the inside USING statement. please help me to solve this
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3718

Welcome to the Ranch!

1) I don't know why you're getting the error. I've casually counted your question marks and it seems there are 16 of them, so it is indeed quite strange. However, I'm successfully using the following SQL query with bind variables:

As you can see, I'm binding the values in the USING clause, but nowhere else (I'd say this is the "natural" way of doing it, but I don't see a reason why binding the values elsewhere should not work).

If this approach does not hep or you want to untangle your original problem anyway, please post the stack trace so we can see on which line the error occurs. Make sure the line numbers match the code you've posted.

2) You definitely should use a newer Oracle JDBC driver, unless you're still on JDK 1.4. See Which version of JDBC driver shall I use?.

3) Make sure you've turned autocommit off if you're using batch updates. Also, without the 11g version of the driver the standard JDBC batching is not very effective.

4) This is not related to your issue, but you don't need to stuff the schema name into your SQL queries. You can set the default schema using ALTER SESSION SET CURRENT_SCHEMA = schema .
Have you checked out Aspose?
subject: cannot bind variables to upsert query using preparedstatement
jQuery in Action, 3rd edition