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
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
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
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.
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.
