--- title: "Scripting | Examples | N3uron KB V.1.20" slug: "scripting-examples" description: "Scripting is a N3uron module designed to run custom scripts based on events like timers, tag changes, conditions, system start-up and system shutdown…" updated: 2022-03-29T07:33:36Z published: 2022-03-29T07:33:36Z canonical: "docs.n3uron.com/scripting-examples" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.n3uron.com/llms.txt > Use this file to discover all available pages before exploring further. # Examples ## [](/v120/docs/scripting-examples#_Toc36806232)Inserting tag values to a MySQL database Scripting can be used to connect to MySQL by using the [mysql](https://www.npmjs.com/package/mysql)[](https://www.npmjs.com/package/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: ```javascript 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: ```javascript 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: ```javascript 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: ![](https://cdn.document360.io/54093ab5-6b22-4542-a265-04377931f11a/Images/Documentation/23.png)Figure 23. Database columns used in this example In the below example, the tag table contains the following data: ![](https://cdn.document360.io/54093ab5-6b22-4542-a265-04377931f11a/Images/Documentation/24.png)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: ![](https://cdn.document360.io/54093ab5-6b22-4542-a265-04377931f11a/Images/Documentation/25.png)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: ```javascript 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: ```javascript 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: ```javascript 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: ![](https://cdn.document360.io/54093ab5-6b22-4542-a265-04377931f11a/Images/Documentation/26.png)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: ```javascript 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(); }); }; } ``` ## [](/v120/docs/scripting-examples#_Toc36806234)