Error in JDBC query

Question

sql = "select milk_rate from special_milk_rate 
 where code_producer_id=? and effective_from <= ? 
 and effective_till >= ?"

I am getting error in this query when in my JDBC code

Exception:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? and effective_from <= ? and effective_till>= ?' at line 1

But when I run it in MySQL command prompt with some values it runs fine there.

• Jeremiah Willcock: You should add tags like mysql, jdbc, and sql to get more answers.
• Nishant: query's fine, there is a problem with your PreparedStatement. Show the Java code.
• codeomnitrix: Hey can anyone tell me what do this query??

Answer 1

ResultSet rst= prest.executeQuery(sql);

This is incorrect. It should be executeQuery().

The sql gets passed to execute when using a Statement. It gets passed to the PreparedStatement instead when using binding variables. Since you are using the Statement API, JDBC is executing it without substituting in your binding variables and wondering what to do with the literal question marks.

Answer 2

You have to use prepared statements instead of "normal" statements to have the question marks replaced by parameters.

• amit4444: hi i am using preparestatement my code is sql = " select milk_rate from special_milk_rate where code_producer_id='? and effective_from <= '? and effective_till >= '?" prest=conn.prepareStatement(sql) prest.setLong(1,id) prest.setDate(2,sqlDate) prest.setDate(3,sqlDate) ResultSet rst= prest.executeQuery(sql)
• Costis Aivalis: Get rid of the single quotes in your prepared statement.
• Stephen C: @Costis is correct. In addition, you appear to be missing the semicolons after each statement.