topic Re: tInformixRow in Talend Studio

tInformixRow

_AnonymousUser — Sat, 16 Nov 2024 13:47:49 GMT

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.

Re: tInformixRow

_AnonymousUser — Fri, 28 Aug 2009 14:38:50 GMT

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.

Re: tInformixRow

Anonymous — Tue, 22 Sep 2009 17:22:53 GMT

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.

Re: tInformixRow

Anonymous — Wed, 23 Sep 2009 07:56:33 GMT

Hello Oramon

java.sql.SQLException: A syntax error has occurred.

Can you show us your sql statement? I check informix11.5 on TOS3.1.4 and it works fine.
Best regards

shong

Re: tInformixRow

Anonymous — Wed, 23 Sep 2009 18:42:00 GMT

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");

Re: tInformixRow

Anonymous — Thu, 24 Sep 2009 04:34:16 GMT

Hello

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

shong