aspose file tools*
The moose likes JDBC and the fly likes java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 4). Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 4)." Watch "java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 4)." New topic
Author

java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 4).

Daniel Stege Lindsjo
Greenhorn

Joined: Jun 28, 2010
Posts: 13
Using PreparedStatement I get the following error:
java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 4).


And the complete code:


I've checked the number of parameters witch is 5 and it seems to match with the 5 setString methods so what am I doing wrong?


D

Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30352
    
150

Daniel,
You can't do this:


Parameters/binding variables can only be used for values not table names.


[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
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30352
    
150

Similarly for parameter #4 which is a column name.
Daniel Stege Lindsjo
Greenhorn

Joined: Jun 28, 2010
Posts: 13
Jeanne:
It appears that it can't parse the ' ' quotes needed around 'Daniel'. So it's a shortcoming of the parser not the statement:

SELECT userName, password FROM user WHERE userName = 'Daniel';


The above works when you enter it in the mysql prompt.

But you can only say ? and NOT '?' and that is the problem I've been fighting with all day.


D
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30352
    
150

The quotes shouldn't be int he prepared statement either, but inserting table/column names is a bigger problem. or are you saying your database supports that? If so, which one is it? I've never heard of that.
Daniel Stege Lindsjo
Greenhorn

Joined: Jun 28, 2010
Posts: 13
I've already made a page that adds users with attributes and it works just fine :P
If however the quotes are not in the statement the SQL call to MYSQL will fail with a syntax error

Could you rewrite the statement then that I may test it?

Cheers,

D
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Some databases/drivers unfortunately support PreparedStatements with table and columns names as parameters BUT often time this is just a fluke. The JDBC driver could escape the value of the column or table name, and the escaped value happens to produce valid SQL. It's extremely dangerous to do though since any change to the driver or database could easily break the query.

For things like this, you really need to build the query yourself with StringBuilder and only apply PreparedStatement parameters to things that are parameters, not tables and columns. I've seen people write JDBC code such as "ORDER BY ?" which happens to work for some drivers, but in general should never work. The query should be resolved as part of building the query string and fed into the PreparedStatement fully formed.


My Blog: Down Home Country Coding with Scott Selikoff
 
Consider Paul's rocket mass heater.
 
subject: java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 4).