Talend community forum / using date with sybase

mouseman · 2007-07-18 19:13:58

hello,

I'm trying to use Date from Sybase table.

When running i have the error :

[statistics] disconnected
Exception in component tSybaseInput_1
java.sql.SQLException: JZ009: L'erreur suivante a été détectée pendant la conversion : java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff
at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
at com.sybase.jdbc3.jdbc.Convert.objectToTimestamp(Unknown Source)
at com.sybase.jdbc3.jdbc.Convert.objectToDateObject(Unknown Source)
at com.sybase.jdbc3.tds.TdsJdbcInputStream.getDateObject(Unknown Source)
at com.sybase.jdbc3.jdbc.SybResultSet.getTimestamp(Unknown Source)
at defi2_bis.alimentation_defi_001.Alimentation_Defi_001.tSybaseInput_1Process(Alimentation_Defi_001.java:2264)
at defi2_bis.alimentation_defi_001.Alimentation_Defi_001.tSybaseInput_2Process(Alimentation_Defi_001.java:436)
at defi2_bis.alimentation_defi_001.Alimentation_Defi_001.main(Alimentation_Defi_001.java:2441)

the code is :

if (rs_tSybaseInput_1.getTimestamp(4) != null) {
row5.DateDebutValidite = new java.util.Date(
rs_tSybaseInput_1.getTimestamp(4).getTime());
} else {
row5.DateDebutValidite = null;
}

this append on a lookup table ( row5 ), and ( for instance ) i don(t use the date column for any test..

is it a specific problem with SYBASE ?

How is possible to convert the data ?

thank you for your help.

cantoine · 2007-07-18 19:29:19

Hi,

Do you need Nano seconds precision ?

Because you can modify your timestamp format : "yyyy-mm-dd hh:mm:ss"

Normally it works.

I'm trying some tests and i am searching a bug fix.

Maybe this is a java bug.

Regards.

SybaseUnhappyUser · 2007-07-18 20:19:54

Sybase and MS SQL Server have a silly way to store date/time. The precision is... 3 milliseconds!

Just try running "select getdate()" several times:
2007-07-18 19:44:48.330
2007-07-18 19:45:22.053
2007-07-18 19:45:27.880
2007-07-18 19:45:45.266
2007-07-18 19:45:52.236
...

You can notice that the timestamp always ends with 0, 3 or 6. Other values are illegal.

So the safe thing to do is to round up a date/time to 10 milliseconds, at least.

2007-07-18 19:45:52.237526 => illegal
2007-07-18 19:45:52.237 => illegal
2007-07-18 19:45:52.230 => OK
2007-07-18 19:45:52 => OK

mouseman · 2007-07-20 11:21:41

thank you.

Sometime the convert(datetime,xxx,103) function is usefull to have a correct timestamp

dministrator · 2010-11-09 04:51:51

Sorry to revive an old thread...but I'm facing the same issue.

I tried using CONVERT like Mouseman suggested in my MSSQLInput's SELECT Query, as well as cantoine's suggestion with removing microseconds. It isn't working.

Please someone help.

I'm using Talend Data Quality version: 4.0.1
Build id: r41260-20100427-0748
JDK and JRE 1.6. Release 22

Thank you.

shong · 2010-11-09 08:15:47

Hello dministrator
Please describe your question with more info, what's the error message do you get? What's the data type?

Best regards
Shong

dministrator · 2010-11-09 19:48:22

Thank you Shong.

My data type in Talend Schema is Date. The corresponding column in MS SQL Server query is of DateTime type.

The exception I get is this like that of OP:

java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff

Since my goal is a CSV file, for now, I'm managing it by changing the schema's data type as String.

My job is very simple - the components I use are, a single tMSSQLInput with a SELECT query (without any joins) and it's output connected to tLogRow.

cypher · 2011-04-07 05:22:36

Hi,

I'm facing the same issue with talend Date type saying that :

java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

But when I put the said pattern i'm still getting the same exception

I'm using the tOracleInput component and connect to a tMap component.

My talend schema is Date and the corresponding column in Oracle is TIMESTAMP.

The job is pretty much simple, Select all valid record in table_1 then insert the current records onto table_1_history then update the current record STAT_CODE and TIMESTAMP column and save it to a CSV file.

TIS version : 4.1.2 r 53616
OS : Win XP SP3
Java Version : 1.6.0_22

shong · 2011-04-07 05:52:22

Hi cypher
What's data looks like in table?

Best regards
Shong

cypher · 2011-04-07 06:21:25

Hi Shong,

Thanks for the reply.

Here's some of the record that's need to be updated.

Column Definition:
ACTVTY_CD (Varchar : PK),
ENDT_EFF_DT (Timestamp: PK),
REF_GROUP_CD (Varchar : PK),
STAT_CD (Varchar),
TIMESTAMP (Timestamp)

Record(s):
ACT00152 2011-03-16 12:42:03.065 001000 P 2011-03-16 12:42:03.065
ACT00153 2011-03-16 12:42:03.081 001000 P 2011-03-16 12:42:03.081
ACT00160 2011-02-24 17:11:20.463 001000 P 2011-02-24 17:11:20.525

Only the TIMESTAMP column throws an error.

Last edited by cypher (2011-04-07 06:22:23)

shong · 2011-04-08 08:07:16

Hi cypher

How do you set the data pattern when you set the DB type as timestamp? "yyyy-MM-dd HH:mm:ss.SSS"? To work around it, you can read it as a string, and then parse the string to a Date on tMap, for example: TalendDate.parseDate("yyyy-MM-dd HH:mm:ss.SSS", row1.TIMESTAMP)

Best regards
Shong

cypher · 2011-04-08 11:07:15

Hi Shong,

I think we didn't set any date pattern when inserting to database.

I'll try the workaround you suggest and hopefully will work.

Thanks a lot shong,

shong · 2011-04-08 11:20:32

Hi

Can you show us the the whole error message? on which component does the error occur? You said you use tOracleInput in previous post.

Best regards
Shong

cypher · 2011-04-08 11:28:34

Here the error message:

Exception in component tOracleInput_1
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
at java.sql.Timestamp.valueOf(Unknown Source)
at oracle.jdbc.driver.CharCommonAccessor.getTimestamp(CharCommonAccessor.java:486)
at oracle.jdbc.driver.T4CVarcharAccessor.getTimestamp(T4CVarcharAccessor.java:897)
at oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.java:1060)
at clpgas_talend.preprocessactivityclosing_0_1.preProcessActivityClosing.tOracleInput_1Process(preProcessActivityClosing.java:2683)
at clpgas_talend.preprocessactivityclosing_0_1.preProcessActivityClosing.tOracleConnection_1Process(preProcessActivityClosing.java:807)
at clpgas_talend.preprocessactivityclosing_0_1.preProcessActivityClosing.tJava_1Process(preProcessActivityClosing.java:691)
at clpgas_talend.preprocessactivityclosing_0_1.preProcessActivityClosing.runJobInTOS(preProcessActivityClosing.java:4906)
at clpgas_talend.preprocessactivityclosing_0_1.preProcessActivityClosing.runJob(preProcessActivityClosing.java:4601)
at clpgas_talend.activityclosing_0_1.activityClosing.tRunJob_5Process(activityClosing.java:4324)
at clpgas_talend.activityclosing_0_1.activityClosing.tJava_2Process(activityClosing.java:4130)
at clpgas_talend.activityclosing_0_1.activityClosing.tOracleConnection_1Process(activityClosing.java:6035)
at clpgas_talend.activityclosing_0_1.activityClosing.runJobInTOS(activityClosing.java:6541)
at clpgas_talend.activityclosing_0_1.activityClosing.main(activityClosing.java:6089)
Exception in component tRunJob_5

