0

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//

enter image description here

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:

enter image description here

enter image description here

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:

enter image description here

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.

enter image description here

On my second iteration (to update the french translation), here's what I got:

enter image description here

enter image description here

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

1

Beware when using the same name:

WHERE  `language_code` = language_code

Suggest you precede any arguments or locally DECLAREd variables with a _.

You can probably combine both procedures into a single SQL statement.

  • You are right about the error. I cannot combine both procedure though before the inner procedure do the data validation and errors managing. In principle, external software should update one translation at the time (the english name of a category is much probably not the same as the chinese or arabic name). If I created the update all procedure, it is mainly because our company main software support only one database translation for product, category, etc. (probably won't change soon). Thank you for your help, I'll be careful next time. – Jonathan Parent Lévesque Feb 2 '16 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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