SQL Parameter out of index error

Question

I am getting this error when i try to execute this piece of code. When i remove the quotes of the question marks, then it says "You have an error in your SQL syntax;" Can somebody help me to get this fixed?

String query = "select date from ticket where date >='?' and date <= '?'  ";

        PreparedStatement pstmt1 =  con.prepareStatement(query);
        pstmt1.setString(1, fromdate);
        pstmt1.setString(2, todate);
        pstmt1.executeQuery(query);

You don't quote parameters, just use where date >= ? and date <= ? — Phil
– Phil, Commented Mar 23, 2015 at 5:46
I removed quotes and them it says You have an error in your SQL syntax — saranga.aw
– saranga.aw, Commented Mar 23, 2015 at 5:47
What's the error? Also, have you seen the BETWEEN clause, eg WHERE date BETWEEN ? AND ? — Phil
– Phil, Commented Mar 23, 2015 at 5:47
check the manual that corresponds to your MySQL server version for the right syntax to use near '? and date <=?' at line 1 — saranga.aw
– saranga.aw, Commented Mar 23, 2015 at 5:50
Update your question with the current code. Don't type it, copy and paste — Phil
– Phil, Commented Mar 23, 2015 at 5:51

paxdiablo · Accepted Answer · 2015-03-23 07:24:49Z

2

Your actual error here is that you're executing the query string itself rather than the prepared statement:

pstmt1.executeQuery(query);

There is no executeQuery(String) in the PreparedStatement interface, instead it reverts to the one from the Statement interface, which just runs the string, as-is, as a query. That means it will complain bitterly because ? is not considered valid in that context.

You should just be using (including retrieving the result set):

ResultSet rs = pstmt1.executeQuery();

so the query execution is done in the prepared statement context rather than the statement context.

edited Mar 23, 2015 at 7:24

answered Mar 23, 2015 at 5:48

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Over a year ago

date is a valid column name that doesn't need identifier quotes. See the list under this table - dev.mysql.com/doc/refman/5.5/en/…

Manish Kothari · Accepted Answer · 2015-03-23 07:10:43Z

0

Do not enclose the placeholders with quotes

String query = "select date from ticket where date >='?' and date <= '?'  ";

The java API will take care of adding quotes and adding escape sequences to the special characters, this should suffice:

String query = "select date from ticket where date >=? and date <= ?  ";

answered Mar 23, 2015 at 7:10

Manish Kothari

1,7221 gold badge24 silver badges34 bronze badges

Comments

Rick James · Accepted Answer · 2015-03-23 17:54:49Z

0

setString(...) --> setDate(...)

answered Mar 23, 2015 at 17:54

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

SQL Parameter out of index error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related