How to connect sql server to thingworx

[quangnguyenduc4]

0

I've done some research on how to connect thingworx with sql server or postgre to show data on mashup gird. I found out that I need to import MSSQLConnector_Extension but i've spent days to find where to download these jdbc extension but i couldn't find it in ptcmarketplace. Does anyone have an idea where to download it or another way to connect dbserver to thingworx. Thanks alot

[cmorfin]

Hi @quangnguyenduc4 

If you want to query a external MSSql database (or any other external DB), you simply need to create a new Thing using the Database Thing Template , set the connection details under Configuration and copy the relevant db driver under Tomcat/lib - For MSSqlServer, you can have a look at this Help Center page to download and install the JDBC driver (note you do not need to modify the platform-settings.json file if you create a database Thing for query).

You do not need any extra extension.

You are then able to create your own service of type SQL Query or Command to query what you need and report it in a grid if you so wish.

Hope this helps

Christophe

[quangnguyenduc4]

Thank u! Currently, i'm using thingworx hosting trial and using postgresql 12.2. I saw some posts that "Database extension package is not needed to connect to an external database if the database type matches the one used as the ThingWorx Persistence Provider, for example postgres". So I didn't import any jdbc extension. I just created a Thing using Database Template. And in configurations tab, u configured connection string as bellow:

 + JDBC Driver Class Name : org.postgresql.Driver

 + JDBC Connection String: jdbc:postgresql://localhost:5432/IOTDeviceManagement

 + connectionValidationString : SELECT NOW()

 +Database User Name : username to login to postgres with full permission

 + Database Password: password of user

But I've still couldn't connected to postgresql. Can u give me some advices. I'm struggling with this trouble for days. Thanks a lot

[cmorfin · Accepted solution]

Hi @quangnguyenduc4 

This string

jdbc:postgresql://localhost:5432/IOTDeviceManagement

is trying to connect to a PostgreSQL database named IOTDeviceManagement on localhost.
Given that this is read from the ThingWorx server point of view, localhost here means the ThingWorx server.
Since you indicate using a hosted trial edition, I doubt that this is correct.
I am guessing you are trying to access your own Postgres installed locally.
In which case you should use the ip address of your machine BUT I am not sure that the ThingWorx server can connect to your local machine.
It might be best to use the locally installed trial version instead.

Please let me know if your setup is different than what I understood above.

Christophe

[quangnguyenduc4]

Thanks alot. What u said u is absolutely correct with my setup. I think I have to use local install. 

[slangley]

Hi @quangnguyenduc4.

If you feel your question has been answered, please mark the appropriate reply as the Accepted Solution for the benefit of others with the same question.

Regards.

--Sharon