Talend community forum / Getting dynamic Database connection info inside a job

raviprakash007 · 2011-03-28 15:10:02

Tags: [context, database, delimited file, deployment, java, mysql, positional file, schema]

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.

Last edited by raviprakash007 (2011-03-28 15:12:08)

JohnGarrettMartin · 2011-03-28 19:41:03

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

raviprakash007 · 2011-03-29 10:39:14

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.

[statistics] connecting to socket on port 3807
[statistics] 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
[statistics] disconnected
Job testjob ended at 13:43 29/03/2011. [exit code=1]

-----------------------------------------

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.

Uploaded Images

Last edited by raviprakash007 (2011-03-29 10:42:20)

lijolawrance · 2011-03-30 05:14:40

Hi

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

Announcement

Unanswered posts

#1 2011-03-28 15:10:02

Getting dynamic Database connection info inside a job

#2 2011-03-28 19:41:03

Re: Getting dynamic Database connection info inside a job

#3 2011-03-29 10:39:14

Re: Getting dynamic Database connection info inside a job

#4 2011-03-30 05:14:40

Re: Getting dynamic Database connection info inside a job

Board footer