execute mysql query present in variable

Question

I have a MySQLvariable

@query = CONCAT('INSERT INTO history VALUES (',1,',',50,',UTC_TIMESTAMP()');

I want to execute the insert statement present in the variable.

see my answer, i think it will work

Ankit Sharma
– Ankit Sharma

2012-04-12 11:53:44 +00:00
Commented Apr 12, 2012 at 11:53 — Ankit Sharma
– Ankit Sharma, Commented Apr 12, 2012 at 11:53

Mathew Thompson · Accepted Answer · 2012-04-12 11:51:44Z

18

You must first prepare the statement using PREPARE and then use EXECUTE

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

Something like:

SET @query = CONCAT("INSERT INTO history VALUES (",1,",",50,",UTC_TIMESTAMP()");

PREPARE stmt1 FROM @query; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

edited Apr 12, 2012 at 11:51

answered Apr 12, 2012 at 11:43

Mathew Thompson

56.5k15 gold badges131 silver badges151 bronze badges

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

2 Comments

Avinash Over a year ago

I got this error #1064 - 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 'EXECUTE stmt_name' at line 2

NetXpert Over a year ago

So how would you then recover the new record Id that was created by that executed query?

Ankit Sharma · Accepted Answer · 2012-04-12 11:52:34Z

2

Set @query = CONCAT("INSERT INTO history VALUES (",1,",",50,",UTC_TIMESTAMP()");

and then

PREPARE stmt_name FROM @query

and at last

EXECUTE stmt_name

answered Apr 12, 2012 at 11:52

Ankit Sharma

4,0792 gold badges31 silver badges50 bronze badges

Comments

Rui Marques · Accepted Answer · 2015-07-02 21:20:39Z

-2

remove the CONCAT(), it's not doing anything except generate an error since it's trying to concatenate a string with nothing else.

answered Jul 2, 2015 at 21:20

Rui Marques

9

2 Comments

Toumash Over a year ago

It is actually concatenting 5 elements, it wont be any error => your answer is bad -> dont answer already answered questions - look at the new section and show your skills :)

Irfan Raza Over a year ago

illogical answer.

Collectives™ on Stack Overflow

execute mysql query present in variable

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related