topic Re: How to use SQL function and where to put it? in Talend Studio

How to use SQL function and where to put it?

Anonymous — Sat, 16 Nov 2024 12:39:16 GMT

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.

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

Anonymous — Thu, 29 Sep 2011 06:28:25 GMT

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

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

Anonymous — Thu, 29 Sep 2011 09:09:56 GMT

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

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

Anonymous — Thu, 29 Sep 2011 10:49:49 GMT

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

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

Anonymous — Thu, 29 Sep 2011 11:50:23 GMT

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,

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

Anonymous — Thu, 29 Sep 2011 12:08:54 GMT

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

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

Anonymous — Thu, 29 Sep 2011 14:34:02 GMT

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.

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

Anonymous — Thu, 29 Sep 2011 14:39:37 GMT

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

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

Anonymous — Fri, 30 Sep 2011 08:40:43 GMT

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

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

Anonymous — Fri, 30 Sep 2011 09:25:56 GMT

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 ;

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

janhess — Fri, 30 Sep 2011 09:43:49 GMT

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

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

Anonymous — Fri, 30 Sep 2011 11:46:48 GMT

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?

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

Anonymous — Fri, 30 Sep 2011 14:18:11 GMT

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

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

janhess — Fri, 30 Sep 2011 14:27:36 GMT

How did you resolve it?

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

Anonymous — Fri, 30 Sep 2011 14:37:52 GMT

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

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

janhess — Fri, 30 Sep 2011 16:44:01 GMT

You have to do that with all SQL in Talend

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

Anonymous — Fri, 30 Sep 2011 16:50:58 GMT

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