Skip to main content
P
paic1-Visitor
1-Visitor
November 1, 2011

SQL Server JDBC Connection Setup

  • November 1, 2011
  • 3 replies
  • 5362 views

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\


    3 replies

    M
    mattt1-Visitor
    1-Visitor
    December 8, 2011

    Pai,

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

    A
    abalousek11-Garnet
    11-Garnet
    December 9, 2011

    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!




    K
    kmagdum14-Alexandrite
    14-Alexandrite
    May 31, 2016

    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.

    P
    PaiChung22-Sapphire I
    22-Sapphire I
    May 31, 2016

    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.

    J
    jbrooks11-Visitor
    1-Visitor
    March 24, 2017

    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

    Terms & ConditionsAccessibility statement