topic Re: Can't import Microsoft Access date field using tAccessInput in Talend Studio

Can't import Microsoft Access date field using tAccessInput

Anonymous — Sat, 16 Nov 2024 13:34:27 GMT

I am very new to TOS.
I have a very simple table in Access 2003 with several fields. One of the fields is a Date field. This field has no special format. However, when I tried to use tAccessInput to import the data from the table, I received an exception caused by that date field:
xception in component tAccessInput_1
java.sql.SQLException: No data found
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLGetDataStringTimestamp(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getDataStringTimestamp(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getTimestamp(Unknown Source)
at talenddemosjava.testing_0_1.testing.tAccessInput_1Process(testing.java:645)
at talenddemosjava.testing_0_1.testing.runJobInTOS(testing.java:966)
at talenddemosjava.testing_0_1.testing.main(testing.java:860)
I have no idea why this is happening. I tried to change the data type of the date field in the schema and I got something like: 1984-12-05 00:00:00
I tried many date pattern possible such as "yyyy-MM-dd'T'HH:mm:ss". I constantly received the same exception.
Would you please help me to solve this problem?
Thank you very much.
Toan.

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:14:16 GMT

Hello
What's your data looks like in access table? Can you show me the schema of tAccessInput_1?
Best regards
shong

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:20:38 GMT

Thank you very much for replying. The table structure and schema are posted.

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:23:26 GMT

Hello
If the date looks like :1984-12-05 00:00:00, the date pattern should be set as: "yyyy-MM-dd HH:mm:ss"
Best regards

shong

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:25:15 GMT

I tried that already and same thing happned.

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:28:01 GMT

Hello
First, make sure you set the correct data source and table name.
Please show me a screenshot of your data.
Best rega

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:31:37 GMT

The data source and table name should be correct because when I got rid of that date field, everything runs just fine.

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:34:36 GMT

Hello
From your image, I see the date pattern looks like
"dd/MM/yyyy"
or
"dd/MM/yyyy HH:mm:ss"
Best regards

shong

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:40:56 GMT

I've tried everything
"dd/MM/yyyy HH:mm:ss" Length = 19
"MM/dd/yyyy HH:mm:ss" Length = 19
"dd/MM/yyyy" Length = 10
"MM/dd/yyyy" Length = 10
Nothing works. I don't know why a simple task is so hard.

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:44:54 GMT

Hello
Which version of TOS do you use?
Don't worry! As your said, it is just a simple job. Please send your data source to me via email. I will try..
Best regards
shong

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:47:33 GMT

My TOS is 4.0.0M1

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 06:52:12 GMT

Email sent. Did you have it? Please post your reply here. Thank you so much.

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 07:00:58 GMT

Hello
I test it on TOS3.2.3 and it works fine.
query:

"select Table1.SalesUnits,Table1.SalesDollar,Table1.SalesCost,Table1.CustID,Table1.StoreID,
Table1.ItemID,Table1.myDate 
from Table1"

Best regards

shong

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 07:14:25 GMT

I did exactly what you did and had no luck. Same exception appeared. Is there any chance you can test it on TOS 4.0.0 because it's the version that I can download.

Re: Can't import Microsoft Access date field using tAccessInput

Anonymous — Thu, 04 Feb 2010 15:59:25 GMT

The dates displayed on my computer is in "MM/dd/yyyy" format. So I use "MM/dd/yyyy" date pattern in Talend. But I still can't get it to work. Is there any chance this problem is caused by the 64bit operating system that I'm using. Moreover, the "Date pattern" in TOS should only define what date data should look like in TOS only. Is that correct? If I have to define the explicit "date pattern" to match each format of all the date fields from all the data sources, I wonder how many different jobs I have to create to handle just date fields.

Re: Can't import Microsoft Access date field using tAccessInput

_AnonymousUser — Mon, 22 Feb 2010 22:04:05 GMT

I am having the same issues in TOS 4.0.0M3. I have several date fields in an Access table, some of which are formatted as "mm/dd/yyyy" and some that are formatted as short time ("hh:mm"). If I leave all the date fields out, my import runs fine. As soon as I include any of the date fields in the import process, it fails with the same error messages shown above. I am using Windows XP, so it not limited to just 64-bit systems.

Re: Can't import Microsoft Access date field using tAccessInput

_AnonymousUser — Thu, 25 Feb 2010 15:49:45 GMT

Update: my import runs fine in TOS 3.2. There must be a bug in TOS 4.0 that causes dates in Access to not work.