topic Re: Dynamic compatibility of jobs with different DBMS like MsSQL , MySQL , Postgres in Talend Studio

Dynamic compatibility of jobs with different DBMS like MsSQL , MySQL , Postgres

raowaqasakram — Fri, 15 Nov 2024 22:34:48 GMT

I have create a job in which I have used "tDBOutput - Microsoft SQL Server" to store the data into into MS-SQL DB.

I have providing the connection details (ip, port, username, password, db) etc from context variables.

Now my requirement is to provide connection details from context var and the job should automatically loads the data into target DB whether it would be MSSQL, MySQL or any other.

How to pass the DBMS type in context variables and the job should dynamically set the target DB based on this type?

Re: Dynamic compatibility of jobs with different DBMS like MsSQL , MySQL , Postgres

Anonymous — Wed, 07 Sep 2022 09:40:02 GMT

The Database field does not support a dynamic value, you have to select the DB type at design time. A workaround is to use different tDBOutput components for each db type and use runIf connector to fire each tDBOutput，for example:

....store the data to memory or temporary file-->tHashOutput (or tFileOutputDelimited)

|onsubjobok

tJava--runIf1---tHashInput1--main-->tDBOutput1

******--runIf2---tHashInput2--main-->tDBOutput2

define a context variable called db_type, the variable store the db type such as "mysql" and it will be used in the expression of runIf connector, load the value to context variable at runtime or use the default value:

runIf1: context.db_type.equals("mysql")

runIf2: context.db_type.equals("mssql")

....

Regards

Shong

Re: Dynamic compatibility of jobs with different DBMS like MsSQL , MySQL , Postgres

raowaqasakram — Wed, 07 Sep 2022 09:50:24 GMT

Hi,

I got the idea. But i'm not using tHashOutput.

My flow is tMongoDBInput --> tJavaRow --> tMap --> tLogRow --> tDBOutput

As per your answer it should be like.

tMongoDBInput --> tJavaRow --> tMap --> tLogRow (run-if sql) --> tDBOutput -Microsft SQL

or (run-if mysql) --> tDBOutput - MySQL

but I'am unable to connect tLogRow with tDBOutput using run-if.

Can you please comment the updated flow?

Re: Dynamic compatibility of jobs with different DBMS like MsSQL , MySQL , Postgres

Anonymous — Wed, 07 Sep 2022 10:37:02 GMT

In your case, you use tLogRow to print the data to console, but you need to store the data into memory or temporary file first, and then read the data back from memory or temporary file in next subjob. tDBOutput is output component that requires an input component, the runIf is a trigger connector, it does not transfer data flow to next component, so

change your job to:

tMongoDBInput --> tJavaRow --> tMap --> tHashOutput

tJava--- (run-if sql) --> tDBOutput -Microsft SQL

****** --- (run-if mysql) --> tDBOutput -Mysql

Re: Dynamic compatibility of jobs with different DBMS like MsSQL , MySQL , Postgres

raowaqasakram — Wed, 07 Sep 2022 11:03:31 GMT

It's getting connected via flow.

tMongoDBInput --> tJavaRow --> tMap --> tHashOutput --run-if-->tJava--> tDBOutput -Microsft SQL

thanks for this answer. Let me implement and test it.