Connect PLC to MS SQL from e!Cockpit

Tested on:

All links open in new tab. Use them in case you don’t have the necessary software installed.

Compared to Visu+, connecting to MS SQL Server from e!Cockpit looks much complicated. The following example uses Structured Text (ST) but once you understand how it works you can create it as Ladder Logic Diagram (LD), Continuous Function Chart (CFC) etc.

Jump to

Prerequisites

Check the SQL Server and firewall settings as instructed here.

The program

Insert the WagoAppSQL_MsSQL library

Right-click on Program Library and add a Library manager. Click on Add New and type WagoAppSQL or just mssql.

Add the SQL functions to POU

  • FbMsSql_Login, FbMsSql_Logout that connects and disconnects the SQL server
  • FbMsSql_Execute used for execute queries like insert, delete, update, and FbMsSql_Select used for reading data. See code for all:
Click to expand
 
  1. mssqlLogin(      
  2.        sHost:= sINHost,
  3.        uiPort:= uiINPort,
  4.        sUsername:= sINUsername,
  5.        sPassword:= sINPassword,
  6.        sAppName:= sINAppName,
  7.        sLibName:= sINLibName,
  8.        sDatabase:= sINDatabase,
  9.        xTrigger:= xINTrigger,
  10.        xBusy=> xINBusy,
  11.        xError=> xINError,
  12.        oStatus=> oINStatus,
  13.        sStatus=> sINStatus,
  14.        xConnected=> xINConnected);
  15.        
  16. mssqlSelect(      
  17.        aSqlCommand:= aSELSqlCommand,
  18.        xTrigger:= xSELTrigger,
  19.        typResultSet:= typSELResultSet,
  20.        xBusy=> xSELBusy,
  21.        xError=> xSELError,
  22.        oStatus=> oSELStatus,
  23.        sStatus=> sSELStatus);
  24.        
  25. mssqlExecute(      
  26.        aSqlCommand:= aEXESqlCommand,
  27.        xTrigger:= xEXETrigger,
  28.        xBusy=> xEXEBusy,
  29.        xError=> xEXEError,
  30.        oStatus=> oEXEStatus,
  31.        sStatus=> sEXEStatus);
  32.        
  33. mssqlLogout(      
  34.        xTrigger:= xOUTTrigger,
  35.        xBusy=> xOUTBusy,
  36.        xError=> xOUTError,
  37.        oStatus=> oOUTStatus,
  38.        sStatus=> sOUTStatus);

Create variables for all In/Out/InOut

Assign them to the function arguments. See below code for all. Change the IP with your database IP, port, SQL username and password.

Click to expand
 
  1. VAR
  2.       // needed library: WagoAppSQL_MsSQL
  3.       // function block variables                  
  4.       mssqlLogin            :      WagoAppSQL_MsSQL.FbMsSql_Login;
  5.       mssqlSelect            :      WagoAppSQL_MsSQL.FbMsSql_Query;
  6.       mssqlExecute      :      WagoAppSQL_MsSQL.FbMsSql_Execute;
  7.       mssqlLogout            :      WagoAppSQL_MsSQL.FbMsSql_Logout;
  9.       // login variables                                          
  10.       sINHost                  :      STRING      :=      '127.0.0.1';
  11.       uiINPort            :      UINT      :=      1433;
  12.       sINUsername            :      STRING      :=      'myUser';
  13.       sINPassword            :      STRING      :=      'myPass';
  14.       sINAppName            :      STRING      :=      '';
  15.       sINLibName            :      STRING      :=      '';
  16.       sINDatabase            :      STRING      :=      'myDatabase';
  17.       xINTrigger            :      BOOL;
  18.       xINBusy                  :      BOOL;
  19.       xINError            :      BOOL;
  20.       oINStatus            :      WagoSysErrorBase.FbResult;
  21.       sINStatus            :      STRING(200);
  22.       xINConnected      :      BOOL;
  23.                                                 
  24.       // select variables                                          
  25.       aSELSqlCommand      :      ARRAY [0..MSSQL_SQL_UPPER_BOUND] OF STRING(MSSQL_SQL_LENGTH);
  26.       xSELTrigger            :      BOOL;
  27.       typSELResultSet      :      WagoAppSQL_MsSQL.typMsSql_ResultSet;
  28.       xSELBusy            :      BOOL;
  29.       xSELError            :      BOOL;
  30.       oSELStatus            :      WagoSysErrorBase.FbResult;
  31.       sSELStatus            :      STRING(200);
  32.                                                 
  33.       // execute variables                                          
  34.       aEXESqlCommand      :      ARRAY [0..MSSQL_SQL_UPPER_BOUND] OF STRING(MSSQL_SQL_LENGTH);
  35.       xEXETrigger            :      BOOL;
  36.       xEXEBusy            :      BOOL;
  37.       xEXEError            :      BOOL;
  38.       oEXEStatus            :      WagoSysErrorBase.FbResult;
  39.       sEXEStatus            :      STRING(200);
  40.                                                 
  41.       // logout variables                                          
  42.       xOUTTrigger            :      BOOL;
  43.       xOUTBusy            :      BOOL;
  44.       xOUTError            :      BOOL;
  45.       oOUTStatus            :      WagoSysErrorBase.FbResult;
  46.       sOUTStatus            :      STRING(200);
  48. END_VAR

