Application Note
WAGO-I/O-SYSTEM
Using library
“WagoLibMSSQL_03.lib”
in CoDeSys 2.3 for interface to
Microsoft SQL Server 200x
2 Impressum
© 2012 by WAGO Kontakttechnik GmbH & Co. KG All rights reserved.
WAGO Kontakttechnik GmbH & Co. KG
Hansastraße 27 D-32423 Minden Phone: +49 (0) 571/8 87 – 0 Fax: +49 (0) 571/8 87 – 1 69 E-Mail: [email protected] Web: http://www.wago.com Technical Support Phone: +49 (0) 571/8 87 – 7 77 Fax: +49 (0) 571/8 87 – 87 77 E-Mail: [email protected]
Every conceivable measure has been taken to ensure the accuracy and completeness of this documentation. However, as errors can never be fully excluded, we always appreciate any information or suggestions for improving the documentation.
We wish to point out that the software and hardware terms as well as the trademarks of companies used and/or mentioned in the present manual are generally protected by trademark or patent.
Table of Contents 3
Table of Contents
1 Important Notes ... 4 1.1 Legal Principles... 4 1.1.1 Subject to Changes ... 4 1.1.2 Copyright ... 4 1.1.3 Personnel Qualification ... 4 1.1.4 Intended Use ... 4 1.2 Scope of Validity... 5 1.3 Number Notation... 5 1.4 Font Conventions ... 5 2 Description... 6 2.1 Components... 6 3 Setup database... 7 3.1 Enable TCP/IP... 73.2 Enable “SQL Server Authentication” ... 9
3.3 Enable remote connection ... 12
3.4 Enable or create database account ‘sa’... 14
3.5 Create database ‘testdb’ ... 15
4 Important Notes
1
Important Notes
To ensure quick installation and start-up of the units, we strongly recommend that the following information and explanations are carefully read and adhered to.
1.1
Legal Principles
1.1.1
Subject to Changes
WAGO Kontakttechnik GmbH & Co. KG reserves the right to provide for any alterations or modifications that serve to increase the efficiency of technical progress. WAGO Kontakttechnik GmbH & Co. KG owns all rights arising from the granting of patents or from the legal protection of utility patents. Third-party products are always mentioned without any reference to patent rights. Thus, the existence of such rights cannot be excluded.
1.1.2
Copyright
This Manual, including all figures and illustrations, is copyright-protected. Any further use of this Manual by third parties that violate pertinent copyright
provisions is prohibited. Reproduction, translation, electronic and phototechnical filing/archiving (e.g., photocopying) as well as any amendments require the written consent of WAGO Kontakttechnik GmbH & Co. KG, Minden, Germany. Non-observance will involve the right to assert damage claims.
1.1.3
Personnel Qualification
The use of the product detailed in this document is exclusively geared to specialists having qualifications in PLC programming, electrical specialists or persons instructed by electrical specialists who are also familiar with the valid standards. WAGO Kontakttechnik GmbH & Co. KG declines any liability resulting from improper action and damage to WAGO products and third party products due to non-observance of the information contained in this document.
1.1.4
Intended Use
For each individual application, the components are supplied from the factory with a dedicated hardware and software configuration. Modifications are only admitted within the framework of the possibilities documented in this document. All other changes to the hardware and/or software and the non-conforming use of the components entail the exclusion of liability on part of WAGO Kontakttechnik GmbH & Co. KG.
Please direct any requirements pertaining to a modified and/or new hardware or software configuration directly to WAGO Kontakttechnik GmbH & Co. KG.
Important Notes 5
1.2
Scope of Validity
This application note is based on the stated hardware and software of the specific manufacturer as well as the associated documentation. This application note is therefore only valid for the described installation.
New hardware and software versions may need to be handled differently. Please note the detailed description in the specific manuals.
1.3
Number Notation
Table 1: Number Notation
Number code Example Note
Decimal 100 Normal notation Hexadecimal 0x64 C notation Binary '100'
'0110.0100'
In quotation marks, nibble separated with dots (.)
1.4
Font Conventions
Table 2: Font Conventions
Font type Indicates
italic Names of paths and data files are marked in italic-type. e.g.: C:\Programme\WAGO-I/O-CHECK
Menu Menu items are marked in bold letters. e.g.: Save
> A greater-than sign between two names means the selection of a menu item from a menu.
e.g.: File > New
Input Designation of input or optional fields are marked in bold letters, e.g.: Start of measurement range
“Value” Input or selective values are marked in inverted commas.
e.g.: Enter the value “4 mA” under Start of measurement range.
[Button] Pushbuttons in dialog boxes are marked with bold letters in square brackets.
e.g.: [Input]
[Key] Keys are marked with bold letters in square brackets. e.g.: [F5]
6 Description
2
Description
Library “WagoLibMSSQL_03.lib” provide function blocks for accessing Microsoft databases for executing SQL-Statements.
The “WagoLibMSSQL_03.lib” library will work with any WAGO Ethernet controller and is based on the SysLibSockets.lib system library. It is expected that the person using this library has a solid base knowledge of CoDeSys and the IEC 61131-3 standard.
The configuration of Microsoft SQL Server takes a little time, for this application we used ‘Microsoft SQL Server Management Studio Express’ with SQL Server Configuration Manager – both freely available from the Microsoft website. This application note covers those steps as well as some of the more common errors that could occur from an incorrect set-up as well as an example of how to use the library in an application.
2.1
Components
Table 3: Components
Supplier Pieces Name Item No.
WAGO 1 Programmable Fieldbus Controller 750-8xx WAGO 1 End Module 750-600
WAGO 1 WAGO-I/O-PRO CAA 759-333 Microsoft 1 SQL Server 2005(EXPRESS)
Microsoft 1 SQL Server 2008(EXPRESS)
Microsoft 1 SQL Server Management Studio Express’ Microsoft 1 SQL Server Configuration Manager
Trademarks: All terms mentioned in this document that are known to be
trademarks or service marks have been appropriately capitalized.
Use of a term in this document should not be regarded as affecting the validity of any trademark or service mark. Microsoft is a registered trademark of Microsoft Corporation. SQL Server is a trademark of Microsoft Corporation.
Setup database 7
3
Setup database
Follow the online guide on Microsoft’s website to download and install ‘Microsoft SQL Server Management Studio Express Edition’ and all other relevant software (Microsoft Core XML Services (MSXML) 6.0 and Microsoft .NET Framework 2.0.). These are available on the Microsoft web site.
3.1
Enable TCP/IP
After installation TCP/IP driver is disabled by default and have to be enabled manually.
Enable TCP/IP access to database by selecting
Start>Programs>Microsoft SQL Server 2005> Configuration Tools> SQL Server Configuration Manager.
In the configuration manager expand SQL Server 2005 Network Configuration on the left of the window and then select Protocols for SQLEXPRESS
Check minimum system specification on Microsoft’s website for the SQL Server package. This has been tested with SQL Express 2005 and 2008. For more information on SQL Server components beyond this application note, please consult the Microsoft web site.
8 Setup database
If TCP/IP isn’t enabled – right click on it and select Enable
Double click on TCP/IP and expand register card “IPAll”
Edit the TCP Port to the TDS default port 1433.
The port number is also used as input parameter of the MSSQL_Login function block and can be changed to meet your IT requirements.
Setup database 9
3.2
Enable “SQL Server Authentication”
SQL Server provides two different authentication methods: - “Windows-Authentication” (Enabled by default)- “SQL Server Authentication”(Disabled by default).
Libraries function block “MSSQL_Login” utilizes “SQL Server Authentication”, where database user and password are input parameters who must match with the one stored inside the database self.
Open Microsoft SQL Server Management Studio Express.
Select Database Engine, and Windows Authentication as shown in the screenshot below.
For Server name: select <Browse for more…>.
In the new window, select the Network Servers tab and wait a few seconds while it retrieves data.
Eventually you will see the Database Engine displayed only, expand this and select the SQLEXPRESS Server.
10 Setup database
Click OK to connect to the Server.
Now in the object explorer section, right click on the server and select Properties from the drop down menu.
Choose the Security page and in the Server authentication section select SQL
Server and Authentication mode
Setup database 11
You can do this in the object explorer section, right click on the server as before but this time select Restart from the menu.
12 Setup database
3.3
Enable remote connection
Open the SQL Server Service Area Configuration.
The following dialog should be displayed
Select Surface Area Configuration for Services and Connections
Select Remote Connections and then on the right choose 'Local and remote
Setup database 13
The final bit to check here is in the SQL Server Browser.
14 Setup database
3.4
Enable or create database account ‘sa’
In Microsoft SQL Server Management Studio Express, go to the object explorer and expand the Security folder then the Logins folder.
If ‘sa’ doesn’t exist here, right click on the Logins folder and select New Login… If it already exists then double click on it.
Create the login name sa if it isn’t already there.
Set the password to wago00 (2 zeros) which is the password used in the CoDeSys example project.
You are free to create other user with more or less access rights, just update login creditals in example project.
Setup database 15
3.5
Create database ‘testdb’
In Microsoft SQL Server Management Studio Express, go to the object explorer and right click the Databases folder.
Select New Database and enter “testdb” as the Database name.
16 Setup database
In here right click on the Tables folder and choose New Table… Following steps 1 to 3 in the screenshot below
After saving this new table the SQL set-up should now be complete.
Alternative you can execute attached SQL-Script “Create_aTable.sql” in
Microsoft SQL Server Management Studio Express, to create the table “aTable in one step.
“Menu-> File -> Open -> File ...” or press [Crtl + O] will show File open dialog. Browse to location where “Create_aTable.sql” stored and open it.
Run SQL-CREATE-Statement with “Menu -> Query -> Execute”.
Microsoft SQL Server Management Studio also useful to take a look into current content of a table, just a right click on the table of interest open the background menu. Select “Open Table” will show table data with the opportunity to modify it. You can also use function block “MSSQL_Execute” to create databases and tables from your CoDeSys application, because SQL-CREATE-Statements are nothing else then SQL-Statements who do not return result sets, like INSERT, UPDATE, DELETE, DROP, ALTER and so on.
WagoLibMSSQL03_Example01.pro 17
4
WagoLibMSSQL03_Example01.pro
Copy library file “WagoLibMSSQL_03.lib” into folder ‘CoDeSys V2.3\Targets\WAGO\Libraries\Application\’ first.
The start of this program demonstrates how to connect to a database and then add values to a table named ‘aTable’ in the ‘testdb’ database.
Below is an example of what the MSSQL_Login function block should look like in your application.
Function block MSSQL_Login establish the connection to your server. Be sure to match the parameters here to those on your server.
To run this change the “xConnect” variable to TRUE.
Verify function blocks output params “diError“, “sStatus” and “xConnected” that the connection is established.
Don’t forget to Logout after work is done, otherwise you will waste performance and memory on server machine. Also you can not be shure that a TCP/IP
connection is working for days and weeks.
18 WagoLibMSSQL03_Example01.pro
The next thing we will do is inserting some data into the database. There for we need a SQL INSERT statement.
What we will do is hard code a few values, these will likely be variables in your application. GETDATE() calls a database function who returns servers local time. Its recommended to build and test your SQL-Statement in the SQL Management Studio prior to coding it to ensure proper syntax.
INSERT INTO aTable
(aLongDT, aBool, aDint, aReal, aNVarChar) VALUES
(GETDATE(), 1, 12345, 1.73, 'Hello world')
Because the apostrophe [’] character in CoDeSys used as STRING-BEGIN and STRING-END identifier, each occurrence within the SQL-Statement have to be “escaped” by character [§].
So that final CoDeSys-SQL-Statement looks like below:
sSql := 'INSERT INTO aTable
(aLongDT, aBool, aDint, aReal, aNVarChar) VALUES
GETDATE(), 1, 12345, 1.73, $'Hello world$')’;
Take time to find the additional and the escaped apostrophe [’].
To run this, change the “xExec1” variable to TRUE. The “xExec1” variable will reset itself upon completion.
Again, check “diError” and “sStatus” to make sure the command executed. If you received no errors, this will have inserted a record into your database
WagoLibMSSQL03_Example01.pro 19
Next step is to retrieve data from the database.
First we need a SQL-SELECT-Statement, this example will get all data sets from table “aTable”.
SELECT * from aTable
Next, the asSqlStatement2 needs to be populated with our query.
asSqlStatement2[0] := ‘SELECT * from aTable’;
asSqlStatement2[1] := ‘’; (* END OF SQL-Statement*)
After the statement is populated,
Getting the results from the database from CoDeSys requires a few things more in your application.
In the example application we have created two user defined data types, one labelled “RowData” and the other “TableData”, where “RowData” descripes all columns of the table as in IEC-Datatypes.
“TableData” will be filled in the Convert2IEC action of PLC_PRG and hold the query data.
To run the query, set “xQuery2” variable to TRUE. The “xQuery2” variable will reset itself upon completion.
This will send the SQL command to the database and the status will come back at ‘Successful executed’ and list the number of rows that it returned.
Now the Convert2IEC action is run, and this will break apart the binary data from the query into meaningful records.
20 WagoLibMSSQL03_Example01.pro
It is important to understand that the WagoLibMSSQL_03 has no knowledge about the structure and dataypes of a query result set.
There are helper functions written to get the different data types from the MS format and convert them to IEC format (CoDeSys.)
So after some error checking the Convert2IEC action comes to converting field 1, and executes the MSSQL_GetDATETIME.
If the database you are connected to varies, the changes to this routine happen here where the data is broken up.
The routine can be followed with the comments to see where columns 2, 3, 4 and 5 are also converted to their respective data types.
Serie 21 Artikelnummer Artikelbezeichnung
WAGO Kontakttechnik GmbH & Co. KG Postfach 2880 D-32385 Minden Hansastraße 27 D-32423 Minden Phone: +49/5 71/8 87 – 0 Fax: +49/5 71/8 87 – 1 69 E-Mail: [email protected] Internet: http://www.wago.com