MySQL Forums
Forum List  »  Stored Procedures

CALL in stored procedure doesn't work
Posted by: Никита Тропин
Date: February 08, 2015 10:13AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
CALL in stored procedure doesn't work
3433
February 08, 2015 10:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.