Supervisory Control and Data Acquisition for Arduino, with Visual Basic.net and Database Firebird

Introduction

With the same intent of SPADA via Sprotocol, I begin Spada with Modbus. I would improve the protocol, was my though. I said "I can not build a castle on the sand". So I decide to pass to Modbus via RTU, a standard de facto for the communication between industrial micro controller. The idea was to delete my old project, and replace with this. But after a bit, I decided to leave also Spada via Sprotocol. Why? Because works with the same logic, but for programming is very fast and easier. But Modbus is also a standard. So if you decide to make a project that want grow and will communicate with other devices, software ecc, if you want build a castle, Spada via Modbus is the solution. If you want something fast to code, that nobody will never see but also good, Spada with Sprotocol, I think is better indeed. And it is good also for move the first steps, because it speak with variables that are more familiar and not with registers.

I would remember the target of this project:

  • Control of the variables with hysteresis (threshold ON != threshold OFF)
  • Control of the variables with feedback
  • Set the thresholds On or OFF and the feedback "Setpoint" run-time, via PC
  • Actuate the corrections
  • Detect the local actions (such push of a button, example: a pump will be activated when the tank is full or when I push a button (may be because I want to empty it). I must be able to activate it also from PC)
  • If something is activated manually, show a warning
  • save in DB

ModBus

The principle behind ModBus is resumed in the picture: set variables->Information logic ->read variable:

Architecture

ModBus give you some functions, to write or read variables. That is all. And are also uncomfortable.

But is better use the right names, and right understand this part, because is the main difficulty when you will code. I lost half a day, with wrong coding.

ModBus, does not give you variable, but Register or Coils. In Spada with MODBUS are implemented only registers, so we do not speak about coils(that I think are not so bad, for send commands and on/off values in general).

Read careful this part: A register is composed from two bytes. Two. It means that if you utilize float or long, you have to divide in different registers. If you have for example a 4 byte value, you need two registers. There is a hard work of mapping, because the register size is bigger than the amount of variables that you actual utilize to work. You have to know in which register is stored your variable, and also which variable is stored in the register n. I suggest this link, to explain the problematic: http://www.chipkin.com/how-real-floating-point-and-32-bit-data-is-encoded-in-modbus-rtu-messages/

