tFlowToIterate passing database details into

[Anonymous]

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.

[Anonymous]

Sorry, screenshots here.

[Anonymous]

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

Regards,
Pedro

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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();
}
}