MySQL Library - CODESYS

[Pages:25]MySQL Library

MySQL Library for use with CoDeSys Control v3

Short Description: This Library allows your CODESYS v3.5 application, to connect with a MySQL Server database. It gives you the opportunity to read and store process data to an external or internal MySQL database, without any 3rd party software or driver. Whether you want to store data for long-term logging, exchange data with 3rd party applications or collecting mass of machine data for your next IoT project, it's all possible with our MySQL-Library for CODESYS.

Revision: 0.1 02.08.2016 0.2 06.08.2016 1.0 08.09.2016

Kevin Rohn Kevin Rohn Kevin Rohn

Created first revision with Quick-Start G. Added FB Desc. and example Desc, Release

Pf?nder GmbH Germany 74585 Hausen am Bach Bachstrasse 15

Internet: pfaender.de Phone: +49 7958 9800 0 E-Mail: support@pfaender.de

Table of Contents

Product Description ................................................................................................. 3 Functional Description ....................................................................................................... 3 Function Box "MySQL_Open"......................................................................................... 4 Function Box "MySQL_Close" ........................................................................................ 5 Function Box "MySQL_Exec" ......................................................................................... 6 Function Box "MySQL_Query"........................................................................................ 8 Function Box "MySQL_ConnectionString" .....................................................................10 Function "MySQL_GetStringValue" ...............................................................................11 Global Parameter List....................................................................................................12

Example applications ............................................................................................. 13 Example application with CFC and web visualisation........................................................13 Example application with Structured Text (ST)..................................................................16 Open the MySQL-Server connection .............................................................................16 Close the MySQL-Server Connection............................................................................17 Execute a MySQL command with no response result....................................................17 Execute a MySQL command with response result.........................................................20

MySQL-Library compatibility ................................................................................. 21

Quick Start - MySQL-Server installation............................................................... 21 Install MySQL-Server on Raspberry Pi .............................................................................22 Step 1: Preparation for the MySQL-Server installation ..................................................22 Step 2: Install MySQL-Server package..........................................................................22 Step 3: MySQL-Server configuration wizard ..................................................................22 Install phpMyAdmin on Raspberry Pi ................................................................................23 Step 1: Preparation for the phpMyAdmin installation .....................................................23 Step 2: Install phpMyAdmin ...........................................................................................23 Step 3: phpMyAdmin configuration ................................................................................23 Step 4: Configure Apache to use phpMyAdmin .............................................................24 Step 5: Accessing phpMyAdmin ....................................................................................25 MySQL database SQL dump: ...........................................................................................25

Author Pf?nder GmbH

Topic MySQL Library for CoDeSys v3

Page 2/25

Product Description

This Library allows your CODESYS v3.5 application, to connect with a MySQL-Server database. It gives you the opportunity to read and store process data to an external or internal MySQL database, without any 3rd party software or driver. Whether you want to store data for long-term logging, exchange data with 3rd party applications or collecting mass of machine data for your next IoT project, it's all possible with our MySQL-Library for CODESYS.

You will be able to connect to a MySQL-Server, which is directly running on the same system, as your PLC (local) or you can also connect to a remote hosted MySQL-Server, anywhere in the world.

MySQL is a fast, easy-to-handle, and lightweight database system and compatible with most of the well-known operating systems.

Functional Description

With the Library you can perform most of the common MySQL Commands. For Example: INSERT, UPDATE, SELECT, DELETE, ALTER, DROP, etc.

The following topic will describe the different function boxes and functions, which you can use within your application.

All function boxes are built on best practice coding guidelines and are very easy to use. We've tried to design this library as simple as possible. For "worry free" troubleshooting we've defined meaningful error categories and output messages.

Author Pf?nder GmbH

Topic MySQL Library for CoDeSys v3

Page 3/25

Function Box "MySQL_Open"

Short Description: This function block opens the MySQL connection.

Detailed Description: The opening process starts on a raising edge of "xStart". The "xStart" var will be resetted at the end of the process. If an error occurs during the closing process, you will see an ERROR code and the current execute state, this helps you to identify the problem.

InOut:

Scope Name

Type

Initial Comment

sHost

STRING

MySQL-Server hostname or IP-Address

uiPort

UINT

3306 MySQL-Server port (Default: 3306)

Input sDatabase

STRING

MySQL database name

sUsername

STRING

MySQL database user

sPassword

STRING

MySQL user password

Inout

xStart

BOOL

MySQL_Connec- MySQL_ConnectionS-

