Examples

Inserting tag values into 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 tag changes into the database. This assumes a database and a table are already created with the following schema: 

In order to insert the events, two scripts will be created, one serving as a wrapper over the raw MySQL connection (as a static library), and one startup script that will subscribe to events and export them to the database.

The following libraries configuration is used:

The mysql_connector static library contains the following script:

// Import the MySQL internal library
const mysql = $.lib("mysql");

// Export the MySqlConnection class
module.exports = class MySqlConnection {
  // Constructor of the class taking a MySQL client instance
  constructor(client) {
    this._client = client;

    // Error event handler that logs a message to the logger
    client.on("error", (err) => {
      $.logger.warn(
        "Error on client: %s (%d - %s)",
        err.message,
        err.errno,
        err.sqlMessage
      );

      // Additionally, if the error is fatal, the client is dropped
      if (err.fatal) {
        this._client = null;
      }
    });
  }

  // This function must be called when the client is no longer being used
  // in order to terminate the connection
  dispose() {
    return new Promise((resolve, reject) => {
      if (this._client) {
        // If the client is online, end the client
        this._client.end((err) => {
          if (err) {
            reject(err);
          } else {
            resolve();
          }
          this._client = null;
        });
      } else {
        reject(new Error("Connection already terminated"));
      }
    });
  }

  // Checks whether the client is online or not
  online() {
    return this._client !== null;
  }

  // This function executes a query and resolve to a promise
  // containing a tuple with the first element being the result
  // of the query, and the second element the fields returned from
  // the query
  query(query, ...params) {
    return new Promise((resolve, reject) => {
      if (this._client) {
        this._client.query(query, params, (err, result, fields) => {
          if (err) {
            //If the error is fatal, set the client to null
            if (err.fatal) {
              this._client = null;
            }
            reject(err);
          } else {
            resolve([result, fields]);
          }
        });
      } else {
        reject(new Error("Connection is terminated"));
      }
    });
  }

  // Static method used to asynchronously create a new client, which is necessary since creating a connection
  // is an asynchronous operation
  static Connect(connOpts) {
    return new Promise((resolve, reject) => {
      // Create the client with the provided options
      const client = mysql.createConnection(connOpts);

      // Connect the client to the DB
      client.connect((err) => {
        if (err) {
          reject(err);
        } else {
          // If the connection is OK, create a MySqlConnection instance using the client
          // and resolve the promise with that instance
          const connection = new MySqlConnection(client);
          resolve(connection);
        }
      });
    });
  }
};

The startup script that relies on the library and is responsible for sending the event uses the following code:

const MySqlConnection = $.lib("mysql_connector");

// Define the options used to connect to MySQL
const OPTIONS = {
  host: "localhost",
  port: 3306,
  user: "admin",
  password: "n3uron",
  database: "n3_scripting",
};

// Define insertion period
const PERIOD = 10000;

// Define the tags that will be inserted into the DB
const TAGS = ["/Simulated/String", "/Simulated/Bool", "/Simulated/Number"];

// Subscribe to the tags
const subscription = await $.api.tag.subscribe(TAGS, onTagEvent);

// Buffer that holds the events between insertions
const events = [];

// Database client
let client = null;

//Set a 10 seconds timer before pushong the events
setTimeout(pushEvents, PERIOD);

//Handler that gets called whenever the tags have a new event
function onTagEvent(tag, data) {
  //Save the event to the buffer
  events.push({ tag, value: data.value, quality: data.quality, ts: data.ts });
}

// Async function called every PERIOD milliseconds to insert data into the DB
async function pushEvents() {
  // If the client is null or offline, connect the client
  if (!client || (client && !client.online())) {
    try {
      await connect();
    } catch (ex) {
      // If the connection fails, schedule a new timer
      $.logger.warn("Error connecting: %s", ex.message);
      return setTimeout(pushEvents, PERIOD);
    }
  }

  // If the client is connected, remove the events from the buffer
  // and transform them into a format that can be used by MySQL
  const ev = events.splice(0).map((el) => {
    // Set number_ev, string_ev and bool_ev either to the event
    // or to null if the type does not match
    const number_ev = typeof el.value === "number" ? el.value : null;
    const string_ev = typeof el.value === "string" ? el.value : null;
    const bool_ev = typeof el.value === "boolean" ? el.value : null;

    // Return a 6 element tuple representing the values that will be inserted into the DB
    return [el.tag, number_ev, string_ev, bool_ev, el.quality, new Date(el.ts)];
  });
  try {
    $.logger.debug("Inserting %d events", ev.length);

    // Execute an INSERT query using ? as placeholder for the values
    await client.query(
      `INSERT into tag_events (tag, number_value, string_value, bool_value, quality, ts) VALUES ?`,
      ev
    );
    $.logger.debug("Inserted OK", ev.length);
  } catch (ex) {
    $.logger.warn("Error during query: %s", ex.message);

    // If the query fails and the client is offline, null the client
    if (!client.online()) {
      client = null;
    }
  } finally {
    // After finishing the query (whether it was successful or not)
    // schedule a new timer
    setTimeout(pushEvents, PERIOD);
  }
}

