Using library

WagoLibMySQL
to communicate with
MySQL-Databases



Application note
A302000, English
Version 1.1.0
ii General
Application note
A302000
Copyright 2010 by WAGO Kontakttechnik GmbH & Co. KG
All rights reserved.


WAGO Kontakttechnik GmbH & Co. KG
Hansastrae 27
D-32423 Minden
Phone: +49 (0) 571/8 87 0
Fax: +49 (0) 571/8 87 1 69
E-Mail: info@wago.com
Web: http://www.wago.com
Technical Support
Phone: +49 (0) 571/8 87 5 55
Fax: +49 (0) 571/8 87 4 30
E-Mail: support@wago.com









Every conceivable measure has been taken to ensure the correctness and com-
pleteness of this documentation. However, as errors can never be fully ex-
cluded we would appreciate any information or ideas at any time.
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 gen-
erally trademark or patent protected.

Table of Contents iii
Application note
A302000

TABLE OF CONTENTS
1 Important comments .............................................................................. 4
1.1 Legal principles.............................................................................. 4
1.1.1 Copyright ...................................................................................... 4
1.1.2 Personnel qualification................................................................... 4
1.1.3 Intended use .................................................................................. 4
1.2 Range of validity............................................................................ 5
1.3 Symbols......................................................................................... 5
2 Description.............................................................................................. 6
3 Set up Environment ............................................................................... 7
3.1 Install XAMPP .............................................................................. 7
3.2 Create database testdb................................................................ 8
3.3 Create database account............................................................... 10
3.4 Restart database or reload privileges ............................................ 12
4 The library WagoLibMySql_02.lib.................................................. 13
5 Example_01........................................................................................... 14
6 Example_02........................................................................................... 15











4 Important comments
Application note
A302000
1 Important comments
To ensure fast installation and start-up of the units described in this manual, we
strongly recommend that the following information and explanation is carefully
read and adhered to.
1.1 Legal principles
1.1.1 Copyright
This manual is copyrighted, together with all figures and illustrations contained
therein. Any use of this manual which infringes the copyright provisions stipu-
lated herein, is not permitted. Reproduction, translation and electronic and
photo-technical archiving and amendments require the written consent of
WAGO Kontakttechnik GmbH & Co. KG. Non-observance will entail the right
of claims for damages.
1.1.2 Personnel qualification
The use of the product detailed in this manual is exclusively geared to special-
ists having qualifications in PLC programming, electrical specialists or persons
instructed by electrical specialists who are also familiar with the valid stan-
dards. WAGO Kontakttechnik GmbH & Co. KG declines all liability resulting
from improper action and damage to WAGO products and third party products
due to non-observance of the information contained in this manual.
1.1.3 Intended use
For each individual application, the components supplied are to work with a
dedicated hardware and software configuration. Modifications are only admit-
ted within the framework of the possibilities documented in the manuals. 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 Kontakttech-
nik 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 comments 5
Application note
A302000

1.2 Range of validity
This application note is based on the stated hardware and software of the spe-
cific manufacturer as well as the correspondent documentation. This applica-
tion 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 Symbols

Danger
Always observe this information to protect persons from injury.

Warning
Always observe this information to prevent damage to the device.

Attention
Marginal conditions must always be observed to ensure smooth operation.

ESD (Electrostatic Discharge)
Warning of damage to the components by electrostatic discharge. Observe the
precautionary measure for handling components at risk.

Note
Routines or advice for efficient use of the device and software optimisation.

More information
References to additional literature, manuals, data sheets and INTERNET
pages

6 Description
Application note
A302000
2 Description
This application note describes how to communicate with a MySQL database
directly, based on native MySQL protocol to transmit SQL-Statements.
SQL is the acronym for Structured-Query-Language(SQL) and defines stan-
dard command set to access or manipulate data like SELECT, INSERT,
UPDATE, DELETE inside a database.
Also defined are SQL commands to modify the structure of a database self, like
CREATE, DROP, ALTER, and more.
The focus of this application note points to the transfer of data between a
WAGO Ethernet-Controller and a MySQL database by SQL statements.


The easiest way to get running MySQL database is installing XAMPP, but any
other setup of MySQL above version 4.1 should work.
XAMPP is a free and open source cross-platform web server package, consist-
ing mainly of the Apache HTTP Server, MySQL database, and interpreters for
scripts written in the PHP and Perl programming languages.
This application note only utilize the MySQL database, webserver and scripting
engines are not used.
The examples of this application note requires a suitable database structure for
operating. Read next how to setup the needed environment.
Set up Environment 7
Application note
A302000

3 Set up Environment
This chapter guides you step-by-step to your own MySQL solution.
3.1 Install XAMPP
Download the XAMPP package for your operating system from
http://www.apachefriends.org/en/xampp.html and follow the installation in-
structions. This application note is created on a Microsoft operating system.
Please keep in mind that XAMPP provides an easy to install system for devel-
oping and testing of web solutions. Care should be taken when applying secu-
rity settings for your own application.
Test: After Apache starts, open the URL http://localhost or http://127.0.0.1
and examine all of the XAMPP examples and tools.

To check that all required services are running, go to the XAMPP menu and
press Status. You should see all needed services activated (PHP, MySQL).

The next step is creating the specific database for our examples.
8 Set up Environment
Application note
A302000
3.2 Create database testdb
Open the MySQL administration tool by typing URL http://localhost or
http://127.0.0.1 and click the PhpMyAdmin item in the XAMPP menu.

