Cursor fetch variable holding null
Posted by:
anoop g
Date: September 19, 2012 11:52PM
Please help me, am getting null values from cursor
DELIMITER $$
DROP PROCEDURE IF EXISTS `csms_hq_db`.`sp_test`$$
CREATE DEFINER=`csms`@`%` PROCEDURE `sp_test`(IN `tbl_name` VARCHAR(255),IN `rec_id` int,
IN `crud_action` VARCHAR(1),IN `topublish` datetime,IN `updatedat` datetime,IN `is_published` int,IN `table_id` VARCHAR(255))
BEGIN
DECLARE last_fetched_value int;
START TRANSACTION;
BEGIN
DECLARE shop_id BIGINT(20);
DECLARE db_server VARCHAR(255);
DECLARE done INT;
DECLARE cur_shop CURSOR FOR SELECT shop_id,db_server FROM shop_db_settings;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_shop;
read_loop: LOOP
FETCH cur_shop INTO shop_id,db_server;
IF done THEN
LEAVE read_loop;
END IF;
#Insert entity part
INSERT INTO `sync_queue` (`table_name`, `record_id`, `shop_id`, `crud_action`, `publishing_date`, `last_updated`, `sync_status`, `error_msg`)
VALUES (db_server, rec_id, shop_id, crud_action, topublish, updatedat, 0,'');
END LOOP read_loop;
CLOSE cur_shop;
END;
COMMIT;
END$$
DELIMITER ;