// Creates a new MySQL client
async function connect() {
  $.logger.debug(
    "Connecting to MySQL using %s@%s:%d",
    OPTIONS.user,
    OPTIONS.host,
    OPTIONS.port
  );
  client = await MySqlConnection.Connect(OPTIONS);
  $.logger.debug("Connected OK");
}

Reading tag values from Microsoft SQLServer database

This example reads data from a Microsoft SQL Server database and saves it to Scripting source tags. This is done using the mssql NPM library, which is integrated in N3uron (and thus can be used as an internal library).

This example assumes the target table has the following schema, and that it's data it's being updated from an external source:

In this example, the table has the following data:

This data will be saved to Scripting source tags using an alias, this allows the tags in N3uron to have a different structure to those in Microsoft SQL Server. A configuration of a source tag can be seen in the next screenshot:

This example will use two scripts (a periodic script and a static library) as well as an internal library. The following two libraries are used:

The mssql_connector static library has the following script:

// Import the Microsft SQL Server internal library
const mssql = $.lib("mssql");

// Export the SqlServerConnection class
module.exports = class SqlServerConnection {
  // Constructor of the class taking a SqlServerConnection client instance
  constructor(client) {
    this._client = client;
    // Error event handler that logs a message to the logger and sets the client to null
    client.on("error", (err) => {
      $.logger.warn("Error on client: %s", err.message);
      // Drop the client after an error
      this._client = null;
    });
  }

  // This function must be called when the client is no longer being used
  // in order to terminate the connection
  dispose() {
    if (this._client) {
      this._client.close();
      this._client = null;
    } else {
      throw new Error("Connection is already closed");
    }
  }

  // Checks whether the client is online or not
  online() {
    return this._client !== null;
  }

  // This function executes a query and resolve to a promise
  // containing a tuple with the first element being the result
  // of the query, and the second element the fields returned from
  // the query
  async query(query, ...params) {
    if (this._client) {
      // Creates a new request on the connection, and executes the query
      return await new mssql.Request(this._client).query(query);
    } else {
      throw new Error("Connection is offline");
    }
  }

  // Static method used to asynchronously create a new client, which is necessary since creating a connection
  // is an asynchronous operation
  static async Connect(connOpts) {
    //Create the client with the provided options and connect with DB
    const client = await mssql.connect(connOpts);

    //If the connection is OK, create a SqlServerConnection instance
    //using the client and return it
    return new SqlServerConnection(client);
  }
};

The periodic script has a 10-second trigger, and it's used to retrieve data from the database and save it to the source tags. It has the following code:

const SqlServerConnection = $.lib("mssql_connector");

// Define the options used to connect to Microsoft SQL Server
const OPTIONS = {
  server: "10.1.3.169",
  port: 1433,
  user: "sa",
  password: "n3uron$!",
  database: "n3_scripting_manual",
};

// If the local variables are not initialized, initialize them
if ($.local.init === undefined) {
  await init();
}

// Retrieve the data, and exit the script after the async call is finish
await getData();

// Async function used to retrieve the data
async function getData() {
  // If the client is not online, attempt to reconnect
  if ($.local.client === null || !$.local.client.online()) {
    const client = await connect();
    // If the client connects OK, save it to $.local.client and keep going
    if (client !== null) {
      $.local.client = client;
    } else {
      // If the client is null, it means the connection was unsuccessful, so the query can't be executed
      return;
    }
  }
  try {
    // Try to retrieve the data using a SELECT query
    const queryResult = await $.local.client.query(
      "SELECT tag, value FROM dbo.data"
    );
    // For every row in the result, destructure the row object into the tag and value variables
    // and execute a tag alias update
    for (const { tag, value } of queryResult) {
      $.api.tag.updateAlias(tag, value);
    }
  } catch (ex) {
    // If the query has an error, log the error
    $.logger.warn("Error during query: %s", ex.message);
  }
}

// Creates and returns a new Microsoft SQL Server client
async function connect() {
  $.logger.debug(
    "Connecting to Microsoft SQL Server using %s@%s:%d",
    OPTIONS.user,
    OPTIONS.server,
    OPTIONS.port
  );
  try {
    const client = await SqlServerConnection.Connect(OPTIONS);
    $.logger.debug("Connected OK");
    return client;
  } catch (ex) {
    $.logger.warn("Error connecting client: %s", ex.message);
    return null;
  }
}