ModBus, has some standard functions. Are implemented only function 3, and 16. 3 is read from multiple registers, 16 is write in multiple registers(copy-pasted from http://www.codeproject.com/Articles/20929/Simple-Modbus-Protocol-in-C-NET?fid=473146&fr=26 .

.

Function 3 - Read Multiple Registers Message Framing

Request

  • Address (one byte denoting the slave ID)
  • Function Code (one byte denoting the function ID, in this case '3')
  • Starting Address (two bytes representing the modbus register at which to begin reading)
  • Register Quantity (two bytes denoting the amount of registers to read)
  • CRC (two bytes containing the cyclical redundancy check checksum for the outgoing message)

Response

  • Address (one byte containing the ID of the slave responding)
  • Function Code (one byte denoting the function to which the slave is responding, in this case '3')
  • Byte Count (one byte representing the quantity of bytes being read. Each modbus register is made up of 2 bytes, so this value would be 2 * N, with N being the quantity of registers being read)
  • Register Values (2 * N bytes representing the values being read)
  • CRC (two bytes containing the CRC checksum for the incoming message)

Function 16 - Write Multiple Registers Message Framing

Request

  • Address (one byte denoting the slave ID)
  • Function Code (one byte denoting the function ID, in this case '16')
  • Starting Address (two bytes representing the modbus register at which to begin writing)
  • Register Quantity (two bytes denoting the amount of registers to write)
  • Byte Count (one byte representing the quantity of bytes being written. Each modbus register is made up of 2 bytes, so this value would be 2 * N, with N being the quantity of registers being written to)
  • Register Values (2 * N bytes containing the actual bytes being written)
  • CRC (two bytes containing the cyclical redundancy check checksum for the outgoing message)

Response

  • Address (one byte containing the ID of the slave responding)
  • Function Code (one byte representing the function being responded to, in this case '16')
  • Starting Address (two bytes stating the starting register address that was first written to)
  • Register Quantity (two bytes denoting the quantity of modbus registers that were written to)
  • CRC (two bytes representing the CRC checksum of the incoming message)

What surprise me, is the lack of the address in the read-response.Practically we reach, values without know exactly who these come from. Fortunately, we can read the whole register in a read. ( And I can not imagine why somebody can need single read). We assume that each read-response we reach, is a whole register scan. This fact imply also, each answer must follow a question.

After all there is the job to scan the whole register, and re-build, where need, the more-byte variable. It is what at begin said the difficulties of ModBus

Arduino

ModBus is just a protocol of communication. SCADA systems, are build around the system that monitor. And a certain point you have to recognize the nature of your variable. So that when you grab your ModBus-Slave sketch (the 1.0 is the more update), among the others, the only thing that interest you is this part:

 
     
     /* slave registers example*/

//It is uncomfortable a slave register, better a variable register, and than 
    enum {        
            MB_REG0,
            MB_REG1,
            MB_REGS         /* total number of registers on slave */
    };

    int regs[MB_REGS];    /* this is the slave's modbus data map CHANGE ONLY THE INDEX IF YOU USE OTHER NAME IN THE ENUM-- */

    void setup() 
    {
            /* Modbus setup example, the master must use the same COM parameters */
            /* 115200 bps, 8N1, two-device network */
            configure_mb_slave(115200, 'n', 0);
    }


    void loop() 
    {
            /* This is all for the Modbus slave */
        update_mb_slave(MB_SLAVE, regs, MB_REGS);

        /* your code goes here */
    } 
      

What do with these things?

MAPPING:enum the variables:

 enum variables{
                        SYSTEM, //1 Register -reserved - position in reg= 0
                      LIGHT_ON, //1 Register  -    threshold on - position in reg=1,2
                      LIGHT_OFF,//1 Register  -    threshold off
                      LIGHT_READ,//1 Register- Light analog read pin A03
                      L1,//1 Register - Light read
                       L1_MANUALLY,//1 Register -  warning if L1 is activated manually 
                      PRESSURE_ON,//1 Register 
                      PRESSURE_OFF, //1 Register 
                      PRESSURE_SETPOINT,//1 Register -
                      DELAY, //2 Register- not more used... but leaved for example
                        SAVE,//1 Register
                      LOAD,//1 Register
                    RESET_EEPROM,//1 Register
                      
                  
                  //(registers count until here =14)
                  VARIABLES_COUNT
                  };

    #define REGISTER_COUNT 14  

but is not sufficient you have to map the register , so that the the array at position [YOUR_VARIABLE_NAME], will give you back his position in the register in his first column, and the count of register in the second column

 
                //                int map_Address[VARIABLES_COUNT][2]={{0,1},
                                        {1,1},
                                        {2,1},
                                        {3,1},
                                        {4,1},
                                        {5,1},
                                        {6,1},
                                        {7,1},
                                        {8,1},
                                        {9,2},//here is 2 bytes because I want reserve a long
                                        {11,1},
                                        {12,1} ,
                                        {13,1}  
                                        };
                                         

Now I need internal methods in my code for read and write, from inside the controller. Function 3 and 16 works from outside. But inside? So there is two functions, that due his frequently use, I used very short name:

 
        long V(int variable) //return the value of the desired variable, max 4 byte
    {
        long returnValue;
        if (map_Address[variable][1]=1) 
            return regs[map_Address[variable][0]];
        else 
        return regs[map_Address[variable][0]] << 16 +
                regs[map_Address[variable][0]+1];
            

    }

    void writeV(int variable, long value)//set my register
    {
        if (map_Address[variable][1]=1) 
          regs[map_Address[variable][0]]=value;
        else 
        {
            int bHI, bLOW;
            bHI=value>>16;
            bLOW=value & 0XFFFF;
            regs[map_Address[variable][0]] =bHI;
            regs[map_Address[variable][0]+1] =bLOW;
        }
    }

     

