topic Insert Data Using tmysqlrow in Talend Studio

Insert Data Using tmysqlrow

Anonymous — Fri, 24 Mar 2017 19:20:21 GMT

My scenario is: XXXXX -> tmap -> tmysqlrow. The output of tmap is row1(Main) which has two columns column1 and column2, now I want to insert column1 and column 2 as well as the curdate() into table1 in my database. How can I achieve it?

insert into table1(column1, column 2, date) values(select column1, column 2 from '"+row1.????+"', curdate());

What should I change? Thanks!

Re: Insert Data Using tmysqlrow

cterenzi — Fri, 24 Mar 2017 20:29:27 GMT

There doesn't seem to be a reason to use tMysqlRow component here. You can create a global variable containing a datestamp at the start of your job using a tSetGlobalVar with value: TalendDate.getDate("yyyy-MM-dd")
Add a column containing your datestamp to your flow with your tMap and insert with tMysqlOutput.

Re: Insert Data Using tmysqlrow

Anonymous — Fri, 24 Mar 2017 20:46:13 GMT

cterenzi wrote:
There doesn't seem to be a reason to use tMysqlRow component here. You can create a global variable containing a datestamp at the start of your job using a tSetGlobalVar with value: TalendDate.getDate("yyyy-MM-dd")
Add a column containing your datestamp to your flow with your tMap and insert with tMysqlOutput.

Thanks for your reply. DateStamp will be fine if go with what you said. But for the column1 & column2, I can't find proper way to call those two roles in the insert query since they are not in any tables. Thanks!!!

Re: Insert Data Using tmysqlrow

vapukov — Fri, 24 Mar 2017 21:19:54 GMT

You don't need GlobalVariable for date, You can use direct TalendDate.getDate("yyyy-MM-dd") in tMySQLRow
but in any case - SQL is wrong, it will not work and direct in database

Re: Insert Data Using tmysqlrow

cterenzi — Fri, 24 Mar 2017 21:20:29 GMT

If you use tMysqlOutput instead, you don't need to write the SQL. Just map the incoming schema to the schema of your table.