Insert into table using string in MySql

Question

CREATE TABLE IF NOT EXISTS tmp_park_log (
  uniqueid VARCHAR (20),
  parked_sec INT
) ;
DELETE 
FROM
  tmp_park_log ;
INSERT INTO tmp_park_log (uniqueid, parked_sec) 
SELECT 
  uniqueid,
  SUM(parked_sec) 
FROM
  park_log 
GROUP BY uniqueid ;

This is executing successfully in MySql: But, when i put this in a string and use Prepared Statement it gives following Error:

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 'DELETE FROM tmp_park_log;  INSERT INTO tmp_park_log (uniqueid, parked_sec) SELEC' at line 1

You should put each command into separate statement.

vadchen
– vadchen

2014-01-06 07:22:06 +00:00
Commented Jan 6, 2014 at 7:22 — vadchen
– vadchen, Commented Jan 6, 2014 at 7:22

vadchen · Accepted Answer · 2014-01-06 07:25:27Z

1

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by “;” characters).

See here: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

answered Jan 6, 2014 at 7:25

vadchen

1,4621 gold badge11 silver badges15 bronze badges

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

Comments

Lukas Warsitz · Accepted Answer · 2014-01-06 07:24:16Z

0

In the first case you arent using one commando instead you use a few commandos and when you put it in a string as a prepared statement you must create one prepared statement for every single commando you want to execute.

answered Jan 6, 2014 at 7:24

Lukas Warsitz

1,2391 gold badge11 silver badges20 bronze badges

Comments

asafm · Accepted Answer · 2014-01-06 07:28:23Z

0

Since you didn't attached the Java code I assume you are trying to d prepare statement for all the text and not to prepare every and execute it.

few hints:

There is no need to prepare the CREATE TABLE statement you can just use create statement and execute it (as no bind variables are used) http://docs.oracle.com/javase/tutorial/jdbc/basics/tables.html
If your application allows it , consider the use of truncate instead of delete, it will be faster and reduce the log generation.
Don't forget to commit :).

BR

answered Jan 6, 2014 at 7:28

asafm

9196 silver badges17 bronze badges

Collectives™ on Stack Overflow

Insert into table using string in MySql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related