topic Re: [resolved] tMysqlRow update from Excel - does this look right? in Talend Studio

[resolved] tMysqlRow update from Excel - does this look right?

Anonymous — Sat, 16 Nov 2024 13:36:31 GMT

My job needs to grab the value from the excel file, compare it to a column in the mysql table
and increment a count value if they match. I've got a tFileInputExcel component and tMysqlRow
component. I've attached pics of the job. The update query contains the only tFileInput Excel variable
that was available. I can't tell that it's the right one though. Take a look and see if this should work. Thanks in advance!

Re: [resolved] tMysqlRow update from Excel - does this look right?

Anonymous — Thu, 14 Jan 2010 22:45:33 GMT

I switched to using a tMap. I have to filter out records that aren't correct so in the tMap
I take the excel input and check it for the correct string then pass it on through the other side
of the tMap using a variable. As you can see from the pics it's passing the name through correctly
but the query doesn't like it for some reason. Help!

Re: [resolved] tMysqlRow update from Excel - does this look right?

Anonymous — Fri, 15 Jan 2010 02:35:39 GMT

Hello
What's the data type of account field in your table?
The first way is not correct, the global var is only avaliable after the component finished. So you should design the jobs like:
tFileInputExcel_1---tLogRow
|
onSubJobok
|
tMysqlRow
on tMysqlRow: if account is a Integer/int try
"update Users set loginAccount=loginAccount+1 where account="+((Integer)globalMap.get("tFileInputExcel_1_NB_LINE"))
if account is a string, try
"update Users set loginAccount=loginAccount+1 where account='"+String.valueOf(((Integer)globalMap.get("tFileInputExcel_1_NB_LINE")))+"'"
About the second way, if account is a string, try
"update Users set loginAccount=loginAccount+1 where account='"+filterset.account+"'"
Best regards
shong

Re: [resolved] tMysqlRow update from Excel - does this look right?

Anonymous — Fri, 15 Jan 2010 14:51:24 GMT

Thanks for the response Shong. Before getting this post I had already figured out how to make it work.
tFileInputExcel ---> tFilterRow ----> tMysqlRow
The account is a string value. I use tFilterRow's advanced setting to look for "input_row.account.endsWith("@TDOTX.TX")".
The Filter flow is then ran to the mysql component and this query runs on each row :
"Update users set logincount = logincount + 1
where account = \""+row2.account+"\""
One of my issues yesterday was that I needed to use escape characters for the double quotes around row2.account.
It was passing the first part of the account and getting an error when it hit the @ symbol. Once I figured that out
I switched back to tFilterRow so I didn't have to alter incorrect accounts just so they'd never match in the update query.
After that I realized it was running but not updating the logincount. It took a few minutes but I dug in there and
found out I'd set the columns default value to null. null = null + 1 doesn't compute. I set the default value for that column
to 0 and ran the import again. This time everything worked.
It always amazes me that the job ends up being pretty simple when you step back and think about it. I seem to try and make it
more complicated than it has to be sometimes.
Thanks for that String.valueOf() comment. That will come in handy later when I need the cell value. I had been wondering how it
was going to translate that integer into the string value of the cell.
Thanks again. Hope this post helps somebody down the line!