Sending data to a SQL database

Details

Wago 750-841 is a network controller - it has the word 'Ethernet' in its name.  Being connected to the network it offers many powerful functions.  Below I describe how the data can be sent from the PLC to a SQL database.

All who are interested in the subject should start from watching the movie, which in details describes all the needed steps. Additionally under the following links you will find the WAgoLibMySql library description and an application note.

The first thing to do is to prepare the database.  On my web-server I have created a database named 'WagoDB' and assigned an user 'wago'.  In the database I have created a table named 'Temperatures' and added 4 columns: 'Timestamp', 'Temp1'...'Temp4'. 

Additionally in the 'Manager of the remote SQL access' I allowed access to the database from all the external hosts.  I will not describe the details of this process - please take a look at the movie, application notes and let google lead you...

I wanted my PLC to send to the SQL database data from my 4 temperature sensors every 20 minutes.  Additionally in case of communication failure (my rounter is off during the night) the data should be stored and sent to the database at the first communication success. These are the steps, which were necessary:

1. Adding libraries WagoLibMySQL_03.lib and WagoLibSHA1.lib (they can be downloaded from www.wago.com)
2. Creating a new Program (right-click on the POUs tree, Add Object), naming it SQL_DataStorage and choosing ST language.
3. Adding a new task in the Task Configuration.  In my program I called the task SQLConnector, I set the execution interval to 50ms (T#50ms) and added a Program Call: SQL_DataStorage().  Finally the program SQL_DataStorage is executed every half a second.
4. Adding two Global Variables, which allow to extend the size of the array holding the SQL commands: Resources, Global Variables:

VAR_GLOBAL CONSTANT
	gcMySql_iSqlUpperBound :INT:=59;
	gcMySql_iSqlLength :INT:=150;
END_VAR

In SQL_DataStorage() in the variable definition:

VAR
	SQLWrite_Interval :TP:=(IN:=TRUE, PT:=T#20m);
	SQLWrite_StartSignal :R_TRIG;
	
	Command :STRING(150);
	TemperatureCommand :ARRAY [0..59] OF STRING(150);
	
	ManualLogin, ManualLogout, ManualExecute, ManualStorageClean :BOOL;
	
	TemperatureLogin :BOOL;
	TemperatureSQLLogin :MySql_Login;

	Host: STRING:='xxxxxxxxx';
	User: STRING:='xxxxxxxxx';
	Password: STRING:='xxxxxxxxxxx';
	Database:STRING:='xxxxxxxxxxxx';

	TemperatureConnection :MySql_Context;

	TemperatureLoginEnds :R_TRIG;
	TemperatureExecute :BOOL;
	TemperatureSQLQuery : MySql_Execute;

	TemperatureExecuteEnds :R_TRIG;

	TemperatureCommandCounter :BYTE:=0; 
	i :BYTE;
	TemperatureLogout :BOOL;
	TemperatureSQLLogout:MySql_Logout;
END_VAR

Where:

• SQLWrite_Interval - measures the time between write commands (20 minutes),
• SQLWrite_StartSignal - which for 1 program cycle signals the beginning of the write signal,
• Command - used to construct the SQL command,
• TemperatureCommand - the array holding the SQL commands (its size is identical to that defined in Global Variables),
• ManualLogin, ManualLogout, ManualExecute, ManualStorageClean - variables used to manually launch individual phases of communication - used for tests,
• TemperatureLogin - holds the signal of Login start,
• TemperatureSQLLogin - function block performing the login,
• Host, User, Password, Database - are the variables holding login data.  Defining them like that allows the on-line modification via a visualization,
• TemperatureConnection - is the context of loging in, sending data and loging out,
• TemperatureLoginEnds - trigger signaling a successful login,
• TemperatureExecute - holds the signal of Communication start,
• TemperatureSQLQuery - is a function block, which sends queries to the SQL database,
• TemperatureExecuteEnds -trigger signaling the end of communication
• TemperatureCommandCounter - holds the information about the number of SQL commands stored in TemperatureCommand array,
• i - technical variable
• TemperatureLogout - holds the signal of Logout start,
• TemperatureSQLLogout - is a function block, which logs out from the SQL database.

In the program code:

SQLWrite_StartSignal(CLK:=NOT SQLWrite_Interval.Q);

SQLWrite_Interval(IN:=SQLWrite_StartSignal.Q);

(* Prepare SQL command *)
IF (SQLWrite_StartSignal.Q OR ManualLogin) THEN
	Command:='INSERT INTO Temperatures (Timestamp, Temp1, Temp2, Temp3, Temp4) VALUES ("';
	Command:=CONCAT(Command,MID(DT_TO_STRING(CURRENT_TIME),10,4));
	Command:=CONCAT(Command,' ');
	Command:=CONCAT(Command,RIGHT(DT_TO_STRING(CURRENT_TIME),8));
	Command:=CONCAT(Command,'",');
	Command:=CONCAT(Command,REAL_TO_STRING(SensorReader.TEMPERATURE_1));
	Command:=CONCAT(Command,',');
	Command:=CONCAT(Command,REAL_TO_STRING(SensorReader.TEMPERATURE_5));
	Command:=CONCAT(Command,',');
	Command:=CONCAT(Command,REAL_TO_STRING(SensorReader.TEMPERATURE_6));
	Command:=CONCAT(Command,',');
	Command:=CONCAT(Command,REAL_TO_STRING(SensorReader.TEMPERATURE_8));
	Command:=CONCAT(Command,');');
	TemperatureCommand[TemperatureCommandCounter]:=Command;
	TemperatureCommandCounter:=TemperatureCommandCounter+1;

(* If the table is full, set index to 0, start writing from the beginning *)
	IF TemperatureCommandCounter=60 THEN
		TemperatureCommandCounter:=0;
	END_IF;
END_IF;

(* Start the communication with SQL database *)
TemperatureLogin:=SQLWrite_StartSignal.Q OR ManualLogin;
TemperatureSQLLogin(sHost:=Host, sUsername:=User, sPassword:=Password, sDatabase:=Database,oMySql:=TemperatureConnection, xStart:=TemperatureLogin);
TemperatureLoginEnds(CLK:=TemperatureSQLLogin.xConnected);

TemperatureExecute:=TemperatureLoginEnds.Q OR ManualExecute;
TemperatureSQLQuery(xStart:=TemperatureExecute, oMySql:=TemperatureConnection, asSqlCommand:=TemperatureCommand);
TemperatureExecuteEnds(CLK:=(TemperatureSQLQuery.wState=0));

(* If writing to SQL successful, clean TemperatureCommand table*)
IF (TemperatureExecuteEnds.Q AND (TemperatureSQLQuery.diError=16#00000000)) OR ManualStorageClean THEN
	TemperatureCommandCounter:=0;
	FOR i:=0 TO 59 BY 1 DO
		TemperatureCommand[i]:='';
	END_FOR;
END_IF;

(* Logout from the database *)
TemperatureLogout:=(TemperatureExecuteEnds.Q OR ManualLogout);
TemperatureSQLLogout(xStart:= TemperatureLogout, oMySql:=TemperatureConnection);

In order to observe how the communication develops I propose to build a simple visualization, which will include buttons changing the values of ManualLogin, ManualLogout, ManualExecute, ManualStorageClean and will watch throught textfields the output variables sStatus of TemperatureSQLLogin, TemperatureSQLQuery, TemperatureSQLLogout function blocks.

Why all the hustle?  For example to see and analyze the development of the temperatures in a given time period.  An example of a chart (prepared in html with the use of Highcharts Tools) looks as follows:

An description of how such charts can be build can be found at the article: Charting the data - Highcharts

• Prev
• Next