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. (@anon45965781, 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