Connecting Thingworx to ODBC MSSQL Data Source

[slucasTN]

Hello, i'm trying to connect Thingworx to MSSQL using ODBC instead of JDBC and i found some CS and community posts about ADO Service, but i'm not sure if this is a good choice for using on Thingworx 9.3 as the ADO is like ten years old and i don't even know if it's still supported by newer Thingworx versions.

So, i came here asking for help with some solution, because i don't want to connect ODBC on Kepware Server for getting data because i don't want to overload my database with periodic queries from Kepware Server.

Thingworx version: 9.3-b20

[VladimirN]

Take a look: 

• "Configuring the ADO Service for SQL Server DB connection For ThingWorx": https://community.ptc.com/t5/IoT-Tips/Configuring-the-ADO-Service-for-SQL-Server-DB-connection-For/ta-p/820374 
• "MSSQL Database Installation and Configuration": https://support.ptc.com/help/thingworx/platform/r9.6/en/index.html#page/ThingWorx/Help/Composer/DataStorage/PersistenceProviders/mssql_database_installation_and_configuration.html 

[wcui]

Hi @slucasTN 

ThingWorx ADO service is considered deprecated and is no longer supported so we dont suggest using it.

What is the reason JDBC not acceptable on your side?

Thingworx Database template support JDBC connection with major relation database. Check below article for more details.

https://www.ptc.com/en/support/article/CS318728

[slucasTN]

There is no problem with JDBC, we use it actually, but we are trying to find some ODBC alternatives for connecting Thingworx on SQL Server using our ODBC Data Source drivers. It's because we have faced some blockings from the DB side with JDBC and with ODBC driver i think this can be possible.

[wcui · Accepted solution]

Hi @slucasTN, thanks for the details.

Currently ThingWorx does not supply any OOTB functions to support ODBC connection.

The possible workarounds would be

- use Kepware ODBC Client driver to fetch data from database and foward to ThingWorx

   https://www.ptc.com/en/support/article/CS353458

- develop your own extension to achieve ODBC connection 

  https://www.ptc.com/en/support/article/CS328028

  https://www.ptc.com/en/support/article/CS233537

Please refer to above links for the guides.

[wcui]

Hi @slucasTN ,

I wanted to follow up with you on your post to see if your question has been answered.
If so, please mark the appropriate reply as the Accepted Solution for the benefit of other members who may have the same question.
If not, our industry expert is also reviewing the replies and may accept one of them as solution on your behalf.
Of course, if you have more to share on your issue, please let the Community know. 

Thanks,
Wenjing

[VladimirRosu]

Hi @slucasTN,

Can you please share what exactly were the blockings that you received from the DB side?

I find it unusual that only JDBC would be blocked while ODBC would be allowed, especially if they use the same port, hence I am curious to find out what exactly is the technical reason JDBC is not working in your situation.