6 Replies Latest reply on Dec 15, 2016 5:05 AM by nicolai

    Showing Time from Oracle Date Using ESS?

    eric

      Oracle date fields often have a time component, making them the equivalent of a FileMaker timestamp.

      A Comparison of Oracle's DATE and TIMESTAMP Datatypes — DatabaseJournal.com

      However, FileMaker's ESS (External SQL Source) shows Oracle dates as FileMaker date types, which fails to reveal the time component that is present on Oracle.

      Without altering the Oracle field types, is there a way to get FileMaker's ESS to display the time component of an Oracle date field?

        • 1. Re: Showing Time from Oracle Date Using ESS?
          fmpdude

          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.

           

          All quick.

           

          All programmatically.

           

          All free.

           

          HOPE THIS HELPS.

          • 2. Re: Showing Time from Oracle Date Using ESS?
            eric

            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?

            • 3. Re: Showing Time from Oracle Date Using ESS?
              fmpdude

              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?

              • 4. Re: Showing Time from Oracle Date Using ESS?
                nicolai

                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.

                1 of 1 people found this helpful
                • 5. Re: Showing Time from Oracle Date Using ESS?
                  eric

                  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.

                  • 6. Re: Showing Time from Oracle Date Using ESS?
                    nicolai

                    These seems to be an issue for a long time:

                     

                    ess (to oracle) date / timestamp field

                     

                    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