Here's my schema definition:

ACTVTY_CD (varchar : PK)
ENDT_EFF_DT (timestamp : PK)
REF_GROUP_CD (varchar : PK)
STAT_CD (varchar)
TIMESTAMP (timestamp)
DYNAMIC_FIELD (dynamic)

Sample Query : SELECT * FROM TABLE_1;

sara_marouan · 2011-06-26 18:22:22

hii
when running i have this error

Démarrage du job chaine_logistique a 17:09 26/06/2011.

[statistics] connecting to socket on port 3792
[statistics] connected
Exception in component tMap_1
java.lang.NullPointerException
at bi.chaine_logistique_0_1.chaine_logistique.tAccessInput_7Process(chaine_logistique.java:3884)
at bi.chaine_logistique_0_1.chaine_logistique.runJobInTOS(chaine_logistique.java:6541)
at bi.chaine_logistique_0_1.chaine_logistique.main(chaine_logistique.java:6412)
[statistics] disconnected
Job chaine_logistique terminé à 17:09 26/06/2011. [Code sortie=1]

plzz help

Manabi · 2012-03-01 07:59:51

Did you get the solution? I am facing the same issue.

lisajackusde · 2012-12-10 12:50:27

deletepostspam

Last edited by lisajackusde (2012-12-10 13:11:15)

Announcement

Unanswered posts

#1 2007-07-18 19:13:58

using date with sybase

#2 2007-07-18 19:29:19

Re: using date with sybase

#3 2007-07-18 20:19:54

Re: using date with sybase

#4 2007-07-20 11:21:41

Re: using date with sybase

#5 2010-11-09 04:51:51

Re: using date with sybase

#6 2010-11-09 08:15:47

Re: using date with sybase

#7 2010-11-09 19:48:22

Re: using date with sybase

#8 2011-04-07 05:22:36

Re: using date with sybase

#9 2011-04-07 05:52:22

Re: using date with sybase

#10 2011-04-07 06:21:25

Re: using date with sybase

#11 2011-04-08 08:07:16

Re: using date with sybase

#12 2011-04-08 11:07:15

Re: using date with sybase

#13 2011-04-08 11:20:32

Re: using date with sybase

#14 2011-04-08 11:28:34

Re: using date with sybase

#15 2011-06-26 18:22:22

Re: using date with sybase

#16 2012-03-01 07:59:51

Re: using date with sybase

#17 2012-12-10 12:50:27

Re: using date with sybase

Board footer