Dynamically change DB Connection

Highlighted
Community Manager

Dynamically change DB Connection

I was asked not so long ago how to dynamically change a connection in a Talend Job. I was curious about a use case for this as I hadn't really considered doing this before. The use case was that they had a standard job that they wanted to reuse across all of their environments. "Why not use the Context Variables?" was my response. I was then given the real reason that this is needed. Some of their environments used a SID to connect, others used a Service Name (they were using Oracle). If you look at the tOracleConnection component, you cannot parameterise this. You have to select one or the other and cannot modify the selected value after compilation time. At least, you can't without a bit of code. This mini tutorial focuses on doing this.

The screenshot below shows a high level layout for this to work. I will talk through each of the numbers and explain the significance of each.

 

DynamicConnectionSwitching.jpg

 

1) The "Dummy" component is an empty tJava component. Its purpose is just a starting point.

2) This "RunIf" is triggered if a Context Variable called "UseSID" (of type Boolean) is true. If it is true, the intention is to run with the SID connection.

3) This "RunIf" is triggered if a Context Variable called "UseSID" (of type Boolean) is false. If it is false, the intention is to run with the Service Name connection.

4) This is a tOracleConnection set up to use SID. If you check the code, you will see that it has been given the name conn_tOracleConnection_1.

5) This is a tOracleConnection set up to use Service Name. If you check the code, you will see that it has been given the name conn_tOracleConnection_2.

6) This "RunIf" is triggered if a Context Variable called "UseSID" (of type Boolean) is true. If it is true, the intention is to run with the SID connection.

7) This "RunIf" is triggered if a Context Variable called "UseSID" (of type Boolean) is false. If it is false, the intention is to run with the Service Name connection.

8) The "Set Connection" component is a tJava. This is where the magic happens. In this component I have the following code....
 

//Change the connection value of "conn_tOracleConnection_1" if not context.UseSID
if(!context.UseSID){
    java.sql.Connection conn_tOracleConnection_2 = (java.sql.Connection)globalMap.get("conn_tOracleConnection_2");
    globalMap.put("conn_tOracleConnection_1",conn_tOracleConnection_2);
}

If you click on the "Code" tab on the designer, you can search for the actual names of the tOracleConnection components. For Oracle components they will have "conn_tOracleConnection_" followed by a number. You need to work out which number corresponds to your SID connection and Service Name connection. In this case "1" corresponds to the SID connection and "2" corresponds to the Service Name connection. We are going to use tOracleConnection_1 for all of our tOracle components. So what we are doing here is replacing the value of the connection held by the conn_tOracleConnection_1 variable. If we want to use the SID connection, we leave the connection as it is. If we want to use the Service Name connection, we replace conn_tOracleConnection_1 with the value of conn_tOracleConnection_2.

9) This tOracleInput component is simply used to return values from the database using whichever connection is specified by the UseSID context variable. The existing connection it is set to use is "tOracleConnection_1". This will actually be whatever we have set it to be in the previous "Set Connection" component.

 

Hopefully that is pretty clear, but if you have any questions please feel free to ask below....

Six Stars

Re: Dynamically change DB Connection

Very very impressive!!! great thinking.
Six Stars

Re: Dynamically change DB Connection

pls share the code using in runif
Four Stars

Re: Dynamically change DB Connection

Hello,
Similar way, I would like to change the SQL query columns dynamically in talend. I am fetching the columns values from one file and I was trying to store those values in Context Variable. So for every iteration it will choose different value and it will change the SQL column in query. Later I would like to store output file system with the name of value which we have retrieved from variable i.e. <Variable_Value>.dat

Could you please help in this use case? or Did somebody try this scenario before?

Thank you
Nine Stars

Re: Dynamically change DB Connection

I suggest you use java to dynamically build you sql request and store it in a variable (it can be a context variable)

Then you use it later
The commercial version provides a way to build dynamic shema by simple using the data type " dynamic"

on Tos, I suggest generic names (column1, culumn2, ...) for the shema

Regards
DGM
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Dynamically change DB Connection

Hello Experts,

 

Looks like we can not shuffle between connection types in Talend...in this context it was not easy to shuffle between connection types ORACLE_SID and ORACLE_SERVICENAME. We know that this is not a ongoing scenario where on one day you want to fetch data from SID and another day you want to fetch from Service Name connection data sources.

 

Just trying to understand, what if i take the jobscript, do a find/replace and replace ORACLE_SID with ORACLE_SERVICENAME and save it... do you think it would still serve the purpose? because as long as the underlying DB objects remain the same, we should still be able to play around the connection types (provided we have the metadata of the tables defined for both the connection types)

 

Share your thoughts please.

  

Community Manager

Re: Dynamically change DB Connection

Your use case is what my post is demonstrating is it not? 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads