I'm trying to make a procedure for MySQL 5.1 that will take a database name as an argument and issue a MULTI DELETE inside that database. I'm running into two problems that I'm not sure how to work around:
USE
can't be used in a prepared statement. When I try, I get "This command is not supported in the prepared statement protocol yet".A code sample is as follows:
DELIMITER $$
CREATE PROCEDURE multi_test (
IN dbname VARCHAR(20)
)
BEGIN
SET @us = CONCAT('USE ', dbname, ';');
PREPARE ustmt FROM @us;
EXECUTE ustmt;
DEALLOCATE PREPARE ustmt;
SET @s = CONCAT('DELETE t FROM ', dbname, '.t as t INNER JOIN ', dbname, '.t2 as t2 IN t.f_id = t2.id');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
Again, this results in "This command is not supported in the prepared statement protocol yet".
One other objective is to keep this in MySQL - I'd rather not use a scripting language that connects to MySQL, but rather just have some MySQL procedure that I can call from a client CLI. However, I'm not opposed to having procedures in other languages (like you can do in PostgreSQL) if that's possible in MySQL.
It appears I need to use the dbname and the table ALIAS when trying to use a MULTI DELETE.
DELETE dbname.talias FROM dbname.t as talias INNER JOIN dbname.t2 as t2 ON talias.f_id = t2.id
I believe I had tried that and it failed, but it now appears to be working :-/