nested cursor loop: Column count doesn't match value count at row 1

Question

This is my first time dealing with cursors and procedures in MySQL. I have followed the examples given in MySQL documentation on cursors and roseindia.net and now encounter an error:

Column count doesn't match value count at row 1

Here are the tables involved (the tables are not normalised to 3NF yet as I receive them as so, and yes, I do realised that I would need to use artists.id instead of artists.name):

CREATE TABLE `user_artists_rankings` (
  `user_id` varchar(50) NOT NULL DEFAULT '' COMMENT 'kpop_users.user_id',
  `artist_name` varchar(100) NOT NULL,
  `calculated_score` int(11) DEFAULT NULL,
  `last_calculated_score_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`artist_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `kpop_users` (
  `user_id` char(50) NOT NULL,
  `username` char(150) DEFAULT '',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`) USING BTREE,
  KEY `name_idx` (`username`),
  KEY `twitter_id_idx` (`twitter_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
CREATE TABLE `artists` (
  `name` varchar(100) NOT NULL,
  `type` varchar(45) DEFAULT NULL,
  `official_twitter_screenname` varchar(45) DEFAULT NULL,
  `thumbnail_url` varchar(500) DEFAULT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `Index_2` (`official_twitter_screenname`)
) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

The objective is to have user-artist(n) rows in the table, user_artists_rankings, i.e:

user1 | artist1
user1 | artist2
user1 | artist3
user2 | artist1
user2 | artist2
user2 | artist3

Here is the procedure I have created:

delimiter ;
drop procedure if exists testrun;
delimiter //
create procedure testrun()
begin
    declare d int default 0;
    declare user_id varchar(50);
    declare artist_name varchar(100);
    declare cur_users cursor for select user_id from kpop_users;
    declare cur_artists cursor for select name from artists;
    declare continue handler for sqlstate '02000' set d=1;
    declare continue handler for sqlstate '23000' set d=1;
    open cur_users;
    open cur_artists;
    lbl1: loop 
        if d=1 then 
            leave lbl1;
        end if;
        if not d=1 then
            fetch cur_users into user_id;
            lbl2: loop
                if d=1 then
                    leave lbl2;
                end if;
                if not d=1 then
                    fetch cur_artists into artist_name;
                    insert into user_artists_rankings values (user_id, artist_name);
                end if;
            end loop lbl2;
        end if;
    end loop lbl1;
    close cur_users;
    close cur_artists;
end
//
delimiter ;
call testrun();

Any help would be greatly appreciated. Thank you.

Jocelyn · Accepted Answer · 2012-09-09 12:37:02Z

1

This line of code is wrong:

insert into user_artists_rankings values (user_id, artist_name);

Since the table has 4 fields and you are using only two of them for INSERT, you need to specify which ones are used. It should be:

insert into user_artists_rankings(`user_id`, `artist_name`) VALUES(user_id, artist_name);

answered Sep 9, 2012 at 12:37

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Robert Sim · Accepted Answer · 2012-09-10 07:34:10Z

After that initial boo-boo on the INSERT statement, which @Jocelyn had helped, I discovered that my nested loop was wrong. I have to put in another label for the nested loop as well. Here is the corrected loop:

delimiter ;
DROP procedure IF EXISTS firstrunUser_Artists_Rankings;
delimiter //
CREATE PROCEDURE firstrunUser_Artists_Rankings()
block1: BEGIN
    DECLARE d INT DEFAULT 0;
    DECLARE new_user_id VARCHAR(50);
    DECLARE new_artist_id INT(10);
    DECLARE cur_users CURSOR FOR SELECT `user_id` FROM kpop_users;
    DECLARE cur_artists CURSOR FOR SELECT `id` FROM artists;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET d=1;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET d=1;
    OPEN cur_users;
    lbl1: LOOP 
        IF d=1 THEN
         SHOW ERRORS;
            LEAVE lbl1;
        END IF;
        IF NOT d=1 THEN
            FETCH cur_users INTO new_user_id;
            block2: BEGIN
             DECLARE e INT DEFAULT 0;
             DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET e=1;
       DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET e=1;
             OPEN cur_artists;
             lbl2: LOOP
                 IF e=1 THEN
                     LEAVE lbl2;
                 END IF;
                 IF NOT e=1 THEN
                     FETCH cur_artists INTO new_artist_id;
                     INSERT INTO user_artists_rankings(`user_id`, `artist_id`) VALUES (new_user_id, new_artist_id);
                     INSERT INTO user_artists_rankings_raw(`user_id`, `artist_id`) VALUES (new_user_id, new_artist_id);
                 END IF;
             END LOOP lbl2;
             CLOSE cur_artists;
            END block2;
        END IF;
    END LOOP lbl1;
    CLOSE cur_users;
END block1;
//
delimiter ;
CALL firstrunUser_Artists_Rankings();

Collectives™ on Stack Overflow

nested cursor loop: Column count doesn't match value count at row 1

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related