I'm currently involved in a very large project where we have to transfer data from our Informix DB to PostgreSQL.
I am trying to test using Talend Open Studio and I'm running into some problems. I created a job to test and keep getting the following:
Starting job Cards at 15:07 25/06/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 nitro.cards_0_1.Cards.tInformixInput_1Process(Cards.java:1399)
at nitro.cards_0_1.Cards.runJobInTOS(Cards.java:2087)
at nitro.cards_0_1.Cards.main(Cards.java:2005)
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
I'm using:
tInformixInput
tMap -- Since the table structures are different
tPostgresqlOutput
I thought this was all I needed but I'm getting the above errors.
Hello,
I think you have a problem with your SQL Statement on the informix component.
Can you give us your SQL Query? What is the result of "Guess Schema" button on the tInformixInput component?
Thank,
This is the sql produced by Guess query,
"SELECT account.\"account \",
account.\"pin \",
account.\"client_id \",
account.\"group_id \",
account.\"pref_id \",
account.\"custcode \",
account.\"cust_host \",
account.\"loy_percent \",
account.\"expire_date \",
account.\"status \",
account.\"create_type \",
account.\"life_cycle_status \",
account.\"card_type \",
account.\"activated \",
account.\"enrolled \",
account.\"loy_redemp_ok \",
account.\"activated_date \",
account.\"enroll_date \",
account.\"issuer \",
account.\"card_is_test \",
account.\"create_id \",
account.\"create_dtime \",
account.\"deact_dtime \",
account.\"min_bal \"
FROM account"
but I only need 4 columns, this is the query I used
"SELECT \"account\".\"account\",
\"account\".\"pin\",
\"account\".\"create_dtime\",
\"account\".\"expire_date\"
FROM \"account\""
Should I remove the extra \ and " ?
thank you
Hello
THere are some bug on 'guess query' button,
8017
7563
At the moment, you can edit the sql statement manuallly,eg:
"SELECT account.account,
account.pin,
account.create_dtime,
account.expire_date
FROM account"
Best regards
Modified sql as shown, still get the following:
Starting job Cards at 10:04 29/06/2009.
Exception in thread "main" java.lang.Error: Unresolved compilation problems:
Syntax error on token "SELECT", , expected
account cannot be resolved
account cannot be resolved
account cannot be resolved
account cannot be resolved
Syntax error, insert ")" to complete MethodInvocation
Syntax error, insert ";" to complete LocalVariableDeclarationStatement
FROM cannot be resolved to a type
Syntax error, insert ";" to complete LocalVariableDeclarationStatement
String literal is not properly closed by a double-quote
String literal is not properly closed by a double-quote
Syntax error on token "SELECT", , expected
Syntax error, insert ")" to complete MethodInvocation
Syntax error, insert ";" to complete Statement
Syntax error, insert ";" to complete LocalVariableDeclarationStatement
at nitro.cards_0_1.Cards.tInformixInput_1Process(Cards.java:1421)
at nitro.cards_0_1.Cards.runJobInTOS(Cards.java:2128)
at nitro.cards_0_1.Cards.main(Cards.java:2046)
Job Cards ended at 10:04 29/06/2009.
Exception in thread "main" java.lang.Error: Unresolved compilation problems:
There are some compile error in your sql, open 'Code' tab and you will see the error, it looks you don't close the sql with double quote. Can you create a simpe table just with one or two filed and test?
Best regards
shong