Use the FbMsSql_Execute function

See below an example: building a SQL string to insert a log line. Note that everything is converted to string and strings inside are quoted too, in this case $ or 27 needs to be used before apostrophe as escape character. Last command will trigger the writing in the database.

From here, gvl represents the Global Variable List.

Click to expand
 
  1. aEXESqlCommand[0]:='INSERT INTO mytable (str_value,int_value,bool_value,timestamp) VALUES ($'';
  2. aEXESqlCommand[1]:=gvl.sAstr_value;
  3. aEXESqlCommand[2]:='$',';
  4. aEXESqlCommand[3]:=TO_STRING(gvl.iAint_value);
  5. aEXESqlCommand[4]:=',';
  6. aEXESqlCommand[5]:=TO_STRING(TO_BIT(gvl.xAbool_value));
  7. aEXESqlCommand[6]:=',$'';      
  8. aEXESqlCommand[7]:=gvl.sAtimestamp;
  9. aEXESqlCommand[8]:='$');';
  10. aEXESqlCommand[9]:='';
  12. xEXETrigger:=TRUE;

Use the FbMsSql_Select function

This function brings either no value, one or an array of raw data. It needs then to be transformed into known data types.

Click to expand
 
  1. aSELSqlCommand[0]:='SELECT str_value,int_value,real_value,bool_value,timestamp FROM mytable ';
  2. aSELSqlCommand[1]:='WHERE bool_value=1 ORDER BY timestamp DESC;';
  3. aSELSqlCommand[2]:='';
  4. xSELTrigger:=TRUE;

Transform selected raw data into known data types

Depending on the type of data from the database, you need to apply the appropriate function in order to get the correct information. The case below transform the result from column 1 into STRING, column 2 into DINT etc.

Click to expand
 
  1. CASE GVL.iSelectedCol OF  
  2.       // string
  3.       1:       WagoAppSQL_MsSQL.FuMsSql_GetSTRING(iCol:=gvl.iSelectedCol , iRow:=gvl.iSelectedRow , sValue:=sValue , typResultSet:=typSELResultSet);
  4.             gvl.sSelectResult:=sValue;
  5.       // dint
  6.       2:       WagoAppSQL_MsSQL.FuMSSQL_GetDINT(iCol:=gvl.iSelectedCol , iRow:=gvl.iSelectedRow , diValue:=diValue , typResultSet:=typSELResultSet);
  7.             gvl.sSelectResult:=TO_STRING(diValue);
  8.       // real
  9.       3:       WagoAppSQL_MsSQL.FuMSSQL_GetREAL(iCol:=gvl.iSelectedCol , iRow:=gvl.iSelectedRow , rValue:=rValue , typResultSet:=typSELResultSet);
  10.             gvl.sSelectResult:=TO_STRING(rValue);
  11.       // bool
  12.       4:       WagoAppSQL_MsSQL.FuMSSQL_GetBOOL(iCol:=gvl.iSelectedCol , iRow:=gvl.iSelectedRow , xValue:=xValue , typResultSet:=typSELResultSet);
  13.             gvl.sSelectResult:=TO_STRING(xValue);
  14.       // datatime
  15.       5:       WagoAppSQL_MsSQL.FuMSSQL_GetDATETIME(iCol:=gvl.iSelectedCol , iRow:=gvl.iSelectedRow , dtValue:=dtValue , typResultSet:=typSELResultSet);
  16.             gvl.sSelectResult:=TO_STRING(dtValue);
  17.       ELSE
  18.             gvl.sSelectResult:='There is no such column, max is 5.';
  19. END_CASE

