Tested on:
- win10 64bit, e!Cockpit 1.4.0.29, SQL Server Express 2012, SQL Management Studio 17.4 (SSMS)
- win10 64bit, e!Cockpit 1.4.0.29, SQL Server Express 2017, SQL Management Studio 17.4
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:
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.
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.
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.
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.
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.