topic Re: how to connect tContextLoad to tMysqlConnection in Talend Studio

how to connect tContextLoad to tMysqlConnection

Anonymous — Mon, 05 Nov 2012 02:30:21 GMT

Hi all,
I am trying to establish connection from tContextLoad to tMysqlConnection. Before to accomplish the process I have prepared an excel file that contain context variable for the database connection (e.g : host, port, username, database, table, password).
These context will be loaded from tFileInputExcel using tContextLoad. As the context successfully loaded, I try to connect tContextLoad to tMysqlConnection. This is to ensure that I can retrive the data based on the context variable that are stated in excel file without inserting any variables in the tMysqlConnection components. How do I get an automatic default database connection from the context I've loaded without putting anything in tMysqlInput component settings back?
Is this possible? Is there any alternative way to accomplish above processes?
Your help is very much appreciated. Thank you.

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Mon, 05 Nov 2012 08:08:53 GMT

Hi
The job design is not wrong, it should be:
tContextLoad
|
onsubjobok
|
tMysqlConnection
|
onsubjobok
|
tMysqlInput--main--tLogRow
|
onsubjobok
|
tMysqlCommit
tMysqlConnection: use context variable to set the db setting, create a db connection.
tMysqlInput: check the option 'use an existing connection', it will use the db connection created by tMysqlConnection.\
tMysqlCommit: commit the change and close the db connection.
Have a look at the user component manual and learn tContextLoad component, there is an example about this component .

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Mon, 05 Nov 2012 09:53:37 GMT

thank you so much shong..thank you for replying my questions..

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Mon, 05 Nov 2012 09:56:50 GMT

Dear Shong.
I am a little bit confuse on how to use the context variable to set the db setting in order to create a db connection. Can you show me the processes..
Thank you..

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Mon, 05 Nov 2012 18:23:23 GMT

hi
Define a context variable called host in this job. In the Host field of db component, set the field with context variable like:
context.host

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Tue, 06 Nov 2012 01:23:56 GMT

Dear Shong.
Thank you for replying my question. Is it possible that I set the context variable in the excel file as the picture above instead of I set the context variable in the field of db components?. If yes can you show me the steps that should be taken?
Thank You Shong.

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Tue, 06 Nov 2012 02:24:47 GMT

Hi
No, the db fieldds must be filled in static value or context variable. The value of context variable can be stored in excel file. Set the db fields with context variables, as my job showed, use tContextLoad to load dynamically the value from excel file at runtime.

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Tue, 06 Nov 2012 04:14:25 GMT

Hi Shong,
Do you know what is the meaning for these statement :
Exception in component tMysqlInput_1
java.sql.SQLException: Cannot convert value '1' from column 1 to TIMESTAMP.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1339)
at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:129)
at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6754)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6073)
at talend.tcontextload_0_1.tContextLoad.tMysqlInput_1Process(tContextLoad.java:1523)
at talend.tcontextload_0_1.tContextLoad.tMysqlConnection_1Process(tContextLoad.java:1170)
at talend.tcontextload_0_1.tContextLoad.tFileInputExcel_1Process(tContextLoad.java:1037)
at talend.tcontextload_0_1.tContextLoad.runJobInTOS(tContextLoad.java:1998)
at talend.tcontextload_0_1.tContextLoad.main(tContextLoad.java:1837)
disconnected
Caused by: java.sql.SQLException: Bad format for Timestamp '1' in column 1.
at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1313)
Thank You..

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Tue, 06 Nov 2012 05:03:15 GMT

Hi
As the error showed, bad format for timestamp "1". You define the data type of column1 as Date, but the value of this column is 1, so please check you really need Date type for this column or Integer/int type?

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Tue, 06 Nov 2012 06:24:09 GMT

thank you so much shong..

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Tue, 06 Nov 2012 07:08:46 GMT

Dear Shong,
Thank You for assisting me for the whole process. Your help are much appreciated. Thank You again.

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Fri, 09 Nov 2012 02:56:59 GMT

Dear Shong,
I have another situation where I want to create a connection using configuration file. At tMysqlConnection, I don't want to put any values at all. My intention is to create a database connection that thanks to a file not thanks to Talend.

Regards,
naim

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Fri, 09 Nov 2012 03:02:31 GMT

Hi
No, it is impossible to create a db connection from a file. This task have to be done on DB component. As I said, the file can store the db connection information, the job read these data and create db connection on DB component dynamically.

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Fri, 09 Nov 2012 08:02:24 GMT

Hi Shong,
Thank you for the information.
I have read the user document about tContextLoad. I just need to clarify what is the use of tContextLoad because I thought that tContextLoad will automatically load variables that I have defined in the config file. But when I want to connect a db; tMysqlConnection, i need to redefine the context variables exactly the same like variables in config file.
Actually, I just couldn't grasp the idea of how contextload is working.
Regards,
naim

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Sat, 10 Nov 2012 00:22:39 GMT

Yes, that's what exactly the tContextLoad component do. I think you define the configure the schema correctly. Let's say that you have two context variables in the job, host and dbname, the config file have the contents as below(note that the data format, host is the name of context variable, ":" is column separator, it can be other character, set the corresponding character in Field separator on tFileInputDelimited component):
host:192.168.0.23
dbname:talend
on schema of tFileInputDelimited, add two columns, key and value. // the column name must be key and value.
on tContextLoad, check the box 'print operation', and you will see the detailed load operation on the console during the job execution.
Shong

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Wed, 14 Nov 2012 02:17:16 GMT

Hi Shong,

Thank you for the reply.
I understand and have followed through all of your steps mentioned above.Sorry if I'm asking this twice but tContextLoad actually doesn't load visibly, automatically the variables from a file (external) into the Job contexts in Talend isn't it? It's just set and read the variables during Job execution when I check the 'print operation' right? Because in the Job context panel, there is no context variables at all been loaded after I hit run.

Thank You
Regards,
Naim

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Wed, 14 Nov 2012 02:25:45 GMT

Hi
Can you email me your job and the file which contains the variable value? I will see what are you doing wrong.

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Fri, 23 Nov 2012 01:12:03 GMT

Hi Shong,
Do you have any idea about the job that I have sent through your email?
Regards,
Naim.

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Sat, 24 Nov 2012 18:41:25 GMT

Hi Shong,
Do you have any idea about the job that I have sent through your email?
Regards,
Naim.

Hi
Sorry, I did't receive your email yet, can you try it again?
Shong

Re: how to connect tContextLoad to tMysqlConnection

Anonymous — Tue, 27 Nov 2012 06:20:08 GMT

Hi,
I have sent the job into you email accounts.
Thank you.