Insert query using prepared-statement in mysql?

Question

I have an stored procedure like this:

SET @query = CONCAT('insert into tblcommodity (id , idname , count)values (',p1, p2,p3,')');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

But when I want to run it, it has this error:

> Procedure execution failed 1136 - Column count doesn't match value
> count at row 1

and another thing is that when I just run the insert code it runs!

plz help me.

you prepare a statement to deallocate it just after ? and take care : CONCAT() returns NULL if any argument is NULL. — NeeL
– NeeL, Commented Dec 22, 2012 at 13:27
actually i had a long sp that this part is not executed, so i seperate this part ans test in another sp, but it doesnt run too. — Fatemeh Gharri
– Fatemeh Gharri, Commented Dec 22, 2012 at 13:30

raina77ow · Accepted Answer · 2012-12-22 13:30:00Z

11

With all due respect, the way you do it kindof defeats the whole purpose of prepared statements. I'd use this form instead:

SET @query = 'INSERT INTO tblcommodity (id, idname, count) VALUES (?, ?, ?)';
PREPARE stmt FROM @query;
EXECUTE stmt USING @p1, @p2, @p3;

answered Dec 22, 2012 at 13:30

raina77ow

107k16 gold badges205 silver badges240 bronze badges

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

Comments

Ankur · Accepted Answer · 2012-12-22 13:27:48Z

3

It should be

SET @query = CONCAT('insert into tblcommodity (id , idname , count)values (',',',p1,',', p2,',',p3,')');

answered Dec 22, 2012 at 13:27

Ankur

12.8k7 gold badges40 silver badges68 bronze badges

Comments

Saharsh Shah · Accepted Answer · 2012-12-25 08:50:25Z

1

Try this:

SET @query = CONCAT("insert into tblcommodity (id , idname , count) values (", p1, ", '", p2,"',",p3,")"); 
PREPARE stmt FROM @query; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;

edited Dec 25, 2012 at 8:50

answered Dec 22, 2012 at 13:31

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Comments

Grenville Tryon · Accepted Answer · 2017-06-19 14:59:06Z

0

BTW, Your code:

SET @query = CONCAT('insert into tblcommodity (id , idname , count)values (',p1, p2,p3,')');

has an starting comma:

    ,p1,p2,p3

it means FOUR fields in the statement. Thats why you get an "Column count doesn't match value"

answered Jun 19, 2017 at 14:59

Grenville Tryon

1201 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

Insert query using prepared-statement in mysql?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related