Java Prepared statement Sql query error

Question

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= ?");

I tried that also ,i have updated the query to show the new statement — kelevra88
– kelevra88, Commented Oct 27, 2015 at 12:56
the issue seems to be with ''hello' i have only added "hello" with no single quotation.Is there a reason this is added to my query ? — kelevra88
– kelevra88, Commented Oct 27, 2015 at 12:59
@Maria88 I am sorry if it offended you, but would really like to help you. Did you get your answer for the gauges or still you need assistance? — Praveen Kumar Purushothaman
– Praveen Kumar Purushothaman, Commented Feb 1, 2016 at 10:18
I didn't get help no ,It is not an easy thing to get help with or even find out about jquery gauges doesnt give me the layout i need — kelevra88
– kelevra88, Commented Feb 1, 2016 at 10:21

aleroot · Accepted Answer · 2015-10-27 13:27:36Z

2

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));

edited Oct 27, 2015 at 13:27

answered Oct 27, 2015 at 13:02

aleroot

73k32 gold badges182 silver badges220 bronze badges

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

3 Comments

kelevra88 Over a year ago

I would need this parameter to be dynamic if possible ,do you know a way of doing this instead of having a static name ?

kelevra88 Over a year ago

it works ,can someone explain why i am unable to do this in a PreparedStatement setString ? ,I have alot of similar statements to write and it would be useful if there was a way around this

aleroot Over a year ago

Because as I've already said when the argument is passed is automatically quoted, and if you quote a table name in the CREATE TABLE or CREATE EVENT your database gives you back a syntax error ...

Collectives™ on Stack Overflow

Java Prepared statement Sql query error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related