How to insert a value into a table from a variable within a prepared statement

Question

I'm attempting to insert a string into a table via a prepared statement. My specific use case is complicated, but I've reduced the problem down to its essence. Consider the following code:

drop table if exists foo;
create table foo (
  id int primary key,
  name varchar(10)
);
set @myname := "Bob";
insert into foo (name) values(@myname); # this DOES work

#set @sql := CONCAT("insert into foo (name) values((", @myname, ")"); # does not work
#set @sql := CONCAT("insert into foo (name) values((\'", @myname, "\')"); # does not work
#set @sql := CONCAT("insert into foo (name) values(('", @myname, "')"); # does not work
#set @sql := "insert into foo (name) values(''Alice'')"; # does not work
#set @sql := "insert into foo (name) values('Alice')"; # does not work

#set @sql := "insert into foo (name) values(Alice)"; # this DOES work
prepare stmt from @sql;
execute stml;
select * from foo;

Of course doing an insert without a prepared statement works. However, all the of commented lines fail to work (except the last which doesn't use the variable) with error code 1064.

How do I insert the variable @myname into a table using a prepared statement? This code needs to run in a stored procedure, so I'm not looking for a solution that is written in another language.

Did you try #set @sql := "insert into foo (name) values(@myname)";? Prepared statements don't usually require quotations around variables, and you've assigned the variable above — nomistic
– nomistic, Commented Aug 27, 2015 at 23:25

Drew · Accepted Answer · 2015-08-27 23:32:24Z

1

Not much to comment here other than adding the auto_increment, and some extra left parenthesis stuff. And the typo near the last line.

create table foo (
  id int auto_increment primary key, -- EDIT done here
  name varchar(10)
);
set @myname := "First Bob";
insert into foo (name) values(@myname);

set @myname:="Second Bob";
set @sql := CONCAT("insert foo(name) values('", @myname, "')");
select @sql; -- eyeball it

prepare stmt from @sql;
execute stmt;
select * from foo;
+----+------------+
| id | name       |
+----+------------+
|  1 | First Bob  |
|  2 | Second Bob |
+----+------------+

answered Aug 27, 2015 at 23:32

Drew

25k10 gold badges47 silver badges81 bronze badges

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

1 Comment

poindextrose Over a year ago

Thanks @Drew, It was the typo that caused the problem.

Collectives™ on Stack Overflow

How to insert a value into a table from a variable within a prepared statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related