topic Re: Dynamic query in Talend Studio

Dynamic query

Anonymous — Sat, 16 Nov 2024 14:31:01 GMT

Our DW is in MySQL, with inputs from both MySQL and MSSQL transactional systems and Access (for legacy data).
Every night we wish to load in the latest transactions. (Each transaction is uniquely numbered in the source).
We keep a record of the last transaction in a table in the DW.
How do we use the result of a tMysqlRow component to modify the query on a MSSQLInput or MySQLInput job? For example:
tMySQLRow select lastransactionid from transactionhistory where transactiontype='web';
tMSSQLRow/tMySQLRow select .... from transactiontable where transactionid > x ... (where x is the output from tMySQL Row)
We have looked at using tMap, but the performance appears to be slow as it traverses the remote systems row by row. If we can modify the query we can reduce the amount of data transferred.
Thanks
ce

Re: Dynamic query

Anonymous — Tue, 13 Nov 2007 15:34:00 GMT

Did some additional research (manual is not too explicit in these areas)...
1. Created contextvariable
2. Created MysqlInput job to query for single row, single field field
3. Created tJavaRow job (from (2)) to context.setProperty("contextvariable",row1.singlefield)
4. Then Run, MSSqlInput job with an amended query: "SELECT ... FROM ...."+(String)((String)context.getProperty("contextvariable"))
Works perfectly.

Re: Dynamic query

Anonymous — Wed, 14 Nov 2007 08:35:53 GMT

Good solution contentengineer. A more TOS way to do things would be to replace the tJavaRow with a tContextLoad (so that you don't use manual code). Anyway, using a context variable is a good solution. In my opinion, it would be better to use a simple "global" variable because context is a global variable with a specific meaning. But as we don't have a tSetGlobalVariable, using a tContextLoad is better.

Re: Dynamic query

Anonymous — Mon, 02 Nov 2009 16:48:39 GMT

Hi,
I need to do the same with integer field. how can i do this?

Re: Dynamic query

Anonymous — Tue, 03 Nov 2009 05:24:07 GMT

Hello

I need to do the same with integer field. how can i do this?

"select * from person where id="+context.id
Best regards

shong

Re: Dynamic query

_AnonymousUser — Fri, 12 Jul 2013 10:12:39 GMT

hi

i want to execute mysql query in tool ,which components to use,the requirement is as follows
TAB1 TAB2
id name age id place
1 x 10 1 a
2 y 12 2 b
the query is : select a.id,a.name,a.age,b.place from TAB1 a,TAB2 b where a.id=b.id;
the output should be
1 x 10 a
2 y 12 b

Re: Dynamic query

Anonymous — Fri, 12 Jul 2013 10:38:52 GMT

Hi banu,
You can use tMySqlInput and tMysqlRow to execute query. In addition, tMap is also a good option if you don't execute query.
Best regards
Sabrina

Re: Dynamic query

_AnonymousUser — Fri, 12 Jul 2013 10:45:03 GMT

Hi can u show the job design i want the result from query to be loaded into database table

Re: Dynamic query

Anonymous — Fri, 12 Jul 2013 11:21:40 GMT

Hi,
To make it clear, I have designed a demo job.
I choose to use tMysqlInput to execute the simple query. tMySqlInput-->tMySqlOutput
see my screenshots
Best regards
Sabrina