• 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.