Oracle Database connection installation

Ignition
1

So I’m up to my neck doing a remote installation in China right now (seriously), and I want to create an Oracle DB connector. This is a fresh install so the required files aren’t on the target computer. I see the link to Oracle for the JDBC driver files download, and now I am confused.

Does it matter which version I select? ojdbc8.jar or ojdbc11.jar? The only difference seems to be that the “8” version is also certified with JDK8, while both 8 and 11 are also certified with JDK’s 11, 12, 14, 14 and 15.

I also don’t know what the actual Oracle DB I’m meant to connect to is. I just have a computer and DB name to point to. So I don’t know if the DB version matters either.

2

The DB version may end up mattering:

image
image2230×1104 221 KB

You might just need to pick one (8 or 11) and then try it and see if it works…

3

I’m trying to find out the DB version. It actually could be a v12 DB as the hardware would have been originally spec’ed out about 5 or 6 years ago

4

I think that 8 may be the best choice in this case

5

Really stupid question. When I edit the Oracle Database connector, and supply the jar file, does Ignition move it to some special location? IE I can remove it from the place that I selected the file from?

6

It goes into $IGNITION/user-lib/jdbc.

7

I'm not conversant in Oracle, but I think there could be an error in the DB connector configuration page for Oracle DBs.

In the Connect URL section, the format of the string is given as this example:

jdbc:oracle:thin:@host:port:SID

But what I found worked for me was:

jdbc:oracle:thin:@//host:1521/SID

This is using the obc8.jar file an connecting to a v12 DB

8

Well hopefully if somebody else has an issue in the future they find this post because nothing in their docs suggest the // is necessary.

9

I’m not 100% sure about the //host, but the /SID as opposed to the :SID seemed to be critical

10

This page suggests it is host:post:sid: Features Specific to JDBC Thin

:man_shrugging:

11

In fact I screwed the connector up so much that I got a java error saying it was the wrong format, and teh correct format was //host:port/servicename

12

Obvious YMMV with the Oracle docs :thinking:

13

Yeah I’m not really sure. Let me see if our QA has anything to say.

14

This page suggests that the // on the host is optional.
https://docs.oracle.com/database/121/TDPJD/getconn.htm#TDPJD138

But I still didn’t it to work until I had /SID

15

In testing against the v18.4.0-xe docker image with the ojdbc8.jar I am finding the following connection strings working in Ignition:

jdbc:oracle:thin:@localhost:1521:XE
jdbc:oracle:thin:@10.0.1.153:1521:XE   #Just in case localhost is a special case
jdbc:oracle:thin:@<hostname>:1521:XE   #Just to confirm my hostname works with same syntax

#Formatting you recommended
jdbc:oracle:thin:@//<hostname>:1521/XE 
jdbc:oracle:thin:@<hostname>:1521/XE

It could be something with the DB version, but 12 is only about 5 years old if I remember correctly and I don't think the connection strings have changed in that time. Are there any special or non-ASCII characters in either the hostname or the SID?

One thing I did find odd is this syntax faults with a port error despite Oracle claiming it should work:

jdbc:oracle:thin:@//<hostname>:1521:XE

Edit:

Looks like for SIDs / and : work. If you are using a service name, then only / will work

As I had no clue about being different types of connections for Oracle, this article looks to explain the differences:

2 Likes
16

Ahhh ,… that is it … I’m using the service name and not the SID. The service name is what we use in general to connect to our Oracle DBs with all our other (non-Ignition) processes, and in not being an Oracle person I was trying to build the connection string in the same format based on what we are currently using. I saw online how to extract the SID from an Oracle DB, but was joking with a colleague that it looks like I need to know the SID in order to connect to the DB so I can run the query to determine the SID :laughing:

That makes a lot more sense now. Of course I had to encounter this right in the middle of my deployment! So the Oracle DB connectors page on the GW is correct, it’s just that there is more to the story (and I managed to run into a dark, not so well read corner of it).

Thank you very much for that research. It’s really informative.

OTOH How come the DB Connection page allows me to attempt to create an Oracle DB connector when the DB drivers page knows that there is no Oracle DB driver loaded? :man_facepalming: Knowing that would have saved me a lot of panic by allowing me to be prepared before hand. :roll_eyes: