Dynamic query

Dynamic query

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

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.
Employee

Re: Dynamic query

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

Re: Dynamic query

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

Re: Dynamic query

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Dynamic query

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
Moderator

Re: Dynamic query

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Dynamic query

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

Re: Dynamic query

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.