Click on Databases to open the following dialog.

Insert the name of your new database and press the Create button.
In this application note, we will create a database called testdb.
Set up Environment 9
Application note
A302000

An empty database has been created.
The next step is to create tables inside the database. Switch to the tab Im-
port, and browse to the files:
- /Example01/Database/CreateTableExample01.sql
- /Example02/Database/CreateTableExample02.sql..

Press the Go button to create tables.

Now you should see the two tables atable and data.
Next step is to create a database account for our WAGO-Ethernet-Controllers.
10 Set up Environment
Application note
A302000
3.3 Create database account
It is not a good idea to use the default admin-account inside the PLC-program.
Better is using an additional account with limited privileges.

Use link Rights to open the Current User view.

Use link Add user to to open the New User view.
Set up Environment 11
Application note
A302000

In this application note we expect a user with following settings.
Param Value: Description:
Username user. Name of database user account.
Password password Password of database user account
Access Any Host IP-Address,r hostname or placeholder Any_Host
Privileges SELECT,
INSERT,
UPDATE,
DELETE
Privileges define the the set of operations allowed for this
account.

Update the New User view.

Press the Go button to create the new database account.

Now the new database account is configured, but currently not active.
12 Set up Environment
Application note
A302000
3.4 Restart database or reload privileges
To activate the new database account you have to reload privileges.
This could be done by stopping and restart the whole database or use
phpMyAdmin to force reload of privileges.
To restart the whole database open the XAMPP Control Panel.

Press button Stop afterwards press Start.
Alternatively you can force reloading of privileges with link reload privileges
in phpAdmin.

The server is now configured and ready to use.

The library WagoLibMySql_03.lib 13
Application note
A302000

4 The library WagoLibMySql_03.lib
The library WagoLibMySql_03.lib provide function blocks to communicate
with MySQL-Database-Servers version 4.1 and above.
The principal item of this library are function blocks Execute and Query.
Function block MySql_Execute is designed for all SQL-Statements who do
not respond with a resultset, such as INSERT, UPDATE, DELETE,
CREATE,
ALTER, DROP and so, who just return OK or ERROR.
Function block MySql_Query is designed for SQL-SELECT-Statements who
respond with a resultset. The resultset are stored in an optimized format, use
function MySql_GetStringValue() to convert field data into any IEC-Type.
All non numeric SQL param values must be quoted with an apostroph().
Because apostroph() is also CoDesys-String-Start-End-Identifier.
To use a apostroph() inside a CoDeSys-String type $27 or $
Example:
(* Prepare SQL-Insert-Statement *)
asSqlStatement1[0] := 'INSERT INTO atable ';
asSqlStatement1[1] := '( aBool, aByte, aWord, aDint, aReal, ';
asSqlStatement1[2] := ' aString, aDT, aTime, aUdint ) ';
asSqlStatement1[3] := 'VALUES ';
asSqlStatement1[4] := '( TRUE, 17, 32333, 1111111, 1.42, ';
asSqlStatement1[5] := ' $'Hallo$', $'2010-08-13 11:56:42$', ';
asSqlStatement1[6] := ' $'13:12:57$', 62222 )';
asSqlStatement1[7] := ''; (* End of SQL-Statement *)
This library make extensive use of global variables to allow optimize memory
usage depending customer requirements.
You can modify the "length" and number of elements in "asSqlStatement" by
hiding libraries global constants with global constants of same name in your
project:
- gcMySql_iSqlUpperBound:
Defines the UpperBound of "asSqlStatement",to provide an SQL
statement as"ARRAY [0..UpperBound] OF STRING(Size)"
- gcMySql_iSqlLength:
Defines the Size in byte of an array element of "asSqlStatement",
to provide an SQL statement as "ARRAY [0..UpperBound] OF
STRING(Size)".
For details see library description ml0600e WagoLibMySql_02.pdf.
14 Example_01
Application note
A302000
5 Example_01
Open WagoLibMySql_Example01.pro in CodeSys 2.3.
You will find a PLC program written in FUP, who uses function blocks:
- MySql_Login to open a database connection.
- MySql_Execute for insert and update of data in table data.
- MySql_Query is used to read back the complied content of table data.
- MySql_GetStringValue() is used to convert QueryData into IEC-datatypes.
- MySql_Logout to close the database connection

Update inputs sHost, sUsername, sPassword depending your environment.
To let the example do something toggle input xStart by double click and
write it with [Ctrl] + [F7].
Information about current state of operation can be found at output sStatus.
Example_02 15
Application note
A302000

6 Example_02
Open WagoLibMySql_Example02.pro in CodeSys 2.3.
You will find a PLC program written in ST, who uses function blocks:
- MySql_Login to open a database connection.
- MySql_Execute for insert and update of data in table data.
- MySql_Query is used to read back the complied content of table data.
- MySql_GetStringValue() is used to convert QueryData into IEC-datatypes.
- MySql_Logout to close the database connection

Update inputs sHost, sUsername, sPassword depending your environment.
To let the example do something toggle input xDoIt by double click and
write it down, with [Ctrl] + [F7].
Information about current state of operation can be found at output sStatus.



WAGO Kontakttechnik GmbH & Co. KG
Postfach 2880 D-32385 Minden
Hansastrae 27 D-32423 Minden
Telefon: 05 71/8 87 0
Telefax: 05 71/8 87 1 69
E-Mail: info@wago.com

Internet: http://www.wago.com