| Subject: | Problem calling Stored Procedure that calls a second SP |
Hello,
As far as I can tell this is a possible bug around the user defined stored procedures available in 5.0.0-alpha. I'm pretty sure it is not on MySQL but in the DBD::mysql package. This is my first bug submission so please let me know if you need any more info.
Enviroment info:
perl v5.6.1
mysql Ver 14.3 Distrib 5.0.0-alpha, for sun-solaris2.7 (sparc)
package Bundle::DBD::mysql running on sun-solaris 2.7 and 2.8 client machines
I'm also using DBD::Oracle version 1.12 on this same client host
I have 2 user defined Stored Procedures (SP hereafter) that can both be executed from the mysql> prompt without any problems. The first or "complex" SP opens a cursor and calls the second or "simple" SP.
I can call the "simple" SP without any problem from perl script using:
$statement="call sp_complete_order($order_key)";
$sth=$dbh->prepare($statement) || die "..;
$sth->execute() || die "...;
but when I call the "complex" SP the execution fails with:
Error Message:
DBD::mysql::st execute failed: PROCEDURE %f does not exist at /usr/local/apache/cgi-bin/test/CompleteVarPrintBatch.cgi line 107
calling syntax:
$statement="call sp_complete_batch($batch_key)";
$sth=$dbh->prepare($statement) || die "..;
$sth->execute() || die "...;
The only possible problem might be that the "complex" SP is opening a cursor on a table that the "simple" SP will delete rows from during it's execution.?.
I'm not reporting this problem to MySQL.org because as stated before both SP's execute from the mysql> prompt.
Here are the 2 Stored Procedures:
The "complex" one which calls the another SP:
DELIMITER //
create procedure sp_complete_batch(
IN p_batch_key INT
)
BEGIN
DECLARE counter INT;
DECLARE cur1 CURSOR for select order_key from orders where batch_key = p_batch_key;
DECLARE v_order_key INT;
OPEN cur1;
SELECT COUNT(*) INTO counter from orders where batch_key = p_batch_key;
WHILE counter > 0 DO
FETCH cur1 into v_order_key;
CALL SP_COMPLETE_ORDER(v_order_key);
SET counter = counter - 1;
END WHILE;
CLOSE cur1;
END
//
DELIMITER ;
The "simple" which executes without a problem using DBD::mysql:
DELIMITER //
create procedure sp_complete_order(
IN p_order_key INT
)
BEGIN
update orders set status_id='COMPLETED', date_done=now() where order_key = p_order_key;
insert into inactive_orders select * from orders where order_key = p_order_key;
delete from orders where order_key = p_order_key;
END
//
DELIMITER ;
Here are the table creation scripts for the 2 tables involved:
create table orders (
order_key int unsigned not NULL AUTO_INCREMENT,
batch_key int unsigned,
date_done datetime,
status_id varchar(20) not NULL DEFAULT 'PENDING'
primary key (order_key)
);
create table inactive_orders (
order_key int unsigned not NULL,
batch_key int unsigned,
date_done datetime,
status_id varchar(20) not NULL DEFAULT 'PENDING'
primary key (order_key),
);
NOTE: We are using ISAM tables so the foreign constraints are really not working but we kept them in there just in case we migrate to InnoDB tables in future.
Please let me know if you need any more info and if there is anything I can try on my end to help debug this possible problem.
Regards,
Bill Yarnell