Paramaterise db connection. Help me with logic please!!

[nivedhitha]

Hi,

I have a situation where I have to get the metadata of different tables used in a project and insert these details into a different table.

But the problem is these projects can have tables in different databases like db2 or SQL Server etc. I have to be able to connect to different databases dynamically to get the metadata depending on the input data.

My input data will have details like source(db2,sql server etc), database, schema and table name.

Solution I have in mind:

1. Create context variables for different parameters of the tDbInput component like host, username,password,db,schema.

2. pass the output of dbinput(list of tables to get metadata) to tflowtoiterate and check the sourcedb. If it is SQL Server , then assign the context variables with credentials to connect to SQL Server using tJAVA.

But the problem with this approach is ,in the tDbInput component everything can be passed dynamically but not the database(marked in the image below)

Is there a way to decide the database dynamically? Please provide me with some suggestions

Thanks in advance

[Anonymous]

Hi,

   If you are looking for generic connections, you will have to go for tJDBC set of DB components. Even in that case also you will have to handle the drivers and associated details.

   Please note that native components always will give more performance compared to JDBC Components. But you cannot dynamically change the native components during runtime.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[nivedhitha]

Hi @nthampi ,

Thanks for your response.

I have no experience with JDBC components. I will do some reading and get back if I have questions

[Anonymous]

Sounds good. Please note that it may not be straightforward solution as you will have to handle different associated drivers.

In your case, the best way is to handle as separate components for each DB types. I would always request to give it a try as it will help you to enhance your knowledge 🙂

Also, please remember to mark the topic as resolved once you are happy with the details.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[nivedhitha]

@nthampi ,

I would also prefer using different DB components for different database. 

If I choose this method, then the approach I ve to take is, check the input records with run if and direct the control to different db components based on the data.

Please let me know if my assumption is right

[Anonymous · Accepted solution]

@nivedhitha 

You are right. You can group the data to multiple output datasets. 

Then based on the categorization, you can move them to required output DBs. I would say, you should try to do it using tMap filter conditions at output so that you can pump the entire dataset in one shot rather than iterative model (where one record will be processed at a time)

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