topic Getting dynamic Database connection info inside a job in Talend Studio

Getting dynamic Database connection info inside a job

Anonymous — Sat, 16 Nov 2024 13:00:44 GMT

I am fetching data from multiple tables(using tMysqlInput ) and putting it into other set of multiple tables(using tMysqlOutput ). For setting database connection information of tMysqlInput and tMysqlOutput I'm using "DB Connections" in Metadata section.
I want to deploy ETL job on Tomcat as WAR. When I start the server my ETL job should get database connection settings from context.xml which may look like:
------------------------------------------------------------------------------------------------------
<Resource name="jdbc/myDB1" auth="Container" type="javax.sql.DataSource"

initialSize="10" maxActive="30" maxIdle="15" minIdle="3" maxWait="10000"
suspectTimeout="60" timeBetweenEvictionRunsMillis="30000" minEvictableIdleTimeMillis="60000" testOnBorrow="true"

validationInterval="3600000" validationQuery="SELECT 1"
driverClassName="com.mysql.jdbc.Driver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

username="root" password="root" url="jdbc:mysql://mydb1"/>
<Resource name="jdbc/myDB2" auth="Container" type="javax.sql.DataSource"

initialSize="10" maxActive="30" maxIdle="15" minIdle="3" maxWait="10000"
suspectTimeout="60" timeBetweenEvictionRunsMillis="30000" minEvictableIdleTimeMillis="60000" testOnBorrow="true"

validationInterval="3600000" validationQuery="SELECT 1"
driverClassName="com.mysql.jdbc.Driver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

username="root" password="root" url="jdbc:mysql://mydb2"/>
------------------------------------------------------------------------------------------------------
Since server location can change. I should only change DB setting in context.xml and my ETL should pick it from there every time it is started.
Kindly help.

Re: Getting dynamic Database connection info inside a job

Anonymous — Mon, 28 Mar 2011 18:41:03 GMT

take a look at the tContextLoad component. This will allow you to parse your configuration file and populate all the db context variables.

Re: Getting dynamic Database connection info inside a job

Anonymous — Tue, 29 Mar 2011 09:39:14 GMT

My text file contains entries:
-----------------
host;localhost
port;3306
database;mydb
username;root
password;root
-----------------
I have attached JPG of what I'm doing. tFileInputDelimited is reading the text file. Log is

-----------------------------------------
Starting job testjob at 13:43 29/03/2011.
connecting to socket on port 3807
connected
2011-03-29 13:43:34|ReZVpL|ReZVpL|ReZVpL|4664|TALENDDEMOSJAVA|testjob|_2N3S8FlAEeCjBbsqNga8JQ|0.1|Default||begin||
tContextLoad_1 set key "host" with value "localhost"
tContextLoad_1 set key "port" with value "3306"
tContextLoad_1 set key "database" with value "mydb"
tContextLoad_1 set key "username" with value "root"
tContextLoad_1 set key "password" with value "root"
Warning: Parameter "host" is a new parameter of tContextLoad_1
Warning: Parameter "port" is a new parameter of tContextLoad_1
Warning: Parameter "database" is a new parameter of tContextLoad_1
Warning: Parameter "username" is a new parameter of tContextLoad_1
Warning: Parameter "password" is a new parameter of tContextLoad_1
Exception in component tMysqlOutput_1
java.lang.NullPointerException
at talenddemosjava.testjob_0_1.testjob.tMysqlInput_1Process(testjob.java:1208)
at talenddemosjava.testjob_0_1.testjob.tFileInputDelimited_1Process(testjob.java:764)
at talenddemosjava.testjob_0_1.testjob.runJobInTOS(testjob.java:4939)
at talenddemosjava.testjob_0_1.testjob.main(testjob.java:4803)
2011-03-29 13:43:34|ReZVpL|ReZVpL|ReZVpL|4664|TALENDDEMOSJAVA|testjob|_2N3S8FlAEeCjBbsqNga8JQ|0.1|Default||end|failure|130
disconnected
Job testjob ended at 13:43 29/03/2011.
-----------------------------------------
My problem is how to propagate the database connection information from Text file to tMySqlInput's and tMySqlOutput's which are in a long chain.

I have even tried using tFileInputXML for reading XML file. On running the Job XML data is fetched by the component but is not passed to tMySqlInput.
I need help in passing the database connection information from XML or Text file to a long chain of tMySqlInput's and tMySqlOutput's which ultimately populates a final DataBase.
If someone has used and successfully implemented it can post Basic Setting information,Schema etc info will help me alot. I have read the documentation and know the theory but facing while implementation.

Re: Getting dynamic Database connection info inside a job

Anonymous — Wed, 30 Mar 2011 04:14:40 GMT

Hi
use TJavaRow and manually save your values in the context variable and try.