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

[raowaqasakram]

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?

[Anonymous]

Hi

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 tDBO​utput 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-->tDBOutput​2

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

[raowaqasakram]

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?

[Anonymous · Accepted solution]

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

[raowaqasakram]

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.