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.
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|
java.sql.Connection conn_tOracleConnection_2 = (java.sql.Connection)globalMap.get("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....
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.
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Watch the recorded webinar!
Find out about Talend's 2019 Summer release
Talend continues to revolutionize how businesses leverage speed and manage scale
Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend