Getting dynamic Database connection info inside a job
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"
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.
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.
