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