MySQL Forums
Forum List  »  Custom Storage Engines

Cursor fetching NULL values into variables
Posted by: Clement Francis
Date: December 22, 2012 12:43AM

Hi,

I am working on a solution in which I have to read data from a table parse it and load them into the target table. I though of starting with a sample code that will read data from the source table using cursor and display them. However, My cursor is fetching NULL values into the variables.
The source table has data. I tested the SELECT statement used in the cursor declaration seperately and it is working as expected.

Below is the steps I followed. Am I missing something? Is there something wrong with the code?

thanks in advance.


-- 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



Edited 1 time(s). Last edit at 12/22/2012 12:45AM by Clement Francis.

Options: ReplyQuote


Subject
Views
Written By
Posted
Cursor fetching NULL values into variables
7698
December 22, 2012 12:43AM


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.