I have two stored procedures. The first one fetch language codes in my language table.
CREATE PROCEDURE `updateCategoryNameAll` ( IN categorieNumber VARCHAR (10), IN categoryName VARCHAR (128) ) BEGIN DECLARE break BOOLEAN DEFAULT FALSE; DECLARE languageCode CHAR(5); DECLARE languageCursor CURSOR FOR SELECT `language_code` FROM `ps_lang` WHERE `active` = 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET break = TRUE; DECLARE EXIT HANDLER FOR 1644 RESIGNAL; OPEN languageCursor; language_loop: LOOP FETCH languageCursor INTO languageCode; IF (break) THEN LEAVE language_loop; ELSE CALL updateCategoryName(categorieNumber, categoryName, languageCode); END IF; END LOOP language_loop; CLOSE languageCursor; END//
The second procedure update the category translation based on the selected language.
Here's my problem. In the main procedure, when I'm fetching the languages everything is fine:
But, when I'm trying to fetch the language I want to work with in my nested stored procedure:
SELECT IFNULL( ( SELECT `id_category` FROM `pc_import_category` WHERE `source_category_number` = categorieNumber LIMIT 1), 0 ) AS id_category, IFNULL( ( SELECT `id_lang` FROM `ps_lang` WHERE `language_code` = language_code LIMIT 1), 0 ) AS id_lang INTO idCategory, idLang;
Here's the result when I'm fetching for active language in the parent procedure:
There should be only one language (USA english), but it returns both my languages (with the good id, but the second language code should be 'fr-ca'). When I'm fetching for the other language, here's what I got.
On my second iteration (to update the french translation), here's what I got:
As a result, I find myself always updating only my english language (which is the id 1).
Is there some kind of limitation, in MySQL, with the table lock, I'm not aware of?