Examples

Inserting tag values to a MySQL database

Scripting can be used to connect to MySQL by using the mysql internal library. More information about internal libraries can be found at Libraries.

The following example shows how to insert each new value of a tag into one of the tables in the database. In order to do so, create a start-up action that connects to the data base, a shutdown action to easily disconnect from the database, and a tag change action to insert the new value of the tag to the database.

The table in the database will be named data and its columns are labelled tag, boolValue, quality, ts, millis.

Start-up script:

connect();

function connect(){ 
  var mysql = $.lib["mysql"];
  //Options used to connect to the library
  var options = {
    host:"localhost",
    user:"username",
    password:"password",
    database:"DatabaseName",
    insecureAuth:true //Needed if using the legacy auth method 
  };
  //Create and save the connection as a task variable
  $.local.sql = $.lib.mysql.createConnection(options);
  //Connect to the database
  $.local.sql.connect(function(err){
    if(err) $.exit(err);
    $.logger.info("Connected with ID %d", $.local.sql.threadId);
  });
}

Shutdown script:  

end();

function end(){
  $.logger.info("Disconnecting from MySQL...");
  //Get the task variable sql, and end the connection
  $.local.sql.end(function(err){
    if(err) return $.exit(new Error("Error disconnecting: "+err));
    $.logger.info("Disconnected OK")
    $.exit();
  })
}

Tag changes script for each tag to insert into the database:  

insert();

function insert(){
  //The tag to insert
  var tagPath = "/Scripting/BoolTag"
  //Query preparation
  var query = "INSERT INTO `data` (tag, boolValue, quality, ts, millis) VALUES              (?,?,?,FROM_UNIXTIME(?), ?)"
  $.logger.debug("Tag changed: %s", tagPath);
  //Reading the tag 
  $.api.tag.read(tagPath, {}, function(err, res){
    if(err) return $.exit(err);
    $.logger.trace("Tag change %s -> %j", err, res.data);
    $.logger.debug("Inserting into DB...")
  //The data as Array used for the query (required by the library)
  var data = [tagPath, res.data.value, res.data.quality, Math.round(Date.parse(res.data.ts)/1000), Date.parse(res.data.ts)%1000]
  //Query execution
    $.local.sql.query(query, data, function(err, results, fields){
      if(err) return $.exit(err);
      $.logger.debug("Inserted OK");
      $.logger.trace(results);
      $.exit();
    });
  });
}

Reading tag values from Microsoft SQLServer database

This example periodically reads values from an MS SQLServer database and writes the values to N3uron tags. The database is labelled n3uron and the table is labelled tags and contains two columns: TagName and TagValue: 

Figure 23. Database columns used in this example 

In the below example, the tag table contains the following data:

Figure 24. Data sources used in this example 

• Step 1: Create a mssql library instance in the Libraries configuration. This is an internal library. More information can be found at Libraries.
• Step 2: Create a task (MSSQL in this example) in the Scripting module and then create a start-up action and a periodic action inside that task:

Figure 25. Configuration of task, actions, and scripts for this example 

• Step 3: Create the connection script inside the Start-up action with the following code:

var mssql = $.lib.mssql;
var config = {
  user: "sa",
  password: "myPassword",
  server: "localhost",
  database: "n3uron"
}
$.local.connected = false;
_connect();
function _connect(){
  $.logger.info("Connecting to MSSQL...");
  $.local.db = new mssql.Connection(config)
  .on("error", function(err){
    $.logger.error("Error in MSSQL connection: %s", err);
  }).on("connect", function(){
    $.logger.info("Connected OK");
  });
  
  $.local.db.connect(function(err){
      if(err){
        $.logger.error("Error connecting to MSSQL: %s", err);
        return $.exit();
      }
  });
}

• Step 4: Create the script inside the Periodic action in order to read the data from the database and write the values to the corresponding tags:
• Step 5: Create a Shutdown action to gracefully disconnect the MSSQL connection:

if($.local.db.connected === false) return $.exit();

getData();
function getData(){
  var request = new $.lib.mssql.Request($.local.db);
  $.logger.debug("Executing query...");
  request.query("SELECT * FROM dbo.tags", function (err, res) {
      if (err) return _logger.error("Error executing query: %s");
      $.logger.debug("Query result is %j",res);
      writeTags(res);
  });
}

function writeTags(res){
  $.logger.debug("Writing tags");
  $write(0, res, function(err){
    if(err){
      $.logger.error("Error writing tags: %s", err);
      return $.exit(err);
    }
    $.logger.debug("Write OK");
    $.exit();
  });
}

function $write(i, res, cb){
  if(i >= res.length){
    return cb(null);
  }
  $.api.tag.write(res[i].TagName, res[i].TagValue, function(err){
    if(err) return cb(err);
    else setImmediate($write, i+1, res, cb);
  });
}

• Step 6: Create a Shutdown action to gracefully disconnect the MSSQL connection: 

end();
function end(){
  $.logger.info("Disconnecting from MSSQL...");
  //Get the task variable sql, and end the connection
  $.local.db.close();
  $.exit();
}

Once the task is running, the N3uron tags are updated with the values from the database, in this case every 10 seconds:

Figure 26. Tag values obtained from the database. 

Iterating through an array asynchronously

This example illustrates how to iterate through an array whilst performing asynchronous operations. Since JavaScript is an asynchronous language, normal iteration with asynchronous functions does not work, as the result of each function call won’t be available when the iteration finishes. As such, a different type of iteration is needed.

This example will use the fs function fs.stat to get the stats of an array of file names: 

function getFilesStat(fileArray, cb) {
  //Sync code before starting the async loop
  //Set up the result array
  var res = []
  //Start the async loop at index 0, no other code 
  //should execute after starting the async loop
  $asyncLoop(0);

  function $asyncLoop(i) {
    //Define the next function which will handle calling the next iteration 
    //of the loop, or exiting the loop if called with error
    function $next(err) {
      //If next is called with error, the callback will be called with an error
      if (err) return setImmediate(cb, null);
      //Otherwise, the next iteration is scheduled
      else return setImmediate($asyncLoop, ++i);
    }
    //If the index falls outside of the array range, the iteration is over
    //The callback is called with no error, and with the result object
    if (i >= fileArray.length) {
      return setImmediate(cb, null, res);
    }
    var fileName = fileArray[i];
    //Call the async function using a callback
    fs.stat(fileName, function(err, stat) {
      //If the callback returns an error, call $next with error
      if (err) return $next(err);
      //Other sync code can be placed here to manipulate 
      //the result of the async function
      //Push the resulting value into the result array
      res.push(stat);
      //Call next without error
      $next();
    });
  };
}