SQL Server JDBC Connection Setup

[#1]

One commonly asked question is what are the correct settings for the Configuration Tables tab when creating/setting up a Database Thing to connect to a SQL Server (2005 or later) database.  There are a couple of ways to do this but the tried and true settings are listed below.

connectionValidationString - SELECT GetDate()

jDBCConnectionURL - jdbc:sqlserver://servername;databaseName=databasename

jDBCDriverClass - com.microsoft.sqlserver.jdbc.SQLServerDriver

Max number of connections in the pool - 5 (this can be modified based on number of concurrent connections required)

Database Password - databaseusername

Database User Name - databaseuserpassword

<br>

The jdbc driver file sqljdbc4.jar is by default installed with the ThingWorx server.  It is located in TomcatDir\webapps\Thingworx\WEB-INF\lib\

[mattt]

Pai,

Can a Database Thing connect to a mySQL server or only to a SQL server?  If it can, what jDBCDriverClass do I use?

[abalousek]

Matt,

If you create a new Thing and select the Database template it should by default have the config settings for a mySQL database. In case they are not showing up here are the parameters:

connectionValidationString = SELECT NOW()

jDBCConnectionURL = jdbc:mysql:/server:port/database?param=value

jDBCDriverClass = com.mysql.jdbc.driver

There should already be a jDBC driver .jar file in the same directory as noted above as it is also included in the ThingWorx deployment. Let us know if you can't get things running!

[kmagdum]

Actually sqljdbc4.jar is not available by default even in ThingWorx 7. We need to download it and place it at TomcatDir\webapps\Thingworx\WEB-INF\lib\ and restart the Tomcat.

[PaiChung]

Please note that we recommend creating Extensions using the jar files and this has been much easier now with the JDBC Connector Creator Extension available in the Marketplace.

[jbrooks1]

For me the JDBC Connection string took some trial and error to get working. What finally worked for me was to put the database name directly after the slash after the port number. For example:

jdbc:mysql://example.com:3306/northwind