Reading from the database

Introduction

In this excercise we'll use the Database context object to query the attached database of the project. The object exposes four sub-objects (analogs, digitals, alarms and userlog), and we will use the alarms object to build a rudimentary alarm list.

Note

The complete view can be downloaded from here, but you will still need to follow some of the setup steps for it to work.

Setup

• In SSF_Tutorials, create a new User Area called AlarmList.
• Open Folder Views Tool and remove all the default Widgets and Link Icons.
• Create a new Link Icon and leave the default name as is (UnnamedLinkIcon_1).

In the File attribute, select Create a new view in this user area..., change the name to AlarmList.Rwav and save the the changes in Folder Views Tool.

Open the newly created view for editing.

• Remove the default elements.
• Add a Text element with the name Alarms.
  • Make the element bigger so some text can fit in it.
• Save the file.

In your browser, navigate to the new User Area and make sure the elements display correctly.

Adding the server-side code

In the view root object, on the Javascript tab, open up the ServerSideJS editor and paste the following code:

ServerSideJS

return {
  getAlarms: async (args, callInfo) => { 

    // Store the alarms db object under a shorter name
    const db = callInfo.context.db.alarms;

    // Just some regular SQL to get the three newest alarms
    const sqlQuery = `
      SELECT TOP (3)
        [Name],
        [Class],
        [AlarmTime],
        [AlarmText],
        [Status]
      FROM [Alarm Register]
      ORDER BY [AlarmTime] DESC
    `;

    return await db.query(sqlQuery);
  }
}

Info

Even though we're using the alarms object of the Database context object we still must specify which table we want to query. The diffrent sub-objects (analogs, digitals, alarms and userlog) only handle the connection string mappings.

Danger

Please remember that the SQL code you write will be executed against the database without any limitations or sanity checks. It is fully possible to, for example, delete data or drop tables. Use this power with caution. The Database context object reference contains more information about this.

Adding the client-side code

Open the OnOpen editor for the root object and paste the following code. Make sure to save the view when done:

OnOpen

const getStatusTextFromCode = (statusCode) => {
    // These are the code/description mappings from EXO
    switch (statusCode) {
        case 0:
            return 'Undefined';
        case 1:
            return 'Normal';
        case 2:
            return 'Blocked';
        case 3:
            return 'Acknowledged';
        case 5:
            return 'Returned';
        case 7:
            return 'Alarmed';
        default:
            return 'Unknow status code';
    }
}

this.view.call('.getAlarms', {}).then((alarms) => { 
    // Simply return without doing anything if no 
    // alarms were returned
    if (alarms === undefined) {
        console.error('No alarms were found');
        return;
    }

    // This will be the aggregated text
    let alarmListText = '';

    // Iterate over all alarms
    alarms.forEach(alarm => {
        // Each alarm object have the same properties
        // as the columns we queried for.
        // Note that they are case-sensitive!
        alarmListText += `${alarm.AlarmTime}\n${alarm.Name}\n${getStatusTextFromCode(alarm.Status)}\n${alarm.AlarmText}\n\n`;
    });

    this.view.Alarms.value(alarmListText);
});

The result

Open your browser and navigate to the area. When your view loads it should look like this:

Quick review

In this part we created a very simple and limited (but working) implementation of an alarm list. Please note that it in reality it isn't that useful but mearly exist as example of what can be done using the Database context object.

Ideas and improvements

Perhaps it is better to leave the alarm handling to the Alarm Widget, but there are plenty of other things you could do using the access to the database. You could create a small popup view that displays the last 5-10 values for a sensor in a table. Or doing the same thing in an interactive svg to draw an actual graph.