Hi everyone,

I have a timestamp field in FM and I import (ODBC data source) from a timestamp field from a People Soft view (using Oracle 10g which is supported by FM). This import works perfectly in vs. 9 ... the imported time is 00:00:00 and imports as 12:00 AM. However, when I use vs. 10 (whether FMPA or FMS scheduled import), it no longer imports the time; the results are only the date. And a date only in a timestamp field fails field-level validation!!

I am unsure whether it is restricted to breaking with Oracle 10g or whether it would be ANY ESS data source with 00:00:00 as the time portion of a timestamp. One of the changes in vs. 10 is:

[color:gray]8.2. FileMaker Pro does not differentiate zero and NULL (empty) values in shadow fields. For example, changing a shadow field's value from empty to "0" and then refreshing the window will still display an empty field instead of "0."

But this shouldn't apply here because the field is NOT empty or 0 - it contains a valid date.

I've also read that vs. 10 [color:gray]"Accurately access SQL-supported timestamp data from ODBC data sources.". But my other imports using SQL Server work fine (although none of them that I'm aware of have 00:00:00 timestamps).

In both vs. 9 and 10, using [color:green]Set Field [ timestampField ; Get ( CurrentDate ) ] will include the time in a timestamp field as 12:00 AM (or depending upon system setting). But Insert Date[] only inserts the date (in both FM versions). I believe that a timestamp field, if a time isn't specified, should ALWAYS insert 00:00:00 or 12:00 AM to fill out the timestamp field to make it legal. Because now, I have some records and fields which will fail field-level validation (during import from 10g) because their times are 00:00:00.

I'm unsure if this is something which should be reported. Does anyone have comments on the issue? I'd appreciate the input.

LaRetta

I should follow up on this since I see it came up on another forum here .

Although another developer can also replicate and prove that this issue exists, the engineers at FileMaker replied that they could not replicate the problem.

Maybe if enough people bring it up, it will be addressed again as a serious issue. In meantime, we have not upgraded to vs. 10 on this one server. All I can think to do is pull the data into MySQL and THEN into FileMaker.

I felt I should follow up here as well:

The thing is ... for us, we finally figured out that it was breaking when using 9i and 10g with FM 9. We were NOT getting the time portion of (what we thought was) a timestamp from Oracle but we didn't know it because FileMaker was filling in 12:00 AM. So we thought the Oracle database was sending us a true timestamp.

So when we moved to vs. 10, we only got a date (no time portion) so we thought FM 10 was breaking when in fact, it was FINALLY PROPERLY giving us exactly what was sent from Oracle. I put this together on my own ... FM engineers didn't connect that the BREAK was in our prior configuration in vs. 9 because that's when we first designed this process.

post on timestamp break in vs. 9

This might add more depth to the issue.

Edited May 15, 200916 yr by Guest