| Subject: | Can't call user defined stored procedure that calls a second user defined stored procedure |
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,
base_path varchar(200) not NULL,
file_name varchar(150) not NULL,
date_received datetime not NULL,
date_printed datetime,
date_reworked datetime,
date_ship_due datetime,
date_done datetime,
status_id varchar(20) not NULL DEFAULT 'PENDING' references order_status(status_id),
print_seq int unsigned,
billable_flag ENUM('YES','NO') not NULL,
rework_count smallint unsigned not NULL DEFAULT '0',
customer_id varchar(20) not NULL references customers(customer_id),
order_type_id varchar(20) not NULL references order_types(order_type_id),
qty mediumint unsigned not NULL DEFAULT '1',
image_count int unsigned not NULL DEFAULT '1',
file_type_id varchar(10) not NULL references file_types(file_type_id),
media_type_id varchar(10) not NULL DEFAULT 'PLAIN' references media_types(media_type_id),
binding_type_id varchar(10) not NULL DEFAULT 'NONE' references binding_types(binding_type_id),
sides ENUM('SIMPLEX','DUPLEX') not NULL DEFAULT 'SIMPLEX',
printer_id varchar(20) not NULL references printers(printer_id),
deliv_location varchar(20),
notes tinytext,
primary key (order_key),
index idx_orders_file_name (file_name),
index idx_orders_date_received (date_received),
index idx_orders_deliv_location (deliv_location)
);
create table inactive_orders (
order_key int unsigned not NULL,
batch_key int unsigned,
base_path varchar(200) not NULL,
file_name varchar(150) not NULL,
date_received datetime not NULL,
date_printed datetime,
date_reworked datetime,
date_ship_due datetime,
date_done datetime,
status_id varchar(20) not NULL DEFAULT 'PENDING' references order_status(status_id),
print_seq int unsigned,
billable_flag ENUM('YES','NO') not NULL,
rework_count smallint unsigned not NULL DEFAULT '0',
customer_id varchar(20) not NULL references customers(customer_id),
order_type_id varchar(20) not NULL references order_types(order_type_id),
qty mediumint unsigned not NULL DEFAULT '1',
image_count int unsigned not NULL DEFAULT '1',
file_type_id varchar(10) not NULL references file_types(file_type_id),
media_type_id varchar(10) not NULL DEFAULT 'PLAIN' references media_types(media_type_id),
binding_type_id varchar(10) not NULL DEFAULT 'NONE' references binding_types(binding_type_id),
sides ENUM('SIMPLEX','DUPLEX') not NULL DEFAULT 'SIMPLEX',
printer_id varchar(20) not NULL references printers printer_id),
deliv_location varchar(20),
notes tinytext,
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