topic Re: Continue Job Iterations after SQL Connection Failure in Talend Studio

Continue Job Iterations after SQL Connection Failure

Papademuchos — Fri, 15 Nov 2024 22:05:16 GMT

I'd like to iterate through several databases and run a query to collect certain values. My problem is that the user account I'm using doesn't have access / permission to all of the databases on my list. Here's my steps:

Query 1: Retrieve a list of values from a common table. This list includes the server name and the database name.

FlowToIterate: I want to iterate through each of the records returned and run a query

Query 2: Using the current iteration, query a table and retrieve a value.

Problem: Some of the databases on returned from Query 1 are not accessible for the user account. Talend stops with a SQLException (cannot open database).

Question: How do I ignore this and continue to the next iteration? I've tried several components (tDBConnection, tDBInput, tDBRow...) They all cause the job to stop immediately. I've tried "OnComponentError" hoping it would execute that branch and then continue, but it doesn't.

I'm looking for some way to test the connection without failing the job.

Any ideas or work-arounds are appreciated!

Re: Continue Job Iterations after SQL Connection Failure

Anonymous — Mon, 20 Feb 2023 02:39:56 GMT

Do you want to catch the exception information and log them to somewhere? or don't need the logs, just want to ignore the exception and continue to iterate next record?

Regards

Shong

Re: Continue Job Iterations after SQL Connection Failure

Oliemar84 — Mon, 20 Feb 2023 14:54:31 GMT

You could setup Docker and run your own set of DB's from there.

Or use the fixedflowinput insteadof DB's you don't have access to and mimick the values which you need.

Re: Continue Job Iterations after SQL Connection Failure

Papademuchos — Tue, 21 Feb 2023 15:43:32 GMT

Both would be good, but primarily to ignore the exception and continue iterating to the next record.

Re: Continue Job Iterations after SQL Connection Failure

Papademuchos — Tue, 21 Feb 2023 15:47:08 GMT

Thanks, but I don't have the option of setting up different databases. FixedFlowInput doesn't really work in this scenario - I need to read a value from client databases, which will vary from client to client.

Re: Continue Job Iterations after SQL Connection Failure

Anonymous — Wed, 22 Feb 2023 02:21:45 GMT

@Torry Slaton , go to the tip page and read the tip of Step 27, 2022. This tip shows you how to ignore the exception and continue to iterate the next record. Please try and let me know if you have any questions.

Regards

Shong

Re: Continue Job Iterations after SQL Connection Failure

Papademuchos — Wed, 22 Feb 2023 16:49:08 GMT

Thanks for the suggestion. My situation is different. In the "Tip of the Day" example, the test is whether an inbound record is valid or not. In my example, the test is whether a database connection is valid or not. The list of database values coming from the left are fine - there is not a problem with the list. It's the connection after the iteration that's blowing up. I tried the Try/Catch suggestion anyways and it still didn't work. It still stops on the first database connection that it has a problem with and doesn't continue iterating. 😞