cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Stay updated on what is happening on the PTC Community by subscribing to PTC Community Announcements. X

Connecting to Oracle

knix
1-Newbie

Connecting to Oracle

Trying to connect to an Oracle database and getting the following error: "Error loading tables. Please check your configuration" when create sql query. I do not see the Oracle ojdbc14.jar file anywhere.  How do I get it to connect to an Oracle database

16 REPLIES 16
paic
1-Newbie
(To:knix)

Hi Kent, not sure if you have access to our documentation/training material and the market place.

But based on the proper jdbc jar file that you have to provide, you can use the JDBC Extension creator Extension in the Marketplace to easily create a Thingworx extension that will bring in that jdbc and will allow you to connect to your Oracle DB and interact with it.

in our training material we show an example with MySQL

knix
1-Newbie
(To:paic)

 

I have access to the market place.  I am unable to find where the documentation/training material is located at.

Aanjan
9-Granite
(To:knix)

Kent, the training materials are available at the PTC University Precision LMS page. This specific topic should be a part of the 'Introduction to ThingWorx' course under section 'Connecting to External Data Sources'.

Here is the Marketplace link to the JDBC Extension which should make the entire process of creating a JDBC extension easier.

And finally here is a link to our Help Centers which has links to both ThingWorx 6.0 and 6.5 versions and a separate link for Edge/Connectivity.

knix
1-Newbie
(To:knix)

We were able to use the JDBC Connector Extension to create the ThingWorx extension. We then used this template to create a new thing and connect to our data source using JDBC and we get the following Oracle Error: Unable to Invoke Service OracleDBService on Oracle DB Connection : Io exception: Oracle Error ORA-12650.

We tested to make sure we could connect to our Oracle DB using SQlDeveloper using JDBC driver from the server ThingWorx is on and we were able to.

Any thoughts on this?

Aanjan
9-Granite
(To:knix)

Error ORA-12650 usually points to errors related to encryption. Is the server setup to accept encrypted connections?

knix
1-Newbie
(To:Aanjan)

Which Server, Oracle or ThingWorx?  I can run ThingWorx both clear and secure and have tried them both. None of the configuration is pointing it to connect secure to Oracle.

We might have something configured wrong but not sure what.  We created another external jdbc source to a virtual database we have and we get the following error: [context: com.thingworx.webservices.context.HttpExecutionContext@78e968b6][message: Unable to Invoke Service DenService on DenConnection : connection error: Remote host closed connection during handshake]

Aanjan
9-Granite
(To:knix)

What is your connection string and the driver class name?

knix
1-Newbie
(To:Aanjan)

Driver Class Name: oracle.jdbc.OracleDriver

Connection String: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dev3)(PORT=1601)))(CONNECT_DATA=(SERVICE_NAME=dev.px.com)))

Aanjan
9-Granite
(To:knix)

The error you seem to have is related to SSL handshake. Either an invalid certificate is getting passed or no certificate is getting passed at all. We would most likely have configure the Oracle Server to either accept self signed certificates or disable encryption. You might've already checked this, but is 1601 an unsecure port?

knix
1-Newbie
(To:Aanjan)

Yes, it is an unsecure port.

knix
1-Newbie
(To:knix)

I take that back, I did some checking and found out that it is a secure port. I did not think we had this set up that way.

Aanjan
9-Granite
(To:knix)

Yep, so looks like you would either need to try to connect to an unsecure port (for testing purposes), or check any setting on the Oracle server side about allowing all connections.

knix
1-Newbie
(To:knix)

1601 is a secure port. If I go against an unsecure port it works.  However, I need to be able to use the secure port going forward.  From what I am reading, the Oracle JDBC thin driver should handle it automatically.  Is there some configuration with Tomcat or something that needs to be setup?

Aanjan
9-Granite
(To:knix)

From the error messages I'm seeing, the connection fails when you try to hit the Oracle server from the platform. I'm not sure how securing Tomcat/ Thingworx would help in this particular scenario (we can try though), but it is a good practice to have your platform over SSL connection. Here is an article on how to quickly setup SSL on Tomcat with a self-signed certificate.

knix
1-Newbie
(To:Aanjan)

Figure it out... Was not using the latest Oracle jdbc driver. Was using ojdbc14 and switched to ojdbc6.  The older driver did not know how to handle the AES256 data encryption.  Thanks for you help.

Aanjan
9-Granite
(To:knix)

No problem, glad you got that working!

Top Tags