mysql PREPARE statement

Question

I have a series of 16 PREPARE, EXECUTE and DEALLOCATE statements (in a stored procedure), each inserting rows into a diffterent table (table 1 to table16). eg:

SET @Command1 = CONCAT("insert into TABLE1" ,  ...etc.. );
PREPARE stmt1 FROM @Command1 ;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SET @Command1 = CONCAT("insert into TABLE2" ,  ...etc.. );
PREPARE stmt1 FROM @Command1 ;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

.
.
.

SET @Command1 = CONCAT("insert into TABLE16" ,  ...etc.. );
PREPARE stmt1 FROM @Command1 ;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

When I execute the stored procedure, the INSERT works intermittently. Sometimes all the 16 inserts works, but sometimes they don't.

In the last CALL of the stored procedure, the first 2 inserts (into TABLE1 and TABLE2 ) and the last 4 inserts (TABLE 13 to 16) work, but not the inserts into Table 3 to 12.

Can you explain why? Can't be because I'm using the same variable/handle command1 and stmt1?

Answer 1

In MySQL, the combinations of Prepare and Execute commands should be used with placeholders - saving you time with concat strings (maybe one of your concats simply goes wrong) and of course - prevent sql injections (yey!)

So... just build your statements like this one:

-- in MySQL ? is a placeholder
set @sampleQuery = 'select name into @testValue from myTable where id = ?'; 
set @idParam = 'NT54X9';

prepare sampleStatement from @sampleQuery;
execute sampleStatement using @idParam; -- `using` is the key point here
deallocate prepare sampleStatement;

select @testValue