Talend Open Studio Cookbook question

[Anonymous]

Anyone who has used "Talend Open Studio Cookbook" by Rick Barton may have encountered my problem. After following the "How to do it..." steps in chapter 7 job jo_cook_ch07_0120_inProcessDatabase I find the job seems to hang on csv input file record 2. Any tips or ideas for getting the job working would be appreciated.

Thanks.

[Anonymous]

Can you post a screenshot of the job and give an example of the data?

[Anonymous]

Here are screen shot images of Talend Open Studio Cookbook  by Rick Barton chapter 7 job jo_cook_ch07_0120_inProcessDatabase run in Talend Open Studio on Windows 10 64-bit machine. The first image is the main TOS screen showing the run tab results. The second image shows the tMap component editor. The third shows the tFileInputDelimited_2 csv input file data. Let me know if you need any other information.

Thanks,

Tony Blaes

[Anonymous]

It looks like it is struggling to get a result from the tHSQLDbInput_1 component. Can you test the query that you are running there?
Another thing to check is the commit status of the DB table that holds the Country data. It looks like an update has taken place from the first row. I suspect (actually, I'm pretty sure this is the reason now) that the update has locked the table and meant that the next query cannot fire. 

[Anonymous]

@blaestony I have sent you an internal mail (the envelope at the top right of the Community). Can you take a look and respond when you have time please? Thanks 🙂

[Anonymous]

Hello Richard,

The query executing against Talend component tHSQLDbInput_1 is "SELECT id, countryName FROM countryRef". I agree that the next row read from tFileInputDelimited_2 is ready for processing by tMap_1, which seems to start the country lookup from tHSQLDbInput_1. Your suggestion concerning checking the commit status of tHSQLDbOutput_2 is reasonable. Being new to Talend I need to ask for some guidance on accomplishing your suggestion. The components: tHSQLDbInput_1, tHSQLDbInput_2 and tHSQLDbOutput3 all use the same HSQLDb in Memory table. The tHSQLDbOutput_2 component's Action on table is Default and its Action on data is Insert. My question is does Oracle MySQL automatically commit inserts on in memory tables in Talend? How do you confirm the table commit status? Look forward to your reply.

Thanks,

Tony Blaes

[Anonymous]

It doesn't look like you are using MySQL. Maybe I misunderstood the question? You could try adjusting the commit settings (Advanced tab) of the tHSQLDbOutput components. Since you have so few rows, maybe set it to 1. It's not terribly efficient, but it might solve this. I'm afraid I do not really use a HSQL DB, so I am guessing at the cause of this. This is the behaviour you might expect from non-in-memory DBs though, given this sort of job layout.

[Anonymous]

Hello Richard,

I  adjusted the "Commit every" setting for component tHSQLDbOutput_2 on the Advanced settings tab from 10000 to 1 which allowed the job to run successfully. It seems the structure of the job requires commits on each insert when using HSQLDb in Memory running mode components. I will try to remember this fix if I need to use multiple HSQLDb components in future jobs in this manner. I appreciate your help.

Thanks and have a great day,

Tony Blaes 

[Anonymous]

This is likely to be because you have your lookup firing on each row. By that I mean that it is firing a new query for every row that comes in from your main source. This is arguably because the data that is added to the data at the end of your flow, is needed for subsequent rows in the same job. If you are able to carry out the lookup in one go at the beginning of the job, you would not get this issue. It should have been explained in the cookbook 

[Anonymous]

Hello Richard,

I understand your explanation. My point is that the cookbook code was already set up in the imported code zip file accompanying the book. There is an errata submission page on the publisher's web site to tell them about mistakes in the text. Do you think an errata should be submitted to tell the publisher to adjust the tHSQLDbOutput_2 components commit setting to 1? I spent a number of hours attempting to resolve the issue and believe I am probably not the only reader who has encountered this issue. Just wondered what your opinion would be.

Thanks again for the assistance,

Tony Blaes