Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, and insert it into a few tables on my end. My cursor seems to only be iterating through once, though - here is the code.

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE; 

    -- Declare Person variables --
    DECLARE person_id INT;
    DECLARE era_username VARCHAR(100);
    DECLARE last_name VARCHAR(50);
    DECLARE first_name VARCHAR(50);
    DECLARE email VARCHAR(100);
    DECLARE email_primary VARCHAR(50);
    DECLARE degree_id_1 INT;
    DECLARE degree_id_2 INT;
    DECLARE member_status INT;

    DECLARE user_id INT;
    DECLARE user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR 
        SELECT person_id, era_username, last_name, first_name, TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2, member_status
        FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor 
        INTO person_id, era_username, last_name, first_name, email, email_primary, degree_id_1, degree_id_2, member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET user_id = SELECT (MAX(uid) + 1) FROM users;
        IF email_primary IS NOT NULL AND email_primary NOT LIKE '%null%' THEN
            SET user_email = email_primary;
        ELSE
            SET user_email = email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (user_id, era_username, SHA1(RAND()), user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`, `member_category_id`, `subscription_weekly_email`, `subscription_monthly_newsletter`)
        VALUES (user_id, first_name, last_name, phone_num, member_status, weekly_com, monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

It's as if the SELECT statement used to populate the cursor is returning no results. I've tested that independently, and it DOES return results correctly. I've tried putting a SELECT person_id directly below the first FETCH, and the returns NULL. So...although this query should return results, they're not making it into the cursor's result set. Am I missing something?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Try tagging all local variables with something like my_

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE; 

    -- Declare Person variables --
    DECLARE my_person_id INT;
    DECLARE my_era_username VARCHAR(100);
    DECLARE my_last_name VARCHAR(50);
    DECLARE my_first_name VARCHAR(50);
    DECLARE my_email VARCHAR(100);
    DECLARE my_email_primary VARCHAR(50);
    DECLARE my_degree_id_1 INT;
    DECLARE my_degree_id_2 INT;
    DECLARE my_member_status INT;

    DECLARE my_user_id INT;
    DECLARE my_user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR 
        SELECT person_id, era_username, last_name, first_name,
              TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2,
              member_status FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor 
        INTO my_person_id, my_era_username, my_last_name, my_first_name,
              my_email, my_email_primary, my_degree_id_1, my_degree_id_2,
              my_member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET my_user_id = SELECT (MAX(uid) + 1) FROM users;
        IF my_email_primary IS NOT NULL AND my_email_primary NOT LIKE '%null%' THEN
            SET my_user_email = my_email_primary;
        ELSE
            SET my_user_email = my_email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (my_user_id, my_era_username, SHA1(RAND()), my_user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`,
              `member_category_id`, `subscription_weekly_email`,
              `subscription_monthly_newsletter`)
        VALUES (my_user_id, my_first_name, my_last_name, my_phone_num,
              my_member_status, my_weekly_com, my_monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = my_person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

In the book MySQL Stored Procedure Programming, all cursor examples give the local variable a unique name from its corresponding table column (if you have the book, see page 108 Example 5-15).

Sidenote : Where are weekly_com and monthly_com coming from ???

share|improve this answer
add comment

I too faced the same kind of issue.

The local variable name and table column name should not be the same.

Just append some characters for the local variable and try out. Then it will definitely work.

I had appended V_ for all columns for local variables and it worked out.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.