Now you can run your setup and loop. My loop for example is :

 //note how I utilize just variables until the very end
    void loop() 
    {
            /* check for master requests- This is MODBUS*/
         update_mb_slave(MB_SLAVE, regs, VARIABLES_COUNT);
     
     //FROM HERE BEGIN YOUR CODE
      writeV(LIGHT_READ,analogRead(0)); //read the light sensor and store in the register, available to enquire
        
        //catch the push of the button
      if (digitalRead(3)&& millis()-t_bounce>200  ) 
        {
          t_bounce=millis();
          writeV(L1,V(L1) ^ 1) ;     //turn on/off the led
          writeV(L1_MANUALLY,V(L1_MANUALLY) ^ 1); //activate/deactivate the warning manually activation
       }
     
      
     //process the variables
      if(V(L1_MANUALLY)) //if manually do what we store
       {
         digitalWrite(2,V(L1));
       }
     else
      {// else look if the actual read is bigger or smaller than the thresholds 
         if ( V(LIGHT_READ)>V(LIGHT_OFF) )
           {
            digitalWrite(2, LOW);
            writeV(L1,0);
         }  
         else
         {     if (V(LIGHT_READ)< V(LIGHT_ON))
                     {
                        digitalWrite(2, HIGH);
                        writeV(L1,1);
                      } 
                      else
                      {
                        digitalWrite(2, V(L1));
                      }
         }
       }
    }

    

Visual Basic

The Controller is set.It makes his job, and is ready to answer each question that we will made.

We must implement in our Master, the Master function Read and Write. I translate from C# to VB.NET register The Protocol from distantcity, http://www.codeproject.com/Articles/20929/Simple-Modbus-Protocol-in-C-NET?fid=473146&fr=26 .

I just made few changes:

  • Modbus inherits a SerialPort.
  • The CRC function, is the translation from the sketch of Arduino. Function that encode=Function that decode.
  • I catch the event DataReceived. Where I recompose the token and I send outside via new Reise of a event, MessageRecived(b() as byte).

IMPORTANT: you can catch the MessageRecived, but you have to Invoke another function.

So how it works:

                Private WithEvents com As New Modbus_S.Modbus 'It declares the variable com that is inherited from SerialPort  
                ...
                
                ...
                'For Open we need the port number, the baud rate, the number of bits, the parity space and the bit stop                        
                com.Connect(tscbCOMList.Text, com.BaudRate, 8, IO.Ports.Parity.Space, IO.Ports.StopBits.One)
                
    

IMPORTANT: The Parity byte is not right set. Theoretical should be even, but does not work. It works with Space.

Now is ready. to send our function 3 and function 16

    ... ' reads from slave 1 the registers from 0 to 13
        com.SendFc3(1, 0, 13)            
    ...
         'writes on slave 1 the register of the variable Light_on, the count of bytes and the values()
         com.SendFc16(1, map_Variables(variables.LIGHT_ON, 0), map_Variables(variables.LIGHT_ON, 1), {CUInt(nuOn.Value)})
    

Arduino will answer, com will rebuild the message and send a new event that, we can catch outside the class:

         Private Sub com_MessageReceive(b() As Byte) Handles com.MessageRecived
            Invoke(New MsgReceive(AddressOf ProcessMessage), b)
        End Sub
        ...
         Private Sub ProcessMessage(b() As Byte)
         'read the message b() and make stuff
         End Sub
        
    

And I think it is all

The Graphic

I did some conceptual corrections to the graphic. Also if seems unnatural, the right track is from right to left. Here is the explanation. I think that everyone that used a oscilloscope will not have any problem

The base class was taken from Carl Morey

http://www.codeproject.com/Articles/42557/Arduino-with-Visual-Basic

I did a lot of improvements. Now is fully re-sizable, and allow to draw more variables in the same graphic.

How use it

Declare else where a normal PictureBox control, then a variable such:

dim dImg as Display   
    dImg = New Display(Me.PictureBox1.Width, Me.PictureBox1.Height) 
            Me.PictureBox1.Image = dImg.GetImage 'dImg will create a Image and attach to it 

Each line, need is own buffer. Therefore we must pass these information with:

       dImg.AddVariable(variables.LIGHT_READ, Color.Lime , "Light Read")  

At this point the graphic is ready to accept values:

 dImg.NewPoint(variables.LIGHT_OFF, Now(), value) 'In order, the index,the time and the value
    'at end of the insertions remember to call again:
                    Me.PictureBox1.Image=dImg.GetImage
      

The Database

The database is FIREBIRD.

I think no database is more indicated for this purpose. Embedded(does not need to install nothing), free, extremely powerful, and what normally let say you: "uff", such the case sensitivity, when you work with numbers does not boring. So lets enjoy this incredible powerful db.

The GUI

The GUI is just a text box where write the queries for select, insert, update and delete.

As told you, the graphic, will read the datatable, (where the First column is the time,remember) and track the relative graphic.

