Unable to connect database thing to MSSQL DB

[aallari]

Hi

We have installed thingworx with mssql database. We created one Thing and selected "Database" as "ThingTemplate",  now in configuration tab we have filled the options as below :

jDBCConnectionURL =  jdbc:sqlserver://localhost:1433;databaseName=thingworx;

jDBCDriverClass = com.mysql.jdbc.driver

But the connection is not getting established, the property "isConnected" is showing as false only.

We can able to connect to the database through persistence provider with the same jDBCConnectionURL, so I think the problem is with the jDBCDriverClass.

we have tried DriverClass as below also:

jDBCDriverClass =  com.microsoft.sqlserver.jdbc.SQLServerDriver

Please let us know if we are missing any thing or using wrong driver class name for mssql.

[supandey]

Hi Aahlad, is MS SQL installed as persistence provider or you trying to connect to MS SQL via the RDBMS Extension? It looks like you are mixing two methods. If you are trying to connect via the RDBMS extension you'll need to create Thing of type MSSqlServer ThingTemplate

If you are are attempting to configure it for Persistence Provider it'll need to be done in platform-settings.json file in ThingworxPlatform folder

[aallari]

Yes we are connecting MS SQL as persistence provider, it is done separately.

But here what we are doing is connecting thing which has "Databse" as base ThingTemplate to MsSQL.

So here all we need is the possible values of DriverClassName for MSSQL.

Please let us know what need to do to achieve this.

[supandey · Accepted solution]

Thanks for clarifying . I could see the same behaviour until i imported the MSSQLConnector_Extension Relation Databases connectors

It appears the JDBC driver already included is not accessible like that, after importing the extension I created a Thing of ThingTemplate MSSqlServer (FYI this added sqljdbc jar in \\webapps\Thingworx\WEB-INF\extensions)

And following configuration :

Hope this'd help.

[aallari]

We are using "Database" as Base Thing Template and configuring MSSQL DB details in Configuration tab, not MSSqlServer(as ThingTemplate).

For Postgressql we are able to connect thing, it is giving connected as true, but for MSSql it is giving connected as false.

Is there any way to connect thing to DB by using "Database" as base thing template.

[supandey]

Rechecking your old replies seems like you are configuring mysql in the class "jDBCDriverClass = com.mysql.jdbc.driver" see my previous response for correct JDBC driver class for MSSql

[aallari]

Now it is working fine. I have given wrong Connection string. If i change the connection string from SELECT NOW() to SELECT GET DATE(), am able to connect to database.

thank you supandey.

[asekar]

Were you able to solve this issue. I'm facing the same

Any information would be helpful

Arvind

[supandey]

Hi Arvind, could you please open a new thread, referencing this one and may be also attach the log files or screenshot on your configuration of the JDBC extension?