CODEWITHSUNDEEP
javamysqlsql
kelevra88
kelevra88

Reputation: 1562

Java Prepared statement Sql query error

I am trying to run the following statement in a java program.My problem is the first question mark(parameter) in the statement is failing and the error message reads :

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 ''hello' ON SCHEDULE EVERY 1 WEEK STARTS CONCAT(CURRENT_DATE + INTERVAL 1 - WEEKD' at line 1.

Is there something im missing can i not carry out a statement in this fashion ? The query worked before i tried to add the first parameter and just added a name manually into the query ,Any help will of course be appreciated.

PreparedStatement ps1 = null;
   ps1 = connection.prepareStatement("CREATE EVENT ? ON SCHEDULE EVERY 1 WEEK STARTS CONCAT(CURRENT_DATE + INTERVAL ? - WEEKDAY(CURRENT_DATE) DAY, ? ) "
                    + " DO UPDATE tablename SET status = ? WHERE name= ? AND address= ?");

Upvotes: 1

Views: 546

Answers (1)

aleroot
aleroot

Reputation: 72616

This is because the name hello get quoted since is passed as a parameter, in this case you could remove the parameter and just concatenate the string or just embed into the string :

connection.prepareStatement("CREATE EVENT hello ON SCHEDULE EVERY 1 WEEK STARTS CONCAT(CURRENT_DATE + INTERVAL ? - WEEKDAY(CURRENT_DATE) DAY, ? ) "
                    + " DO UPDATE tablename SET status = ? WHERE name= ? AND address= ?");

or going with String format : 

connection.prepareStatement(String.format("CREATE EVENT %s ON SCHEDULE EVERY 1 WEEK STARTS CONCAT(CURRENT_DATE + INTERVAL ? - WEEKDAY(CURRENT_DATE) DAY, ? ) "
                    + " DO UPDATE tablename SET status = ? WHERE name= ? AND address= ?", hello));

Upvotes: 2

Related Questions