The target of this program is qualified people, with programming skills. I think it is not a problem "speak" with the program via SQL. Better, it is incredible powerful. You can make each kind of setting.

Below I will show you some important queries, such the one in the picture, that allow you to re-build the tuple of a given moment. And determine the whole system in a certain moment.

The functions are:

1)Queries: Inserts in the textbox the SQL instruction :

  Select trim(name),qryString from queries 

this instruction calls the table queries where you can choose a saved command, make copy-paste ready to lunch.

2) Run: It executes the SQL instruction in the textbox

3) Quota Qry: it prepares the text in the box, ready to be inserted in the database with quotation marks and the clause "INSERT" . For example you write :

Select t , val as Pressure  from dates   

if you click Quota Qry in your box appear:

 insert into queries(name,qrystring)
    values('NAME HERE',
    'Select t , val as Pressure  from dates ;'); 

where you have to to put the "Name" where indicated, and then click Run.

4) Backup It make a backup of your database in a .sql file: nothing else than a text file, that contains the SQL instruction (such CREATE TABLE...bla bla) to rebuild your database. If you open this file, you may gotta scared, for all those strange instruction at the begin. You can look the explanation in my site. It makes the back up of whole database.

5)Export to Excel: Exactly, it export to excel. Buuuut just the query in the textbox (no need to run).

Getting start with SQL

If you wanna begin to make queries, you must know the structure.

The tables are just three and are extremely simple. Two are for the data logging and the third is where we can store the queries.

IMPORTANT :Firebird, has no autoincrement value. But works with triggers and generators. Here such info's about. The tables variables and queries, have the column i with a trigger for the autoicrement. The backup will include also these instructions.

 CREATE TABLE variables ( 
        i  INTEGER   NOT NULL, -- this is a value, where is associated a trigger 
        name  VARCHAR(255)  ,
        description BLOB SUB_TYPE 1 ,
        um  VARCHAR(5)  ,
        CONSTRAINT  PRIMARY KEY(i)
    );  

       

Variables contains the descriptive definition of the variables... such, the name and description. It has a trigger associated to i, that is also his primary key (if you set all right,i will be the same value that you send to Arduino, for require his register)

CREATE TABLE dates ( 
        var  INTEGER   NOT NULL,
        t  TIMESTAMP   NOT NULL,
        val  BIGINT  ,
        CONSTRAINT  PRIMARY KEY(var,t)
    ); 
    CREATE INDEX ixVal on table dates(val);  
    

Dates is the scope of the work, contains the tuples that come from Arduino. var is the foreign key that point to i of the table Variablesis timestamp. And val is the value.

Dates is fully indicized. The primary key is composed from the columns (vart) and there is also an addition index onval.

Dates can become huge. These index are important.

And a third table called queries store our SQL instructions:

CREATE TABLE queries (
    i  INTEGER   NOT NULL,
    name  VARCHAR(255)  ,
    qrystring BLOB SUB_TYPE 1 ,
    CONSTRAINT PRIMARY KEY(i)
    );   

Getting serious with SQL

It is important look at, the same time, all the graphics of all the variables to identify for example correlation between cause and effects or if your system start up, when happen something wrong. So we need the tuple of all variables at the same time. The number of variable could be huge, and not possible to determine in develop time. Instead to put more columns in the table I just put a column, but each tuple has the same insertion time. Virtually is like the system insert all the variables contemporaneous at the same millisecond. (VB make this. is commented in the source code)

The query to rebuild the tuple is the follow:

 
    SELECT a.T, a.VAL as Light_on,b.val as Light_off , c.val as Light_read,(500*d.val) as L1,e.val as pressure_on,
    f.val as pressure_off, g.val as pressure_setpoint
    FROM DATES a
    inner join DATES B on a.t=b.t
    inner join DATES C on a.t=c.t
    inner join DATES D on a.t=d.t
    inner join DATES E on a.t=e.t
    inner join DATES F on a.t=f.t
    inner join DATES G on a.t=g.t
    where  a.t between '23.03.2014 14:00:00' and '23.03.2014 18:00:00' 
    AND a.var = 1
    AND b.var = 2
    AND C.var = 3
    AND D.var = 4
    AND E.var =5
    AND F.var=6
    AND G.var=7  
    order by a.t; 

Pratically I put so much join of the table dates as the number of variables that I wanna display.