Example

You can download a fully functional test example from here (stored in gdrive).

To create the SQL database and table you can use the following script.

Click to expand
 
  1. USE [master]
  2. GO
  4. IF EXISTS(SELECT * FROM master.sys.DATABASES
  5.           WHERE name='myDatabase') DROP DATABASE myDatabase
  7. CREATE DATABASE myDatabase
  8. GO
  10. USE [myDatabase]
  11. GO
  13. IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.[myTable]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
  14.       BEGIN
  15.             DROP TABLE dbo.myTable
  16.       END
  17. GO
  19. SET ANSI_NULLS ON
  20. GO
  22. SET QUOTED_IDENTIFIER ON
  23. GO
  25. CREATE TABLE [dbo].[myTable](
  26.       [INDEX] [BIGINT] IDENTITY(1,1) NOT NULL,
  27.       [str_value] [VARCHAR](80) NULL,
  28.       [int_value] [INT] NULL,
  29.       [real_value] [REAL] NULL,
  30.       [bool_value] [bit] NULL,
  31.       [TIMESTAMP] [datetime] NULL
  32. ) ON [PRIMARY]
  33. GO
  35. -- add some data
  37. INSERT INTO dbo.myTable (str_value,int_value,real_value,bool_value,TIMESTAMP) VALUES('SHENMT SHENMT SHENMT SHENMT SHENMT SHENMT SHENMT SHENMT SHENMT SHENMT SHENMT #$&',32767,65535.99,1,'2018/06/03 05:27:23')
  38. INSERT INTO dbo.myTable (str_value,int_value,real_value,bool_value,TIMESTAMP) VALUES('RGDGJT RGDGJT RGDGJT RGDGJT RGDGJT RGDGJT RGDGJT RGDGJT RGDGJT RGDGJT RGDGJT #$&',2462,-65535.99,0,'2018/06/03 04:29:15')
  39. INSERT INTO dbo.myTable (str_value,int_value,real_value,bool_value,TIMESTAMP) VALUES('PKJHGD PKJHGD PKJHGD PKJHGD PKJHGD PKJHGD PKJHGD PKJHGD PKJHGD PKJHGD PKJHGD #$&',28,5346.33,1,'2018/06/02 22:21:49')
  40. INSERT INTO dbo.myTable (str_value,int_value,real_value,bool_value,TIMESTAMP) VALUES('E39DJL E39DJL E39DJL E39DJL E39DJL E39DJL E39DJL E39DJL E39DJL E39DJL E39DJL #$&',4574,-32666.777,0,'2018/06/02 20:12:27')
  41. INSERT INTO dbo.myTable (str_value,int_value,real_value,bool_value,TIMESTAMP) VALUES('873902 873902 873902 873902 873902 873902 873902 873902 873902 873902 873902 #$&',658,654.5,1,'2018/06/03 11:03:44')
  42. INSERT INTO dbo.myTable (str_value,int_value,real_value,bool_value,TIMESTAMP) VALUES('01KS91 01KS91 01KS91 01KS91 01KS91 01KS91 01KS91 01KS91 01KS91 01KS91 01KS91 #$&',5,-1,0,'2018/06/03 01:06:21')
  43. INSERT INTO dbo.myTable (str_value,int_value,real_value,bool_value,TIMESTAMP) VALUES('KSI098 KSI098 KSI098 KSI098 KSI098 KSI098 KSI098 KSI098 KSI098 KSI098 KSI098 #$&',8,5455.444,1,'2018/06/03 10:52:56')
  44. INSERT INTO dbo.myTable (str_value,int_value,real_value,bool_value,TIMESTAMP) VALUES('NFH4J1 NFH4J1 NFH4J1 NFH4J1 NFH4J1 NFH4J1 NFH4J1 NFH4J1 NFH4J1 NFH4J1 NFH4J1 #$&',352,63637.55,0,'2018/06/03 10:18:42')
  45. GO

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.