I'll only mention that using JDBC and a little Java, you can do whatever you need to. I've done tons of JDBC development with Oracle.
Oracle has a free JDBC driver
FileMaker has a free JDBC driver
Add code and stir.
I wrote an import to/from FileMaker and MySQL. In that import, you need to handle the various data types (as you would with Oracle as well, but you're in complete control.). My MySQL/FileMaker import dynamically created the entire database, tables, and everything using the MetaData from the "source" database to first create the structure in destination. Then, the actual data "import" happens. Also in code. Nothing interactive needed or wanted.
HOPE THIS HELPS.
Yes, I can already cast the Oracle date to timestamp if I only want to import from ODBC (or JDBC).
But can JDBC make ESS Oracle dates work properly?
I see what you're saying... so probably not. JDBC/Java don't run natively inside FMP so if you're looking for real-time database access to Oracle, within FMP, ESS is probably the best (only?) route. Perhaps it would be a good idea to place a call to the ODBC driver vendor to see what they could do for you?
1 of 1 people found this helpful
Sorry, it has been long time since I worked with Oracle, so it just a suggestion.
Obviously, DATE data type from Oracle maps to Date in FileMaker and TIMESTAMP to FileMaker Timestamp. FileMaker Date format does not have time information, so you are losing it.
I would suggest instead of ESSing directly to the table, create a view, cast your columns with DATE as TIMESTAMP and ESS to the view instead of the table. Make sure Views are selected in ESS ODBC setup.
Alternatively, add a second TIMESTAMP column to the table and set a trigger (
AFTER INSERT OR UPDATE, I think) to fill them with DATE casted as TIMESTAMP (just don't tell your DBA , there might be some performance issues in busy tables ). Use this column in FileMaker instead of the original column.
And by the way, ESS is ODBC only. JDBC is not supported.
Yes, in another database we created a view and casted several dates as timestamps. It can be a little tedious doing all this, and creates more dependencies; so I'm hoping we can avoid that in the future.
In another situation, we don't have the environment or access where we can create those views or add columns.
These seems to be an issue for a long time:
I can see the question comes up on the forums, but there is no answer for a simple work-around
I wonder what is happening with time component when you change the date in FileMaker and it is written back to Oracle.
If you do not have access to modification of the database schema, I would suggest to run ODBC import/export into a local FileMaker table. I know it is a pain, but I do not see any other way at the moment