topic Re: Two tDBConnection components, one doesn't work in Talend Studio

Two tDBConnection components, one doesn't work

PhilHibbs — Fri, 15 Nov 2024 21:54:48 GMT

I have a simple extract/transform/load job. It has two tDBConnection components, one for extract and one for load, hooked to a tPreJob, then a read/transform/write subjob.

The two tDBConnections actually connect to the same database, but with different default schemas. I want two separate connections so that they could in theory be different databases.

The extract works fine, and the load appears to work but the destination table doesn't get created. I don't think the data goes anywhere, if it is being created and loaded somewhere then I don't know where that is!

If I use the same tDBConnection for both the extract and the load, then it works and the table is created and the data loaded.

It doesn't matter which of the two tDBConnection objects I use - if I delete the extract connection and point the extract to use the load connection, then it extracts and loads just fine using the load tDBConnection that didn't work earlier!

is there anything about the tDBConnection component that would cause a second one in the tPreJob flow to simply not work?

Re: Two tDBConnection components, one doesn't work

Anonymous — Wed, 12 Apr 2023 00:49:13 GMT

Hello @Phil Hibbs ,

As you use different schemas for the 2 tDBConnection, so please double check if the destination table is created in the related schema in database side?

Thanks

Aiming

Re: Two tDBConnection components, one doesn't work

PhilHibbs — Wed, 12 Apr 2023 10:35:15 GMT

Yes, the source tables are in the source schema, the destination tables are in the destination shcema. If we use two different tDBConnection components to connect, then anything writing to the second tDBConnection does not end up being written (or is being rolled back). If we use built-in connection details, then it works. And if we delete the first tDBConnection and use built-in for the source schema, then the destination loads also work so it isn't the connection or the database schema that is at fault.

Re: Two tDBConnection components, one doesn't work

Anonymous — Wed, 12 Apr 2023 11:23:45 GMT

It sounds weird, they are completely two different database connections. I want to see your job design, can you please share a screenshot of Job?

Re: Two tDBConnection components, one doesn't work

mks02 — Thu, 13 Apr 2023 03:56:52 GMT

Hi @Phil Hibbs

I guess data is not committing in the db tables. Please check any of these below:

1) On tDBConnection got to Advance settings --> check the AutoCommit box.

2) alternatively after tPostJob you can use tDBCommit to commit the DB transactions.

Thanks!