Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

Tmysqlinput returns 2 rows instead of 1

Highlighted
One Star

Tmysqlinput returns 2 rows instead of 1

I have a file in a folder with a 17 character numeric name, the same name is stored in a MYSQL table as a primary key.
As part of my workflow as there are 100's of these files I use a tfileinputpositional to read the header lines to see if the file is one I want to process.
If the filter defines the file to be valid then I store the file name as a context variable i.e.
in tjavarow context.RptID = ((String)globalMap.get("tFileList_1_CURRENT_FILE"));
as a test i then present the value of context.RptID in a message box thus a single value i.e. 15982000216817701
the process then uses a Tmysqlinput with the selection string as "SELECT * FROM HOLDxCONTROL WHERE ID IN("+String.valueOf(context.RptID)+")";
for testing i use a Tjavarow to show the expected results however 2 rows are returned ?.
15982000216817700|CRF.GLSHORT|GB0010002|03-10-2011|01-01-1970|Y|201110031806|03-10-2011
15982000216817701|CRF.MBGL|GB0010002|03-10-2011|01-01-1970|Y|201110031806|03-10-2011
looking at the run-stats the filtre to tjavarow shows 1 row returned as does the tjavarow to tmsgbox.
the tmsgbox is connected to tmysqlinput with on component ok which works but the stat of course from tmysqlinput shows 2 rows returned.
Does anybody know why this has occured and how to solve the problem please.
Highlighted

Re: Tmysqlinput returns 2 rows instead of 1

Hi,
Could you please attach a picture of your job.
Have you tried to run the query with the RptID manually to see how many results are returned?
The input String might also need to be changed to:
"SELECT * FROM HOLDxCONTROL WHERE ID IN('"+String.valueOf(context.RptID)+"')"
--- adding the apostrophe's
I want to see the layout of your job because I think you might have to use a tFlowtoIterate.
For example -
tFileInputPostitional --> tJavaRow -(Passing the RptID value as an output)-> tFlowToIterate - iterate -> tjava (Set the context eg. context.RptID = ((String)globalMap.get("row2.RptID")); -on Component ok-> tMySQLInput -> rest of your job
Regards,
Brandon
Highlighted
One Star

Re: Tmysqlinput returns 2 rows instead of 1

The reason I use the Tfileinputpositional is to read in line by line the contents of the txt file. The filter allows me to identify from the report code if it is a file i want to use, note this code is not the same as the name of the file.
This is clearly shown in the statistics where the row count from the text file is anything between 1 and 10 depending on which line my filter finds the information I want then you can see only one row is used on the subsequent tjavarow etc. however as you see at the end tmysqlinput returns 2 rows one being the primary key before my required primary key.
Highlighted
One Star

Re: Tmysqlinput returns 2 rows instead of 1

I have solved the error by putting the extra single ' in the select string. not sure why it would have cause the problem in the first place but now the process works. Many thanks for the help on this.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog