topic Re: Tmysqlinput returns 2 rows instead of 1 in Talend Studio

Tmysqlinput returns 2 rows instead of 1

Anonymous — Sat, 16 Nov 2024 12:34:09 GMT

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.

Re: Tmysqlinput returns 2 rows instead of 1

Anonymous — Wed, 16 Nov 2011 15:56:21 GMT

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

Re: Tmysqlinput returns 2 rows instead of 1

Anonymous — Thu, 17 Nov 2011 10:40:04 GMT

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.

Re: Tmysqlinput returns 2 rows instead of 1

Anonymous — Thu, 17 Nov 2011 10:51:19 GMT

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.