CODEWITHSUNDEEP
mysql
firefly
firefly

Reputation: 419

MySQL cursor is returning only one row

Here i want the 20 records in the variable ISSUDAYS. but my cursor is returning only the first reord. the column issue_book.issue_date consist of 20 records..! please help me. thank you in advance.

DECLARE RESULTDAYS,ISSUEDAYS,TODAYS DATE;
DECLARE done VARCHAR(10);
DECLARE DAYS INT DEFAULT 1;
DECLARE CUR1 CURSOR FOR SELECT issue_book.issue_date FROM bookstore.issue_book WHERE   return_date IS NULL AND status = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;       
OPEN CUR1;
READ_LOOP: LOOP
 FETCH CUR1 INTO ISSUEDAYS;
 IF done THEN
 LEAVE READ_LOOP;
 END IF;
  SELECT ISSUEDAYS;
 END LOOP;
CLOSE CUR1;

Upvotes: 1

Views: 3098

Answers (2)

firefly
firefly

Reputation: 419

yes here to display all record which are cursor is fetching need to create one temp table.. DECLARE RESULTDAYS,ISSUEDAYS,TODAYS DATE;

DECLARE DAYS INT DEFAULT 1;
DECLARE COUNTER, done INT DEFAULT FALSE;
DECLARE CUR1 CURSOR FOR SELECT issue_book.issue_date FROM bookstore.issue_book WHERE return_date IS NULL AND status = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;  
CREATE TEMPORARY TABLE temp
(
id int not null auto_increment primary key, 
tempdate DATE
);
OPEN CUR1;
READ_LOOP: LOOP
FETCH CUR1 INTO ISSUEDAYS;
SET COUNTER=COUNTER+1;
IF done THEN LEAVE READ_LOOP;
END IF;
  INSERT INTO temp(tempdate)
  SELECT ISSUEDAYS AS tempdate;
    END LOOP;
CLOSE CUR1;
SELECT * FROM temp;
DROP TABLE temp;

Upvotes: 0

Romi
Romi

Reputation: 399

Try to create a temp table and insert. Then query once from the temp table once the cursor is done.

BEGIN
DECLARE RESULTDAYS,ISSUEDAYS,TODAYS DATE;
DECLARE done BOOL DEFAULT FALSE;
DECLARE DAYS INT DEFAULT 1;
DECLARE CUR1 CURSOR FOR SELECT issue_book.issue_date FROM bookstore.issue_book WHERE return_date IS NULL AND status = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

DROP TABLE IF EXISTS mytemptable;
CREATE TEMPORARY TABLE IF NOT EXISTS mytemptable(issue_date DATE);

OPEN CUR1;

READ_LOOP: LOOP
FETCH CUR1 INTO ISSUEDAYS;
IF done THEN
LEAVE READ_LOOP;
END IF;

INSERT INTO mytemptable SELECT ISSUEDAYS;
END LOOP;
CLOSE CUR1;
SELECT * FROM mytemptable;
END

Upvotes: 3

Related Questions