Click here to Skip to main content
15,940,218 members
Articles / Programming Languages / T-SQL

SPADA-small SCADA for Arduino

Rate me:
Please Sign up or sign in to vote.
4.88/5 (10 votes)
24 Mar 2014CPOL9 min read 48.1K   3.3K   21   3
Supervisory Control and Data Acquisition for ARDUINO


With the intent to build a small plant, I had the necessity to control it. With the whole functions required from a basic SCADA. I think that, a lot of you, with research intent, school, hobby, need to supervise, control and of course acquire the data of a system.

Safety and functional needs require:

  • 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, because I want to empty it, and I can activate the pump also from PC)
  • If something is activated manually, show a warning
  • And important for the improvement saves in DB .

Image 1


To allow the communication between my PC and my controller, there is the need to map the variables.

With an array we assign automatically a number to each variable. But which variables? All those we are interested to set or also to read.

Therefore in the variable register takes place :

  • Threshold Limits and Set-points
  • Status (on/off)
  • Analog and digital reads
  • Each sensor


Now we organize the communication.

I need a protocol. Which defines the meaning of a 8 byte token. With this token Controller and Pc communicate.

This protocol is included in the source code in the class "Sprotocol".

Image 3


SCADA systems, and SPADA for sure, are massively influenced from the system that monitor. And a certain point you have to recognize the nature of your variable. But is possible define a template: this is a Template of code for Arduino.

 #include <EEPROM.h>
 //these are the index of the register (an array) of variables. For Example:
enum variables{ SYSTEM=0,
              LIGHT_ON=1, //threshold on
              LIGHT_OFF=2  //threshold off,
              LIGHT_READ=3, //lecture of the analog input, (also get in variable)

              L1=4,//status of the LED (my pump)
enum msg_Type // Commands, errors, reports, type etc etc
struct register_record
    long value;
    byte warnings;

   //to save some space, the warnings are stored in just one byte. the 8 bytes
   //act like 8 boolean values. So it is possible attach 8 warning with each variable
  //the type of warning depend of the physic natur of the variable 

  boolean GetWarning(byte i)
    return (warnings & (1 << i))>0; 
   void SetWarning(byte i)
    warnings= warnings ^ (1 << i) ;
}v[VARIABLES_COUNT ]; // declaration is here
 //Send the messages
 void SendMessage(byte a, byte *value ,byte msg_type)

In the loop function is possible recognize clearly the flow such in picture:

  • switch inputs from Pc , and store it in the register
  • Catch local inputs (local inputs, are build on loco, the code depend from your system, but the input must also be stored in a variable)
  • Read the sensors, levels, ecc. and store in a variable
  • NOW Process the variables, actuate the action, and store the result in a variable
  • Send Reports(all the values, reads, levels, inputs)
 //switch the incoming message(if the byte are 8, there is a whole token ready to read)
//For example a token  F0 01 03 00 00 00 02 F7
if (Serial.available()>7 )
    if ( //cnctBegin is my start token F0
    {;//this is the instruction (1=SET ecc);// this is the variable, the third byte I call it "i" because 
                        //than will be the index in this case (3=LIGHT_ON)   
            bi[0];//these byte are the value
            what =bToI(bi); //bToI convert 4 bytes in Long, another function iToB convert a Long in bytes, ready to sent. Same functions are in VB. In order to encrypt and decrypt whit the same algorithm    
... //your code whit read inputs,  

Visual Basic

Arduino will send me therefore, each "DELAY" millis(), the status of the register . The frequency of Arduino, is not the same of the saves in database. Who needs 10 saved values for second? In the program, there is a timer, now set to 1 second, that open the "gate" to the database.

PC catches these variables, and can understand what happen in the controller, and if he stores them in a buffer can draw the temporal graphic.

Image 4

How :
  • VB has available System.IO.Ports.SerialPort, I create a derivative class, Sprotocol.
  • SerialPort, raise an event when the message is incoming. I catch it, and I compose back my byte
  • I raise a new event that makes available the "readable message" outside the class
    Public Class SProtocol
    Inherits System.IO.Ports.SerialPort
    Private Sub SProtocol_DataReceived(value As Object, e As System.IO.Ports.SerialDataReceivedEventArgs) Handles Me.DataReceived
            'Inside this routine I rebuild the message and I will raise my own event 
        RaiseEvent MessageReceived(MainBuffer(1), MainBuffer(2), v) 
    End Sub             
End Class  

For example, in my Form: I will have;

If msg_type = SProtocol.msg_Type.ask_REPORT Then 
                Select Case var
                    Case variables.LIGHT_OFF
                        nuOff.Value = value ' this is my Form control
                        dImg.NewPoint(variables.LIGHT_OFF, Now(), value)' this is my Graphic 
                    Case variables.LIGHT_ON
                        nuOn.Value = value
                        dImg.NewPoint(variables.LIGHT_ON, Now(), value) 
end select
end if  

It is important to say: SerialPort works ThreadSafe, this implicate that the function that handles the event, can not "communicate" with the form class

It will done via the function Invoke that will send the event finally to my ProcessMessage function:

Private Sub com_MessageReceive(var As SProtocol.variables, type As SProtocol.msg_Type, value As Long) Handles com.MessageReceive

 Invoke(New MsgRecived(AddressOf ProcessMessage), var, type, value)

 End Sub

The Graphic

The base class was taken from Carl Morey

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

Instead of the points it can accept also a data table, incoming from a query for example:

img.setDataTable(dt)' where dt is a DataTable

It will recognize the columns with a numeric value and draw the graphic consequently.

IMPORTANT: the first column of the datatable must be a datetime value. And ever crescent. If two fields have the same value could be raised an exception.

then the order of the other columns is not important, it will draw a graphic for each numeric column.

The need to draw more variables and to draw datatables, comes late. Therefore the class is not optimized for this purpose. I just adapt the old class to work with more graphics, but there is margin to improve.

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 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.

Image 5

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)  ,

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)

    var  INTEGER   NOT NULL,
    val  BIGINT  ,
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 Variables. T is timestamp. And val is the value.

Dates is fully indicized. The primary key is composed from the columns (var, t) and there is also an addition index on val.

Dates can become huge. These index are important.

And a third table called queries store our SQL instructions:

CREATE TABLE queries (
name  VARCHAR(255)  ,
qrystring BLOB SUB_TYPE 1 ,

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
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.


  • 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)
    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
    OPEN cur; 
        fetch cur INTO tmpVar,t,tmpVal;
        IF (ROW_COUNT = 0) THEN exit; 
        while (1=1) do 
        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
            if (ctrlFlag=0) then 

            if (ctrlFlag=0) then 
close cur; 
--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:


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

Image 6


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Italy Italy
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

PraiseMy Answer Pin
Member 1491987221-Aug-20 6:42
Member 1491987221-Aug-20 6:42 
GeneralMy vote of 5 Pin
Marco Bertschi24-Mar-14 22:12
protectorMarco Bertschi24-Mar-14 22:12 
GeneralRe: My vote of 5 Pin
jurhas25-Mar-14 1:43
jurhas25-Mar-14 1:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.