How to use SQL function and where to put it?

[Anonymous]

Hi everyone,
Could you tell me how to apply/use a SQL function created and saved under a sql file (file.sql) in Talend?
I am not sure if I make myself clear. I actually want to use a SQL function in tMysql_Input component. How can I do that?
Thanks before hand.

[Anonymous]

Hi
Do you want to call a function or use a function in the query? What does your query looks like?
Best regards
Shong

[Anonymous]

Hi,
Here is my function save in a .sql file :
set global log_bin_trust_function_creators=TRUE;
DROP FUNCTION IF EXISTS function1;
DELIMITER |
CREATE FUNCTION function1( codeTif1 INTEGER, codeTif2 INTEGER, codeTif3 INTEGER) RETURNS float
BEGIN
DECLARE translatedIdTheme INTEGER;
DECLARE classicIdTheme INTEGER;
SELECT th.idTheme
INTO translatedIdTheme
FROM mt_theme AS th, mt_repository_mapping AS rm
WHERE rm.codeConstellation = codeTif6
AND th.codeTif1 = rm.mobi1
AND th.codeTif2 = rm.mobi2
AND th.codeTif3 = rm.mobi3
LIMIT 1;
RETURN translatedIdTheme;
END;
|
DELIMITER ;

and I want to use it in my Talend job, where it is called by a component tMysqlInput in the query section . When I launch the job, the function is unknown and I don't know how to call/initialize/load it into my job.
Thanks in advance

[Anonymous]

Hi,
This will only create the function right and not execute the select statement?
If so I would say you have to use the tMysqlRow component and put your query in there.
Regards,
Mario De Pauw

[Anonymous]

Hello mpa!
Thanks for your reply. Well, as I mentioned earlier. The function is called in tMysqlInput as it needs to use that function the treat some data.
When you suggested me to use tMysqlRow and put the query in there, u mean the sql query that belong to the function1 in sql file? or the sql query that calls the function?
Regards,

[Anonymous]

falashock,
The query to create the function u would have to put in a tMysqlRow.
I think if u want to call the function in a select statement to get the "translatedIdTheme" value, you would have to use the TmsysqlInput.
But i'm assuming you have tried this?
Regards

[Anonymous]

Well, just to make sure, here is a screenshot of my job.
As you can see, in tMysqlInput, there is a SQL query that calls the function TranslateTheme, which is currently written in a .sql file.
I am sorry, I still can't figure out how to put that .sql file into my job.

Thanks in advance.

[Anonymous]

Falashock,
Put your SQL query into a Tmysqlrow. Connect that component between tMysqlconnection and tFileList_1.
set global log_bin_trust_function_creators=TRUE;
DROP FUNCTION IF EXISTS function1;
DELIMITER |
CREATE FUNCTION function1( codeTif1 INTEGER, codeTif2 INTEGER, codeTif3 INTEGER) RETURNS float
BEGIN
DECLARE translatedIdTheme INTEGER;
DECLARE classicIdTheme INTEGER;
SELECT th.idTheme
INTO translatedIdTheme
FROM mt_theme AS th, mt_repository_mapping AS rm
WHERE rm.codeConstellation = codeTif6
AND th.codeTif1 = rm.mobi1
AND th.codeTif2 = rm.mobi2
AND th.codeTif3 = rm.mobi3
LIMIT 1;
RETURN translatedIdTheme;
END;
|
DELIMITER ;

regards

[Anonymous]

Thanks mpa, I will try that out. I will keep you posted.

[Anonymous]

I am back.... with bad news -_-
Well, I still got the error that my function does not exist or with a lot of errors in SQL syntax. The colleague that left me the job and the code told me that the SQL is correct as it has been used many times already.
Did I do something wrong?

Here is the function .sql
----
set global log_bin_trust_function_creators=TRUE;
DROP FUNCTION IF EXISTS translateTheme;
DELIMITER |
CREATE FUNCTION translateTheme( codeTif1 INTEGER, codeTif2 INTEGER, codeTif3 INTEGER, codeTif4 INTEGER, codeTif5 INTEGER, codeTif6 VARCHAR(36)) RETURNS float
BEGIN
DECLARE translatedIdTheme INTEGER;
DECLARE classicIdTheme INTEGER;
IF CHAR_LENGTH( codeTif6 ) = 36 THEN
SELECT th.idTheme
INTO translatedIdTheme
FROM mt_theme AS th, mt_repository_mapping AS rm
WHERE rm.codeConstellation = codeTif6
AND th.codeTif1 = rm.mobi1
AND th.codeTif2 = rm.mobi2
AND th.codeTif3 = rm.mobi3
AND th.codeTif4 = rm.mobi4
AND th.codeTif5 = rm.mobi5
AND th.codeTif6 = rm.mobi6
LIMIT 1;
RETURN translatedIdTheme;
ELSE
SELECT th.idTheme
INTO translatedIdTheme
FROM mt_theme AS th, mt_repository_mapping AS rm
WHERE rm.constellation1 = codeTif1
AND rm.constellation2 = codeTif2
AND rm.constellation3 = codeTif3
AND rm.constellation4 = codeTif4
AND rm.constellation5 = codeTif5
AND rm.constellation6 = CAST(codeTif6 AS SIGNED)
AND th.codeTif1 = rm.mobi1
AND th.codeTif2 = rm.mobi2
AND th.codeTif3 = rm.mobi3
AND th.codeTif4 = rm.mobi4
AND th.codeTif5 = rm.mobi5
AND th.codeTif6 = rm.mobi6
LIMIT 1;
SELECT th.idTheme
INTO classicIdTheme
FROM mt_theme AS th
WHERE th.codeTif1 = codeTif1
AND th.codeTif2 = codeTif2
AND th.codeTif3 = codeTif3
AND th.codeTif4 = codeTif4
AND th.codeTif5 = codeTif5
AND th.codeTif6 = CAST(codeTif6 AS SIGNED)
LIMIT 1;
RETURN IFNULL(translatedIdTheme, classicIdTheme);
END IF;
END;
|
DELIMITER ;