I am currently using TOS to pull data from an informix v7 db into an excel speadsheet.
With basic select query's eg:
SELECT unit FROM unit WHERE scheme = '001';
The tInformixInput Component will perform the task perfectly well.
If for example I need to execute statements on the db before and after a select query eg:
SELECT unit FROM unit WHERE scheme = '001' INTO TEMP matc1; SELECT * FROM matc1; DROP table matc1;
The above code will execute fine if I run it as a metadata query (Not as part of a job).
To run the above code in a job I believe I should be breaking the SQL up into 3 parts using tInformixRow for the first and last statments and tInformixInput for the 2nd statment, but I am having no luck getting any kind of tInformixRow statement to run as part of a job:
Exception in component tInformixRow_1 java.sql.SQLException: Transactions not supported at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:397) at com.informix.jdbc.IfxSqliConnect.setAutoCommit(IfxSqliConnect.java:1858) at os_test_1.mat_test_0_1.Mat_test.tInformixRow_1Process(Mat_test.java:199) at os_test_1.mat_test_0_1.Mat_test.runJobInTOS(Mat_test.java:386) at os_test_1.mat_test_0_1.Mat_test.main(Mat_test.java:295)
I believe tInformixRow just does not seem to be working?
Any help would be very appreciated.
Thanks,
Mat.
Ok..... I think the problem is the connection closes after every SQL statement, so any temp tables are lost. I need to use a persistant connection, but unformtunately that connector doesn't exist. I will submit a feature request.
I'm also getting errors when trying to get data from Informix, we are running V 7.23. Does anyone know what versions of Informix are supported by Talend Open Studio?
I get the following errors:
Starting job infx01 at 11:14 22/09/2009.
Exception in component tInformixInput_1
java.sql.SQLException: A syntax error has occurred.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:397)
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3244)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3553)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2380)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2295)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1461)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1434)
at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:205)
at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1228)
at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218)
at talenddemosjava.infx01_0_1.infx01.tInformixInput_1Process(infx01.java:687)
at talenddemosjava.infx01_0_1.infx01.runJobInTOS(infx01.java:1436)
at talenddemosjava.infx01_0_1.infx01.main(infx01.java:1353)
Caused by: java.sql.SQLException
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:397)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3558)
... 10 more
Job infx01 ended at 11:14 22/09/2009.
One problem I found is that when the SQL is created the FROM statement is not picking the table name it sets it to NULL
java.sql.ResultSet rs_tInformixInput_1 = stmt_tInformixInput_1
.executeQuery("SELECT trans2000.trans_id, trans2000.trans_type FROM null");
One problem I found is that when the SQL is created the FROM statement is not picking the table name it sets it to NULL
Yes, you should check if have typed in the table name. After you define the schema, you can click on 'guess query' button to generate sql statement auto.
Best regards
