0

I have a database made to support multiple languages. Every text that needs to be translated into other languages is an idTranslation linked to my translation table. There's also a entry table with idTranslation and the idLanguage as primary keys that contains all the texts. This Stored procedure is for modification of any text that has a idTranslation. it first checks if theres already an entry matching the new text, then if there isn't, it creates an idTranslation and matching entry and returns the idtranslation.

It works fine if I call it once but as soon as i call it twice with new text in both calls it just returns the same idTranslation both times... Am i doing sumthing wrong?

CREATE DEFINER=`root`@`localhost` PROCEDURE `AjoutEntrySiFieldExiste`(in PentryText varchar(500),in PfieldName varchar(45),in PlanguageCode varchar(5),out PidTranslation int(11))
BEGIN
if PentryText!="" then
Select `idLanguage` into @languageid from language where languageCode=lower(PlanguageCode);
Select `idTranslation` into @idTrans from entry where lower(`Text`) = lower(PentryText) and idLanguage=@languageid;
if @idTrans is null then
    Insert Into translation Values(Null,(Select idField from field where lower(`Name`)=lower(PfieldName)));
    Set @id = LAST_INSERT_ID();
    Set PidTranslation=@id;
    Insert Into entry Values(@id,@languageid,PentryText);
else
    set PidTranslation=@idTrans;
end if;
SELECT PidTranslation;
end if;
END
Captzulu
  • 15
  • 4
  • You shouldn't use user variables (i.e. `@languageid`). Use local variables instead (i.e. `languageid`). User variables are valid for the whole session. – VMai Jun 20 '14 at 14:04
  • Thanks for your help. I was able to resolved my problem with it and this [post](http://stackoverflow.com/a/3075379/3760344) – Captzulu Jun 20 '14 at 14:23

0 Answers0