Steps to create a External Relational Database Thing and update configuration at Runtime in ThingWorx

• How to input Database User Credentials at RunTime.

This Blog considers that you have already imported the Database Extension and Configured the Thing Template. If you have not done this already please see Steps to connecting to your Relational Database first.

Steps:

1. Create a Database Thing template with correct configuration.
   • Example configuration for MySql Database:
     • jDBCDriverClass: com.mysql.jdbc.Driver
     • jDBCConnectionURL: jdbc:mysql://127.0.0.1:3306/<DatabaseNameHere>?allowMultiQueries=true
     • connectionValidationString: SELECT NOW()
     • maxConnections: 100
     • userName: <DataBaseUserNameHere>
     • password: <DataBasePasswordHere>
2. Create any Generic Thing and add a service to create thing based on the Thing template created in Step 1.
   • Example:

     • // NewDataBaseThingName is the String input for name the database thing to be created.
       // MySqlServerUpdatedConfiguration is the Thing template with correct configuration
       var params = {
            name: NewDataBaseThingName /* STRING */,
            description: NewDataBaseThingName /* STRING */,
           thingTemplateName: "MySqlServerUpdatedConfiguration" /* THINGTEMPLATENAME */,
           tags: undefined /* TAGS */
       };
       // no return
       Resources["EntityServices"].CreateThing(params);

3. Add code to enable and then restart the above thing using EnableThing() and RestartThing() service.
   • Example

     • Things[NewDataBaseThingName].EnableThing();
       Things[NewDataBaseThingName].RestartThing();

4. Test and confirm that the Database Thing services runs as expected.
5. Now Create a DataShape with following Fields:
   • jDBCDriverClass: STRING
   • jDBCConnectionURL: STRING
   • connectionValidationString: STRING
   • maxConnections: NUMBER
   • userName: STRING
   • password: PASSWORD
6. Now in the Generic Thing created in Step 1 add code to update the configuration settings of DataBase Thing.
   • Make sure JDBC Driver Class Name should never be changed.
   • If different database connection is required use different Thing Template.
   • Also, add code to restart the DataBase Thing using RestartThing() service.
   • Example:

     • var datashapeParams = {
           infoTableName : "InfoTable",
           dataShapeName : "DatabaseConfigurationDS"
       };
       // CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(DatabaseConfigurationDS)
       var config = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(datashapeParams);
       var passwordParams = {
               data: "DataBasePasswordHere" /* STRING */
       };
       // DatabaseConfigurationDS entry object
       var newEntry = new Object();
       newEntry.jDBCDriverClass= "com.mysql.jdbc.Driver"; // STRING
       newEntry.jDBCConnectionURL = "jdbc:mysql://127.0.0.1:3306/<DatabaseNameHere>?allowMultiQueries=true"; // STRING
       newEntry.connectionValidationString = "SELECT NOW()"; // STRING
       newEntry.maxConnections = 100; // NUMBER
       newEntry.userName = "DataBaseUserNameHere"; // STRING
       newEntry.password = Resources["EncryptionServices"].EncryptPropertyValue(passwordParams); // PASSWORD
       config.AddRow(newEntry);
       var configurationTableParams = {
       configurationTable: config /* INFOTABLE */,
       persistent: true /* BOOLEAN */,
       tableName: "ConnectionInfo" /* STRING */
       };
       // ThingNameForConfigurationUpdate is the input string for Thing Name whose configuration needs to be updated.
       // no return
       Things[ThingNameForConfigurationUpdate].SetConfigurationTable(configurationTableParams);
       Things[ThingNameForConfigurationUpdate].RestartThing();
       

7. Test and confirm that the Database Thing services runs as expected.