topic tFlowToIterate passing database details into in Talend Studio

tFlowToIterate passing database details into

Anonymous — Sat, 16 Nov 2024 12:20:51 GMT

Hi,

I have written a job previously (with help from guys on here) that reads a list of delete statements from a table and runs them against a database.

I'd like to expand this job to read some target database details from a table then apply the delete statements, so using the first solution but by adding a tFlowToIterate.

Screenshots are below;

- tInformixInput3 - get target details for all target databases
- tFlowToIterate
- tInformixConnection2 - accept database details from tInformixInput3 as input parameters
- tInformixInput2 - read delete statements
- tJavaRow2 - apply deletes to the connection defined in tInformixConnection2

I get the following error, which is strange because the machine mentioned in the error message is actually my desktop, all the databases are held on other servers.

Exception in component tInformixConnection_2
java.sql.SQLException: Incorrect password or user com.informix.asf.IfxASFRemoteException: informix@st-iptv05.uk.tiscali.intl is not known on the database server.
at

I would be grateful for any ideas.

Re: tFlowToIterate passing database details into

Anonymous — Tue, 28 Feb 2012 08:49:55 GMT

Sorry, screenshots here.

Re: tFlowToIterate passing database details into

Anonymous — Tue, 28 Feb 2012 08:54:35 GMT

Hi
You type wrong strings in tInformixConnection. It should be like "row1.host".

Regards,
Pedro

Re: tFlowToIterate passing database details into

Anonymous — Tue, 28 Feb 2012 08:55:02 GMT

Hi,
First of all, why doesn't the tInformixInput_1 component use the connection available through tInformixConnection_2 ?

Second: I think the connection parameters used in the tInformixConnection_2 should not refer to row2. The flow to iterate should put the row into global map variables making them directly accessible to the connection component. However I'm not sure this is the case.
Hope this helps!
Regards,
Arno

Re: tFlowToIterate passing database details into

Anonymous — Tue, 28 Feb 2012 09:00:36 GMT

Hi Pedro,
I do not understand what you mean by your last post stating that a Talend job cannot support a dynamic connection. Isn't a repository based connection as variable as TT1 is trying to use?
I've been using dynamic connection variables for a long time, they're being passed to my jobs as command line parameters, but what's the difference with reading them from a different database (of which you have the credential already of course
Or am I completely misunderstanding what you are trying to say?
Regards,
Arno

Re: tFlowToIterate passing database details into

Anonymous — Tue, 28 Feb 2012 09:04:31 GMT

Hi Arno
You are right.
I reproduced it in a wrong way.
Regards,
Pedro

Re: tFlowToIterate passing database details into

Anonymous — Tue, 28 Feb 2012 09:34:37 GMT

guys, thanks for answering;

firstly I think you do need the global variables, and not just "row1.host"

I have a database that contains a table that houses all the target database details, and a table that houses a bunch of delete statements. The target details are read by tInformixInput3, the delete statements in tInformixInput2.

Re: tFlowToIterate passing database details into

Anonymous — Tue, 28 Feb 2012 16:04:55 GMT

I think the answer is to set up a bunch of contexts and run a single job many times. Shame.

Re: tFlowToIterate passing database details into

Anonymous — Wed, 29 Feb 2012 07:53:40 GMT

Hi,
I cannot accept this as a solution, there must be a better way to solve this. So, I've tried to recreate your situation, however I don't have an informix database.
My solution seems to be working, that is: I can iterate over a set of connection details, connect to the different databases and perform selects on their tables.
Can you show what is inside the tJavaRow component in your screenshots?
If you need it I could post an export of my solution, again: no informix on my side, so it's a bit different.
Best regards,
Arno

Re: tFlowToIterate passing database details into

Anonymous — Wed, 29 Feb 2012 09:05:26 GMT

avdbrink, thank you for helping.
Here is the tJavaRow content, as you can see it is picking up the connection detail from tInformixConnection_2 (in turn coming via a globalMap.get from the database connection read.
String astring = "Begin work without replication;";
String bstring = "; commit;";
String cstring = input_row.delete_statement;
String delStringtemp = astring.concat(cstring);
String delString = delStringtemp.concat(bstring);
System.out.println(delString);
boolean success = true;
java.sql.Connection conn = (java.sql.Connection)globalMap.get("conn_tInformixConnection_2");
java.sql.PreparedStatement pstmtDelete_list = null;
try{
pstmtDelete_list = conn.prepareStatement(delString);
}
catch (Exception e){
success = false;
e.printStackTrace();
}

if( success && pstmtDelete_list != null){
try {
pstmtDelete_list.executeUpdate();
System.out.println("List tables truncated");
}
catch (Exception e){
e.printStackTrace();
}
}

Re: tFlowToIterate passing database details into

Anonymous — Wed, 29 Feb 2012 09:40:03 GMT

arno, would love a screenshot of your solution please.
john.

Re: tFlowToIterate passing database details into

Anonymous — Wed, 29 Feb 2012 10:08:50 GMT

Hi,
Couldn't you use a tInformixRow component, instead of the tJavaRow component? I think this could help solving the connection issue. You can activate "Use an existing connection" and put something like the following code in the Query field:

"Begin work without replication; " + row1.delete_statement + "; commit;"

I've tried this with my sql database, but see no reason why this shouldn't work for Informix.
Hope this helps!
Regards,
Arno

Re: tFlowToIterate passing database details into

Anonymous — Wed, 29 Feb 2012 10:23:36 GMT

Hi,
I read about your question for screen shots just after my last post, but here they come:
First screenshot shows an overview of the job. For testing purpose I added a Rowgenerator to fill 2 tables in 2 different schemes, you can of course ignore this.
tMysqlInput_1 reads connection details from a table named connections Screen 2.
On screen 3 the connection details are set into variables using the flow to iterate
The tMysqlConnection_1 then uses these connection details in screen 4, and the tMysqlInput then uses this connection in screen 5
Screen 6 finally shows the use of tMysqlRow which executes the statement on the existing connection tMysqlConnection_1.
You could of course use an additional connection to perform the delete actions on, this would require an additional tMysqlConnection component, and some extra connection details in the first database.
Hope this makes my solution more clear.
Best regards,
Arno

Re: tFlowToIterate passing database details into

Anonymous — Wed, 29 Feb 2012 11:49:42 GMT

Arno, many thanks for the screenshots and suggestions, I'm working on this today, and will let you know how I get on. John.

Re: tFlowToIterate passing database details into

Anonymous — Wed, 29 Feb 2012 14:56:30 GMT

Success !!! Arno, many thanks for your help, I'm up and running.

A database issue was causing problems, the error message was not ideal, but working against your solution allowed me to track through and find the issue.

Thanks again.

Re: tFlowToIterate passing database details into

Anonymous — Wed, 29 Feb 2012 15:06:50 GMT

Great that it worked out!
I'm glad I could help.
Best regards,
Arno

Re: tFlowToIterate passing database details into

Anonymous — Wed, 29 Feb 2012 20:48:10 GMT

Here's some clarity on why this didnt work the first time around, but does work with the tDBConnection components:
The connection is established when the "Start" section of the components code is executed. Since your entire job was a single subjob, the Start section initializes only once, and youre stuck with whatever values were present when the job started.
You can get around that by using a tDBConnection component-- which makes the connection in the "Main" section of its code, or by moving the database components into their own job.