tion

tring

Start execution on a rising edge

MySQL connection string which holds the server and login information

xConnected

BOOL

TRUE when connected

Output eError

ERROR

Error type

sExeute_State STRING(200)

Current execute state

Author Pf?nder GmbH

Topic MySQL Library for CoDeSys v3

Page 4/25

Function Box "MySQL_Close"

Short Description: This function block closes the MySQL connection.

Detailed Description: The closing process starts on a raising edge of "xStart". The "xStart" var will be resetted at the end of the process. If an error occurs during the closing process, you will see an ERROR code and the current execute state, which helps you to identify the problem.

InOut:

Scope Name

Type

Comment

Inout

xStart

MySQL_Connection

BOOL

Start execution on a rising edge

MySQL_ConnectionS- MySQL connection string which holds the server

tring

and login information

eError Output

sExeute_State

ERROR STRING(200)

Error type Current execute state

Author Pf?nder GmbH

Topic MySQL Library for CoDeSys v3

Page 5/25

Function Box "MySQL_Exec"

Short Description: This function block executes a MySQL command without no result like INSERT, UPDATE, DELETE, ALTER, DROP, etc.

Detailed Description: The MySQL commands needs to be built with an array. The length of the array is defined by the GLOBAL CONSTANTS. The FBox is built on the common behavior model "Etrig" so, you are able to handle and see the current process state within your application.

Info about MySQL_Command: Non-Numeric SQL parameter values must be marked with a quote (apostrophe = `). To use an apostrophe inside a CODESYS String you need do declare it with a dollar char + apostrophe' ($) or with $27.

e.g.: MySQL_Command[0] := `SELECT * FROM table WHERE name =' MySQL_Command[1] := `$27nonNumberValue$27 `;

Caution: Maximum length for identifiers are 250 bytes.

InOut:

Scope Name

Type

Input xExecute

BOOL

Output xDone xBusy

BOOL BOOL

Comment

Inherited from

Rising edge: Action start

ETrig

Falling edge: Resets outputs If a falling edge occurs before the function block has completed its action, the outputs operate in the usual manner and are only reset if either the action is completed or in the event of an error. In this case, the corresponding output values (xDone, xError, iError) are present at the outputs for exactly one cycle.

Action successfully completed

ETrig

Function block active

ETrig

Author Pf?nder GmbH

Topic MySQL Library for CoDeSys v3

Page 6/25

Scope Name

Type

Comment

Inherited from

xError

BOOL

TRUE: error occurred, function block aborts action

FALSE: no error

ETrig

Inout MySQL_Con- MySQL_ConnectionString MySQL connection string which holds

nection

the server and login information

Input

MySQL_Com- ARRAY [0..

MySQL command

mand

gc_MySQL_iStatementMax]

OF

STRING(gc_MySQL_iState-

mentLength)

Output eError

ERROR

Error type

sExecute_State

STRING(200)

Current execute state

Author Pf?nder GmbH

Topic MySQL Library for CoDeSys v3

Page 7/25

Function Box "MySQL_Query"

Short Description: This function block executes a MySQL command with results like SELECT, etc.

Detailed Description: The MySQL commands needs to be built with an array. The length of the array is defined by the GLOBAL CONSTANTS. The FBox is built on the common behavior model "Etrig" so, you are able to handle and see the current process state within your application.

Info about MySQL_Command: Non-Numeric SQL parameter values must be marked with a quote (apostrophe = `). To use an apostrophe inside a CODESYS String you need do declare it with an dollar char + apostrophe' ($) or with $27.

e.g.: MySQL_Command[0] := `SELECT * FROM table WHERE name =' MySQL_Command[1] := `$27nonNumberValue$27 `;

Caution: Maximum length for identifiers are 250 bytes and for field data 500 bytes,

InOut:

Scope Name

Type

Input xExecute

BOOL

xDone Output xBusy

xError

BOOL BOOL BOOL

Comment

Inherited from

Rising edge: Action start

Falling edge: Resets outputs If a falling edge

occurs before the function block has completed

its action, the outputs operate in the usual man-

ner and are only reset if either the action is

ETrig

completed or in the event of an error. In this

case, the corresponding output values (xDone,

xError, iError) are present at the outputs for ex-

actly one cycle.

Action successfully completed

ETrig

Function block active

ETrig

TRUE: error occurred, function block aborts action

ETrig

Author Pf?nder GmbH

Topic MySQL Library for CoDeSys v3

Page 8/25

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download