Tricks:

  • give an alias at the column, you will retrieve in the legend
  • to scale the graphic, multiply for a factor in the select (useful with the values 1/0)

The strictly "=" in the condition of the join, and the index allow us to have good answers time.

Same thing you can make with the block below:

--SET TERM !!; -- deactivate the comments with other GUI's Ex: Flamerobin
    EXECUTE BLOCK  --ix=    1         2         3       4        5         6      7  ==>11111110b =254d
    returns(  t timestamp,a bigint,b bigint,c bigint, d bigint,e bigint,f bigint,g bigint,n integer)
    AS  
         
        declare tmpt2 timestamp; 
        declare tmpVar integer;
        declare tmpVar2 integer;
        declare tmpVal bigint;
        declare tmpVal2 bigint;
        declare ctrlFlag integer;
        DECLARE cur cursor FOR  
             /*********************************************************/
            /***         MODIFY THE QUERY                           **/       
           /*********************************************************/
            (SELECT var,t, val from dates 
            where t> '22.03.2014 00:00:00' --between '23.03.2014 00:00:00' and '24.03.2014 15:00:00' 
            order by t,var ); --important, order first for time than for var
        BEGIN
        n=0;
        ctrlFlag=254;  
        OPEN cur; 
            fetch cur INTO tmpVar,t,tmpVal;
            IF (ROW_COUNT = 0) THEN exit; 
            while (1=1) do 
        BEGIN   
            ctrlFlag=bin_xor(ctrlFlag,bin_shl(1,tmpVar));
          
            fetch cur INTO tmpVar2,tmpt2,tmpVal2; 
              IF (ROW_COUNT = 0)   THEN leave;  
           
               IF (tmpVar=1) THEN a=tmpVal;
                IF (tmpVar=2) THEN b=tmpVal;
                IF (tmpVar=3) THEN c=tmpVal;
                IF (tmpVar=4) THEN d=tmpVal;
                IF (tmpVar=5) THEN e=tmpVal;
                IF (tmpVar=6) THEN f=tmpVal;
                IF (tmpVar=7) THEN g=tmpVal;
            if (tmpt2<>t ) then
            BEGIN
                
                if (ctrlFlag=0) then 
                begin
                    n=n+1;
                    suspend;
                end
                ctrlFlag=254;
            
            END
            tmpvar=tmpVar2;
             t=tmpt2;
            tmpVal=tmpVal2;

            END 
                if (ctrlFlag=0) then 
                begin
                    n=n+1;
                    suspend;
                end
    close cur; 
    END
    --SET TERM; !! -- deactivate the comments with GUI Ex: Flamerobin  

When I spoke about power of firebird, this is an example. Firebird supports a complete and powerful procedural language. with which you can do a lot of things. This is a EXECUTE BLOCK. It allow to execute a block of instructions without save any procedure. MySQL does not support it. This block results a bit, more fast than the SELECT : 0.160 ms the SELECT, 0.120, the BLOCK, in a query of 1700 elements for column (so around 11900 elements).

Maybe I have to spend few words about the ctrlFlag.

Work with tuple, imposes to check if all the variables have been set, or saved, or if are already saved. Each variable has an index. If a put a flag 1 at the place of required variable I can properly activate or deactivate his flag. For example:

  SYSTEM LIGHT_ON LIGHT_OFF LIGHT_READ L1 PRESSURE_ON P_OFF P_SETPOINT index 0 1 2 3 4 5 6 7   0 1 1 1 1 1 1 1

I did not require system, therefore is 0. I require all the others that are 1.

It come out the value 11111110b=254

To put the value of an index down, I perform the ctrlFlag=ctrlFlag XOR (1<<index). This instruction will shut down just his own bit.

At end of all. If all values are set to zero, ctrlFlag will be equal to 0.

Using the Program

It is specific for an application, the scope was understand how can I control a system, it is not optimized for speed or for settings.

Exactly for the same purpose, if you want learn to control a system these are the steps:

  • Connect (will connect you, but not start to messaging)
  • Begin Msg, will start to message (if does not work, click more times)
It will send the command to the controller and ask to send reports.

Before shut down:

  • Stop Msg, it will stop the controller to send messages
  • Now you can safely Disconnect (such the USB Smile | :) )

The main trouble is the right synchronization at the begin of the conversation between controller and Pc. This give some trouble that I have to solve.

The Schema