Display Data from SQLite to TwinCAT HMI

Why to use SQLite

SQLite is an embeddable open source database, written in C and queryable with conventional SQL, that is designed to cover those use cases. SQLite is designed to be fast, portable, and reliable, whether we are storing only kilobytes of data or multi-gigabyte blobs.

SQLite’s greatest advantages is that it can run on major platforms. SQLite has been ported to a wide variety of platforms: Windows, macOS, Linux, iOS, Android, etc.

We have already seen in some examples articles with MSSQL Server. It is quite heavy for storing a small amount of data. You need to install the SQL server, Management Studio and then you can save/retrieve data from the server.

We are talking about TwinCAT HMI, we need to store a small amount of data for HMI. This article will convert the example found in https://www.hemelix.com/sql/nhibernate-and-dll/  and adapt it for TwinCAT HMI.

We shall use Fluent NHibernate.  Fluent NHibernate is a DLL that can be linked to our projects and all SQL data can be saved or updated or even deleted without knowing the different SQL syntaxes. Of course, we can write our SQL syntaxes but those are error-prone and our focus will be in another direction than the automation.

What Software we need before continuing

=> FluentNHibernate from Nuget

=> System.Data.SQLite.Core from Nuget

=> Newtonsoft.Json from Nuget

How List can be saved as string

We can declare a variable which can save  our data. 

We have an object like the following in HMI. We want to save these data to database as string to save processing time.

//A list of object should be saved to DB
 let obj = {     startTime : "",     stopTime : "",     maxSpeed : 0,     runTimeAtMaxSpeed : 0,     numberOfSpeedChange : 0
 }    
//Declare a class in the DB module
    public class Data
    {
        public virtual int Id { get; protected set; }
        public virtual string DataString { get; set; } // This will hold the data as string (list of abobe obj)
    }
    //Save the data to DB
    Data d = new Data();
    d.DataString = command.WriteValue;
    sqlConnector.AddItem(d); //We delete any existing data, since DataString will be saved
    //When we are reading back we can do the following.
    string s = "";
    var list = session.QueryOver<Data>().List();
    s = list[0].DataString;
    var data = await ReadPLCVariable('%s%ServerExtensionCSharpEmpty1.GetDataValue%/s%'); // Read from HMI
    var obje = JSON.parse(data);

Tips

Tips 01: no persister for: Fluent NHibernate Exception Error

This error arises from the mapping of the array for example. The following example will produce this error

    public class Data
    {
        public virtual int Id { get; protected set; }
        public virtual string Test1 { get; set; }
    }
    public class DataMap : ClassMap
    {
        public DataMap()
        {
            Id(x => x.Id);
            Map(x => x.Test1);
            Table("DataTable");
        }
    }
        private static void MainFN()
        {
            using (var session = sessionFactory.OpenSession())
            {
                using (var transaction = session.BeginTransaction())
                {
                    Data[] balance = new Data[12];
                    for (int i = 0; i < 12; i++)
                    {
                        balance[i] = new Data();
                        balance[i].Test1 = "Example Data " + (i + 1).ToString();
                        session.SaveOrUpdate(balance[i]);  //Should be like this
                    }
                    //session.SaveOrUpdate(balance);  // Causes no persister for: Fluent nHibernate Exception

The above error can occur in the case if we have not mapped our class also. For example, we don’t have the class DataMap

 

Tips 02:

If we configure the database in the following way then we can have the DB file in a particular folder. Otherwise, it will create the DB file in the c:\programdata  folder somewhere. We can write with the full path instead of a single DB file name.

 if (!File.Exists(@”C:\TwinCAT\Functions\TF2000-HMI-Server\projectData.db”))

 

Tips 03:

Initializing[ServerExtension.MyObject#2]-failed to lazily initialize a collection of roles: ServerExtension.MyObject.another object, no session or session was closed

The above error occurred when we are trying to use the object which was not loaded  yet and the session was closed already.

using(var session = NHibernateHelper.OpenSession()) {
  using(var transaction = session.BeginTransaction()) {
    IList < Car > cars = session.QueryOver < Car > ().Where(c =>c.Id == deleteID).List();
    if (cars.Count > 0) {
      session.Delete(cars[0]);
      session.Flush();
    }
  }
}

 

//now we are trying to use the car’s model which was not loaded yet but function was returning (session was closed).

console.write(car.Make); // will through the above message

 

Tips 04: How to pass JavaScript Object to extension with array

     const person = {
        Test1: test1.getText(),
        Test2: parseInt(test2.getText()),
        Test3: checkBoxStateChecked,
        CurrentTime: timeString,
        ComboBox : comboboxState
    };    

Now say,  Test3 is an array of object and we need to pass to extnsion or other module. We can do like the following:

person.Test2 = the object array or 

     const person = {
        Test1: test1.getText(),
        Test2: the object array 
        Test3: checkBoxStateChecked,
        CurrentTime: timeString,
        ComboBox : comboboxState
    };  

var myJSONString = JSON.stringify(person);

These two cases will generate different types of data! Check it out or do testing

 

Download the sample from the link given above.

Next, let’s try to use ADS and Extension to HMI at https://www.hemelix.com/scada-hmi/twincat-hmi/beckhoff-twincat-hmi-javascript-framework-verses-extension-module/

Ask questions related to Hemelix sample code and design at Google group https://groups.google.com/g/hemelix