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?
Thank you
Best Answer
Beware when using the same name:
Suggest you precede any arguments or locally
DECLAREd
variables with a_
.You can probably combine both procedures into a single SQL statement.