5 Replies Latest reply on Mar 24, 2017 10:38 AM by jbrooks RSS
    paic Collaborator

    SQL Server JDBC Connection Setup

    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\


      • SQL Server JDBC Connection Setup
        mattt Newbie

        Pai,

         

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

          • SQL Server JDBC Connection Setup
            andyb Explorer

            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!




          • Re: SQL Server JDBC Connection Setup
            ketanm Explorer

            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.

            • Re: SQL Server JDBC Connection Setup
              jbrooks Newbie

              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