CALL in stored procedure doesn't work
I have next data set in table rel_user_article
+---------+------------+
| user_id | article_id |
+---------+------------+
| 1 | -1 |
| 97 | 153 |
+---------+------------+
This table implies next logic: each author must have at least 1 article and each article must have at least 1 author. When author hasn't articles, than table must have fake relation:(uid, -1)
When author adds his first article than this fake relation must be deleted.
I have stored procedures for creating new relation and deleting fake ones. Deleting fake relations looks like this:
CREATE PROCEDURE `rel_delete_fake`(IN `ids` TEXT, IN `tbl` VARCHAR(255),
IN `fake_f` VARCHAR(255), IN `real_f` VARCHAR(255))
MODIFIES SQL DATA
proc: begin
if (`ids` = '') then
leave proc;
end if;
set @s = concat('DELETE FROM ', `tbl`, ' WHERE (', `fake_f`,
' = "-1" AND ', `real_f`, ' IN (', `ids`, '))');
prepare qr from @s;
execute qr;
deallocate prepare qr;
end proc
Creating "real" relations looks like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `rel_create`(IN `ids1` TEXT,
IN `ids2` TEXT, IN `tbl` VARCHAR(255), IN `field1` VARCHAR(255),
IN `field2` VARCHAR(255))
MODIFIES SQL DATA
proc: begin
set @cur = 0;
set @id1_cur = 0;
set @id2_cur = 0;
set @id1_cur_old = NULL;
set @id2_cur_old = NULL;
if (`ids1` = '' or `ids2` = '') then
leave proc;
end if;
set @sql_str = concat('insert into ', `tbl`, ' (', `field1`, ', ', `field2`, ") values (?, ?)");
prepare qr from @sql_str;
loop1: loop
set @cur = @cur + 1;
set @id1_cur = substring_index(substring_index(`ids1`, ',', @cur), ',', -1);
set @id2_cur = substring_index(substring_index(`ids2`, ',', @cur), ',', -1);
if (@id1_cur = @id1_cur_old and @id2_cur = @id2_cur_old) then
leave proc;
end if;
execute qr using @id1_cur, @id2_cur;
set @id1_cur_old = @id1_cur;
set @id2_cur_old = @id2_cur;
end loop loop1;
deallocate prepare qr;
-- deleting fake records that became needless
-- even this doesn't work
call `rel_delete_fake`('1','rel_user_article','article_id','user_id');
leave proc;
-- deleting fake records that became needless
call `rel_delete_fake`(`ids1`, `tbl`, `field2`, `field1`);
call `rel_delete_fake`(`ids2`, `tbl`, `field1`, `field2`);
end proc
Procedure for deleting fake relations works. Procedure for creating relations only creates new record but don't even call rel_delete_fake procedure.
For test I'm issuing next call:
call `rel_create`('1','153','rel_user_article','user_id','article_id');
and have in result:
+---------+------------+
| user_id | article_id |
+---------+------------+
| 1 | -1 |
| 1 | 153 |
| 97 | 153 |
+---------+------------+
Why this (1, -1) not deleted?