SPADA-small SCADA for Arduino

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.

• 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  

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

The Database

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.

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.

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  

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)

Before shut down:

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

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