How to use SQL function and where to put it?

One Star

How to use SQL function and where to put it?

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.
Community Manager

Re: How to use SQL function and where to put it?

Hi
Do you want to call a function or use a function in the query? What does your query looks like?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to use SQL function and where to put it?

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
One Star mpa
One Star

Re: How to use SQL function and where to put it?

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
One Star

Re: How to use SQL function and where to put it?

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,
One Star mpa
One Star

Re: How to use SQL function and where to put it?

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
One Star

Re: How to use SQL function and where to put it?

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.
One Star mpa
One Star

Re: How to use SQL function and where to put it?

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
One Star

Re: How to use SQL function and where to put it?

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

Re: How to use SQL function and where to put it?

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 ;
One Star

Re: How to use SQL function and where to put it?

Why don't you just create this function in the database?
One Star

Re: How to use SQL function and where to put it?

Why don't you just create this function in the database?

Can you tell me how?
Here is what I try :
Metadeta->Db Connections->DemoMysql 0.1->Queries?
One Star

Re: How to use SQL function and where to put it?

I am back.
Everything works now. Thanks alot everyone, shong, mpa and janhess.
One Star

Re: How to use SQL function and where to put it?

How did you resolve it?
One Star

Re: How to use SQL function and where to put it?

I followed what mpa suggested and debugged the SQL function itself.
Copy-past the function in the SQL query section of Tmysqlrow did not work. I had to quote the whole function code, making it look like one big long string.
I am not sure if I made myself clear Smiley Happy
One Star

Re: How to use SQL function and where to put it?

You have to do that with all SQL in Talend
One Star

Re: How to use SQL function and where to put it?

You have to do that with all SQL in Talend

Yep. Just figured that out. I am new to Talend. It's been like a week that I use it Smiley Happy