Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cursor Iteration Problem ...

Status
Not open for further replies.

VBRookie

Programmer
May 29, 2001
331
US
Hi,

I'm trying to move data from an old schema to a new one and have written a stored proc to handle all of the necessary mapping and such. It appears to be working ok except that the outer cursor doesn't iterate correctly (the inner one does). It grabs the first record ... inserts it twice and then ends the loop. I'm using two cursors that are nested. Here is my code ... I've been working on it for a while and figured that maybe it just needed another set of eyes looking at it. I'm very new at this so it may be the result of sheer ignorance on my part. Whatever the case please advise.

Any constructive help would be very much appreciated:

Code:
CREATE PROCEDURE `usp_migrate_data`()
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
   DECLARE no_more_rows_1 INT DEFAULT 0;
   DECLARE contribId, donationsMade INT;
   DECLARE cur1 CURSOR FOR select * from contributors where contributorid in (9, 937, 1921, 2138, 2199, 2389, 64107, 65847, 66068, 66672, 133721, 133878, 133879, 134115, 136377, 136907, 136995, 137037, 137106, 137109, 137194, 137455, 137945, 137947, 137948, 137949, 137973, 137974, 138559, 138839, 140555);
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows_1 = 1;

   OPEN cur1;

   loop1: LOOP
   FETCH cur1 INTO [comma delimited var list here];

      IF no_more_rows_1=1 THEN
         CLOSE cur1;
         LEAVE loop1;
      END IF;
      
         IF contribId IS NOT NULL THEN
	-- some logic here
         END IF;

         SELECT COUNT(*) INTO donationsMade
         FROM pledges
         WHERE contributorID = contribId;

         -- IF DONATIONS HAVE BEEN MADE INSERT INTO DONOR_DETAIL
         IF donationsMade > 0 THEN

           pledgRecs:BEGIN
              -- PLEDGES VARIABLES
              DECLARE no_more_rows_2 INT DEFAULT 0;
              DECLARE PldgID INT;
              DECLARE cur2 CURSOR FOR select * from pledges where contributorid = contribId;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows_2 = 1;

              -- GET DONATION DATA
              OPEN cur2;
              -- REPEAT
              loop2: LOOP
              FETCH cur2 INTO [comma delimited var list here];

                 IF no_more_rows_2=1 THEN
                    CLOSE cur2;
                    LEAVE loop2;
                 END IF;
                 
                 IF PldgID IS NOT NULL THEN
    	   -- some logic here
                 END IF;
              END LOOP loop2;
           END pledgRecs;
        END IF;
      END IF;
   END LOOP loop1;
END;

Many Thanks
- VB Rookie
 

I have this working now ...

The problem is with calling select statements within a cursor when using the continue handler with NOT FOUND.

I ended up replacing all of my select statemens with function calls to get around this. Works beautifully now ... many thanks to kolja in the MySQL cursors forum.

Here is my function code:
Code:
CREATE FUNCTION `func_migrate_data_class_values`(class_type VARCHAR(50), class_id VARCHAR(50))
RETURNS smallint(6)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE reference_code SMALLINT;

-- GET THE CODE
SELECT referencecode INTO reference_code
FROM code_mapping
WHERE classtype = class_type
AND classid = class_id;

RETURN reference_code;
END;

Then I call it from my main stored procedure like this:

Code:
-- GET THE STATE CODE
SET stCde := func_migrate_data_class_values ('states', stOrProv);

I hope that this helps someone.
- VB Rookie
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top