Hi,
I explain to you my problem.
I have a tMySQLInput who search a datetime in my database. I have linked her with a main link to a tFlowToIterate.
Then I linked my tFlowToIterate to a tInformixInput.
Inside it I have a query who use the results of my tMySQLInput ( the datetime )
((Date)globalMap.get("row18.myDate"))
When I launch I have this error :
Exception in component tInformixInput_5 java.sql.SQLException: Extra characters at the end of a datetime or interval. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3453) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3762) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2574) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2490) at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1656) at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1629) at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:206) at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1228) at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218) at projet_albin.myProject_0_1.myProject.tMysqlInput_9Process(myProject.java:1487) at projet_albin.myProject_0_1.myProject.runJobInTOS(myProject.java:1952) at projet_albin.myProject_0_1.myProject.main(myProject.java:1826) Caused by: java.sql.SQLException at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3767) ... 10 more
So I use a tMsgBox to get the query of tInformixInput and I have a .0 at the end of my datetime... but I don't understand where it came.
Exemple :
2011-08-29 11:25:18.0
In my schema, myDate is in Date format.
Any idea ?
Thanks.
For information. I have look into my others Job if I have the same problem. I see that this problem appears only when we reuse the datetime like a String.... I explain, if I write my datetime in a tfileOutput or a tLogRow, no problem I have my correct datetime, but if I use it in a tJavaRow or in a other query ( for exemple : ((Date)globalMap.get("row18.myDate")) ), the problem appears. Exemple of result with a tJavaRow with only a System.out.println(input_row.myDate);
You need to manually convert the Date to a String in the correct format for your SQL statement e.g. ... WHERE testdate = "+TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",(Date)globalMap.get("row18.myDate")). (Talend does this for you when you use tfileOutput or tLogRow, applying the Date Pattern from the schema.)
Hi,
I'm posting here because I'm facing a problem similar to this one.
I build all my SQL queries in at tJava component. This is what my select statement looks like -
qryString.append("SELECT c.cntrctid_ref, c.cntrctid_number, c.mildates_strtdt, ");
qryString.append(" cp.conprmid_ref, ");
qryString.append(" (CASE WHEN c.mildates_ustmntdt > '").append(context.convdate)).append("' ");
qryString.append(" THEN ");
qryString.append(" DATE(ADD_MONTHS(c.mildates_ustmntdt, -12)) ");
qryString.append(" ELSE ");
qryString.append(" c.mildates_ustmntdt ");
qryString.append(" END) anniv_date ");
context.convdate contains a valide dd/MM/yyyy format date. When I print this query on screen before a tInformixInput component, this is what I get (formatted for your viewing pleasure)-
SELECT c.cntrctid_ref, c.cntrctid_number, c.mildates_strtdt, cp.conprmid_ref,
(CASE WHEN c.mildates_ustmntdt > '2012-04-23'
THEN DATE(ADD_MONTHS(c.mildates_ustmntdt, -12))
ELSE c.mildates_ustmntdt
END) anniv_date
When I execute this query in the Informix query editor(dbaccess), it works. However, when I run this in a tInformixInput component I get the following stack trace -
Exception in component tInformixInput_2
java.sql.SQLException: String to date conversion error
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3453)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3762)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2574)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2490)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1656)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1629)
at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:206)
at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1228)
at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tInformixInput_2Process(cntr_prem_base.java:3378)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tJava_3Process(cntr_prem_base.java:2716)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tInformixConnection_1Process(cntr_prem_base.java:2552)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tRunJob_1Process(cntr_prem_base.java:1443)
at aquila.cntr_prem_base_0_1.cntr_prem_base.runJobInTOS(cntr_prem_base.java:5123)
at aquila.cntr_prem_base_0_1.cntr_prem_base.main(cntr_prem_base.java:4774)
Caused by: java.sql.SQLException
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3767)
... 13 more
However, when i reformat my date as yyyy-MM-dd, it executes properly. My DBDATE is DMY4
Any idea what's going on here?
Sorry for duplicating this. Reposting with bbcode.
I'm posting here because I'm facing a problem similar to this one.
I use talend 4.1.1.r50363. I build all my SQL queries in at tJava component. This is what my select statement looks like -
context.convdate contains a valide dd/MM/yyyy format date. When I print this query on screen before a tInformixInput component, this is what I get (formatted for your viewing pleasure)-
Query: SELECT c.cntrctid_ref, c.cntrctid_number, c.mildates_strtdt, cp.conprmid_ref, (CASE WHEN c.mildates_ustmntdt > '2012-04-23' THEN DATE(ADD_MONTHS(c.mildates_ustmntdt, -12)) ELSE c.mildates_ustmntdt END) anniv_date
When I execute this query in the Informix query editor(dbaccess), it works. However, when I run this in a tInformixInput component I get the following stack trace -
Stacktrace : Exception in component tInformixInput_2 java.sql.SQLException: String to date conversion error at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3453) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3762) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2574) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2490) at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1656) at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1629) at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:206) at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1228) at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218) at aquila.cntr_prem_base_0_1.cntr_prem_base.tInformixInput_2Process(cntr_prem_base.java:3378) at aquila.cntr_prem_base_0_1.cntr_prem_base.tJava_3Process(cntr_prem_base.java:2716) at aquila.cntr_prem_base_0_1.cntr_prem_base.tInformixConnection_1Process(cntr_prem_base.java:2552) at aquila.cntr_prem_base_0_1.cntr_prem_base.tRunJob_1Process(cntr_prem_base.java:1443) at aquila.cntr_prem_base_0_1.cntr_prem_base.runJobInTOS(cntr_prem_base.java:5123) at aquila.cntr_prem_base_0_1.cntr_prem_base.main(cntr_prem_base.java:4774) Caused by: java.sql.SQLException at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3767) ... 13 more
However, when i reformat my date as yyyy-MM-dd, it executes properly. My DBDATE is DMY4
Any idea what's going wrong here?
If you append a Date to your SELECT String, you have no control of the format used for that Date. As I said above, you need to manually convert the Date to a String in the correct format for your SQL statement e.g.
qryString.append(" (CASE WHEN c.mildates_ustmntdt > '").append(TalendDate.formatDate("dd/MM/yyyy",context.convdate)).append("' ");
And you don't need to re-post, you can just edit your previous post if you register.
