Error code 1172 in stored procedure with cursor [SOLVED]

[Jesu Echarri]

Hello,

I have created this procedure. At the moment I call it I get the 1172 error code.

I have posted it here cause it is about a cursor.

Does anyone see any error? thanks.

delimiter //
DROP PROCEDURE IF EXISTS IMPORTAR_CLIENTES//
CREATE PROCEDURE IMPORTAR_CLIENTES()
BEGIN

DECLARE done INT(1) DEFAULT 0;

DECLARE intAttIdId, intAttIdGender, intAttIdFstName, intAttIdLstName, intAttIdLstLog INTEGER (11);
DECLARE vcharCustEmail VARCHAR(96);
DECLARE intCustId INT(11);
DECLARE charCustGender CHAR(1);
DECLARE vcharCustFstName, vcharCustLstName VARCHAR(32);
DECLARE dtOsComCustLstLog DATETIME;
DECLARE intAcu INT(5);
DECLARE int_last_cli INT(11);

DECLARE cur_clientes CURSOR FOR
SELECT c.customers_email_address, c.customers_id, c.customers_gender, c.customers_firstname, c.customers_lastname, ci.customers_info_date_of_last_logon
FROM customers c LEFT JOIN customers_info ci ON c.customers_id = ci.customers_info_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

#Creo los atributos en phpLists
INSERT INTO phplist_user_attribute (name, type, listorder, required, tablename)
VALUES ('os_customers_id','textline',0,1,'OSID');
SELECT LAST_INSERT_ID() INTO intAttIdId FROM phplist_user_attribute;

INSERT INTO phplist_user_attribute (name, type, listorder, required, tablename)
VALUES ('os_customers_gender','textline',0,1,'OSGENERO');
SELECT LAST_INSERT_ID() INTO intAttIdGender FROM phplist_user_attribute;

INSERT INTO phplist_user_attribute (name, type, listorder, required, tablename)
VALUES ('os_customers_firstname','textline',0,1,'OSNOMBRE');
SELECT LAST_INSERT_ID() INTO intAttIdFstName FROM phplist_user_attribute;

INSERT INTO phplist_user_attribute (name, type, listorder, required, tablename)
VALUES ('os_customers_lastname','textline',0,1,'OSAPELLIDOS');
SELECT LAST_INSERT_ID() INTO intAttIdLstName FROM phplist_user_attribute;

INSERT INTO phplist_user_attribute (name, type, listorder, required, tablename)
VALUES ('os_customers_info_date_of_last_logon','date',0,1,'LASTLOGON');
SELECT LAST_INSERT_ID() INTO intAttIdLstLog FROM phplist_user_attribute;
#Recupero los clientes de osCommerce
OPEN cur_clientes;
REPEAT
FETCH cur_clientes INTO vcharCustEmail, intCustId, charCustGender, vcharCustFstName, vcharCustLstName, dtOsComCustLstLog;
IF NOT done THEN
SET intAcu = intAcu+1;
#Inserto los clientes de osCommerce en phpLists
INSERT INTO phplist_user_user (email, confirmed, blacklisted, entered, modified, uniqid, htmlemail, disabled)
VALUES (vcharCustEmail,NOW(),'0',NOW(),NOW(),MD5(RAND()),'1','0');
SELECT LAST_INSERT_ID() INTO int_last_cli FROM phplist_user_user;
#Inserto los atributos del cliente actual
#osCommerve Customer ID 1
INSERT INTO phplist_user_user_attribute (attributeid, userid, value)
VALUES (intAttIdId, int_last_cli, intCustId);
#osCommerve Customer gender 2
INSERT INTO phplist_user_user_attribute (attributeid, userid, value)
VALUES (intAttIdGender, int_last_cli, charCustGender);
#osCommerve Customer firstname 3
INSERT INTO phplist_user_user_attribute (attributeid, userid, value)
VALUES (intAttIdFstName, int_last_cli, vcharCustFstName);
#osCommerve Customer lastname 4
INSERT INTO phplist_user_user_attribute (attributeid, userid, value)
VALUES (intAttIdLstName, int_last_cli, vcharCustLstName);
#osCommerve Customer lastlogon 5
INSERT INTO phplist_user_user_attribute (attributeid, userid, value)
VALUES (intAttIdLstLog, int_last_cli, dtOsComCustLstLog);
END IF;
UNTIL done END REPEAT;

CLOSE cur_clientes;

SELECT CONCAT('Se han importado ', intAcu, ' clientes');
END;
//
delimiter ;

CALL IMPORTAR_CLIENTES();

Regards.



Edited 2 time(s). Last edit at 04/26/2010 03:41PM by Jesu Echarri.