Supported Oracle DataType with ESS

Hello Everyone.

This turned out to be a long post, and I will say from the start, that, though I am very much interested in people's thoughts, I am not in dire need of a resolution to this. If you have the time to read and comment, great! If not, understood, and appreciated that you read this far. Thank you, either way...

Background:

Several months back I inquired about ESS and ODBC connections, as I was starting a FMS integration project with an Oracle DB, and I wanted to solicit advice, wisdom, tales of experience while making the decision about how to integrate.

Thanks to everyone who replied, I chose a methodology of using the Import Records script step with an ODBC connection to Oracle. It works very well. I am very happy with it, and I am grateful to everyone who shared their knowledge with me back then -- you all helped steer things in an excellent direction.

Beyond, and aside from, the above:

One day last week I decided to temporarily add an ESS table to the relationship graph, to see if it could afford me a quick peek into the Iive Oracle data. This approach did not turn out to be helpful. Since the solution does not rely on ESS, it was just a curious venture on my part, and nothing critical was on the line. That said, there was one curiosity that I noticed...

Columns not showing in ESS:

While trying the above, I noticed that the ESS connection did not seem to recognize what I believed to be Timestamp columns in the Oracle DB. By this, I mean that, at that moment where the ESS setup wizard asks you to select the columns which can serve as a primary key, the timestamp columns were not even listed -- it was as though they did not exist. Moreover, after setting up an ESS table, the timestamp columns which I know are available (due to the successful work with Import Records) did not appear in the shadow table definition.

The question:

The "missing" timestamp columns are the crux of what this post is about.

I am wondering if anyone can explain why these columns might not show up in ESS? (Note: Using the Import Records script step, they show up and import just fine).


My thoughts and investigations, thus far:


1 - Incompatibility of versions:

@WimDecorte had mentioned that ESS is very particular about the match between DB version, driver version, and FMS version. With this in mind, I have been doing my homework on this, and though, admittedly, I have struggled a bit with some of the version numbering, I believe that the set up is a supported configuration (per this doc):

  • Oracle Database 12c 12.2.0.1.0
  • FMS: Server 18.0.3 on MacOS
  • ActualTech Version 5.0.9

2 - Unsupported DataType:

In my initial research on these topics, I read some older documentation which I recalled mentioned that not all datatypes are supported with Oracle. It really seemed difficult for me to imagine that a column of datatype Timestamp would not be supported, but I decided that I could pursue this as an inquiry, too. Per this document, Timestamp columns in Oracle seem to be supported.

At this point, I decided that it would be interesting to see what I might learn if I were to use an Oracle driver, and so with that in mind, I downloaded the appropriate JDBC driver, and studied up on the various Oracle connection string formats, so that I could connect to the DB using the 360Works JDBC plug-in. (@fmpdude, even though strictly speaking I was not the one coding the JDBC requests, I did think of you and imagined you cheering me on as I staggered through issues of choosing the correct class name and also getting the format of the connection string correct).

What I learned when I performed a simple SELECT via JDBC, is that the Oracle view appears to be returning a column of type TIMESTAMPLTZ.

From the looks of of the docs here, it appears as though TIMESTAMPLTZ, though related, is distinct from TIMESTAMP, and I am entertaining the idea that this difference could explain why ESS does not recognize the column, i.e. perhaps ESS supports TIMESTAMP, but not TIMESTAMPLTZ, Does this seem like a sound hypothesis to anyone? Can anyone confirm or refute this based on experience?


I'd welcome any thoughts on the following:

  • Stones which I have left-unturned
  • Details I might have missed or misunderstood
  • Thoughts about whether or not my proposed explanation regarding TIMESTAMPTLZ versus TIMESTAMP might hold water.

Thank you for any insight/help with this. And thanks again to everyone who steered me away from ESS, and towards Import Records. I would definitely use the Import Records methodology again. Not so, with ESS.

Sincerely,

-Steve

There's a few posts on StackOverflow when you search for TIMESTAMPLTZ.
It may give you some clues.

2 Likes

I would like to point out that you can use SQL functions in MBS FileMaker Plugin connect to Oracle database with native Oracle driver or ODBC and do some things directly, e.g. call stored procedures.

Thanks, @MonkeybreadSoftware.

1 Like