Win a copy of Head First Android this week in the Android forum!
  • 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:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

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

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
author & internet detective
Posts: 40797
829
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
Daniel,
You can't do this:


Parameters/binding variables can only be used for values not table names.
 
Jeanne Boyarsky
author & internet detective
Posts: 40797
829
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
Similarly for parameter #4 which is a column name.
 
Daniel Stege Lindsjo
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
author & internet detective
Posts: 40797
829
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
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
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author
Posts: 4278
34
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic