topic How to Use teo different database connection in tdbrow? in Talend Studio

How to Use teo different database connection in tdbrow?

Banupriya — Fri, 15 Nov 2024 22:00:47 GMT

I want to load data from one (source) database to another (target)database using insert query in tdbrow. But I can select only one db connection in tdbrow. How to achieve this? It's giving the error table does not exist because I have selected the target db connection so it's failed to get data from source DB.

Re: How to Use teo different database connection in tdbrow?

Anonymous — Fri, 10 Mar 2023 06:00:44 GMT

Hello,

You need to structure the job something like:

t<DB>Input -> tFlowToIterate -> tDBRow(Insert sql query for target DB)

And your tDBRow component will need to reference the globalMap variables created between the source DB and flow to iterate.

Or just t<DB>Input ->t<DB>output with key in schema setting efficiently.

Best regards

Sabrina

Re: How to Use teo different database connection in tdbrow?

Banupriya — Fri, 10 Mar 2023 06:24:50 GMT

Hi,

Thank you for your reply.

We have set of tables to be loaded from source to target databse. It's not a simple data migration but the source and target database tables should be in sync on daily basis. So for some table we have to do insert or update as well.

We have defined a dynamic schema in the tdbinput and fetched the select query dynamically for the source db and inserted the data to target db using tdboutput dynamic schema.

Issue comes when we have to insert or update the data in tdboutput. It's required key columns for this operation. But due do dynamic schema we can not define key column dynamically. Also key column is different for different table.

How to set the key columns dynamically in tdboutput? .

We have tried with tdbrow and fetched the merge query dynamically to do this insert or update. But in tdbrow we can select one db at a time so it's not getting tables from source DB.

Is there any other way to achieve this?

Please guide here.

Re: How to Use teo different database connection in tdbrow?

Anonymous — Fri, 10 Mar 2023 06:59:19 GMT

Hello,

It is not possible to select two DB connections at one time I'm afraid.

We are supposing you've checked talend CDC feature already as it captures only the changed source data and to move it from a source to a target system(s)

TalendHelpCenter: Change Data Capture (CDC)

Note: CDC can not work on two different DBs

There is no such automatic "Sync" process between two different Databases.

You have to compare tables to get the updated data then insert it to another DB by manual.

For example:

t<DB>input(targetDB)-->main-->tMap-->t<DB>output

t<DB>input(sourceDB)-->lookup-->

Make a inner join and set "Catch Look up inner join reject " as true and the processed data should be updated data, then insert it into another to keep the two database "Sync".

Best regards

Sabrina

Re: How to Use teo different database connection in tdbrow?

Banupriya — Fri, 10 Mar 2023 10:41:09 GMT

Hi,

Thank you.

We can do the steps that you have recommended for only one table. But for multiple table as a genric solution how to achieve it?

Could you please confirm is it possible to set the key columns dynamically in tdboutput which is having the dynamic schema for insert or update?

Re: How to Use teo different database connection in tdbrow?

Anonymous — Mon, 13 Mar 2023 05:21:47 GMT

Hello,

I have no idea about this kind of dynamic key columns.

Could you please indicate which talend solution are you using? Does talend Change Data Capture (CDC) functionality work for your use case?

Best regards

Sabrina

Re: How to Use teo different database connection in tdbrow?

AMale — Tue, 03 Sep 2024 09:36:58 GMT

Hi everyone,

Does Talend had an update and now we can used different connexion in one unique tDBRow please ?

Thank you a lot in advance !

Best regards

Alexis