Description:
Hi,
Sub: MySQL Cursor fetching NULL values into variables
I am working on a solution for a client with MySQL 5.5.28 and I am unable to fetch data using a cursor. I tried the following sample code and the result is the same.
-- Creating the schema
CREATE SCHEMA `test_cursor` ;
-- Creating the table
CREATE TABLE `product` (
`product_id` int(5) NOT NULL AUTO_INCREMENT,
`product_code` varchar(5) DEFAULT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB ;
-- Inserting test data into the table
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodA');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodB');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodC');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodD');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodE');
-- Procedure
DELIMITER $$
CREATE PROCEDURE `test_cursor`.`P_READ_PRODUCT` ()
BEGIN
/*
The procedure fetches data from the source product table and displays the same.
*/
-- VARIABLE/CONSTANT DECLARATION
DECLARE v_done INT DEFAULT FALSE; -- Variable used in the continue handler.
-- variables used to store the values fetched by the cursor itr_product from product table.
DECLARE a int(5);
DECLARE b varchar(5);
-- Declaration of cursor for iterating through the unprocessed records in the source table.
DECLARE itr_product CURSOR FOR SELECT product_id, product_code FROM test_cursor.product;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; -- Declaring continue handler for the cursor stg_product_data
OPEN itr_product; -- Open the cursor
read_loop: LOOP -- Loop through the records
FETCH itr_product INTO a, b; -- Fetching data into variables
IF v_done THEN -- Checking if the cursor has fetched the last record
LEAVE read_loop; -- Exit if last record had been fetched
ELSE
SELECT @a, @b ; -- Displaying the values fetched by the cursor
END IF;
END LOOP;
CLOSE itr_product; -- Close the cursor
END
-- Calling the procedure
call `test_cursor`.`P_READ_PRODUCT`();
-- Result set/Output
@a @b
null null
How to repeat:
Hi,
Sub: MySQL Cursor fetching NULL values into variables
I am working on a solution for a client with MySQL 5.5.28 and I am unable to fetch data using a cursor. I tried the following sample code and the result is the same.
-- Creating the schema
CREATE SCHEMA `test_cursor` ;
-- Creating the table
CREATE TABLE `product` (
`product_id` int(5) NOT NULL AUTO_INCREMENT,
`product_code` varchar(5) DEFAULT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB ;
-- Inserting test data into the table
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodA');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodB');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodC');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodD');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodE');
-- Procedure
DELIMITER $$
CREATE PROCEDURE `test_cursor`.`P_READ_PRODUCT` ()
BEGIN
/*
The procedure fetches data from the source product table and displays the same.
*/
-- VARIABLE/CONSTANT DECLARATION
DECLARE v_done INT DEFAULT FALSE; -- Variable used in the continue handler.
-- variables used to store the values fetched by the cursor itr_product from product table.
DECLARE a int(5);
DECLARE b varchar(5);
-- Declaration of cursor for iterating through the unprocessed records in the source table.
DECLARE itr_product CURSOR FOR SELECT product_id, product_code FROM test_cursor.product;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; -- Declaring continue handler for the cursor stg_product_data
OPEN itr_product; -- Open the cursor
read_loop: LOOP -- Loop through the records
FETCH itr_product INTO a, b; -- Fetching data into variables
IF v_done THEN -- Checking if the cursor has fetched the last record
LEAVE read_loop; -- Exit if last record had been fetched
ELSE
SELECT @a, @b ; -- Displaying the values fetched by the cursor
END IF;
END LOOP;
CLOSE itr_product; -- Close the cursor
END
-- Calling the procedure
call `test_cursor`.`P_READ_PRODUCT`();
-- Result set/Output
@a @b
null null
Description: Hi, Sub: MySQL Cursor fetching NULL values into variables I am working on a solution for a client with MySQL 5.5.28 and I am unable to fetch data using a cursor. I tried the following sample code and the result is the same. -- Creating the schema CREATE SCHEMA `test_cursor` ; -- Creating the table CREATE TABLE `product` ( `product_id` int(5) NOT NULL AUTO_INCREMENT, `product_code` varchar(5) DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB ; -- Inserting test data into the table INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodA'); INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodB'); INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodC'); INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodD'); INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodE'); -- Procedure DELIMITER $$ CREATE PROCEDURE `test_cursor`.`P_READ_PRODUCT` () BEGIN /* The procedure fetches data from the source product table and displays the same. */ -- VARIABLE/CONSTANT DECLARATION DECLARE v_done INT DEFAULT FALSE; -- Variable used in the continue handler. -- variables used to store the values fetched by the cursor itr_product from product table. DECLARE a int(5); DECLARE b varchar(5); -- Declaration of cursor for iterating through the unprocessed records in the source table. DECLARE itr_product CURSOR FOR SELECT product_id, product_code FROM test_cursor.product; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; -- Declaring continue handler for the cursor stg_product_data OPEN itr_product; -- Open the cursor read_loop: LOOP -- Loop through the records FETCH itr_product INTO a, b; -- Fetching data into variables IF v_done THEN -- Checking if the cursor has fetched the last record LEAVE read_loop; -- Exit if last record had been fetched ELSE SELECT @a, @b ; -- Displaying the values fetched by the cursor END IF; END LOOP; CLOSE itr_product; -- Close the cursor END -- Calling the procedure call `test_cursor`.`P_READ_PRODUCT`(); -- Result set/Output @a @b null null How to repeat: Hi, Sub: MySQL Cursor fetching NULL values into variables I am working on a solution for a client with MySQL 5.5.28 and I am unable to fetch data using a cursor. I tried the following sample code and the result is the same. -- Creating the schema CREATE SCHEMA `test_cursor` ; -- Creating the table CREATE TABLE `product` ( `product_id` int(5) NOT NULL AUTO_INCREMENT, `product_code` varchar(5) DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB ; -- Inserting test data into the table INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodA'); INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodB'); INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodC'); INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodD'); INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodE'); -- Procedure DELIMITER $$ CREATE PROCEDURE `test_cursor`.`P_READ_PRODUCT` () BEGIN /* The procedure fetches data from the source product table and displays the same. */ -- VARIABLE/CONSTANT DECLARATION DECLARE v_done INT DEFAULT FALSE; -- Variable used in the continue handler. -- variables used to store the values fetched by the cursor itr_product from product table. DECLARE a int(5); DECLARE b varchar(5); -- Declaration of cursor for iterating through the unprocessed records in the source table. DECLARE itr_product CURSOR FOR SELECT product_id, product_code FROM test_cursor.product; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; -- Declaring continue handler for the cursor stg_product_data OPEN itr_product; -- Open the cursor read_loop: LOOP -- Loop through the records FETCH itr_product INTO a, b; -- Fetching data into variables IF v_done THEN -- Checking if the cursor has fetched the last record LEAVE read_loop; -- Exit if last record had been fetched ELSE SELECT @a, @b ; -- Displaying the values fetched by the cursor END IF; END LOOP; CLOSE itr_product; -- Close the cursor END -- Calling the procedure call `test_cursor`.`P_READ_PRODUCT`(); -- Result set/Output @a @b null null