// Initializes the required local variables
async function init() {
  $.local.init = true;
  // The client must be saved in a local variable to only has one instance
  // that persists between script executions
  $.local.client = null;
}

Creating a table and inserting tag values into a Microsoft SQL database

In this example, we define the table schema directly within the script and insert tag values into a Microsoft SQL Server database. This is done using the mssql NPM library, which is integrated in N3uron (and thus can be used as an internal library).

This example will use two scripts (a periodic script and a static library) as well as an internal library. The following two libraries are used:

The mssql_connector static library has the following script:

// Import the Microsft SQL Server internal library
const mssql = $.lib("mssql");

// Export the SqlServerConnection class
module.exports = class SqlServerConnection {
  // Constructor of the class taking a SqlServerConnection client instance
  constructor(client) {
    this._client = client;
    // Error event handler that logs a message to the logger and sets the client to null
    client.on("error", (err) => {
      $.logger.warn("Error on client: %s", err.message);
      // Drop the client after an error
      this._client = null;
    });
  }

  // This function must be called when the client is no longer being used
  // in order to terminate the connection
  dispose() {
    if (this._client) {
      this._client.close();
      this._client = null;
    } else {
      throw new Error("Connection is already closed");
    }
  }

  // Checks whether the client is online or not
  online() {
    return this._client !== null;
  }

  // This function executes a query and resolve to a promise
  // containing a tuple with the first element being the result
  // of the query, and the second element the fields returned from
  // the query
  async query(query, ...params) {
    if (this._client) {
      // Creates a new request on the connection, and executes the query
      return await new mssql.Request(this._client).query(query);
    } else {
      throw new Error("Connection is offline");
    }
  }

  // Static method used to asynchronously create a new client, which is necessary since creating a connection
  // is an asynchronous operation
  static async Connect(connOpts) {
    //Create the client with the provided options and connect with DB
    const client = await mssql.connect(connOpts);

    //If the connection is OK, create a SqlServerConnection instance
    //using the client and return it
    return new SqlServerConnection(client);
  }
};

This periodic script, triggered every 10 seconds, establishes the connection settings to the database, creates the table if it doesn't already exist, and inserts data from the tags. The script includes the following code: 

const SqlServerConnection = $.lib("mssql_connector");

// Define the options used to connect to Microsoft SQL Server
const OPTIONS = {
  database: "Test",
  server: "127.0.0.1",
  connectionTimeout: 3000,
  port: 1433,
  authentication:{
    type:"default",
    options:{
      userName: "sa",
      domain: null,
      password: "n3uron$!",
      }
    },
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 100000
      },
    options: {
      encrypt: false, 
      trustServerCertificate: false // change to true for local dev / self-signed certs
    }
};

// Define insertion period
const PERIOD = 10000;

// Define the tags that will be inserted into the DB
const TAGS = ["/VoltageBR", "/VoltageRY", "/VoltageYB"];

// Subscribe to the tags
const subscription = await $.api.tag.subscribe(TAGS, onTagEvent);

// Buffer that holds the events between insertions
const events = [];

// Database client
let client = null;

// Set a 10-second timer before pushing the events
setTimeout(pushEvents, PERIOD);

// Handler that gets called whenever the tags have a new event
function onTagEvent(tag, data) {
  // Save the event to the buffer
  events.push({ tag, value: data.value, quality: data.quality, ts: data.ts });
}

// Async function to ensure the table exists before starting data insertion
async function ensureTableExists() {
  const createTableQuery = `
    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='TAG_EVENTS' AND xtype='U')
    CREATE TABLE TAG_EVENTS (
        id INT PRIMARY KEY IDENTITY(1,1),
        tag NVARCHAR(255) NOT NULL,
        number_value FLOAT NULL,
        string_value NVARCHAR(MAX) NULL,
        bool_value BIT NULL,
        quality TINYINT NOT NULL, -- Changed to TINYINT to store values from 0-255
        ts DATETIME2 NOT NULL
    );`;
    $.logger.debug("Create table query: %s", createTableQuery);

  try {
    await client.query(createTableQuery);
    $.logger.debug("Ensured that table TAG_EVENTS exists.");
  } catch (ex) {
    $.logger.warn("Error ensuring table exists: %s", ex.message);
  }
}

