[resolved] tMySQLROW: Use result of 1st query in 2nd Query

One Star

[resolved] tMySQLROW: Use result of 1st query in 2nd Query

Hi,
I want to use the result of 1st query in 2nd query in my tmysqlrow component.
I know tmysqlrow doesnot give output. But is there any work arond?
I tried using mysqlinput to run first query and use its output in 2nd query(in tmysqlrow). But the problem was that i had to make this as a subjob. I want to do it in my existing job. 
Note: The job iterates using tfileList. So need to do it in same job.
Seventeen Stars

Re: [resolved] tMySQLROW: Use result of 1st query in 2nd Query

The tMap has the option "Reload at each row". If you activate this option on the lookup flow you can send the lookup query parameters from the main flow. Take care the lookup input database component uses a separate connection because otherwise the component connects for every main row.
One Star

Re: [resolved] tMySQLROW: Use result of 1st query in 2nd Query

Hi,
I think my question was not clear. Please refer the image:
  :
I have a table to track the process. Table contains a row per file.
I want to changes the status of the row to 'yes' after the file is loaded successfully.
I tried using mysqlrow to update, but in the query i need to do a select too. (Update and select cant be in same query for same table)
Please let me know if any more details required!
Seventeen Stars

Re: [resolved] tMySQLROW: Use result of 1st query in 2nd Query

If one tells my about a query I think about a select. 
For your use case this design is not correct. You want to run the update (with the tMysqlRow component) after the file has been finished successfully but you calls the tMysqlRow for every record you write into your data table.
Do following
Remove the flow to the tMysqlRow and connect this with a OnComponentOk trigger starting from the tMysqlOutput component.
You can use prepared statements (set a ? in your statement like a placeholder and setup the values in the advanced settings.) in the tMysqlRow components and set the values (e.g. file name or count records or timestamps).
One Star

Re: [resolved] tMySQLROW: Use result of 1st query in 2nd Query

Thanks!
Will that update status for all the files being processed or only the last file?
Seventeen Stars

Re: [resolved] tMySQLROW: Use result of 1st query in 2nd Query

It should work for every single file.