// Async function called every PERIOD milliseconds to insert data into the DB
async function pushEvents() {
  // If the client is null or offline, connect the client
  if (!client || (client && !client.online())) {
    try {
      $.logger.debug("Connecting");
      await connect();
      $.logger.debug("ConnectOk");
      $.logger.debug("Creating Table");
      await ensureTableExists(); // Ensure the table exists after connecting
      $.logger.debug("Table Created");
    } catch (ex) {
      // If the connection fails, schedule a new timer
      $.logger.warn("Error connecting: %s", ex.message);
      return setTimeout(pushEvents, PERIOD);
    }
  }

  // If the client is connected, remove the events from the buffer
  // and transform them into a format that can be used by SQL Server
const ev = events.map((el) => {
  // Set number_ev, string_ev and bool_ev either to the event
  // or to null if the type does not match
  const number_ev = typeof el.value === "number" ? el.value : null;
  const string_ev = typeof el.value === "string" ? el.value : null;
  const bool_ev = typeof el.value === "boolean" ? el.value : null;
  
  // Return a 6-element array representing the values that will be inserted into the DB
  const values = [
    `'${el.tag}'`, 
    number_ev !== null ? number_ev : 'NULL', 
    string_ev !== null ? `'${string_ev}'` : 'NULL', 
    bool_ev !== null ? bool_ev : 'NULL', 
    el.quality, 
    `'${new Date(el.ts).toISOString()}'`
  ];
  
  return `(${values.join(", ")})`;
  
});

  try {
    $.logger.debug("Inserting %d events", ev.length);
    // Execute an INSERT query 
    const query = `INSERT INTO TAG_EVENTS (tag, number_value, string_value, bool_value, quality, ts) VALUES ${ev.join(",")}`;
  
    $.logger.debug("Query %s", query);
    await client.query(query);
    $.logger.debug("Inserted OK", ev.length);
  } catch (ex) {
    $.logger.warn("Error during query: %s", ex.message);

    // If the query fails and the client is offline, null the client
    if (!client.online()) {
      client = null;
    }
  } finally {
    // After finishing the query (whether it was successful or not)
    // schedule a new timer
    setTimeout(pushEvents, PERIOD);
  }
}

// Creates a new SQL Server client
async function connect() {
  $.logger.debug(
    "Connecting to SQL Server using %s@%s:%d",
    OPTIONS.user,
    OPTIONS.server,
    OPTIONS.port
  );
  client = await SqlServerConnection.Connect(OPTIONS);
  $.logger.debug("Connected OK");
}

// Initializes the required local variables
async function init() {
  $.local.init = true;
  // The client must be saved in a local variable to only have one instance
  // that persists between script executions
  $.local.client = null;
}

The following screenshot shows the configuration of the Scripting task, where the script is triggered at startup or whenever the Scripting module is restarted. After initialization, the script performs data insertion into the database every 10 seconds. The execution period and tag subscriptions are hardcoded within the script, but these settings can be parameterized if needed using Parameters.

Writing data to XLSX files

This example reads data from Historian, using the history API call, and stores it to a template XLSX file that can be used for further data processing. This example will use the following XLSX file:

The Processed Data worksheet contains an average function over all the rows (skipping the first row, which are going to be used as the headers) of column B of _Data, while the _Data worksheet is empty, and it will be automatically filled by N3uron based on data from Historian.

In order to have access to the XLSX writer library, an internal library instance must be created:

Finally, the following script is used to retrieve events from Historian, and insert them as well as the headers:

// Load the XLSX library
const Workbook = $.lib("xlsx");

const tag = "/Memory/Historical";

// Retrieve 1 hour of data
const end = Date.now();
const start = end - 3600000;
const history = await $.api.tag.history(tag, start, end, { mode: "raw" });

// Open the template workbook
const workbookTemplatePath = String.raw`C:\Dev\Manual.xlsx`;
$.logger.debug("Opening workbook...");
const workbook = await Workbook.Open(workbookTemplatePath);

// Retrieve the _Data worksheet
$.logger.debug("Workbook opened OK, retrieving worksheet...");
const sheet = await workbook.getSheet("_Data");
$.logger.debug("Worksheet retrieved OK, inserting data...");

// Write the headers in the first row
sheet.writeData([["Timestamp", tag]], 0, 0);

// Parse the data as necessary
const cellData = [];
for (const [ts, value] of history.data) {
  cellData.push([ts, value]);
}

// Write the data, setting the startRow to 1 in order to not overwrite the headers
sheet.writeData(cellData, 1, 0);

// Save the workbook to a different path to avoid overwriting the template
const workbookOutPath = String.raw`C:\Dev\Manual_modified.xlsx`;
$.logger.debug("Data inserted OK, saving workbook...");
await workbook.save(workbookOutPath);
$.logger.debug("Workbook saved OK");