Manual

TC3 Database Server

TwinCAT

Version: 1.4
Date: 2016-07-26
Order No.: TF6420
 Table of contents

Table of contents
1 Foreword .................................................................................................................................................... 5
1.1 Notes on the documentation.............................................................................................................  5
1.2 Safety instructions ............................................................................................................................  6

2 Overview..................................................................................................................................................... 7

3 Installation.................................................................................................................................................. 8
3.1 System requirements........................................................................................................................  8
3.2 Installation.........................................................................................................................................  8
3.3 Licensing.........................................................................................................................................  11
3.4 Installation Windows CE .................................................................................................................  15

4 Technical Introduction ............................................................................................................................ 18

4.1 Database Server functionality.........................................................................................................  18
4.2 Using the TwinCAT 3 Database Server in a network environment.................................................  19

5 Configuration ........................................................................................................................................... 21
5.1 TF6420 Database Server Configuration Editor...............................................................................  21
5.2 TwinCAT Database Server : SQL Query Editor..............................................................................  34
5.3 Write Direction Mode ......................................................................................................................  40
5.4 Properties and use of the XML configuration file ............................................................................  43
5.5 Databases.......................................................................................................................................  44
5.5.1 Declaration of the different database types ........................................................................ 44
5.5.2 MS SQL database............................................................................................................... 51
5.5.3 MS SQL Compact database ............................................................................................... 52
5.5.4 MS Azure SQL database .................................................................................................... 53
5.5.5 MS Access database .......................................................................................................... 54
5.5.6 MS Excel database ............................................................................................................. 55
5.5.7 NET / ODBC - MySQL Database........................................................................................ 56
5.5.8 OCI / ODBC - Oracle Database .......................................................................................... 57
5.5.9 SQLite ................................................................................................................................. 58
5.5.10 ASCII - File.......................................................................................................................... 60
5.5.11 XML - Database .................................................................................................................. 60
5.5.12 ODBC - PostgreSQL Database .......................................................................................... 61
5.5.13 ODBC - DB2 Database ....................................................................................................... 63
5.5.14 ODBC - InterBase Database............................................................................................... 63
5.5.15 ODBC - Firebird Database.................................................................................................. 64
5.5.16 Additional information ......................................................................................................... 65
5.6 Expert .............................................................................................................................................  71
5.6.1 Impersonate option ............................................................................................................. 71
5.6.2 Additional Registry configuration ........................................................................................ 72
5.6.3 XML - configuration file ....................................................................................................... 73

6 API............................................................................................................................................................. 76
6.1 PLC reference.................................................................................................................................  76
6.1.1 Tc2_Database..................................................................................................................... 76
6.1.2 Function blocks ................................................................................................................... 78
6.1.3 Data types......................................................................................................................... 106
6.1.4 Global Constants .............................................................................................................. 110

7 Samples.................................................................................................................................................. 112
7.1 Workshop Handout .......................................................................................................................  112
7.2 Generation of a MS Access database ..........................................................................................  123
7.3 Start / Stop of the cyclic log ..........................................................................................................  126
7.4 To log a PLC variable with FB_DBWrite.......................................................................................  128

TC3 Database Server Version: 1.4 3

Table of contents

7.5 Example with the FB_DBRecordInsert and FB_DBRecordSelect block.......................................  132

7.6 Stored procedures with FB_DBStoredProceduresRecordArray ...................................................  135
7.7 Use XML as Database..................................................................................................................  137
7.8 XML Database - XPath Sample to demonstrate the different SELECT types ..............................  142
7.9 XML Database - XPath sample with XML-Schema ......................................................................  145

8 Appendix ................................................................................................................................................ 150
8.1 Errorcodes ....................................................................................................................................  150
8.1.1 ADS Return Codes ........................................................................................................... 150
8.1.2 Internal Errorcodes of the TwinCAT Database Server...................................................... 155
8.1.3 OleDB Errorcodes............................................................................................................. 156
8.1.4 ASCII Errorcodes .............................................................................................................. 162
8.1.5 XML Errorcodes ................................................................................................................ 162
8.2 FAQ - Frequently asked questions and their answers..................................................................  162

4 Version: 1.4 TC3 Database Server

 Foreword

1 Foreword

1.1 Notes on the documentation

This description is only intended for the use of trained specialists in control and automation engineering who
are familiar with the applicable national standards.
It is essential that the following notes and explanations are followed when installing and commissioning
these components.

The responsible staff must ensure that the application or use of the products described satisfy all the
requirements for safety, including all the relevant laws, regulations, guidelines and standards.

Disclaimer
The documentation has been prepared with care. The products described are, however, constantly under
development.
For that reason the documentation is not in every case checked for consistency with performance data,
standards or other characteristics.
In the event that it contains technical or editorial errors, we retain the right to make alterations at any time
and without warning.
No claims for the modification of products that have already been supplied may be made on the basis of the
data, diagrams and descriptions in this documentation.

Trademarks
Beckhoff®, TwinCAT®, EtherCAT®, Safety over EtherCAT®, TwinSAFE®, XFC®and XTS® are registered
trademarks of and licensed by Beckhoff Automation GmbH.
Other designations used in this publication may be trademarks whose use by third parties for their own
purposes could violate the rights of the owners.

Patent Pending
The EtherCAT Technology is covered, including but not limited to the following patent applications and
patents:
EP1590927, EP1789857, DE102004044764, DE102007017835
with corresponding applications or registrations in various other countries.

The TwinCAT Technology is covered, including but not limited to the following patent applications and
patents:
EP0851348, US6167425 with corresponding applications or registrations in various other countries.

EtherCAT® is registered trademark and patented technology, licensed by Beckhoff Automation GmbH,
Germany

Copyright
© Beckhoff Automation GmbH & Co. KG, Germany.
The reproduction, distribution and utilization of this document as well as the communication of its contents to
others without express authorization are prohibited.
Offenders will be held liable for the payment of damages. All rights reserved in the event of the grant of a
patent, utility model or design.

TC3 Database Server Version: 1.4 5

Foreword

1.2 Safety instructions

Safety regulations
Please note the following safety instructions and explanations!
Product-specific safety instructions can be found on following pages or in the areas mounting, wiring,
commissioning etc.

Exclusion of liability
All the components are supplied in particular hardware and software configurations appropriate for the
application. Modifications to hardware or software configurations other than those described in the
documentation are not permitted, and nullify the liability of Beckhoff Automation GmbH & Co. KG.

Personnel qualification
This description is only intended for trained specialists in control, automation and drive engineering who are
familiar with the applicable national standards.

Description of symbols
In this documentation the following symbols are used with an accompanying safety instruction or note. The
safety instructions must be read carefully and followed without fail!

Serious risk of injury!

Failure to follow the safety instructions associated with this symbol directly endangers the
life and health of persons.
DANGER

Risk of injury!
Failure to follow the safety instructions associated with this symbol endangers the life and
health of persons.
WARNING

Personal injuries!
Failure to follow the safety instructions associated with this symbol can lead to injuries to
persons.
CAUTION

Damage to the environment or devices

Failure to follow the instructions associated with this symbol can lead to damage to the en-
vironment or equipment.
Attention

Tip or pointer
This symbol indicates information that contributes to better understanding.

Note

6 Version: 1.4 TC3 Database Server

 Overview

2 Overview
The TwinCAT Database Server enables data exchange between the TwinCAT System and different
database systems. For smaller applications you can use the server over a configurator without influencing
the existing program code. For complex tasks and a maximum of flexibility the Database Server offers a
detailed library of PLC function blocks. Directly out of the PLC you can use SQL-commands like Insert and
Select. If necessary you can relieve the PLC by calling up Stored Procedures in the database. The
transfered parameters from the PLC function block will be used from the database in connection with the
stored procedure and results will be returned to the controller.

At the moment the TwinCAT Database Server supports eleven different database systems [} 44]: MS SQL,
MS SQL Compact, MS Access, MySQL, PostgreSQL, DB2, Oracle, Interbase, Firebird, ASCII (e.g. .txt
or .csv) as well as XML files.

Components:
• TwinCAT Database Server [} 18]: a service which starts and stops along with TwinCAT. It is a
connector between the TwinCAT system and the database.
• Configurator [} 21]: the TwinCAT Database Server configurator enables an easy visual setting of
database parameters, which are necessary for the basic communication with the respective database.
• PLC library [} 76]: the PLC library offers several function blocks to generate a database connection or
a new chart. Furthermore you can write data into any chart structures with Insert-commands or read
them through Select-commands. It is also possible to update or delete database entries. Stored
procedures can be activated.

Functional principle:

The Database Server communicates over ADS within the TwinCAT system. Outwards the server connects
with the respective database. Possible network topologies can be found here [} 19].

TC3 Database Server Version: 1.4 7

Installation

3 Installation

3.1 System requirements

Technical Data TF6420 TwinCAT 3 Database Server
Target system Windows NT/2000/XP/Vista/7,CE
PC (x86, x64 and ARM)
.NET Framework .Net 2.0 or higher
Win CE: .NET 3.5 or higher
Min. TwinCAT-Version 3.1.4011
Min. TwinCAT-Level TC1200 TC3 | PLC

3.2 Installation
Description of the installation procedure of a TwinCAT 3 Function for Windows-based operating Systems.
1. Double-click the downloaded setup file "TE1610 TC3 EAP-Configurator.exe".
Please note: Under Windows 32-bit/64-bit, please start the installation with "Run as Administrator" by
right-clicking the setup file and selecting the corresponding option in the context menu.
2. Click on "Next" and accept the license Agreement.

8 Version: 1.4 TC3 Database Server

 Installation

3. Enter your user information in the specified area.

4. To install the full product, including all sub-components, please choose "Complete" as the Setup
Type.Alternatively you can also install each component seperately by choosing "Custom".

TC3 Database Server Version: 1.4 9

Installation

5. Click on "Install"after pressing the "Next" to start the Installation.

The TwinCAT system has to be stopped before proceeding with installation

6. Confirm the Dialog with "Yes".

10 Version: 1.4 TC3 Database Server

 Installation

7. Select "Finish" to end the installation process.

ð The installation is complete now.

After a successful installation the TC 3 Function needs to be licensed [} 11]

3.3 Licensing
The TwinCAT 3 functions are available both as a full and as a 7-Day trial version. Both license types can be
activated via TwinCAT XAE.For more information about TwinCAT 3 licensing, please consult the TwinCAT 3
Help System.The following document describes both licensing scenarios for a TwinCAT 3 function on
TwinCAT 3 and is divided into the following sections:
• Licensing a 7-Day trial version [} 11]
• Licensing a full version [} 12]

Licensing a 7-Day trial version

1. Start TwinCAT XAE
2. Open an existing TwinCAT 3 project or create a new project
3. In “Solution Explorer”, please navigate to the entry “System\License”

TC3 Database Server Version: 1.4 11

Installation

4. Open the tab "Manage Licenses" and add a "Runtime License" for your product (in this screenshot
“TE1300: TC3 Scope View Professional”)

5. Optional: If you would like to add a license for a remote device, you first need to connect to the remote
device via TwinCAT XAE toolbar

6. Switch to the tab "Order Information" and click the button "Activate 7 Days Trial License..."to
activate a test version

7. Please restart TwinCAT 3 afterwards.

Licensing a full version

8. Start TwinCAT XAE
9. Open an existing TwinCAT 3 project or create a new project

12 Version: 1.4 TC3 Database Server

 Installation

10. In "Solution Explorer", please navigate to the entry "SYSTEM\License"

11. Open the tab "Manage Licenses" and add a "Runtime License" for your product (in this screenshot "
TE1300: TC3 Scope View Professional”).

12. Optional:If you would like to add a license for a remote device, you first need to connect to the remote
device via TwinCAT XAE toolbar

13. Navigate to the "Order Information" tab

The fields "System-ID" and "HW Platform" cannot be changed and just describe the platform for the
licensing process in general a TwinCAT 3 license is always bound to these two identifiers:
the "System-ID" uniquely identifies your system.
The "HW Platform" is an indicator for the performance of the device.
14. Optionally, you may also enter an own order number and description for your convenience

TC3 Database Server Version: 1.4 13

Installation

15. enter the "Beckhoff License ID" and click on "Generate License Request File...". If you are not aware
of your "Beckhoff License ID" please contact your local sales representative.
16. After the license request file has been saved, the system asks whether to send this file via E-Mail to the
Beckhoff Activation Server

17. After clicking "Yes", the standard E-Mail client opens and creates a new E-Mail message to
"tclicense@beckhoff.com" which contains the "License Request File"
18. Send this Activation Request to Beckhoff
NOTE! The “License Response File“ will be sent to the same E-Mail address used for sending
out the ”License Request File”
19. After receiving the activation file, please click on the button "Activate License Response File..."in the
TwinCAT XAE license Interface.

14 Version: 1.4 TC3 Database Server

 Installation

20. Select the received "Licnse response file" and click on "Open"

21. The "License Response File" will be imported and all included licenses will be activated. If there have
been any trial licenses, these will be removed accordingly.
22. Please restart TwinCAT to activate licenses..

NOTE! The license file will be automatically copied to "..\TwinCAT\3.1\Target\License" on the

local device.

3.4 Installation Windows CE

This part of the documentation describes, how you can install the TwinCAT 3 Function TF6310 TCP/IP on a
Beckhoff Embedded PC Controller based on Windows CE.

The setup process consists of four steps:

TC3 Database Server Version: 1.4 15

Installation

1. Downloading the setup file

2. Installation on a host computer
3. Transfering the executable to the Windows CE device
4. Software installation

The last paragraph describes the Software upgrade

Downloading the setup file

The CAB installation file for Windows CE is part of the TFxxxx setup. Therefore you only need to download
one setup file from www.beckhoff.com which contains binaries for Windows XP, Windows 7 and Windows
CE (x86 and ARM).

Installation on a host computer

After installation, the install folder contains three directories - each one for a different hardware platform:
• CE-ARM: ARM-based Embedded Controllers running Windows CE, e.g. CX8090, CX9020
• CE-X86: X86-based Embedded Controllers running Windows CE, e.g. CX50xx. CX20x0
• Win32: Embedded Controllers running Windows XP, Windows 7 or Windows Embedded Standard

The CE-ARM and CE-X86 folders contain the TFxxx ( here TF6310) CAB-File for Windows CE -
corresponding to the hardware platform of your Windows CE device. This file needs to be transfered to the
Windows CE device.

Transfering the executable to the Windows CE device

Transfer the corresponding executable to you Windows CE device. This can be done via one of the following
ways:
• via a Shared Folder
• via the integrated FTP-Server
• via ActiveSync
• via a CF/SD card

For more information, please consult the "Windows CE" section in our Infosys documentation system.

Software installation
After the CAB-File has been transfered via one of the above methods, you need to execute the file and
acknowledge the following dialog with "Ok". Restart your Windows CE device after the installation has
finished.

After the restart has been completed, the TFxxxx executable files will be automatically started in background
and is now available to use.

16 Version: 1.4 TC3 Database Server

 Installation

The software will be installed in the following directory on the CE device: \Hard Disk\TwinCAT\Functions
\TFxxxx

Upgrade instructions
If you have already a version of TF6310 installed on your Windows CE device, you need to perform the
following things on the Windows CE device to upgrade to a newer version:
1. Open the CE Explorer by clicking on Start --> Run and entering "explorer"
2. Navigate to \Hard Disk\TwinCAT\Functions\TFxxx\xxxx
3. Rename the file “Tc*.exe” to “Tc*.old”
4. Restart the Windows CE device
5. Transfer the new CAB-File to the CE device
6. Execute the CAB-File and install the new version
7. Delete “Tc*.old.”
8. Restart the Windows CE device
ð After the restart is complete, the new version is active.

TC3 Database Server Version: 1.4 17

Technical Introduction

4 Technical Introduction

4.1 Database Server functionality

The Database Server provides different communication ways and is configured by a XML configuration file.
• Communication flow in two directions
• ADS_TO_DB
Cyclic checking of a TwinCAT ADS device (e.g. TwinCAT PLC) and writing of these data to a
"database"
• DB_TO_ADS
Cyclic checking of a "database" (e.g. SQL database) and writing of these data to a TwinCAT-ADS
device (e.g. a PLC) via ADS

• Configuration of the "TwinCAT Database Server" is based on an XML file. This configuration file
describes the required "databases", ADS devices (e.g. PLC runtime systems), and variables.
Two storage methods:
- "Double" The compatible variable data types are: BOOL, LREAL, REAL, INT, DINT, USINT, BYTE,
UDINT, DWORD, UINT, WORD, SINT
- "Bytes" Compatible to all variable data types especially for strings and data structures

Two different function modes:

• AutoStart:
Starts the cyclic checking of the PLC values from a ADS device (ADS_TO_DB) or the cyclic checking
of a database (DB_TO_ADS) automatically, as soon as the TwinCAT System is an "RUN" - mode. The
checking PLC program should be running as a bootproject.
• Manual:
Functions like logging in a database or reading from a database could be started out of the PLC with
function blocks.

The following diagram illustrates the server functionality.

The central unit of the TwinCAT 3 Database Server is the XML-Configuration file editor from which all
needed options and configurations are made. The created Configuration could be used in two different
modes (AutoStart/Manual). The TwinCAT 3 Database Server is the connective link between the PLC and the
database.

18 Version: 1.4 TC3 Database Server

 Technical Introduction

TwinCAT Database Server

Errors can occur as a result of byte alignment when logging structures from ADS devices
with ARM processors.
Note Errors can occur when logging structures from the BC9000 with floating point variables.

4.2 Using the TwinCAT 3 Database Server in a network

environment
The TwinCAT Database Server can be operated in various ways in the network. The network topology is
mostly influenced by the database type, the local conditions and the area of application.

The following synoptic shows various network topologies in which the TwinCAT Database Server can be
used.

TC3 Database Server Version: 1.4 19

Technical Introduction

For trouble-free remote access of the TwinCAT Database Server to a data-

base, various things need to be considered on the database side:
• Is remote access generally permitted?
Note
• How many simultaneous connections are permitted? (In case the TwinCAT Database
Server needs to open several connections).
• Does the user who wishes to log onto the database with the Database Server have suf-
ficient rights?
• Is the firewall of the remote system configured appropriately?

Please refer to the associated information for more precise details regarding how your database server
needs to be configured.

20 Version: 1.4 TC3 Database Server

 Configuration

5 Configuration

5.1 TF6420 Database Server Configuration Editor

The TwinCAT 3 DataBase Server is configured via XML configuration file.
The settings in the configuration file can easily be created and modified with the help of the XML
configuration file editor. New configuration files can be created, or existing configuration files can be read in
and edited.

TC3 Database Server Version: 1.4 21

Configuration

The Menu Bar

22 Version: 1.4 TC3 Database Server

 Configuration

Description
New configuration file Creates an empty configuration file with default
settings.
Open Opens an existing XML configuration file.
Open from Targetsystem Opens an active XML configuration file from a target.
Save Saves all the changes that have been made, creating
a configuration file with the name:
"CurrentConfigDataBase.xml".
File Upload Uploading of the configuration file to the Database
Server, saves the configuration file at the specified
"Boot"-directory and activates the configuration.
New database Creates a new database configuration entry.
New ADS device Creates a new ADS device configuration entry.
New symbol group Creates a new symbol group configuration entry.
Copy Copies the selected configuration entry. This can be
a database, an ADS device or a symbol group.
Delete Deletes the selected configuration entry. This can be
a database, an ADS device or a symbol group.
Insert symbols TPY Imports symbols/variables from a TPY file into the
selected symbol group.
Insert symbols ROUTE Imports symbols/variables from a Runtime system of
a specified route.
SQL Query Editor Editor for generate SQL commands easily. Further
information here [} 34].
Live Status Shows the current state of the TF6420 Database
Server and start or stop the cyclic read/write function.
Error Log Shows the logged errors of the error logfile
"TcDBSrvErrorLog.txt"

Option dialog
This dialog can be used to set options for the TwinCAT Database Server:
• StartUp Option:
Two options are available.
"Manual" => TwinCAT Database Server is active and waiting for function calls from the PLC, with
the aid of function blocks from TcDatabase.lib.
"AutoStart" => TwinCAT Database Server is active and starts logging the set symbol groups as
soon as the TwinCAT system is in "RUN" mode.
ATTENTION: PLC programs from which variable values are to be logged must run as boot
projects!
• ErrorLog Option:
This option can be used to log errors in a text file. The logged errors can then be used for
troubleshooting. In addition, you can specify the path for the text file and the maximum file size. To
activate logging, the TcDBSrv configuration has to be reloaded onto the target system. For
performance reasons, this should be done explicitly. We also recommend that logging is deactivated
again after the error analysis, unless it is specifically still required.
• Impersonate Option:
For network access to file-based databases such as Access or SQL Compact, the Impersonate option
must be set, so that the TwinCAT Database Server can connect to this network drive. For further
information see Impersonate [} 71]. This feature is currently not supported in Windows CE.
• Password:
The generated XML configuration file can be secured with a password. Before the file can be edited,
the user then has to enter the specified password.

TC3 Database Server Version: 1.4 23

Configuration

Database Configuration Dialog

All the information required for database communication can be entered in this dialog. Only fields for the
necessary entries are enabled.

The DBValueType indicates the data type of the "Value" column. PLC structures and strings cannot be
logged if the data type is "double". Structures and strings can be logged if the data type is "bytes".
You can choose if the database communication needs an authentication or not. If you want to add
authentication information, the fields DBSystemDB, DBUserId and DBPassword will be enable.
The field DBSystemDB is needed for Access databases only. In this field you have to write the path of the
MDW-file. In this file all information like usernames and passwords are saved.
DBUserId is the username and DBPassword is the necessary password.

configuration of all database types: database-configuration [} 44]

24 Version: 1.4 TC3 Database Server

 Configuration

If you choose ODBC-database types the input mask will be changed. Further information for the ODBC-
connection like Port, Protocol, Driver, Scheme and Sequence have to be insert.

TC3 Database Server Version: 1.4 25

Configuration

ADS Device Configuration Dialog

All the information required for communication with the ADS devices can be entered in this dialog. For easier
input all declared RemotePCs of the TwinCAT System will be listed and could be selected. Of course it is
possible to insert other NetIds, which are not declared as RemotePCs.

Symbol Groups Configuration Dialog

Symbol groups can be arranged in this dialog, and assigned to ADS devices or databases. Additionally the
writing direction of the symbol group is defined. Different settings are possible, see write direction mode.
[} 40]

26 Version: 1.4 TC3 Database Server

 Configuration

This dialog also contains a symbol counter. It returns the number of symbols declared in the individual
groups. If more than 500 symbols are declared, the display turns red, as no more than 500 symbols/
variables may be declared for each symbol group.

If, due to the database settings, data types are not supported, these symbols are highlighted in red and a
warning message is displayed in the upper region.

Of course symbolrows could be copy and paste. You only have to select a row and click the right mouse

TC3 Database Server Version: 1.4 27

Configuration

button. A context menu open and you can choose the desired function.

Import of symbols
It gives two possibilities to import symbols into the TwinCAT Database XML configurator. One way is to read
out the TPY file. The other way is directly from the target device with the Target Browser.

Symbols from TPY

All symbols of this project will be listed very clearly in the following dialog. Also you can import each element
of an array or of a structure into the symbol group. So you can log arrays and structs which consist of
numeric data types if the selected DBValueTyp is "Double".

28 Version: 1.4 TC3 Database Server

 Configuration

Directly from the target with the Target Browser

The Target Browse is used to add channels by a known Symbol to the configuration. The Target Browser is
separated in three parts. The left one shows a tree view with the root named ROUTES. Beneath all TwinCAT
System Manager known targets are listed. The color of the nodes explain the system state: Red= not
Connected (Stop-Mode), Blue= Config Mode, Green= Run Mode.

The second part contains a list view showing the details of the selected node in the tree view.

In the third part is a list view which show all choose symbols.

TC3 Database Server Version: 1.4 29

Configuration

It is possible to add new ADS ports in the Target Browser. So it is realisable to log values directly from
EtherCAT terminals, if the ADS port is enabled in the TwinCAT System Manager under EtherCAT Device
Image.

Upload of the XML configuration file to the TwinCAT Database Server

The created XML configuration file has to be copied into the specified "TwinCAT\Boot" directory. Reload it
with restarting the TwinCAT System or with the function block FB_DBReloadConfig for activating.

Another possibility for this procedure is to activate the file with the help of the "Upload"-dialog. You only have
to choose the right ADS-device and start the upload procedure with the Upload button. The XML

30 Version: 1.4 TC3 Database Server

 Configuration

configuration file will be uploaded to the TwinCAT Database Server, stored in the boot directory and reload
from the Database Server.

Attention: To use this service, the TwinCAT system has to be installed on the host PC where the XML-
configuration file editor runs. The host pc and the ADS-device has to be in RUN mode.

Live Status of the TF6420 Database Server

At this dialog the current state of the Database Server will be shown. It is possible to check the status of the
Database Server from a remote computer. It is also possible to start or stop the cyclic read/write functionality
of the Database Server. If error occurs durring the cyclic read/write process, the sqlstate and the errorcode
of the occured error will be shown too.

TC3 Database Server Version: 1.4 31

Configuration

Error Log (TcDBSrvErrorLog.txt)

At this dialog the all items of the error log file "TcDBSrvErrorLog.txt" will be shown. It is also possible to clear
the error log file.

32 Version: 1.4 TC3 Database Server

 Configuration

SQL Query Editor

The SQL Query Editor helps to generate SQL commands and test the configured database connections.
Further information here [} 34]

TC3 Database Server Version: 1.4 33

Configuration

5.2 TwinCAT Database Server : SQL Query Editor

The TwinCAT Database server combine two different worlds. On the one hand the know how of automation
engeniers and on the other hand the IT database administrators.

To work more efficent the SQL Query Editor would be developed.

With the editor you are able to generate SQL commands and test them with standard PLC function blocks of
the TwinCAT Database Server. Additional it is possible to export the generated commands or structs to
TwinCAT. It is also possible to select, insert, delete datasets or create tables. The generated SQL
commands based of the database specific syntax. The different functionalities of the editor will be sent to the
configured database connections.

Menu Bar

34 Version: 1.4 TC3 Database Server

 Configuration

Description
1 Target TwinCAT Database Server Choose the Target TwinCAT Database
Server for communication.
2 Database List of the configurated database connections
3 Table Tablenname for SQL command generating
4 Copy for PLC Copy the created SQL command with correct
PLC syntax into the clipboard
5 Export TC2 Create an export file for TwinCAT 2 for the
SELECT PLC struct
6 Export TC3 Create an export file for TwinCAT 3 for the
SELECT PLC struct
7 Get Tableschema Reads the table structure of the given table
8 Create Cmd Create SQL commands based of the different
database syntax
9 Execute Executes the SQL command

SQL "CREATE TABLE"

With the tab "CREATE TABLE" you are able to create arbitary tables at the selected database. This
operation will be executed internally with the function block FB_DBTableCreate. With the table text field (3) it
is possible to indicate the name of the new table.

TC3 Database Server Version: 1.4 35

Configuration

SQL "INSERT" Commands

With the tab "INSERT", INSERT SQL commands could be created of a fast and easy way. You can configure
the individual columns of the table by hand, or you read the whole tableschema with the help of the button
"Get Tableschema".

If all column values are insert, and the button "Create Cmd" would be clicked, the INSERT SQL command
will be created with the right database syntax. After that, you only have to press the "Execute" button to send
the command to the database. This operation will be execute internally with the function block
FB_DBRecordInsert_EX

36 Version: 1.4 TC3 Database Server

 Configuration

SQL "SELECT" Commands

With the tab "SELECT" datasets of a table could be read. Therefore you have to declare at the bottom of the
tab the struct with standard PLC data types in which the data will be saved. You can do that by your own or
you use the button "Get Tableschema". The created struct can be export to the TwinCAT PLC program. This
operation will be executed internally with the function block FB_DBRecordArraySelect.

TC3 Database Server Version: 1.4 37

Configuration

SQL "DELETE" Commands

Of course the SQL Query Editor contains a possibility to delete datasets or complete tables. For that purpose
the SQL Query Editor contains the tab "DELETE". Here it is possible to generate DELETE SQL commands.
Internally the commands will be executed with the function block FB_DBRecordDelete.

DELETE Records

38 Version: 1.4 TC3 Database Server

 Configuration

DELETE Tables

TC3 Database Server Version: 1.4 39

Configuration

5.3 Write Direction Mode

The TwinCAT Database Server provides four different write direction modes.

DB_TO_ADS:
With this write mode it is possible to read cyclic values out of a database and write them in variables in the
PLC.

ADS_TO_DB_APPEND:
With this write mode it is possible to write cyclic values from the PLC into a database. Each cycle a new
record is created and added to the end of the table / file.

ADS_TO_DB_UPDATE:
With this write mode values will be cyclically read out of the PLC. These values will be compared with the
records in the database. If the values differs the specified record will be updated with the new value.

40 Version: 1.4 TC3 Database Server

 Configuration

ADS_TO_DB_RINGBUFFER:
With this write mode you can limit the count or the age of datasets on database tables.
This write mode is available for the cyclic logging with symbol groups and for logging with the function block
FB_DBWrite.
Every databasetype can be used with this write mode. Also logging in ASCII-files can be influenced with the
RingBufferMode.

"RingBuffer"-Versions:
The RingBuffer works in two different ways: - "RingBuffer_Time"
- "RingBuffer_Count"

RingBuffer "Time":
In this mode a timestamp can be set, this timestamp defines the maximum age of the datasets. If this age is
exceeded, the affected datasets will be deleted.

RingBuffer "Count":
In this mode a maximum count of datasets can be defined. If the maximum count is obtained, the old
datasets will be deleted to get space for new datasets.

Declaration of the RingBuffer Mode with XML-configuration file editor:

RingBuffer_Time

The time is given in milliseconds.

RingBuffer_Count

TC3 Database Server Version: 1.4 41

Configuration

Declaration of the RingBuffer Mode with FB_DBWrite:

RingBuffer_Time

RingBuffer_Count

42 Version: 1.4 TC3 Database Server

 Configuration

5.4 Properties and use of the XML configuration file

Storage place of the XML-configuration file:
The configuration file has a fixed storage place.

On CE devices the configuration file is placed at the following folder "\Hard Disk\TwinCAT\Boot" (If you
change the XML-configuration file with the XML-configuration file editor, you have to copy this file back to the
folder "\Hard Disk\TwinCAT\Boot")

On PCs the configuration file is placed at the folder "C:\TwinCAT\Boot"

Loading the XML-configuration file:

The configuration file will be read once if the TcDatabaseSrv.exe is starting.
With the Function block "FB_DBReloadConfig" you can read the configuration file once again. (This is only
possible, if the cyclic read/write isn't started.)
After every restart of the TwinCAT System the XML-configuration file will be reloaded.

Mode 1 (StartUp = "AutoStart") :

The XML-configuration file loads automatically, when the TcDatabaseSrv.exe starts. Because of the value
"AutoStart" at the tag StartUp, the Database Server starts immediately creating the connections to the
declared databases und ADS-devices. Also all variables, which are described in symbol groups, are logged
to the specified database resp. written with values of the database. This process will be execute with the
declared cycle time. The process will be continued until the Database Server is stopped by the PLC with the
function block "FB_DBCyclicRdWrt"

Mode 2 (StartUp = "Manual") :

The XML-configuration file loads automatically, when the TcDatabaseSrv.exe starts. Because of the value
"Manual" at the tag StartUp, no further function will be execute. After this the Database Server is waiting for
commands from the PLC.

"FB_DBCyclicRdWrt"
All sections of the configuration file are needed for this function block.

TC3 Database Server Version: 1.4 43

Configuration

Create the connection to the databases and the ADS-devices which are declared in the XML-configuration
file and the cyclic logging with all symbol groups will be started.

All other function blocks only needs the declared databases and ADS-devices of the configuration file. The
symbol groups will be ignored. You can see that at the following function block.

"FB_DBWrite"
A connection to the selected database (hDBID) and the selected ADS-device (hAdsID) will be created. After
this the variable which is indicated at the function block will be read out from the ADS-device and logged into
the database.

5.5 Databases

5.5.1 Declaration of the different database types

The corresponding database drivers are not provided by the TwinCAT Database Server and have to be
installed separately.

Microsoft SQL database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "MS SQL". PLC: eDBType_Sequal_Server.
DBServer: Enter the name of your SQL server. e.g.
("TESTSERVER\SQLEXPRESS")
DBProvider: "SQLOLEDB" or the provider of the SQL native
client, e.g. "SQLNCLI10"
DBName DBName contains the name of the database
DBTable: DBTable contains the name of the table.
WinCE Support: Yes - via network connection

Microsoft Compact SQL database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "MS Compact SQL". PLC:
eDBType_Mobile_Server.
DBServer: Not required.
DBProvider: Not required.
DBUrl DBUrl contains the path to the SDF file. e.g. ("C:
\TwinCAT\TcDatabaseSrv\Samples\TestDB.sdf")
DBTable: DBTable contains the name of the table.
WinCE Support: Yes

44 Version: 1.4 TC3 Database Server

 Configuration

Microsoft Azure SQL database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "MS Azure SQL". PLC:
eDBType_AzureSQL.
DBServer: Enter the name of your SQL server.
DBName: DBName contains the name of the database
Port: Enter the connection port. Standard: 1433
DBTable: DBTable contains the name of the table.
UserId: User name
Password: Password
WinCE Support: No

Microsoft Access database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "MS Access". PLC: eDBType_Access.
DBServer: Not required.
DBProvider: Access 2000 - Access 2003: The provider is
"Microsoft.jet.OLEDB.4.0".
Access 2007: The provider is
"Microsoft.ACE.OLEDB.12.0".

DBUrl DBUrl contains the path to the MDB file. e.g. ("C:
\TwinCAT\TcDatabaseSrv\Samples\TestDB.mdb")
DBTable: DBTable contains the name of the table.
WinCE Support: No

Microsoft Excel database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "MS Excel". PLC: eDBType_MSExcel.
DBServer: Not required.
DBProvider: "Microsoft.Jet.OLEDB.4.0".
or "Microsoft.ACE.OLEDB.12.0".

DBUrl DBUrl contains the path to the Excel file. e.g. ("C:
\TwinCAT\TcDatabaseSrv\Samples\TestDB.xls")
DBTable: DBTable contains the name of the table.
WinCE Support: No

TC3 Database Server Version: 1.4 45

Configuration

ASCII - file
DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "ASCII". PLC: eDBType_ASCII.
DBServer: Not required.
DBProvider: Not required.
DBUrl DBUrl contains the path to the ASC, TXT or CSV file.
e.g. ("C:\TwinCAT\TcDatabaseSrv\Samples
\TestDB.asc")
DBTable: Not required. It is not possible to create tables in
ASCII files.
WinCE Support: Yes - local

NET-MySQL database
DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "NET_MySQL". PLC:
eDBType_NET_MySQL.
ODBC Driver: Not required.
Server name: Contains the name of the server or the IP address/
name of the host.
Database name: Contains the name of the database.
Port: Contains the port for the connection. (Standard 3306)
Protocol: Not required.
Scheme: Not required.
Sequence: Not required.
Table name: Contains the name of the table for writing or reading.
UserId: Contains the name of the user for logging into the
server.
Password: Contains the password to be used for authentication.
WinCE Support: Yes

46 Version: 1.4 TC3 Database Server

 Configuration

ODBC-MySQL Database
DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "ODBC_MySQL". PLC:
eDBType_ODBC_MySQL.
ODBC Driver: Enter the name of the ODBC driver. ("MySQL ODBC
3.51 Driver")
Server name: Contains the name of the server or the IP address/
name of the host.
Database name: Contains the name of the database.
Port: Contains the port for connection of the ODBC driver.
Protocol: Not required.
Scheme: Not required.
Sequence: Not required.
Table name: Contains the name of the table for writing or reading.
UserId: Contains the name of the user for logging into the
server.
Password: Contains the password to be used for authentication.
WinCE Support: No

SQLite
DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "SQLite". PLC: eDBType_SQLite.
DBServer: Contains the name of the database.
DBProvider: Not required.

DBUrl DBUrl contains the path to the SQLite file. e.g. ("C:
\TwinCAT\TcDatabaseSrv\Samples\")
DBTable: DBTable contains the name of the table.
WinCE Support: Yes, but only for ARM-based devices!

TC3 Database Server Version: 1.4 47

Configuration

ODBC PostgreSQL database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "ODBC_PostgreSQL". PLC:
eDBType_ODBC_PostgreSQL.
ODBC Driver: Enter the name of the ODBC driver. ("PostgreSQL
UNICODE")
Server name: Contains the name of the server or the IP address/
name of the host.
Database name: Contains the name of the database.
Port: Contains the port for connection of the ODBC driver.
Protocol: Not required.
Scheme: Contains the name of the scheme to be used.
Sequence: Not required.
Table name: Contains the name of the table for writing or reading.
UserId: Contains the name of the user for logging into the
server.
Password: Contains the password to be used for authentication.
WinCE Support: No

ODBC DB2 database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "ODBC_DB2". PLC: eDBType_ODBC_DB2.
ODBC Driver: Enter the name of the ODBC driver. ("IBM DB2
ODBC DRIVER")
Server name: Contains the name of the server or the IP address/
name of the host.
Database name: Contains the name of the database.
Port: Contains the port for connection of the ODBC driver.
Protocol: Contains the name of protocol for the connection.
("TCPIP")
Scheme: Contains the name of the scheme to be used.
Sequence: Not required.
Table name: Contains the name of the table for writing or reading.
UserId: Contains the name of the user for logging into the
server.
Password: Contains the password to be used for authentication.
WinCE Support: No

48 Version: 1.4 TC3 Database Server

 Configuration

OCI-Oracle database (Oracle Calling interface)

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "OCI_Oracle". PLC: eDBType_OCI_Oracle.
ODBC Driver: Not required
Server name: Contains the host name / IP address of the server.
Database name: Contains the name of the database or the
ServiceName.
Port: Port to be used for the connection. (Standard: 1521)
Protocol: Protocol to be used for the connection. (Standard:
TCP)
Scheme: Contains the name of the scheme to be used.
Sequence: Contains the name of the sequence to be used.
Table name: Contains the name of the table for writing or reading.
UserId: Contains the name of the user for logging into the
server.
Password: Contains the password to be used for authentication.
WinCE Support: No

ODBC Oracle database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "ODBC_Oracle". PLC:
eDBType_ODBC_Oracle.
ODBC Driver: Enter the name of the ODBC driver. ("Microsoft
ODBC for Oracle")
Server name: Not required.
Database name: Contains the name of the database.
Port: Not required.
Protocol: Not required.
Scheme: Contains the name of the scheme to be used.
Sequence: Contains the name of the sequence to be used.
Table name: Contains the name of the table for writing or reading.
UserId: Contains the name of the user for logging into the
server.
Password: Contains the password to be used for authentication.
WinCE Support: No

TC3 Database Server Version: 1.4 49

Configuration

ODBC InterBase database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "ODBC_InterBase". PLC:
eDBType_ODBC_InterBase.
ODBC Driver: Enter the name of the ODBC driver. ("Firebird/
InterBase(r) driver")
Server name: Contains the name of the server or the IP address/
name of the host.
Database name: Contains the name of the database.
ClientDll: Contains the path to fbclient.dll.
Table name: Contains the name of the table for writing or reading.
UserId: Contains the name of the user for logging into the
server.
Password: Contains the password to be used for authentication.
WinCE Support: No

ODBC Firebird database

DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "ODBC_Firebird". PLC:
eDBType_ODBC_Firebird.
ODBC Driver: Enter the name of the ODBC driver. ("Firebird/
InterBase(r) driver")
Server name: Contains the name of the server or the IP address/
name of the host.
Database name: Contains the name of the database.
ClientDll: Contains the path to fbclient.dll.
Table name: Contains the name of the table for writing or reading.
UserId: Contains the name of the user for logging into the
server.
Password: Contains the password to be used for authentication.
WinCE Support: No

XML database
DBValueType: To limit logging to data types alphanumeric and
Boolean select "Double".
To also log structures and strings select "Bytes"
DBType: Select "XML". PLC: eDBType_XML.
DBServer: Contains the name of the database.
DBProvider: Not required.

DBUrl DBUrl contains the path to the XML file. e.g. ("C:
\TwinCAT\TcDatabaseSrv\Samples\TestDB.xml")
The XSD file must be in same directory and have the
same file name. e.g. ("C:\TwinCAT\TcDatabaseSrv
\Samples\TestDB.xsd")
DBTable: DBTable contains the name of the table.
WinCE Support: Yes - local

50 Version: 1.4 TC3 Database Server

 Configuration

5.5.2 MS SQL database

The values of the variables are saved in a Microsoft SQL database.

Compatible versions: Microsoft SQL database 20xx. Declarations see "Declaration for different
databases" [} 44]

The variable values are saved in the following table structure.

Column name Data type Null permitted Characteristic
ID bigint no IDENTITY(1,1)
Timestamp datetime no
Name ntext no

ValueType="Double"

Value float no

ValueType="Bytes"

Value varbinary no

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1. This functionality makes the IDENTITY property possible.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

The table is created with the following SQL command

/*ValueType="Double"*/
CREATE TABLE myTable(
    ID     bigint IDENTITY(1,1) NOT NULL,
    Timestamp datetime         NOT NULL,
    Name     ntext         NOT NULL,
    Value     float         NOT NULL
)

/*ValueType="Bytes"*/
CREATE TABLE myTable(
    ID     bigint IDENTITY(1,1) NOT NULL,
    Timestamp datetime         NOT NULL,
    Name     ntext         NOT NULL,
    Value     varbinary         NOT NULL
)

TC3 Database Server Version: 1.4 51

Configuration

E_DBColumnTypes MS SQL PLC Control

eDBColumn_BigInt bigint T_ULARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer integer DINT
eDBColumn_SmallInt smallint INT
eDBColumn_TinyInt tinyint SINT
eDBColumn_Bit bit BYTE
eDBColumn_Money money LREAL
eDBColumn_Float float LREAL
eDBColumn_Real real REAL
eDBColumn_DateTime datetime DT
eDBColumn_NText ntext STRING
eDBColumn_NChar nchar STRING
eDBColumn_Image image ARRAY OF BYTE
eDBColumn_NVarChar nvarchar STRING
eDBColumn_Binary binary ARRAY OF BYTE
eDBColumn_VarBinary varbinary ARRAY OF BYTE

5.5.3 MS SQL Compact database

The values of the variables are saved in a Microsoft SQL Compact database.
Microsoft SQL Server 2005 Compact Edition is a compact database, ideal for embedding in mobile and
desktop applications. It offers developers a programming model common with other editions of SQL Server
for developing system dedicated and managed applications. This SQL Server requires relatively few
resources, but nevertheless provides the necessary functionality for relational databases such as a robust
data store, an optimized query processor and reliable, scalable connection functions.

Compatible version: Microsoft Compact SQL Database 3.5

The variable values are saved in the following table structure.

Column name Data type Null permitted Characteristic
ID bigint no IDENTITY(1,1)
Timestamp datetime no
Name ntext no

ValueType = "Double"

Value float no

ValueType = "Bytes"

Value image no

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1. This functionality makes the IDENTITY property possible.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

52 Version: 1.4 TC3 Database Server

 Configuration

The table is created with the following SQL command:

/* ValueType = "Double"*/
CREATE TABLE myTable(
    ID     bigint IDENTITY(1,1) NOT NULL,
    Timestamp datetime         NOT NULL,
    Name     ntext         NOT NULL,
    Value     float         NOT NULL
)

/*ValueType = "Bytes"*/
CREATE TABLE myTable(
    ID     bigint IDENTITY(1,1) NOT NULL,
    Timestamp datetime         NOT NULL,
    Name     ntext         NOT NULL,
    Value     image         NOT NULL
)

E_DBColumnTypes MS Compact SQL PLC Control

eDBColumn_BigInt bigint T_ULARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer integer DINT
eDBColumn_SmallInt smallint INT
eDBColumn_TinyInt tinyint SINT
eDBColumn_Bit bit BYTE
eDBColumn_Money money LREAL
eDBColumn_Float float LREAL
eDBColumn_Real real REAL
eDBColumn_DateTime datetime DT
eDBColumn_NText ntext STRING
eDBColumn_NChar nchar STRING
eDBColumn_Image image ARRAY OF BYTE
eDBColumn_NVarChar nvarchar STRING
eDBColumn_Binary binary ARRAY OF BYTE
eDBColumn_VarBinary varbinary ARRAY OF BYTE

TwinCAT Database Server

Do not save the database on the Compact Flash Card in case of an embedded system.
Either use the database in RAM, i.e. do not save to the "Hard disk" folder, or save on a net-
CAUTION work folder. To many write cycles to the Compact Flash Card can shorten its service life.

5.5.4 MS Azure SQL database

The variable values are stored in a Microsoft Azure SQL database.

Declarations see "Declaration for different databases" [} 44]

The variable values are saved in the following table structure.

Column name Data type Null permitted Characteristic
ID bigint no IDENTITY(1,1)
Timestamp datetime no
Name ntext no

ValueType="Double"

TC3 Database Server Version: 1.4 53

Configuration

Value float no

ValueType="Bytes"

Value varbinary no

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1. This functionality makes the IDENTITY property possible.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

The table is created with the following SQL command

/*ValueType="Double"*/
CREATE TABLE myTable(
    ID     bigint IDENTITY(1,1) NOT NULL,
    Timestamp datetime         NOT NULL,
    Name     ntext         NOT NULL,
    Value     float         NOT NULL
)

/*ValueType="Bytes"*/
CREATE TABLE myTable(
    ID     bigint IDENTITY(1,1) NOT NULL,
    Timestamp datetime         NOT NULL,
    Name     ntext         NOT NULL,
    Value     varbinary         NOT NULL
)

E_DBColumnTypes MS SQL PLC Control

eDBColumn_BigInt bigint T_ULARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer integer DINT
eDBColumn_SmallInt smallint INT
eDBColumn_TinyInt tinyint SINT
eDBColumn_Bit bit BYTE
eDBColumn_Money money LREAL
eDBColumn_Float float LREAL
eDBColumn_Real real REAL
eDBColumn_DateTime datetime DT
eDBColumn_NText ntext STRING
eDBColumn_NChar nchar STRING
eDBColumn_Image image ARRAY OF BYTE
eDBColumn_NVarChar nvarchar STRING
eDBColumn_Binary binary ARRAY OF BYTE
eDBColumn_VarBinary varbinary ARRAY OF BYTE

5.5.5 MS Access database

The values of the variables are saved in a Microsoft Access database.

Databasefiles of Access 2000 and Access 2003 (*.mdb) are as compatible as databasefiles of Access 2007
(*.accdb).
You only have to declare different provider in the XML - configuration file for these different filetypes. More

54 Version: 1.4 TC3 Database Server

 Configuration

infos at "Declaration different databases" [} 44]

The variable values are saved in the following table structure.

Column name Data type Characteristic
ID AutoNumber Field Size:= "Long Integer"; New
Values:= "Increment"
Timestamp Date/Time Format:= "General Date"
Name Text
ValueType="Double"
Value Number Field Size:= "Double"
ValueType="Bytes"
Value OLE Object

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column
The "Value" column stores the value of the variable.

E_DBColumnTypes MS Access PLC Control

eDBColumn_BigInt Integer4 DINT
eDBColumn_Integer Integer2 INT
eDBColumn_SmallInt Integer2 SINT
eDBColumn_TinyInt Integer1 SINT
eDBColumn_Bit YESNO BYTE
eDBColumn_Money Currency LREAL
eDBColumn_Float Double LREAL
eDBColumn_Real Single REAL
eDBColumn_DateTime DATETIME DT
eDBColumn_NText Text STRING
eDBColumn_NChar VarChar STRING
eDBColumn_Image OLEOBJECT ARRAY OF BYTE
eDBColumn_NVarChar VarChar STRING
eDBColumn_Binary OLEOBJECT ARRAY OF BYTE
eDBColumn_VarBinary OLEOBJECT ARRAY OF BYTE

TwinCAT Database Server

Do not save the database on the Compact Flash Card in case of an embedded system.
Either use the database in RAM, i.e. do not save to the "Hard disk" folder, or save on a net-
Attention work folder. To many write cycles to the Compact Flash Card can shorten its service life.

5.5.6 MS Excel database

The variable values are stored in an Microsoft Excel database.

For details re. MS Excel declaration see "Declaration for different databases" [} 44].

TC3 Database Server Version: 1.4 55

Configuration

Mapping of data types between database and PLC:

E_DBColumnTypes MS Excel PLC Control

eDBColumn_BigInt Number LREAL
eDBColumn_Integer Number LREAL
eDBColumn_SmallInt Number LREAL
eDBColumn_TinyInt Number LREAL
eDBColumn_Bit BOOLEAN BOOL
eDBColumn_Money Currency LREAL
eDBColumn_Float Number LREAL
eDBColumn_Real Number LREAL
eDBColumn_DateTime Date DT
eDBColumn_NText Text STRING(255)
eDBColumn_NChar Text STRING(255)
eDBColumn_NVarChar Text STRING(255)

Non-supported data types

Binary, VarBinary and Image are not supported with Excel databases.

Attention

5.5.7 NET / ODBC - MySQL Database

The values of the variables are saved in a MySQL database.

The variable values are saved in the following table structure.

Column name Data type Null permitted Characteristic
ID INTEGER no IDENTITY(1,1)
Timestamp DATETIME no
Name VARCHAR(50) no

ValueType="Double"

Value DOUBLE no

ValueType="Bytes"

Value BLOB no

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1. This functionality makes the IDENTITY property possible.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

56 Version: 1.4 TC3 Database Server

 Configuration

E_DBColumnTypes MySQL PLC Control

eDBColumn_BigInt BIGINT T_ULARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer INT DINT
eDBColumn_SmallInt SMALLINT INT
eDBColumn_TinyInt TINYINT SINT
eDBColumn_Bit CHAR(1) BYTE
eDBColumn_Money DEZIMAL(18,4) LREAL
eDBColumn_Float DOUBLE LREAL
eDBColumn_Real FLOAT REAL
eDBColumn_DateTime DATETIME DT
eDBColumn_NText TEXT STRING
eDBColumn_NChar CHAR STRING
eDBColumn_Image BLOB ARRAY OF BYTE
eDBColumn_NVarChar VARCHAR(254) STRING
eDBColumn_Binary BLOB ARRAY OF BYTE
eDBColumn_VarBinary BLOB ARRAY OF BYTE

5.5.8 OCI / ODBC - Oracle Database

The values of the variables are saved in an Oracle Database.

The variable values are saved in the following table structure.

Column name Data type Null permitted Characteristic
ID NUMBER no IDENTITY(1,1)
Timestamp DATE no
Name VARCHAR2 no

ValueType="Double"

Value FLOAT no

ValueType="Bytes"

Value BLOB no

To get the functionality of an AutoID a sequence will be create at the scheme you use with the following
attributes:

name: For example: "AUTO_INCREMENT_myTable$"

typ: "ascending"

minimum: "1"

TC3 Database Server Version: 1.4 57

Configuration

maximum: "1.0E27"

intervall: "1"

cache: "no Cache"

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1. This functionality makes the sequence possible.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

E_DBColumnTypes Oracle PLC Control

eDBColumn_BigInt DECIMAL(15,0) T_LARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer INTEGER T_LARGE_INTEGER
eDBColumn_SmallInt SMALLINT T_LARGE_INTEGER
eDBColumn_TinyInt SMALLINT T_LARGE_INTEGER
eDBColumn_Bit CHAR(1) STRING
eDBColumn_Money DECIMAL(18,4) LREAL
eDBColumn_Float DOUBLE PRECISION LREAL
eDBColumn_Real FLOAT LREAL
eDBColumn_DateTime DATE DT
eDBColumn_NText VARCHAR(254) STRING
eDBColumn_NChar CHAR(254) STRING
eDBColumn_Image BLOB ARRAY OF BYTE
eDBColumn_NVarChar NVARCHAR(254) STRING
eDBColumn_Binary BLOB ARRAY OF BYTE
eDBColumn_VarBinary BLOB ARRAY OF BYTE

5.5.9 SQLite
SQLite

The values of the variables are saved in a SQLite database. SQLite is a compact database, which is ideal for
embedding in mobile applications. This file-based SQL database requires no installation, since it is already
integrated in the TwinCAT Database Server. The TwinCAT Database Server facilitates administration and
configuration. The relational database offers most of the features of SQL databases and supports the
commands of the SQL92 standard. The database enables reliable and fast data storage. However, the
database does not allow distinction of users. It is therefore particularly suitable for safe storage of variables
on the local system.

By default, SQLite is used in the rollback journal.

Compatible version: SQLite 1.0.94

TwinCAT Database Server standard table structure in SQLite:

Column name Data type Null permitted Characteristic

ID bigint no IDENTITY(1,1)
Timestamp datetime no
Name ntext no

58 Version: 1.4 TC3 Database Server

 Configuration

ValueType = "Double"

Value float no

ValueType = "Bytes"

Value image no

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1. This functionality makes the IDENTITY property possible.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column
The "Value" column stores the value of the variable.

The table is created with the following SQL command:

/* ValueType = "Double"*/
CREATE TABLE myTable(
    ID     bigint IDENTITY(1,1) NOT NULL,
    Timestamp datetime         NOT NULL,
    Name     ntext         NOT NULL,
    Value     float         NOT NULL
)

/*ValueType = "Bytes"*/
CREATE TABLE myTable(
    ID     bigint IDENTITY(1,1) NOT NULL,
    Timestamp datetime         NOT NULL,
    Name     ntext         NOT NULL,
    Value     image         NOT NULL
)

The Data type mapping looks like this:

E_DBColumnTypes MS Compact SQL PLC Control

eDBColumn_BigInt bigint T_ULARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer integer DINT
eDBColumn_SmallInt smallint INT
eDBColumn_TinyInt tinyint BYTE
eDBColumn_Bit bit BOOL
eDBColumn_Money money LREAL
eDBColumn_Float float LREAL
eDBColumn_Real real LREAL
eDBColumn_DateTime datetime DT
eDBColumn_NText ntext STRING
eDBColumn_NChar nchar STRING
eDBColumn_Image image ARRAY OF BYTE
eDBColumn_NVarChar nvarchar STRING
eDBColumn_Binary binary ARRAY OF BYTE
eDBColumn_VarBinary varbinary ARRAY OF BYTE

TwinCAT Database Server

Do not save the database on the Compact Flash Card in case of an embedded system.
Either use the database in RAM, i.e. do not save to the "Hard disk" folder, or save on a net-
Attention work folder. Too many write cycles to the Compact Flash Card can shorten its service life.

TC3 Database Server Version: 1.4 59

Configuration

5.5.10 ASCII - File

The values of the variables are saved in an ASCII file.
The values are written into the ASCII file separated by semicolons.
The file created can then be imported into other spreadsheet programs such as "Microsoft Excel", where it
can be further processed.

The ASCII file has the following structure:

[Timestamp];[NAME];[VALUE]
[YYYY-MM-DD hh:mm:ss];[Variablename];[Variablevalue]
[YYYY-MM-DD hh:mm:ss];[Variablename];[Variablevalue]
[YYYY-MM-DD hh:mm:ss];[Variablename];[Variablevalue]
[YYYY-MM-DD hh:mm:ss];[Variablename];[Variablevalue]

The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

Important!
Do not save the database on the Compact Flash Card in case of an embedded system.
Either use the database in RAM, i.e. do not save to the "Hard disk" folder, or save on a network folder. To
many write cycles to the Compact Flash Card can shorten its service life.

5.5.11 XML - Database

The variable values will be saved at a XML file. The structure of the database, tables and columns are
defined at the XSD-file. With the function blocks FB_DBCreate [} 87] and FB_DBTableCreate [} 89] it is
possible to create the XML file and the XSD file. Further information to work with XML files together with the
TwinCAT 3 Database Server you can find here: here [} 66].

<?xmlversion="1.0"encoding="UTF-8"?>

<TestDB_XMLxmlns:xs="http://www.w3.org/2001/XMLSchema-
instance"xs:noNamespaceSchemaLocation="TestDB_XML.xsd">

<myTable_Double>

<rowID="1"Timestamp="2012-03-08T12:45:08"Name="TestValue1"Value="222.222" />

<rowID="2"Timestamp="2012-03-08T12:45:14"Name="TestValue1"Value="222.222" />

<rowID="3"Timestamp="2012-03-08T12:45:18"Name="TestValue1"Value="222.222" />

<rowID="4"Timestamp="2012-03-08T12:45:22"Name="TestValue1"Value="222.222" />

<rowID="5"Timestamp="2012-03-08T12:45:23"Name="TestValue1"Value="222.222" />

</myTable_Double>

</TestDB_XML>

60 Version: 1.4 TC3 Database Server

 Configuration

Table 1: The variable values are saved in the following table structure.

Spaltenname Datentyp Null zulässig Eigenschaft

ID xsd:long nein
Timestamp xsd:dateTime nein
Name xsd:string nein 80

ValueType="Double"

Value xsd:double nein

ValueType="Bytes"

Value xsd:hexBinary nein längenwert

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

E_DBColumnTypes XML PLC Control

eDBColumn_BigInt xsd:long T_LARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer xsd:int DINT
eDBColumn_SmallInt xsd:short INT
eDBColumn_TinyInt xsd:byte BYTE
eDBColumn_Bit xsd:boolean BOOL
eDBColumn_Money xsd:double LREAL
eDBColumn_Float xsd:double LREAL
eDBColumn_Real xsd:double LREAL
eDBColumn_DateTime xsd:dateTime DT
eDBColumn_NText xsd:string STRING
eDBColumn_NChar xsd:string STRING
eDBColumn_Image xsd:hexBinary ARRAY OF BYTE
eDBColumn_NVarChar xsd:string STRING
eDBColumn_Binary xsd:hexBinary ARRAY OF BYTE
eDBColumn_VarBinary xsd:hexBinary ARRAY OF BYTE

TwinCAT Database Server

Do not save the database on the Compact Flash Card in case of an embedded system.
Either use the database in RAM, i.e. do not save to the "Hard disk" folder, or save on a net-
CAUTION work folder. To many write cycles to the Compact Flash Card can shorten its service life.

5.5.12 ODBC - PostgreSQL Database

The values of the variables are saved in a PostgreSQL Database.

TC3 Database Server Version: 1.4 61

Configuration

The variable values are saved in the following table structure.

Column name Data type Null permitted Characteristic
id bigint no IDENTITY(1,1)
timestamp timestamp no
name text no

ValueType="Double"

value double precision no

ValueType="Bytes"

value BLOB no

To get the functionality of an AutoID a sequence will be create at the scheme you use with the following
attributes:

name: "mytable_ID_seq"

raise step: "1"

minimum: "1"

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1. This functionality makes the sequence possible.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

E_DBColumnTypes PostgreSQL PLC Control

eDBColumn_BigInt bigint DINT
eDBColumn_Integer integer DINT
eDBColumn_SmallInt smallint INT
eDBColumn_TinyInt smallint INT
eDBColumn_Bit bit BYTE
eDBColumn_Money money LREAL
eDBColumn_Float double precision LREAL
eDBColumn_Real real REAL
eDBColumn_DateTime timestamp DT
eDBColumn_NText text STRING
eDBColumn_NChar character STRING
eDBColumn_Image bytea ARRAY OF BYTE
eDBColumn_NVarChar character varying STRING
eDBColumn_Binary bytea ARRAY OF BYTE
eDBColumn_VarBinary bytea ARRAY OF BYTE

62 Version: 1.4 TC3 Database Server

 Configuration

5.5.13 ODBC - DB2 Database

The values of the variables are saved in a DB2 Database.

The variable values are saved in the following table structure.

Column name Data type Null permitted Characteristic
ID BIGINT no IDENTITY (1,1)
Timestamp TIMESTAMP no
Name VARCHAR no

ValueType="Double"

Value DOUBLE no

ValueType="Bytes"

Value BLOB no

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1. This functionality makes the IDENTITY property possible.
The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

E_DBColumnTypes DB2 PLC Control

eDBColumn_BigInt BIGINT T_ULARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer INT DINT
eDBColumn_SmallInt SMALLINT INT
eDBColumn_TinyInt SMALLINT INT
eDBColumn_Bit VARCHAR(1) BYTE
eDBColumn_Money DECIMAL(18,4) LREAL
eDBColumn_Float DOUBLE PRECISION LREAL
eDBColumn_Real FLOAT REAL
eDBColumn_DateTime TIMESTAMP DT
eDBColumn_NText LONG VARCHAR STRING
eDBColumn_NChar CHAR(254) STRING
eDBColumn_Image BLOB ARRAY OF BYTE
eDBColumn_NVarChar NVARCHAR(254) STRING
eDBColumn_Binary BLOB ARRAY OF BYTE
eDBColumn_VarBinary BLOB ARRAY OF BYTE

5.5.14 ODBC - InterBase Database

The values of the variables are saved in an InterBase Database.

TC3 Database Server Version: 1.4 63

Configuration

The variable values are saved in the following table structure.

Column name Data type Null permitted Characteristic
ID BIGINT no IDENTITY(1,1)
Timestamp TIMESTAMP no
Name TEXT no

ValueType="Double"

Value FLOAT no

ValueType="Bytes"

Value BLOB no

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1.

The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

E_DBColumnTypes InterBase PLC Control

eDBColumn_BigInt BIGINT T_ULARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer INTEGER DINT
eDBColumn_SmallInt SMALLINT INT
eDBColumn_TinyInt SMALLINT INT
eDBColumn_Bit CHAR(1) BYTE
eDBColumn_Money DEZIMAL(18,4) LREAL
eDBColumn_Float FLOAT REAL
eDBColumn_Real DOUBLE PRECISION LREAL
eDBColumn_DateTime Timestamp DT
eDBColumn_NText VARCHAR(254) STRING
eDBColumn_NChar CHAR(254) STRING
eDBColumn_Image BLOB ARRAY OF BYTE
eDBColumn_NVarChar VARCHAR(254) STRING
eDBColumn_Binary BLOB ARRAY OF BYTE
eDBColumn_VarBinary BLOB ARRAY OF BYTE

5.5.15 ODBC - Firebird Database

The values of the variables are saved in a Firebird Database.

64 Version: 1.4 TC3 Database Server

 Configuration

The variable values are saved in the following table structure.

Column name Data type Null permitted Characteristic
ID BIGINT no IDENTITY(1,1)
Timestamp TIMESTAMP no
Name TEXT no

ValueType="Double"

Value FLOAT no

ValueType="Bytes"

Value BLOB no

An AutoID is generated in the "ID" column. The value in this column is, in other words, always increased by
1.

The "Timestamp" column stores the time at which the data record was saved.
The name of the variable is stored in the "Name" column.
The "Value" column stores the value of the variable.

E_DBColumnTypes FireBird PLC Control

eDBColumn_BigInt BIGINT T_ULARGE_INTEGER
(TcUtilities.lib)
eDBColumn_Integer INTEGER DINT
eDBColumn_SmallInt SMALLINT INT
eDBColumn_TinyInt SMALLINT INT
eDBColumn_Bit CHAR(1) BYTE
eDBColumn_Money DEZIMAL(18,4) LREAL
eDBColumn_Float FLOAT REAL
eDBColumn_Real DOUBLE PRECISION LREAL
eDBColumn_DateTime Timestamp DT
eDBColumn_NText VARCHAR(254) STRING
eDBColumn_NChar CHAR(254) STRING
eDBColumn_Image BLOB ARRAY OF BYTE
eDBColumn_NVarChar VARCHAR(254) STRING
eDBColumn_Binary BLOB ARRAY OF BYTE
eDBColumn_VarBinary BLOB ARRAY OF BYTE

5.5.16 Additional information

5.5.16.1 Microsoft SQL Server hints

Logs in the Windows Eventlog:

• Error Event "Report Server Windows Service (SQLEXPRESS) can't connect to report server
database."
• Under SQL Server 2005 Services in the SQL Configuration Manager, stop the SQL Server Reporting
Services (SQLEXPRESS) and set the start mode to "Manual". The Database Server doesn't need the
Reporting Service.

TC3 Database Server Version: 1.4 65

Configuration

• Information Event "'TcDataLogger' database will be started"

• Under Databases/TcDataLogger in the SQL Server Management Studio Express, right-click on
Properties and under Options set the option "Automatic close" to "False". This option is not required
because the Database Server opens and closes the database automatically.

It is possible to suppress logging to the Windows Eventlog. Then no events will be logged at all. No
distinction can be made between the different types of event.
• Under SQL Server 2005 Services in the SQL Configuration Manager, select the SQL Server
(SQLEXPRESS) and right-click on Properties. On the Advanced tab there is a sub-item called "Startup
Parameters". The individual parameters are separated by semicolons. Add the parameter –n.
Thereafter restart the service.

From this point onwards no further events will be logged by the SQL Server.

5.5.16.2 XML - Information

1. Use XML file as database with TF6420 Database Server

2. Execute XPath queries at a XML file with the TF6420 Database Server

You can find further information about XML schema- here: http://www.w3.org/TR/xmlschema-0/

1. XML as Database
XSD-Schema for standard table structure
<?xmlversion="1.0"?>
<xsd:schemaxmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:simpleTypename="bigint">
<xsd:restrictionbase="xsd:long" />
</xsd:simpleType>
<xsd:simpleTypename="datetime">
<xsd:restrictionbase="xsd:dateTime" />
</xsd:simpleType>
<xsd:simpleTypename="ntext_80">
<xsd:restrictionbase="xsd:string">
<xsd:maxLengthvalue="80" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleTypename="float">
<xsd:restrictionbase="xsd:double" />
</xsd:simpleType>
<xsd:complexTypename="myTable_Double_Type">
<xsd:sequence>
<xsd:elementminOccurs="0"maxOccurs="unbounded"name="row">
<xsd:complexType>
<xsd:attributename="ID"type="bigint" />
<xsd:attributename="Timestamp"type="datetime" />
<xsd:attributename="Name"type="ntext_80" />
<xsd:attributename="Value" type="float" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:elementname="TestDB_XML">
<xsd:complexType>
<xsd:sequenceminOccurs="1"maxOccurs="1">
<xsd:elementname="myTable_Double"type="myTable_Double_Type" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

XML file Schema for standard table structure (example)

<?xmlversion="1.0"encoding="UTF-8"?>
<TestDB_XMLxmlns:xs="http://www.w3.org/2001/XMLSchema-instance"xs:noNamespaceSchemaLoca-
tion="TestDB_XML.xsd">
<myTable_Double>

66 Version: 1.4 TC3 Database Server

 Configuration

<rowID="1"Timestamp="2012-03-08T12:45:08"Name="TestValue1"Value="222.222" />
<rowID="2"Timestamp="2012-03-08T12:45:14"Name="TestValue1"Value="222.222" />
<rowID="3"Timestamp="2012-03-08T12:45:18"Name="TestValue1"Value="222.222" />
<rowID="4"Timestamp="2012-03-08T12:45:22"Name="TestValue1"Value="222.222" />
<rowID="5"Timestamp="2012-03-08T12:45:23"Name="TestValue1"Value="222.222" />
</myTable_Double>
</TestDB_XML>

Datatypes for XML tables

<xsd:simpleTypename="bigint">
<xsd:restrictionbase="xsd:long" />
</xsd:simpleType>

<xsd:simpleTypename="datetime">
<xsd:restrictionbase="xsd:dateTime" />
</xsd:simpleType>

<xsd:simpleTypename="ntext_80"> //Length can be set individual

<xsd:restrictionbase="xsd:string">
<xsd:maxLengthvalue="80" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="float">
<xsd:restrictionbase="xsd:double" />
</xsd:simpleType>

<xsd:simpleTypename="binary_1"> // Length can be set individual

<xsd:restrictionbase="xsd:hexBinary">
<xsd:maxLengthvalue="1" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="bit">
<xsd:restrictionbase="xsd:boolean" />
</xsd:simpleType>

<xsd:simpleTypename="image_1"> // Length can be set individual

<xsd:restrictionbase="xsd:hexBinary">
<xsd:maxLengthvalue="1" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="integer">
<xsd:restrictionbase="xsd:int" />
</xsd:simpleType>

<xsd:simpleTypename="money">
<xsd:restrictionbase="xsd:double" />
</xsd:simpleType>

<xsd:simpleTypename="nchar_50"> // Length can be set individual

<xsd:restrictionbase="xsd:string">
<xsd:maxLengthvalue="50" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="nvarchar_50"> // Length can be set individual

<xsd:restrictionbase="xsd:string">
<xsd:maxLengthvalue="50" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="real">
<xsd:restrictionbase="xsd:double" />
</xsd:simpleType>

<xsd:simpleTypename="smallint">
<xsd:restrictionbase="xsd:short" />
</xsd:simpleType>

<xsd:simpleTypename="tinyint">
<xsd:restrictionbase="xsd:byte" />
</xsd:simpleType>

<xsd:simpleTypename="varbinary_1"> // Length can be set individual

<xsd:restrictionbase="xsd:hexBinary">

TC3 Database Server Version: 1.4 67

Configuration

<xsd:maxLengthvalue="1" />
</xsd:restriction>
</xsd:simpleType>

Datatype mapping XML => PLC

E_DBColumnTypes XML PLC Control

eDBColumn_BigInt xsd:long T_ULARGE_INTEGER
eDBColumn_Integer xsd:int DINT
eDBColumn_SmallInt xsd:short INT
eDBColumn_TinyInt xsd:byte BYTE
eDBColumn_Bit xsd:boolean BOOL
eDBColumn_Money xsd:double LREAL
eDBColumn_Float xsd:double LREAL
eDBColumn_Real xsd:double LREAL
eDBColumn_DateTime xsd:dateTime DT
eDBColumn_NText xsd:string STRING
eDBColumn_NChar xsd:string STRING
eDBColumn_Image xsd:hexBinary ARRAY OF BYTE
eDBColumn_NVarChar xsd:string STRING
eDBColumn_Binary xsd:hexBinary ARRAY OF BYTE
eDBColumn_VarBinary xsd:hexBinary ARRAY OF BYTE

Creating/Reading of data records into/from a XML file

For creating data records it is possible to use standard SQL commands. The SQL INSERT commands will
be interpreted of the TwinCAT 3 Database Server and creates the specified XML-Nodes for the used XML
file. The SQL SELECT commands will be converted to XPath queries which will be executed at the used
XML file.

Samples for supported INSERT commands:

• INSERT INTO myTable_Double (ID, Timestamp, Name, Value) VALUES(1, CURRENT_TIMESTAMP,
'TestValue1' , 1234.5678)
• INSERT INTO myTable_Double (Timestamp, Name) VALUES(CURRENT_TIMESTAMP,
'TestValue1');
• INSERT INTO myTable_Double VALUES(1, CURRENT_TIMESTAMP, 'TestValue1', 1234.5678);
• INSERT INTO myTable_Double VALUES(1, '2010-01-06 12:13:14', 'TestValue1', 1234.5678);

Samples for supported SELECT commands:

• SELECT ID, Timestamp, Name, Value FROM myTable_Double;
• SELECT * FROM myTable_Double;
• SELECT Timestamp, Name FROM myTable_Double
• SELECT * FROM myTable_Double WHERE Name = 'TestValue1';
• SELECT * FROM myTable_Double WHERE ID > 1;

Supported function blocks

• FB_DBCreate
• FB_DBCyclicRdWrt
• FB_DBRead
• FB_DBRecordArraySelect
• FB_DBRecordDelete
• FB_DBRecordInsert
• FB_DBRecordInsert_EX
• FB_DBRecordSelect

68 Version: 1.4 TC3 Database Server

 Configuration

• FB_DBRecordSelect_EX
• FB_DBTableCreate
• FB_DBWrite

2. XML standard XPath function

XPath Types

3 different modes are supported to read values of an XML file.

• -XPath<ATTR>
◦ All attribute values of the selected XML tag will be returned to the PLC.
◦ If a XML-Schema is available the attribute values will be converted to the defined data types.
◦ If no XML-Schema is available all attribute values will be returned as T_MaxString
• -XPath<TAG>
◦ The inner text of the XML tag will be returned to the PLC.
◦ If a XML-Schema is available the values will be converted to the defined data types.
◦ If no XML-Schema is available all values will be returned as T_MaxString
• -XPath<SUBTAG>
◦ The inner text values of all XML subtags will be returned to the PLC.
◦ If a XML-Schema is available the values will be converted to the defined data types.
◦ If no XML-Schema is available all values will be returned as T_MaxString

Samples

XML Datei:
<?xmlversion="1.0"encoding="utf-8" ?>
<TestXML>
<Nodeattr1="1"attr2="Node1">
<SubNode1>SubNodeWert1</SubNode1>
<SubNode2>200</SubNode2>
<SubNode3>SubNodeWert3</SubNode3>
<SubNode4>400.5</SubNode4>
<SubNode5>SubNodeWert5</SubNode5>
</Node>
<Nodeattr1="2"attr2="Node2">
<SubNode1>SubNodeWert1</SubNode1>
<SubNode2>200</SubNode2>
<SubNode3>SubNodeWert3</SubNode3>
<SubNode4>400.5</SubNode4>
<SubNode5>SubNodeWert5</SubNode5>
</Node>
</TestXML>

XML-Schema:
<?xmlversion="1.0"encoding="utf-8"?>
<xs:schemaattributeFormDefault="unqualified"elementFormDefault="qualified"xmlns:xs="http://
www.w3.org/2001/XMLSchema">
<xs:elementname="TestXML">
<xs:complexType>
<xs:sequence>
<xs:elementmaxOccurs="unbounded"name="Node">
<xs:complexType>
<xs:sequence>
<xs:elementname="SubNode1"type="xs:string" />
<xs:elementname="SubNode2"type="xs:short" />
<xs:elementname="SubNode3"type="xs:string" />
<xs:elementname="SubNode4"type="xs:double" />
<xs:elementname="SubNode5"type="xs:string" />
</xs:sequence>
<xs:attributename="attr1" type="xs:integer"use="required" />
<xs:attributename="attr2" type="xs:string"use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>

TC3 Database Server Version: 1.4 69

Configuration

</xs:complexType>
</xs:element>
</xs:schema>

Sample for XPATH<ATTR>

XPath => XPATH<ATTR>#TestXML/Node[@attr1=2]

Returned structure if no XML schema is available:

TYPEST_Record :
STRUCT
attr1 : T_MaxString := ‘2’;
attr2 : T_MaxString := ‘Node2’;
END_STRUCT
END_TYPE

Returned structure if a XML schema is available:

TYPEST_Record :
STRUCT
attr1 : DINT := 2;
attr2 : T_MaxString := ‘Node2’;
END_STRUCT
END_TYPE

Sample for XPATH<TAG>

XPath => XPATH<TAG>#TestXML/Node[@attr1=2]/SubNode2

Returned structure if no XML schema is available: SubNode2 : T_MaxString := ‘200’;

Returned structure if a XML schema is available: SubNode2 : INT := 200;

Sample for XPATH<SUBTAG>

XPath => XPATH<SUBTAG>#TestXML/Node[@attr1=2]

Returned structure if no XML schema is available:

TYPEST_Record :
STRUCT
SubNode1 : T_MaxString := ‘SubNodeWert1’;
SubNode2 : T_MaxString := ‘200’;
SubNode3 : T_MaxString := ‘SubNodeWert3’;
SubNode4 : T_MaxString := ‘400.5’;
SubNode5 : T_MaxString := ‘SubNodeWert5’;
END_STRUCT
END_TYPE

Returned structure if a XML schema is available:

TYPEST_Record :
STRUCT
SubNode1 : T_MaxString := ‘SubNodeWert1’;
SubNode2 : INT := 200;
SubNode3 : T_MaxString := ‘SubNodeWert3’;
SubNode4 : LREAL := 400.5;
SubNode5 : T_MaxString := ‘SubNodeWert5’;
END_STRUCT
END_TYPE

Supported function blocks

• FB_DBRecordSelect
• FB_DBRecordSelect_EX
• FB_DBRecordArraySelect

70 Version: 1.4 TC3 Database Server

 Configuration

5.6 Expert

5.6.1 Impersonate option

Using the Impersonate option users can enter authentication data in order to create a network connection to
a public directory. The Impersonate option must be set for network access to file-based databases such as
Access databases or SQL Compact databases so that the TwinCAT Database Server can connect to this
network drive.

Since the TwinCAT Database Server runs as a system process, it has no access rights to the target device
for reading or writing. You can only get around this problem by granting the user "Guest" read and write
rights to the public folder. This method is very unsafe, however, because every user then has access rights
to this folder.

The safer way to do this is to use the "Impersonate" option, with which you can log onto this target device
with certain user data. The authentication data have to be saved in the registry, as shown in the picture
below.

TC3 Database Server Version: 1.4 71

Configuration

The following keys have to be created:

->[HKEY_LOCAL_MACHINE\SOFTWARE\Beckhoff\TwinCAT3\System] "ImpersonatedPassword"
Contains the password for the authentication.

>[HKEY_LOCAL_MACHINE\SOFTWARE\Beckhoff\TwinCAT3\System] "ImpersonatedUserDomain"
Contains the name of the domain to which the user is logged on.

>[HKEY_LOCAL_MACHINE\SOFTWARE\Beckhoff\TwinCAT3\System] "ImpersonatedUserName"
Contains the name of the user.

5.6.2 Additional Registry configuration

The following basic settings can be invoked for the TwinCAT 3 Database Server:
• The "DBNullAllowed" key can be used to enable toleration of NULL values from the database.
In this case the function blocks do not return an error if zero values occur.
• You can define maximum lengths for byte arrays and strings.
These maximum lengths are used for the function blocks FB_DBRecordReturn,
FB_DBRecordArrayReturn, FB_DBStoredProceduresRecordReturn und
FB_DBStoredProceduresRecordArray.
• In addition, command and connection timeouts and the number of cached values in case of
disconnection during communication can be set with symbol groups.

72 Version: 1.4 TC3 Database Server

 Configuration

The following keys can be adapted:

->[HKEY_LOCAL_MACHINE\SOFTWARE\Beckhoff\TwinCAT3 Functions\TF6420 Database Server

\Configuration] "DBCommandTimeout"
Here, you can set the timeout for a database command. The default setting is 30 seconds.

->[HKEY_LOCAL_MACHINE\SOFTWARE\Beckhoff\TwinCAT3 Functions\TF6420 Database Server

\Configuration] "DBConnectionTimeout"
Here, you can set the timeout for a database connection attempt. The default setting is 15 seconds.

->[HKEY_LOCAL_MACHINE\SOFTWARE\Beckhoff\TwinCAT3 Functions\TF6420 Database Server

\Configuration] "DBNullAllowed"
Setting this key to 1 activates toleration of DBNull values. If zero values occur and this key is not set, an
error is returned to the respective function block.

->[HKEY_LOCAL_MACHINE\SOFTWARE\Beckhoff\TwinCAT3 Functions\TF6420 Database Server

\Configuration] "MaxByteArrayLength"
Contains the maximum length of a byte array returned from the database to the PLC.

->[HKEY_LOCAL_MACHINE\SOFTWARE\Beckhoff\TwinCAT3 Functions\TF6420 Database Server

\Configuration] "MaxErrBufferEntryCount"
Here, you can define the maximum number of cached values in case of disconnection during communication
via predefined symbol groups. Once the connection is re-established, the cached values are transferred
automatically.

>[HKEY_LOCAL_MACHINE\SOFTWARE\Beckhoff\TwinCAT3 Functions\TF6420 Database Server

\Configuration] "MaxStringLength"
Contains the maximum length of a string returned from the database to the PLC.

5.6.3 XML - configuration file

The TwinCAT 3 DataBase Server is configured via an XML configuration file.
The settings of the configuration file are read once when the TwinCAT Database Server is started. Reading
of the configuration can also be triggered from the PLC via a function block during TwinCAT DataBase
Server runtime.

The configuration contains several sections:

TC3 Database Server Version: 1.4 73

Configuration

• DataBases:
Configuration of all "databases" including SQL database, ASCII file, ...
• AdsDevices:
Configuration of all ADS devices (e.g. PLC runtime systems)
• SymbolGroups:
Grouping of different "symbols" (e.g. PLC variables) associated with an ADS device into a logical
group.
A logical group can be configured for transporting data:
- from a database to an ADS system,
- from an ADS device to a database.

<?xml version="1.0"?>
<Configuration>
<Log>1</Log>
<LogPath>C:\TwinCAT\TcDatabaseSrv</LogPath>
<StartUp>Manual</StartUp>
<PwdInfos>tZuYPxhe+G5NKHWLYSZE+NiFAINdlcBgtIUVD+j076ID3geO7FdGzvdfPl0Q09Zb2CKpwj=</PwdInfos>
<Databases>
<Database Type="Mobile-Server"ValueType="Double">
<DBId>1</DBId>
<DBServer />
<DBProvider />
<DBUrl>C:\TwinCAT\TcDatabaseSrv\Samples\TestDB_CompactSQL.sdf</DBUrl>
<DBSystemDB />
<DBUserId />
<DBTable>myTable</DBTable>
</Database>
</Databases>
<AdsDevices>
<AdsDevice>
<AdsId>1</AdsId>
<NetID>10.1.128.49.1.1</NetID>
<Port>801</Port>
<Timeout>2000</Timeout>
<ADSReadWriteSetting>1</ADSReadWriteSetting>
</AdsDevice>
</AdsDevices>
<SymbolGroups>
<SymbolGroup>
<Direction RingBuffMode="Count">ADS_to_DB_RINGBUFFER</Direction>
<RingBuffCount>20</RingBuffCount>
<CycleTime>30000</CycleTime>
<AdsId>1</AdsId>
<DBId>1</DBId>
<Symbols>
<Symbol>
<DBName>TESTVAR123</DBName>
<Name>MAIN.TESTVAR123</Name>
<Type>LREAL</Type>
<IGroup>16448</IGroup>
<IOffset>172536</IOffset>
<BitSize>64</BitSize>
<DBLogMode>3</DBLogMode>
</Symbol>
</Symbols>
</SymbolGroup>
</SymbolGroups>
</Configuration>

Notes regarding the XML configuration file:

Tag „Log“:
Additional option: You can activate the error log mode for tests, to log occurred error descriptions into a text
file "TcDBSrvErrorlog.txt".

Tag „LogPath“:
Additional option: For tests it is possible to activate the error log mode see tag "Log". The path of the text file
is declared in this tag.

74 Version: 1.4 TC3 Database Server

 Configuration

Tag „Impersonate“:
Additional option: To activate the "Impersonate" - option you have to add this tag.

"StartUp" tag:
Start mode setting.
"Autostart" option: The server starts immediately with tasks specified in the configuration file (e.g. writing of
ADS data into the database).
"Manual" option: The server initially remains passive and is triggered via the "FB_DBCyclicRdWrt" function
block.

"PwdInfos" tag:
Contains all passwords which are needed for the communication with the databases. All passwords are
crypted and consequently nonreadable.

"Databases" tag:
Configuration of the individual databases:

DBId: each database is allocated a unique ID.

Database Type: At present, "Mobile Server", "ASCII", "Access" and "Sequal Server" database types are
supported.
ValueType: Two storage methods are supported
- "Double": Saves all the variable values as double values (structures and strings are not supported here);
- "Bytes": Saves all the variable values as byte streams (structures and strings can also be logged in this
way)

"AdsDevices" tag:
Declaration of ADS devices. Each runtime system is assigned a unique ID. The ADS devices are identified
through NetID, port and timeout.

"SymbolGroups" tag:
Definition of symbol groups: Specifies which database is linked with which ADS device. In addition the
direction of the data flow is specified (from the TwinCAT system to the database or from the database to the
TwinCAT system).

The cycle time determines at which intervals data are read from the database or written to the database.

"Symbol" tag:
Definition of the individual symbols with the following parameters:
DBName: Symbol name used in the database
Name: Symbol name used in the PLC
Optional: Type: Data type of the symbol
Optional: IGroup / IOffset: Storage location of the symbol
Optional: BitSize: Size of the symbol
DBLogMode: Optional: Specifies whether the symbols are checked cyclically or after a change.

The configuration file can be found under "C:\TwinCAT\Boot\CurrentConfigDatabase.xml" or, in

embedded systems, under "\Hard Disk\TwinCAT\Boot\CurrentConfigDatabase.xml"

TC3 Database Server Version: 1.4 75

API

6 API

6.1 PLC reference

6.1.1 Tc2_Database

Overview
The Tc2_Database library contains function blocks for controlling and configuring the TC3 Database Server.

76 Version: 1.4 TC3 Database Server

 API

Function Blocks
Name Description
FB_GetStateTcDatabase [} 78] Call state information

FB_DBConnectionAdd [} 80] Adds database connections to the XML configuration

file
FB_DBAuthentificationAdd [} 100] Adds authentication information for database
connections to the XML configuration file
FB_DBOdbcConnectionAdd [} 82] Adds ODBC database connections to the XML
configuration file
FB_AdsDeviceConnectionAdd [} 83] Adds Ads-device connections to the XML
configuration file

FB_DBReloadConfig [} 79] Reloads the XML configuration file

FB_GetDBXMLConfig [} 84] Read all databaseconfiguration out of the XML-
configuration file.
FB_GetAdsDevXMLConfig [} 85] Read all Ads-deviceconfiguration out of the XML-
configuration file.

FB_DBConnectionOpen [} 86] Open a connection to a database

FB_DBConnectionClose [} 87] Close a connection to a database

FB_DBCreate [} 87] Creates a new database

FB_DBTableCreate [} 89] Creates a table with any desired table structure

FB_DBRead [} 90] Reads one value out of the database

FB_DBWrite [} 92] Writes one variable value, with timestamp, into a
database
FB_DBCyclicRdWrt [} 90] Starts or stops the logging/writing of variables

FB_DBRecordSelect [} 102] Reads a data record out of a table

FB_DBRecordSelect_EX [} 104] Reads a data record out of a table (commandlength
<= 10000 symbols)
FB_DBRecordArraySelect [} 95] Reads some data records out of a table
FB_DBRecordInsert [} 101] Creates a new data record
FB_DBRecordInsert_EX [} 94] Creates a new data record (commandlength <=
10000 symbols)
FB_DBRecordDelete [} 93] Deletes a data record from a table

FB_DBStoredProcedure [} 97] Execute Stored Procedures.

FB_DBStoredProcedureRecordReturn [} 105] Execute Stored Procedures and return a data record.
FB_DBStoredProcedureRecordArray [} 98] Execute Stored Procedures and return a count of
data records.

TC3 Database Server Version: 1.4 77

API

Data Types
Name
ST_DBColumnCfg [} 106]
ST_DBXMLCfg [} 106]
ST_ADSDevXMLCfg [} 107]
ST_DBSQLError [} 107]
ST_DBParameter [} 108]
E_DbColumnTypes [} 108]
E_DBTypes [} 109]
E_DBValueType [} 109]
E_DBWriteModes [} 109]
E_DBParameterTypes [} 110]

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2 Function blocks

6.1.2.1 FB_GetStateTcDatabase

The function block allows to get the current state of the Twincat Database Server.

VAR_INPUT
VAR_INPUT
    sNetID         : T_AmsNetID;
    bExecute        : BOOL;
    tTimeout        : TIME;
END_VAR

sNetID : Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

bExecute : The command is executed with the rising edge.

tTimeout : States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy     : BOOL;
    bError    : BOOL;
    nErrID    : UDINT;
    nAdsSta   : UINT;

78 Version: 1.4 TC3 Database Server

 API

    nDevState : UINT;
END_VAR

bBusy : The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError : Becomes TRUE, as soon as an error occurs.

nErrID : Returns the ADS error code if the bError output is set.

nAdsState : Contains the state identification code of the ADS target device. The codes returned here are
specified for all ADS servers:
• ADSSTATE_INVALID =0 ;
• ADSSTATE_IDLE =1 ;
• ADSSTATE_RESET =2 ;
• ADSSTATE_INIT =3 ;
• ADSSTATE_START =4 ;
• ADSSTATE_RUN =5 ;
• ADSSTATE_STOP =6 ;
• ADSSTATE_SAVECFG =7 ;
• ADSSTATE_LOADCFG =8 ;
• ADSSTATE_POWERFAILURE =9 ;
• ADSSTATE_POWERGOOD =10 ;
• ADSSTATE_ERROR =11;

nDevState : Contains the specific state identification code of the ADS target device. The codes returned
here are supplementary information specific to the ADS device.
• 1 = TwinCAT Database Server started
• 2 = cyclic reading or writing started

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.2 FB_DBReloadConfig

With the FB_DBReloadConfig function block the XML configuration file can be reloaded.
If the XML configuration file was modified, the Database Server must be notified of the modifications with the
aid of FB_DBReloadConfig.

VAR_INPUT
VAR_INPUT
    sNetID   : T_AmsNetId;
    bExecute : BOOL;
    tTimeout : TIME;
END_VAR

TC3 Database Server Version: 1.4 79

API

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

bExecute: The command is executed with the rising edge.

tTimeout: States the length of the timeout that may not be exceeded by execution of the ADS command.

VAR_OUTPUT
VAR_OUTPUT
    bBusy  : BOOL;
    bError : BOOL;
    nErrID : UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code if the bError output is set.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.3 FB_DBConnectionAdd

The FB_DBConnectionAdd function block permits additional database connections to be added to the XML
configuration file.

VAR_INPUT
VAR_INPUT
    sNetID       :T_AmsNetId;
    eDBType      :E_DBTypes;
    eDBValueType :E_DBValueType;
    sDBServer    :T_MaxString;
    sDBProvider  :T_MaxString;
    sDBUrl       :T_MaxString;
    sDBSystemDB  :T_MaxString;
    sDBUserId    :T_MaxString;
    sDBPassword  :T_MaxString;
    sDBTable     :T_MaxString;
    bExecute     :BOOL;
    tTimeout     :TIME;
END_VAR

80 Version: 1.4 TC3 Database Server

 API

sNetID : Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

eDBType : Indicates the type of the database, e.g. 'Mobile server'.

eDBValueType : Indicates the form in which the values are or will be stored.

sDBServer : Provides the name of the server. Optional.

sDBProvider : Gives the provider of the database: Optional.

sDBUrl : Gives the path to the database.

sSystemDB : Only for Access databases. Indicates the path to the MDW file

sUserId : Indicates the login user name.

sPassword : Indicates the password.

sDBTable : Gives the name of the table into which the values are to be written.

bExecute : The command is executed with the rising edge.

tTimeout : States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy   : BOOL;
    bError  : BOOL;
    bErrID  : UDINT;
    hDBID   : UDINT;
END_VAR

bBusy : The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError : Becomes TRUE, as soon as an error occurs.

nErrID : Returns the ADS error code if the bError output is set.

hDBID : Returns the ID of the database.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

TC3 Database Server Version: 1.4 81

API

6.1.2.4 FB_DBOdbcConnectionAdd

The function block FB_DBOdbcConnectionAdd permits additional ODBC - database connections to be

added to the XML configuration file.

VAR_INPUT
VAR_INPUT
    sNetID       :T_AmsNetId;
    eDBType      :E_DBTypes;
    eDBValueType :E_DBValueType;
    sDBDriver    :T_MaxString;
    sDBServer    :T_MaxString;
    sDBDatabase  :T_MaxString;
    nDBPort      :UDINT;
    sDBProtocol  :T_MaxString;
    sDBUserId    :T_MaxString;
    sDBPassword  :T_MaxString;
    sDBScheme    :T_MaxString;
    sDBSequence  :T_MaxString;
    sDBClientDll :T_MaxString;
    sDBTable     :T_MaxString;
    bExecute     :BOOL;
    tTimeout     :TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

eDBType : Indicates the type of the database, e.g. 'Mobile server'.

eDBValueType : Indicates the form in which the values are or will be stored.

sDBDriver : Indicates the name of the ODBC driver to be used.

sDBServer : Provides the name of the server.

sDBDatabase : Indicates the name of the database.

nDBPort : Indicates the port for the ODBC connection.

sDBProtocol : Indicates the protocol to be used (TCPIP).

sDBUserId : Indicates the user name.

sDBPassword : Indicates the password to be used.

sDBScheme : Indicates the database schema to be used.

sDBSequence : Indicates the sequence name for Oracle databases.

82 Version: 1.4 TC3 Database Server

 API

sDBClientDll : Contains the path to fbclient.dll. (Only for Firebird/Interbase databases)

sDBTable : Gives the name of the table into which the values are to be written.

bExecute : The command is executed with the rising edge.

tTimeout : States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy  : BOOL;
    bError : BOOL;
    bErrID : UDINT;
    hDBID  : UDINT;
END_VAR

bBusy : The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError : Becomes TRUE, as soon as an error occurs.

nErrID : Returns the ADS error code if the bError output is set.

hDBID : Returns the ID of the database.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.5 FB_AdsDeviceConnectionAdd

The function block FB_AdsDeviceConnectionAdd permits additional Ads-Device connections to be added to

the XML configuration file.

VAR_INPUT
VAR_INPUT
    sNetID         : T_AmsNetID;
    sADSDevNetID   : T_AmsNetID;
    nADSDevPort    : UINT;
    tADSDevTimeout : TIME;
    bExecute       : BOOL;
    tTimeout       : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

sADSDevNetID: Is a string containing the AMS network identifier of the added Ads-device.

nADSDevPort: Indicates the Ams-port of the added Ads-device.

tADSDevTimeout: Contains the timeout of the added Ads-device.

TC3 Database Server Version: 1.4 83

API

bExecute: The command is executed with the rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy     : BOOL;
    bError    : BOOL;
    nErrID    : UDINT;
    hAdsId    : UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Supplies the ADS Error Code when the bError output is set.

hAdsId: Returns the ID of the ADS-Device.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.6 FB_GetDBXMLConfig

With this function block FB_GetDBXMLConfig all declared databases can be read out of the XML-
configuration file.

VAR_INPUT
VAR_INPUT
    sNetID   : T_AmsNetId;
    cbDBCfg  : UDINT;
    pDBCfg   : POINTER TO ARRAY [0.. MAX_XML_DECLARATIONS] OF ST_DBXMLCfg
    bExecute : BOOL;
    tTimeout : TIME;
END_VAR

sNetID : Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

cbDBCfg : Indicates the length of the array, into which the configurations are to be written.

pDBCfg : Indicates the pointer address of the array, into which the configurations are to be written.

bExecute: The command is executed with the rising edge.

tTimeout : States the time before the function is cancelled.

84 Version: 1.4 TC3 Database Server

 API

VAR_OUTPUT
VAR_OUTPUT
    bBusy  : BOOL;
    bError : BOOL;
    nErrID : UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError : Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code if the bError output is set.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.7 FB_GetAdsDevXMLConfig

With this function block FB_GetAdsDevXMLConfig all declared ADS-devices can be read out of the XML-
configuration file.

VAR_INPUT
VAR_INPUT
    sNetID      : T_AmsNetId;
    cbAdsDevCfg : UDINT;
    pAdsDevCfg  : POINTER TO ARRAY [0.. MAX_XML_DECLARATIONS] OF ST_ADSDevXMLCfg
    bExecute    : BOOL;
    tTimeout    : TIME;
END_VAR

sNetID : Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

cbAdsDevCfg : Indicates the length of the array, into which the configurations are to be written.

pAdsDevCfg : Indicates the pointer address of the array, into which the configurations are to be written.

bExecute : The command is executed with the rising edge.

tTimeout : States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy  : BOOL;
    bError : BOOL;
    nErrID : UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

TC3 Database Server Version: 1.4 85

API

nErrID: Returns the ADS error code if the bError output is set.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.8 FB_DBConnectionOpen

You can open connections to databases with this function block FB_DBConnectionOpen. This can improve
the read and write access speed with the fuction blocks FB_DBWrite, FB_DBRead, FB_DBRecordInsert and
FB_FBRecordSelect.

VAR_INPUT
VAR_INPUT
    sNetID  : T_AmsNetId;
    hDBID   : DINT;
    bExecute: BOOL;
    tTimeout: TIME;
END_VAR

sNetID : Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID : Indicates the ID of the database to be used.

bExecute : The command is executed with the rising edge.

tTimeout : States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
END_VAR

bBusy : The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError : Becomes TRUE, as soon as an error occurs.

nErrID : Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState : Returns the SQL error code [} 107] of the corresponding database type

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

86 Version: 1.4 TC3 Database Server

 API

6.1.2.9 FB_DBConnectionClose

The function block FB_DBConnectionClose can be used to make connections with databases. If a
connection with a database was opened previously, it must be closed again.

VAR_INPUT
VAR_INPUT
    sNetID   : T_AmsNetId;
    hDBID    : DINT;
    bExecute : BOOL;
    tTimeout : TIME;
END_VAR

sNetID : Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID : Indicates the ID of the database to be used.

bExecute : The command is executed with the rising edge.

tTimeout : States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy : BOOL;
    bError: BOOL;
    nErrID: UDINT;
END_VAR

bBusy : The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError : Becomes TRUE, as soon as an error occurs.

nErrID : Returns the ADS error code if the bError output is set.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.10 FB_DBCreate

TC3 Database Server Version: 1.4 87

API

The FB_DBCreate function block allows databases to be created. This block can be used to create MS SQL
databases, MS SQL Compact databases, MS Access databases and XML databases.

ASCII files can (but do not have to) be created with the function block FB_DBCreate. If they do not exist,
they are created automatically during the first write access. They only have to be declared in the XML
configuration file.

It is not possible to create DB2, Oracle, MySQL, PostgreSQL, InterBase and Firebird databases. In addition,
it is not possible to overwrite existing databases. In this case the function block FB_DBCreate would return
an error.

VAR_INPUT
VAR_INPUT
    sNetID      : T_AmsNetID;
    sPathName   : T_MaxString;
    sDBName     : T_MaxString;
    eDBType     : E_DBTypes;
    sSystemDB   : T_MaxString;
    sUserID     : T_MaxString;
    sPassword   : T_MaxString;
    bExecute    : BOOL;
    tTimeout    : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed

sPathName: Gives the path to the database

sDBName: Gives the name of the database that is to be created.

eDBType: Gives the type of the database that is to be created.

sSystemDB: Only for Access databases. Contains the path to the MDW file

sUserID: The user name for the corresponding registration

sPassword: The corresponding password

bExecute: The command is executed with the rising edge

tTimeout: Indicates the duration of the timeout

VAR_OUTPUT
VAR_OUTPUT
    bBusy  : BOOL;
    bError : BOOL;
    nErrID : UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code if the bError output is set.

TwinCAT Database Server

If the newly created databases are to be used by the TwinCAT Database Server, the con-
nection data have to be written to the XML configuration file with the aid of the function
Note block FB_DBConnectionADD.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

88 Version: 1.4 TC3 Database Server

 API

6.1.2.11 FB_DBTableCreate

The FB_DBTableCreate function block permits tables with any desired table structure to be created in
databases.

VAR_INPUT
VAR_INPUT
    sNetID     : T_AmsNetID;
    hDBID      : UDINT;
    sTableName : T_MaxString;
    cbTableCfg : UDINT;
    pTableCfg  : POINTER TO ARRAY[0..MAX_DB_TABLE_COLUMNS] OF ST_DBColumnCfg;
    bExecute   : BOOL;
    tTimeout   : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Is the ID of the database to be used.

sTableName: Provides the name of the table.

cbTableCfg: Returns the length of the array in which the columns are configured.

pTableCfg: Provides the pointer address of the table structure array. The individual columns are written in
this array.

bExecute: The command is executed with a rising edge.

tTimeout: Indicates the duration of the timeout.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
END_VAR

bBusy : The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError : Becomes TRUE, as soon as an error occurs.

nErrID : Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState : Returns the SQL error code [} 107] of the corresponding database type

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

TC3 Database Server Version: 1.4 89

API

6.1.2.12 FB_DBCyclicRdWrt

The FB_DBCyclicRdWrt function block can be used to start or stop the cyclic logging \ writing of variables.

VAR_INPUT
VAR_INPUT
    sNetID  : T_AmsNetId;
    bExecute: BOOL;
    tTimeout: TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

bExecute: A rising edge starts the read/write cycle, while a falling edge stops it.

tTimeout: States the length of the timeout that may not be exceeded by execution of the ADS command.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.13 FB_DBRead

The FB_DBRead allows values to be read from a database.

90 Version: 1.4 TC3 Database Server

 API

VAR_INPUT
VAR_INPUT
    sNetID    : T_AmsNetId;
    hDBID     : DINT;
    sDBVarName: STRING(80);
    cbReadLen : UDINT;
    pDestAddr : POINTER TO BYTE;
    bExecute  : BOOL;
    tTimeout  : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

sDBVarName: Gives the name of the variable that is to be read.

cbReadLen: Indicates the length of the buffer that is to be read.

pDestAddr: Contains the address of the buffer which is to receive the data that has been read.

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

TC3 Database Server Version: 1.4 91

API

6.1.2.14 FB_DBWrite

The FB_DBWrite function block can be used to write the values of individual variables into databases.
The table structure have to contain the columns "Timestamp", "Name" and "Value". see SQL Compact
Database [} 52]. To use the function block, you have to declare the required database and ADS device in the
XML - configuration file.

VAR_INPUT
VAR_INPUT
    sNetID          : T_AmsNetID;
    hDBID           : UDINT;
    hAdsID          : UDINT;
    sVarName        : T_MaxString;
    nIGroup         : UDINT;
    nIOffset        : UDINT;
    nVarSize        : UDINT;
    sVarType        : T_MaxString;
    sDBVarName      : T_MaxString;
    eDBWriteMode    : E_DBWriteModes;
    tRingBufferTime : TIME;
    nRingBufferCount: UDINT;
    bExecute        : BOOL;
    tTimeout        : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Is the ID of the database to be used.

hAdsID: This is the ID of the ADS device to be used.

sVarName: Provides the name of the variable.

nIGroup: Index group of the variable (optional, only on the BC9000).

nIOffset: Index offset of the variable (optional, only on the BC9000).

nVarSize: Size of the variable in bytes (optional, only on the BC9000).

sVarType: Data type of the variable (optional, only on the BC9000).

Possible variable data types: "BOOL" / "LREAL" / "REAL" / "INT16" / "DINT" / "USINT" / "BYTE" / "UDINT" /
"DWORD" / "UINT16" / "WORD" / "SINT"
sDBVarName: Variable name to be used in the database.

eDBWriteMode: Indicates whether the values are to be appended in new datasets or whether the existing
datasets are to be updated.

tRingBufferTime: Indicates the maximum age of datasets in a table. (only for Ringbuffer_WriteMode)

92 Version: 1.4 TC3 Database Server

 API

nRingBufferCount: Indicates the maximum number of datasets in a table. (only for Ringbuffer_WriteMode)

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

Log values of ADS devices (except BC9000) Log values of BC9000

FB_DBWrite1( sNetID:= , hDBID:= 1, hAdsID:= 1, FB_DBWrite1( sNetID:= , hDBID:= 1, hAdsID:= 1,
sVarName:= 'MAIN.TestVar', sDBVarName:= sVarName:= 'MAIN.TestVar', nIGroup:= 16448,
'DBTestVar', eDBWriteMode:= nIOffset:= 0, nVarSize:= 16, sVarType:= 'REAL',
eDBWriteMode_Append, bExecute:= TRUE, sDBVarName:= 'DBTestVar', eDBWriteMode:=
tTimeout:= T#15s, bBusy=> busy, bError=> err, eDBWriteMode_Append, bExecute:= TRUE,
nErrID=> errid, sSQLState=> sqlstate); tTimeout:= T#15s, bBusy=> busy, bError=> err,
nErrID=> errid, sSQLState=> sqlstate);

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.15 FB_DBRecordDelete

The function block FB_DBRecordDelete can be used to delete individual datasets from a database. This
block can be used to execute SQL DELETE commands with up to 10,000 characters.
To use the function block it is necessary to declare the database from which the datasets are to be deleted in
the XML configuration file.

VAR_INPUT
VAR_INPUT
    sNetID   : T_AmsNetId;
    hDBID    : UDINT;
    cbCmdSize: UDINT;
    pCmdAddr : POINTER TO BYTE;
    bExecute : BOOL;
    tTimeout : TIME;
END_VAR

TC3 Database Server Version: 1.4 93

API

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

cbCmdSize: Indicates the length of the INSERT command.

pCmdAddr: Pointer to the executing INSERT command.

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.16 FB_DBRecordInsert_EX

The FB_DBRecordInsert_EX function block allows individual data records to be written into a database. The
length of the SQL - command could be till 10000 symbols. For using this function block you have to declare
the database, you want to write to, in the XML - configuration file.

VAR_INPUT
VAR_INPUT
    sNetID   : T_AmsNetId;
    hDBID    : UDINT;
    cbCmdSize: UDINT;
    pCmdAddr : POINTER TO BYTE;
    bExecute : BOOL;
    tTimeout : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

94 Version: 1.4 TC3 Database Server

 API

cbCmdSize: Indicates the length of the INSERT command.

pCmdAddr: Pointer to the executing INSERT command.

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.17 FB_DBRecordArraySelect

The FB_DBRecordArraySelect allows some individual data records to be read from a database. The length
of the SQL-command could be till 10000 Symbols.

This function block is not compatible with ASCII files.

VAR_INPUT
VAR_INPUT
    sNetID           : T_AmsNetID;
    hDBID            : UDINT;
    cbCmdSize        : UDINT;
    pCmdAddr         : UDINT;
    nStartIndex      : UDINT;
    nRecordCount     : UDINT;
    cbRecordArraySize: UDINT;
    pDestAddr        : POINTER TO BYTE;

TC3 Database Server Version: 1.4 95

API

    bExecute         : BOOL;
    tTimeout         : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

cbCmdSize: Indicates the length of a SELECT command to be executed.

pCmdSize: Indicates the pointer address of a string variable with the SQL command to be executed.

nStartIndex: Indicates the index of the first data set to be read.

nRecordCount: Indicates the number of datasets to be read.

cbRecordArraySize: Indicates the size of the structure array in bytes.

pDestAddr: Indicates the address of the structure array into which the datasets are to be written.

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
    nRecords : UDINT;
END_VAR

ST_DBSQLError [} 107]

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code of the corresponding database type

nRecords: Returns the number of data records.

Sample in ST
Since the table from which datasets are to be read has the following structure (see table),

Column name Data type

ID Bigint
Timestamp datetime
Name nvarchar(80)
Value float

a PLC structure must be created having a comparable structure.

TYPE ST_Record:
STRUCT
    ID       : T_ULARGE_INTEGER;
    Timestamp: DT;
    Name     : STRING(80);
    VALUE    : LREAL;
END_STRUCT
END_TYPE

The library TcUtilities.lib must be integrated in order to be able to use the data type T_ULARGE_INTEGER.

96 Version: 1.4 TC3 Database Server

 API

For ARM processors the data types have to be arranged differently due to the byte alignment, and a "dummy
byte" has to be added.
TYPE ST_Record :
STRUCT
    ID       : T_ULARGE_INTEGER;
    Timestamp: DT;
    Value    : LREAL;
    Name     : STRING(80);
    Dummy    : BYTE;
END_STRUCT
END_TYPE
PROGRAM MAIN
VAR
    FB_DBRecordArraySelect1 : FB_DBRecordArraySelect;
    cmd         : T_Maxstring := 'SELECT * FROM myTable';
    (* Unter ARM*)
    (*cmd       : T_Maxstring := 'SELECT ID,Timestamp,Value,Name FROM myTable'*)
    (*----------*)
    recordArray : ARRAY [1..5] OF ST_Record;
    busy        : BOOL;
    err         : BOOL;
    errid       : UDINT;
    sqlstate    : ST_DBSQLError;
    recAnz      : UDINT;
END_VAR

PLC program
FB_DBRecordArraySelect1(
    sNetID:= ,
    hDBID:= 1,
    cbCmdSize:= SIZEOF(cmd),
    pCmdAddr:= ADR(cmd),
    nStartIndex:= 0,
    nRecordCount:= 5,
    cbRecordArraySize:= SIZEOF(recordArray),
    pDestAddr:= ADR(recordArray),
    bExecute:= TRUE,
    tTimeout:= T#15s,
    bBusy=> busy,
    bError=> err,
    nErrID=> errid,
    sSQLState=> sqlstate,
    nRecords=> recAnz);

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.18 FB_DBStoredProcedures

With this function block FB_DBStoredProcedures you are able to start stored procedures. It is possible to
declare parameters which will be used in the stored procedures.

VAR_INPUT
VAR_INPUT
sNetID         : T_AmsNetID                         :='';
hDBID          : UDINT                             :=1;
sProcedureName : T_MaxString                         :='';

TC3 Database Server Version: 1.4 97

API

cbParameterList: UDINT;
pParameterList : POINTER TO ARRAY[0..MAX_STORED_PROCEDURES_PARAMETERS] OF ST_DBParameter;
bExecute       : BOOL;
tTimeout       : TIME                             := T#15s;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

sProcedureName: Indicates the name of the procedure to be executed

cbParameterList: Indicates the length of the parameter list.

pParameterList: Contains the address of the parameter list

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.19 FB_DBStoredProceduresRecordArray

The function block FB_DBStoredProceduresRecordArray can be used to call stored procedures for returning
datasets. In contrast to the FB_DBStoredProceduresRecordReturn block, this block can be used to return
several datasets with a single call. They can include parameters in the process, which are used in the stored
procedures.

98 Version: 1.4 TC3 Database Server

 API

VAR_INPUT
VAR_INPUT
sNetID           : T_AmsNetID           :='';
hDBID            : UDINT                :=1;
sProcedureName   : T_MaxString          :='';
cbParameterList  : UDINT;
pParameterList   : POINTER TO ARRAY[0..MAX_STORED_PROCEDURES_PARAMETERS] OF ST_DBParameter;
nStartIndex      : UDINT;
nRecordCount     : UDINT
cbRecordArraySize: UDINT;
pDesAddr         : POINTER TO BYTE;
bExecute         : BOOL;
tTimeout         : TIME                  := T#15s;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

sProcedureName: Indicates the name of the procedure to be executed

cbParameterList: Indicates the length of the parameter list.

pParameterList: Contains the address of the parameter list

nStartIndex: Indicates the index of the first data set to be read.

nRecordCount: Indicates the number of datasets to be read.

cbRecordArraySize: Indicates the size of the structure array in bytes.

pDestAddr: Indicates the address of the structure array into which the datasets are to be written.

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
    nRecords : UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

nRecords: Returns the number of data records.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

TC3 Database Server Version: 1.4 99

API

6.1.2.20 Obsolete

6.1.2.20.1 FB_DBAuthentificationAdd

The function block FB_DBAuthentificationAdd permits authentication information of declared database

connection to be added to the XML configuration file or to be changed.

VAR_INPUT
VAR_INPUT
    sNetID     : T_AmsNetID;
    hDBID      : DINT;
    sDBSystemDB: T_MaxString;
    sDBUserId  : T_MaxString;
    sDBPassword: T_MaxString;
    bExecute   : BOOL;
    tTimeout   : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Is the ID of the database to be used.

sSystemDB: Only for Access databases. Indicates the path to the MDW file

sUserId: Indicates the login user name.

sPassword: Indicates the password.

bExecute: The command is executed with the rising edge.

tTimeout: Indicates the duration of the timeout.

VAR_OUTPUT
VAR_OUTPUT
    bBusy : BOOL;
    bError: BOOL;
    nErrID: UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code if the bError output is set.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

100 Version: 1.4 TC3 Database Server

 API

6.1.2.20.2 FB_DBRecordInsert

The FB_DBRecordInsert function block allows individual data records to be written into a database. For
using this function block you have to declare the database, you want to write to, in the XML - configuration
file.

VAR_INPUT
VAR_INPUT
    sNetID    : T_AmsNetId;
    hDBID     : UDINT;
    sInsertCmd: T_MaxString;
    bExecute  : BOOL;
    tTimeout  : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

sInsertCmd: Indicates which INSERT command is to be executed.

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

TC3 Database Server Version: 1.4 101

API

6.1.2.20.3 FB_DBRecordSelect

The FB_DBRecordSelect allows individual data records to be read from a database.

This function block is not compatible with ASCII files.

VAR_INPUT
VAR_INPUT
    sNetID      : T_AmsNetID;
    hDBID       : UDINT;
    sSelectCmd  : T_MaxString;
    nRecordIndex: UDINT;
    cbRecordSize: UDINT;
    pDestAddr   : DWORD;
    bExecute    : BOOL;
    tTimeout    : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

sSelectCmd: Indicates which SELECT command is to be executed.

nRecordIndex: Gives the index of the data record that is to be read.

cbRecordSize: Provides the size of a data record in bytes.

pDestAddr: Indicates the address of the structure to which the data set is to be written.

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
    nRecords : UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

nRecords: Returns the number of data records.

102 Version: 1.4 TC3 Database Server

 API

Sample in ST:
Since the table from which the data set is to be read has the following structure (see table),

Column name Data type

ID Bigint
Timestamp datetime
Name Ntext
Value Float

a PLC structure must be created having a comparable structure.

TYPE ST_Record :
STRUCT
    ID       : T_ULARGE_INTEGER;
    Timestamp: DT;
    Name     : STRING;
    VALUE    : LREAL;
END_STRUCT
END_TYPE

The library TcUtilities.lib must be integrated in order to be able to use the data type T_ULARGE_INTEGER.

For ARM processors the data types have to be arranged differently due to the byte alignment, and a "dummy
byte" has to be added.
TYPE ST_Record :
STRUCT
    ID       : T_ULARGE_INTEGER;
    Timestamp: DT;
    Value    : LREAL;
    Name     : STRING;
    Dummy    : BYTE;
END_STRUCT
END_TYPE
PROGRAM MAIN
VAR
    FB_DBRecordSelect1: FB_DBRecordSelect;
    cmd               : T_Maxstring := 'SELECT * FROM myTable';
    (* Unter ARM*)
    (*cmd             : T_Maxstring := 'SELECT ID,Timestamp,Value,Name FROM myTable'*)
    (*----------*)
    record            : ST_Record;
    busy              : BOOL;
    err               : BOOL;
    errid             : UDINT;
    recAnz            : DINT;
END_VAR

PLC program
FB_DBRecordSelect1(
    sNetID      := ,
    hDBID       := 2,
    sSelectCmd  := cmd,
    nRecordIndex:= 0,
    cbRecordSize:= SIZEOF(record),
    pDestAddr   := ADR(record),
    bExecute    := TRUE,
    tTimeout    := T#15s,
    bBusy       => busy,
    bError      => err,
    nErrID      => errid,
    nRecords    => recAnz);

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

TC3 Database Server Version: 1.4 103

API

6.1.2.20.4 FB_DBRecordSelect_EX

The FB_DBRecordSelect_EX allows individual data records to be read from a database. The length of the
SQL-command could be till 10000 Symbols.

This function block is not compatible with ASCII files.

VAR_INPUT
VAR_INPUT
    sNetID      : T_AmsNetID;
    Hdbid       : UDINT;
    cbCmdSize   : UDINT;
    pCmdAddr    : UDINT;
    nRecordIndex: UDINT;
    cbRecordSize: UDINT;
    pDestAddr   : POINTER TO BYTE;
    bExecute    : BOOL;
    tTimeout    : TIME;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

cbCmdSize: Indicates the length of a SELECT command to be executed.

pCmdSize: Indicates the pointer address of a string variable with the SQL command to be executed.

nRecordIndex: Gives the index of the data record that is to be read.

cbRecordSize: Provides the size of a data record in bytes.

pDestAddr: Indicates the address of the structure to which the data set is to be written.

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;
    sSQLState: ST_DBSQLError;
    nRecords : UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

104 Version: 1.4 TC3 Database Server

 API

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

nRecords: Returns the number of data records.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.2.20.5 FB_DBStoredProceduresRecordReturn

With this function block FB_DBStoredProcedures you are able to start stored procedures which return a data
record. It is possible to declare parameters which will be used in the stored procedures.

VAR_INPUT
VAR_INPUT
sNetID         : T_AmsNetID                         :='';
hDBID          : UDINT                             :=1;
sProcedureName : T_MaxString                         :='';
cbParameterList: UDINT;
pParameterList : POINTER TO ARRAY[0..MAX_STORED_PROCEDURES_PARAMETERS] OF ST_DBParameter;
nRecordIndex   : UDINT;
cbRecordSize   : UDINT;
pRecordAddr    : POINTER TO BYTE;
bExecute       : BOOL;
tTimeout       : TIME                             := T#15s;
END_VAR

sNetID: Is a string containing the AMS network identifier of the target device to which the ADS command is
directed.

hDBID: Indicates the ID of the database to be used.

sProcedureName: Indicates the name of the procedure to be executed

cbParameterList: Indicates the length of the parameter list.

pParameterList: Contains the address of the parameter list

nRecordIndex: Gives the index of the data record that is to be read.

cbRecordSize: Provides the size of a data record in bytes.

pRecordAddr: Indicates the address of the structure to which the data set is to be written.

bExecute: The command is executed with a rising edge.

tTimeout: States the time before the function is cancelled.

VAR_OUTPUT
VAR_OUTPUT
    bBusy    : BOOL;
    bError   : BOOL;
    nErrID   : UDINT;

TC3 Database Server Version: 1.4 105

API

    sSQLState: ST_DBSQLError;
    nRecords : UDINT;
END_VAR

bBusy: The command is in the process of being transmitted by ADS. No new command will be accepted as
long as "bBusy" remains TRUE.

bError: Becomes TRUE, as soon as an error occurs.

nErrID: Returns the ADS error code or TcDatabaseSrv_Error_Codes [} 155] if the bError output is set.

sSQLState: Returns the SQL error code [} 107] of the corresponding database type

nRecords: Returns the number of data records.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.3 Data types

6.1.3.1 ST_DBColumnCfg

VAR_INPUT
TYPE ST_DBColumnCfg :
STRUCT
    sColumnName    : STRING(59);
    sColumnProperty: STRING(59);
    eColumnType    : E_DbColumnTypes;
END_STRUCT
END_TYPE

sColumnName: Contains the name of the column to be created.

sColumnProperty: Contains certain column properties.

eColumnType: Gives the type of column.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.3.2 ST_DBXMLCfg

VAR_INPUT
TYPE ST_DBXMLCfg :
STRUCT
    sDBName : STRING;
    sDBTable: STRING;
    nDBID   : DINT;
    eDBType : E_DBTypes;
END_STRUCT
END_TYPE

sDBName: Contains the name of the database.

sDBTable: Contains the name of the table.

nDBID: Returns the ID of the database.

106 Version: 1.4 TC3 Database Server

 API

eDBType: Gives the type of database.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.3.3 ST_ADSDevXMLCfg

VAR_INPUT
TYPE ST_ADSDevXMLCfg :
STRUCT
    sAdsDevNetID  : T_AmsNetID;
    tAdsDevTimeout: TIME;
    nAdsDevID     : DINT;
    nAdsDevPort   : UINT;
END_STRUCT
END_TYPE

sAdsDevNetID: Is a string containing the AMS network ID of the ADS device.

tAdsDevTimeout: Indicates the timeout time of the ADS device.

nAdsDevID: Returns the ID of the ADS device.

nAdsDevPort: Indicates the port of the ADS device.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.3.4 ST_DBSQLError

VAR_INPUT
TYPE ST_DBSQLError :
STRUCT
    sSQLState    : STRING(5);
    nSQLErrorCode: DINT;
END_STRUCT
END_TYPE

sSQLState: Contains the 5-character error code, which is based on the SQL ANSI standard.

nSQLErrorCode: Returns a database-specific error code.

If no error has occurred, the structure contains the following values:

sSQLState := '00000';
nSQLErrorCode := 0;

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

TC3 Database Server Version: 1.4 107

API

6.1.3.5 ST_DBParameter

VAR_INPUT
TYPE ST_DBParameter :
STRUCT
    sParameterName    : STRING(59);
    cbParameterValue  : UDINT;
    pParameterValue   : UDINT;
    eParameterDataType: E_DBColumnTypes;
    eParameterType    : E_DBParameterTypes;
END_STRUCT
END_TYPE

sParameterName: Indicates the name of the parameter.

cbParameterValue: Contains the size of the variable to be used in bytes.

pParameterValue: Contains the address of the variable to be used.

eParameterDataType: Indicates the data type of the parameter (E_DBColumnTypes [} 108]).

eParameterType: Indicates the parameter type (E_DBParameterTypes [} 110]).

Declaration sample
Variable Declaration
PROGRAM MAIN
VAR
    paraList: ARRAY [0..2] OF ST_DBParameter;

    p1: DINT := 3;
    p2: LREAL;
    p3: STRING;
END_VAR

PLC PROGRAM
paraList[0].sParameterName    := 'p1';
paraList[0].eParameterDataType:= eDBColumn_Integer;
paraList[0].eParameterType    := eDBParameter_Input;
paraList[0].cbParameterValue  := SIZEOF(p1);
paraList[0].pParameterValue   := ADR(p1);
    
paraList[1].sParameterName    := 'p2';
paraList[1].eParameterDataType:= eDBColumn_Float;
paraList[1].eParameterType    := eDBParameter_Output;
paraList[1].cbParameterValue  := SIZEOF(p2);
paraList[1].pParameterValue   := ADR(p1);
    
paraList[2].sParameterName    := 'p3';
paraList[2].eParameterDataType:= eDBColumn_NText;
paraList[2].eParameterType    := eDBParameter_Output;
paraList[2].cbParameterValue  := SIZEOF(p3);
paraList[2].pParameterValue   := ADR(p3);

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.3.6 E_DbColumnTypes
TYPE E_DbColumnTypes :
(
    eDBColumn_BigInt    :=0,
    eDBColumn_Integer   :=1,
    eDBColumn_SmallInt  :=2,
    eDBColumn_TinyInt   :=3,
    eDBColumn_Bit       :=4,
    eDBColumn_Money     :=5,

108 Version: 1.4 TC3 Database Server

 API

    eDBColumn_Float     :=6,
    eDBColumn_Real      :=7,
    eDBColumn_DateTime  :=8,
    eDBColumn_NText     :=9,
    eDBColumn_NChar     :=10,
    eDBColumn_Image     :=11,
    eDBColumn_NVarChar  :=12,
    eDBColumn_Binary    :=13,
    eDBColumn_VarBinary :=14
);
END_TYPE

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.3.7 E_DBTypes
TYPE E_DBTypes :
(
    eDBType_Mobile_Server  := 0,
    eDBType_Access         := 1,
    eDBType_Sequal_Server  := 2,
    eDBType_ASCII          := 3,
    eDBType_ODBC_MySQL     := 4,
    eDBType_ODBC_PostgreSQL:= 5,
    eDBType_ODBC_Oracle    := 6,
    eDBType_ODBC_DB2       := 7,
    eDBType_ODBC_InterBase := 8,
    eDBType_ODBC_Firebird  := 9,
    eDBType_XML            := 10,
    eDBType_OCI_Oracle     := 11,
    eDBType_NET_MySQL      := 12
);
END_TYPE

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.3.8 E_DBValueType
TYPE E_DBValueType :
(
    eDBValue_Double:= 0,
    eDBValue_Bytes := 1
);
END_TYPE

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.3.9 E_DBWriteModes
TYPE E_DBWriteModes :
(
    eDBWriteMode_Update          := 0,
    eDBWriteMode_Append          := 1,
    eDBWriteMode_RingBuffer_Time := 2,
    eDBWriteMode_RingBuffer_Count:= 3
);
END_TYPE

TC3 Database Server Version: 1.4 109

API

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.3.10 E_DBParameterTypes
TYPE E_DBParameterTypes :
(
    eDBParameter_Input       := 0,
    eDBParameter_Output      := 1,
    eDBParameter_InputOutput := 2,
    eDBParameter_ReturnValue := 3,
    eDBParameter_OracleCursor:= 4
);
END_TYPE

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.4 Global Constants

6.1.4.1 Constants
VAR_GLOBAL_CONSTANT
    AMSPORT_DATABASESRV                           : UINT         := 21372;

    DBADS_IGR_RELOADXML                           : UDINT         :=16#100;

    DBADS_IGR_GETSTATE                            : UDINT         :=16#200;

    DBADS_IGR_DBCONNOPEN                          : UDINT         :=16#300;

    DBADS_IGR_DBCONNCLOSE                         : UDINT         :=16#301;
    DBADS_IGR_ADSDEVCONNOPEN                      : UDINT         :=16#302;
    DBADS_IGR_ADSDEVCONNCLOSE                     : UDINT         :=16#303;

    DBADS_IGR_DBSTOREDPROCEDURES                  : UDINT         :=16#400;

    DBADS_IGR_DBSTOREDPROCEDURES_RETURNRECORD     : UDINT         :=16#401;
    DBADS_IGR_DBSTOREDPROCEDURES_RETURNRECORDARRAY: UDINT         :=16#402;

    DBADS_IGR_START                               : UDINT         :=16#10000;

    DBADS_IGR_STOP                                : UDINT         :=16#20000;

    DBADS_IGR_DBCONNADD                           : UDINT         :=16#30000;

    DBADS_IGR_ADSDEVCONNADD                       : UDINT         :=16#30001;
    DBADS_IGR_ODBC_DBCONNADD                      : UDINT         :=16#30010;

    DBADS_IGR_GETDBXMLCONFIG                      : UDINT         :=16#30101;

    DBADS_IGR_GETADSDEVXMLCONFIG                  : UDINT         :=16#30102;

    DBADS_IGR_DBWRITE                             : UDINT         :=16#40000;

    DBADS_IGR_DBREAD                              : UDINT         :=16#50000;

    DBADS_IGR_DBTABLECREATE                       : UDINT         :=16#60000;

    DBADS_IGR_DBCREATE                            : UDINT         :=16#70000;

    DBADS_IGR_DBRECORDSELECT                      : UDINT         :=16#80001;

    DBADS_IGR_DBRECORDINSERT                      : UDINT         :=16#80002;
    DBADS_IGR_DBRECORDDELETE                      : UDINT         :=16#80003;

    DBADS_IGR_DBAUTHENTIFICATIONADD               : UDINT         :=16#90000;

    MAX_DB_TABLE_COLUMNS                          : UDINT         := 255;

    MAX_XML_DECLARATIONS                          : UDINT         := 255;
    MAX_STORED_PROCEDURES_PARAMETERS              : UDINT         := 255;  

110 Version: 1.4 TC3 Database Server

 API

END_VAR

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

6.1.4.2 Library version

All libraries have a specific version. This version is shown in the PLC library repository too.
A global constant contains the library version information:

Global_Version

VAR_GLOBAL CONSTANT
    stLibVersion_TC3_Database_Server : ST_LibVersion;
END_VAR

To compare the existing version to a required version the function F_CmpLibVersion (defined in Tc2_System
library) is offered.

TwinCAT Database Server

All other possibilities known from TwinCAT2 libraries to query a library version are obso-
lete!
Note

TC3 Database Server Version: 1.4 111

Samples

7 Samples
All sample applications for the TwinCAT 3 Database Server were consolidated in a solution. The solution can
be downloaded here from a central location:
TcDBSrv_InfoSysSamples.zip

In addition to the tszip for the TwinCAT 3 solution, the zip file contains all the required file-based databases.
If the folder "Samples" from the zip file is located in the default installation folder: C:\TwinCAT\Functions
\TF6420-Database-Server\Win32, the paths in the Database Server configuration do not have to be edited
further. The samples with non-file-based databases, such as MS SQL, have to be adapted individually with
the configurator.

The individual samples are documented in detail on separate pages:

PLC project name: Description:

Create_DB_Sample [} 123] Creating a database connection and table from the
PLC
Cyclic_RdWrt_Sample [} 126] Cyclic logging/writing to/from a database
Write_DB_Sample [} 128] Writing of variables into a database with a simple
PLC block without SQL command
SQL_InsertSelect_Sample [} 132] Sample with FB_DBRecordInsert/
FB_DBRecordArraySelect block
StoredProcedures_Sample [} 135] Stored procedures with
FB_DBStoredProceduresRecordArray
XML_DB_Sample [} 137] Using XML files as database
XML_XPath_Sample [} 142] XML XPath sample without schema
XML_XPath_Schema_Sample [} 145] XML XPath sample with XML schema - comparable
with TwinCAT XML Server "read"

In addition to the individual samples, there is a Quick Start [} 112] guide for the TwinCAT Database Server.

7.1 Workshop Handout

This chapter describes the use of the XML configuration file editor in detail and illustrates the operating
principle of some function blocks in the PLC.

Two options are available for configuring the TwinCAT Database Server:
• with the TwinCAT Database Server XML configuration file editor [} 113]
• from the PLC [} 120]

A step-by-step-guide for both options is provided below:

112 Version: 1.4 TC3 Database Server

 Samples

Configuration of the Database Server with the XML editor

1. Open the editor via Start -> All Programs -> TwinCAT System -> TwinCAT Database Server -> XML
Configuration File Editor

2. Add a new database by right-clicking on “Database / Add Database” or the corresponding icon in the
toolbar.

TC3 Database Server Version: 1.4 113

Samples

3. Configure your database by selecting the type (in this example DBType ASCII). The declarations for all
supported database types can be found in the information system.
4. If only alphanumeric and Boolean data types are to be logged, select DBValueType "Double", if
structures and strings are to be logged select "Bytes".

5. Under “Database URL” enter the path for the database (this is sufficient for an ASCII database). Later
you will need the database ID (DBID), which can be found here.

114 Version: 1.4 TC3 Database Server

 Samples

6. Add an AdsDevice and enter your AdsNetID (not required if you use a local system) and your AdsPort.

Later you will need the AdsID, which can be found here. To create a configuration for a remote system,
select the required system from the table on the right.
7. Add a symbol group with the variables from your PLC project.

TC3 Database Server Version: 1.4 115

Samples

8. Before you can configure your symbol groups you have to compile your PLC project. To this end open
TwinCAT PLC Control with the file PLCVariableSample.pro and then select Project -> Compile. This is
important for creation of the TPY file.

116 Version: 1.4 TC3 Database Server

 Samples

9. In the database editor use the "Import Symbols" button to look for variables.

TC3 Database Server Version: 1.4 117

Samples

10. For the example select the first five variables from the icon collection. You can change the LogMode for
each variable. Now set the AdsID and the DBID, as described above.

11. Select the communication direction:

ADS_to_DB_APPEND adds the new database entries to the database.
ADS_to_DB_UPDATE updates the existing entries in the database.
ADS_to_DB_RINGBUFFER limits the number or the age of the data sets in the database table.
DB_to_ADS describes the writing of values from the database into the PLC.

118 Version: 1.4 TC3 Database Server

 Samples

In the example ADS_to_DB_APPEND is selected.

12. To use the Database Server without the PLC (only PLC variables are logged), the option AutoStart must
be set. In this case the Database Server starts configuring the connections to the declared databases
and ADS devices directly after a TwinCAT start.
To this end you must have generated a boot project via the program "PLCVariableSample.pro", and the
generated configuration must be saved in directory "C:\TwinCAT\Boot".

TC3 Database Server Version: 1.4 119

Samples

13. To test the configuration enter PLC Control and start the PLC. Generate a boot project and restart
TwinCAT. Now examine the test database "TestDB" under "C:\TwinCAT\TcDatabaseSrv\Samples".

Database Server configuration from the PLC

Guide for:
• Creating a Microsoft Access database from the PLC
• Adding a database connection
• Creating a new table in the database
• Use the write function block with ring buffer mode "RingBuffer_Count" to write a variable 100 times into
the table.
14. add a R_TRIG function block in order to start your program with a rising edge.
15. create a "state machine" for addressing your database function blocks.

16. declare all other required variables, particularly the table structure. Relevant instructions for the
Microsoft Access database can be found in the information system.
An AutoID is generated in the "ID" column. The value in this column is always incremented by 1. The
"Timestamp" column contains the time at which the data set was stored. The name of the variable is

120 Version: 1.4 TC3 Database Server

 Samples

stored in the third column. Under "Value" you can find the variable value. The variables that are familiar
from the next image can be used.

17. FB_DBCreate:
Add the FB_DBCreate in status number 1. The path to the database must be transferred to the function
block.
NOTE! Ensure that the database does not yet exist at this stage. Otherwise an error message is
issued.
In status 11 you have to wait until the output "bBusy" is no longer TRUE.

TC3 Database Server Version: 1.4 121

Samples

18. FB_DBConnectionAdd:
In this function block enter the DBType and the DBValueType (bytes or double). Enter a name for your
table. Refer to the image for the other settings for Access databases.

19. FB_DBTableCreate:
One of the inputs for the function block is "hDBID". For this input enter the value received from
FB_DBConnectionAdd. Enter the size and the address of the table structure.

122 Version: 1.4 TC3 Database Server

 Samples

20. FB_DBWrite:
FB_DBWrite can be used to write the current variable value "iLogVariable" into the database at any
time. Please note that only 100 variable values are stored if the nRingBufferCount is set to 100.

ð At the end of the program code you can see how the variable is incremented by 1 for each PLC cycle.

TwinCAT Database Server

To test the sample code make sure that the database does not yet exist.

CAUTION

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

7.2 Generation of a MS Access database

This sample illustrates the creation of a database from the PLC.
In addition, a table is added, and the database that has been generated is declared in the XML configuration
file.

Download: TcDBSrv_InfoSysSamples.zip

TC3 Database Server Version: 1.4 123

Samples

Database type used MS Access

Compatible database types MS SQL, MS Compact SQL, MS Access, XML
Function blocks used FB_DBCreate, FB_DBConnectionAdd,
FB_DBTableCreate
Libraries to be integrated "Tc2_Database", "Tc2_System", "Tc2_Standard"
Download file list TcDBSrv_InfoSysSamples.tszip

A table with the name "myTable", which has the following structure, is added to the generated database:

Column name Data type Feature

ID Bigint IDENTITY(1,1)
Timestamp datetime
Name Ntext
Value Float

This table structure is generated with the following array:

tablestrc: ARRAY [0..3] OF ST_DBColumnCfg :=
     [(sColumnName:='ID',sColumnProperty:='IDENTITY(1,1)',eColumnType:=EDBCOLUMN_BIGINT),
     (sColumnName:='Timestamp',eColumnType:=EDBCOLUMN_DATETIME),
     (sColumnName:='Name',eColumnType:=EDBCOLUMN_NTEXT),
     (sColumnName:='Value',eColumnType:=EDBCOLUMN_FLOAT)];

124 Version: 1.4 TC3 Database Server

 Samples

Variable Declaration
PROGRAM MAIN
VAR
    R_TRIG1            : R_TRIG;
    bSTART             : BOOL;

    FB_FileDelete1     : FB_FileDelete;
    FB_DBCreate1       : FB_DBCreate;
    FB_DBConnectionAdd1: FB_DBConnectionAdd;
    FB_DBTableCreate1  : FB_DBTableCreate;

    bBusy_Delete       : BOOL;
    bBusy_CreateDB     : BOOL;
    bBusy_ConnAdd      : BOOL;
    bBusy_CreateTable  : BOOL;

    bErr               : BOOL;
    nErrid             : UDINT;

    nDBid              : UDINT;

    arrTablestrc       : ARRAY [0..3] OF ST_DBColumnCfg :=

     [(sColumnName:='ID',sColumnProperty:='IDENTITY(1,1)',eColumnType:=EDBCOLUMN_BIGINT),
     (sColumnName:='Timestamp',eColumnType:=EDBCOLUMN_DATETIME),
     (sColumnName:='Name',eColumnType:=EDBCOLUMN_NTEXT),
     (sColumnName:='Value',eColumnType:=EDBCOLUMN_FLOAT)];

    nState:BYTE := 0;

END_VAR

PLC program
CASE nState OF
    0:
        (*To start this sample you have to set a rising edge to the variable bSTART*)
        R_TRIG1(CLK:=bSTART);
        IF R_TRIG1.Q THEN
            nState   := 1;
            FB_FileDelete1(bExecute:=FALSE);
            FB_DBCreate1(bExecute:=FALSE);
            FB_DBConnectionAdd1(bExecute:=FALSE);
            FB_DBTableCreate1(bExecute:=FALSE);
            bSTART   := FALSE;
        END_IF
    1:
        (*It isn't possible to overwrite an existing database file.
         If the database file exist the FB_FileDelete block will delete the file*)
        FB_FileDelete1(
            sNetId   := ,
            sPathName:= 'C:\TwinCAT\TcDatabaseSrv\Samples\TestDB1000SPS.mdb',
            ePath    := PATH_GENERIC,
            bExecute := TRUE,
            tTimeout := T#5s,
            bBusy     => bBusy_Delete,
            bError    => ,
            nErrId    => );

        IF NOT bBusy_Delete THEN

            nState   := 2;
        END_IF

    2:
        (*The FB_DBCreate block will create the database file
         "C:\TwinCAT\TcDatabaseSrv\Samples\TestDB1000SPS.mdb"*)
        FB_DBCreate1(
            sNetID   := ,
            sPathName:= 'C:\TwinCAT\TcDatabaseSrv\Samples',
            sDBName  := 'TestDB1000SPS',
            eDBType  := eDBType_Access,
            bExecute := TRUE,
            tTimeout := T#15s,
            bBusy     => bBusy_CreateDB,
            bError    => bErr,
            nErrID    => nErrid);

        IF NOT bBusy_CreateDB AND NOT bErr THEN            

            nState   := 3;

TC3 Database Server Version: 1.4 125

Samples

        END_IF
    3:
        (*The FB_DBConnectionAdd adds the connection information to the
         XML configuration file*)
        FB_DBConnectionAdd1(
            sNetID      := ,
            eDBType     := eDBType_Access,
            eDBValueType:= eDBValue_Double,
            sDBServer   := ,
            sDBProvider := 'Microsoft.Jet.OLEDB.4.0',
            sDBUrl      := 'C:\TwinCAT\TcDatabaseSrv\Samples\TestDB1000SPS.mdb',
            sDBTable    := 'myTable',
            bExecute    := TRUE,
            tTimeout    := T#15s,
            bBusy        => bBusy_ConnAdd,
            bError       => bErr,
            nErrID       => nErrid,
            hDBID        => nDBid);

        IF NOT bBusy_ConnAdd AND NOT bErr THEN

            nState      := 4;
        END_IF
    4:
        (*The FB_DBTableCreate create the table "myTable"*)
        FB_DBTableCreate1(
            sNetID      := ,
            hDBID       := nDBid,
            sTableName  := 'myTable',
            cbTableCfg  := SIZEOF(arrTablestrc),
            pTableCfg   := ADR(arrTablestrc),
            bExecute    := TRUE,
            tTimeout    := T#15s,
            bBusy        => bBusy_CreateTable,
            bError       => bErr,
            nErrID       => nErrid);

        IF NOT bBusy_CreateTable AND NOT bErr THEN

            nState := 0;
        END_IF
END_CASE

In order to use this sample, you only need to transfer the NetID of the ADS device (on which the TwinCAT
Database Server is installed) to the sNetID input.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

7.3 Start / Stop of the cyclic log

This sample illustrates the starting and stopping of cyclic logging from the PLC. Download:
TcDBSrv_InfoSysSamples.zip

126 Version: 1.4 TC3 Database Server

 Samples

Database type used MS Compact SQL

Compatible database types ASCII, MS SQL, MS Compact SQL, MS Access,
MySQL, PostgreSQL, DB2, Oracle, InterBase/
Firebird, XML
Function blocks used FB_DBCyclicRdWrt
Libraries to be integrated "Tc2_Database", "Tc2_System", "Tc2_Standard"
Download file list TcDBSrv_InfoSysSamples.tszip,
CurrentConfigDataBase.xml, TestDB_Cyclic.sdf

In this sample the cyclic log function is started or stopped by toggling the bStartStop variable.
The cyclic log process begins in response to a rising edge at the bExecute input.
A falling edge will end the process again.

Variable declaration (PRG data types)

PROGRAM DataTypes
VAR
    DBSrv_DT_INT     : INT;
    DBSrv_DT_UINT    : UINT;
    DBSrv_DT_DINT    : DINT;
    DBSrv_DT_UDINT   : UDINT;
    DBSrv_DT_REAL    : REAL;
    DBSrv_DT_LREAL   : LREAL;
    DBSrv_DT_BYTE    : BYTE := 16#A1;
    DBSrv_DT_BOOL    : BOOL;
    DBSrv_DT_MYSTRUCT: ST_MyStruct;
    DBSrv_DT_ARRAY   : ARRAY [0..19] OF UDINT;
    DBSrv_DT_WORD    : WORD;
    DBSrv_DT_DWORD   : DWORD;
END_VAR

TC3 Database Server Version: 1.4 127

Samples

ST_MyStruct structure
TTYPE ST_MyStruct :
STRUCT
    iValue1 : INT;
    iValue2 : UINT;
    iValue3 : BOOL;
    iValue4 : REAL;
END_STRUCT
END_TYPE

Variable Declaration
PROGRAM MAIN
VAR
    fbDBCyclicRdWrt1: FB_DBCyclicRdWrt;
    
    bCyclic         : BOOL :=TRUE;
    
    bBusy_Cyclic    : BOOL;
    bErr            : BOOL;
    nErrID          : UDINT;
    sSQLState       : ST_DBSQLError;
END_VAR

PLC program
DataTypes;

fbDBCyclicRdWrt(
    sNetID   := ,
    bExecute := bCyclic,
    tTimeout := t#15s,
    bBusy     => bBusy_Cyclic,
    bError    => bErr,
    nErrID    => nErrID,
    sSQLState => sSQLState);

In order to use this sample, you only need to transfer the NetID of the ADS device (on which the TwinCAT
Database Server is installed) to the sNetID input.
When you run the program and set the bCyclic variable to TRUE, all the variables that are declared in the
symbol group of the XML configuration file are logged.

TwinCAT Database Server

All Microsoft SQL Compact databases, which are declared in the XML configuration file,
must exist. They are not generated automatically.
Note The declared ASCII files, on the other hand, are generated automatically if they do not ex-
ist.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

7.4 To log a PLC variable with FB_DBWrite

This sample demonstrates logging of a PLC variable from the PLC in a database. The operating principle of
the individual write modes is illustrated.
Download: TcDBSrv_InfoSysSamples.zip

128 Version: 1.4 TC3 Database Server

 Samples

Database type used MS SQL

Compatible database types ASCII, MS SQL, MS Compact SQL, MS Access,
MySQL, PostgreSQL, DB2, Oracle, InterBase/
Firebird, XML
Function blocks used FB_DBWrite
Libraries to be integrated "Tc2_Database", "Tc2_System", "Tc2_Standard"
Download file list TcDBSrv_InfoSysSamples.tszip,
CurrentConfigDataBase.xml, SQLQuery.sql

In order to be able to use this sample, you have to adapt the server name and the authentication in the XML
configuration file (CurrentConfigDataBase.xml). Also, please ensure that no "TestDB" database is present
before executing the SQLQuery.sql script.

Sample configuration:
The variable "eWriteMode" can be used to set the write mode for logging.
The write operation can then be started with a rising edge at the variable "bSTART".

Table assignment:
• ADS_TO_DB_APPEND => eWriteAppend -> "tbl_Append"
• ADS_TO_DB_UPDATE => eWriteUpdate -> "tbl_Update"
• ADS_TO_DB_RINGBUFFER => eWriteRingBuffer -> "tbl_RingBuffer"

TC3 Database Server Version: 1.4 129

Samples

Table structure used

Column name Data type Null permitted Feature
ID Bigint no IDENTITY(1,1)
Timestamp datetime no
Name Ntext no
Value Float no

Variable Declaration
PROGRAM MAIN
VAR
(*Test symbol which will be logged into the different database tables*)
    lrTestValueVar       : LREAL := 123.456;

    eState               : E_SampleState := eIdle;

    R_TRIG1              : R_TRIG;

(*With a rising edge at bStart the FB_DBWrite block will be start once*)
    bSTART               : BOOL;

(*With eWriteMode you can select which FB_DBWrite block will be used*)
    eWriteMode           : E_SampleState := eWriteAppend;

    FB_DBWrite_Append    : FB_DBWrite;
    FB_DBWrite_Update    : FB_DBWrite;
    FB_DBWrite_RingBuffer: FB_DBWrite;

(*Status outputs from the three FB_DBWrite blocks*)

    bBusy                : BOOL;
    bErr                 : BOOL;
    bErrid               : UDINT;
    stSqlstate           : ST_DBSQLError;
END_VAR

Enum E_SampleState
TYPE E_SampleState :(
    eIdle           := 0,
    eWriteAppend    := 1,
    eWriteUpdate    := 2,
    eWriteRingBuffer:= 3
);
END_TYPE

SPS Programm
CASE eState OF
    eIdle :
        R_TRIG1(CLK:=bSTART);
        IF R_TRIG1.Q THEN
            lrTestValueVar  := lrTestValueVar + 1;
            eState          := eWriteMode;
            bSTART          := FALSE;
        END_IF
    (*Add a new record to the table tbl_Append*)
    eWriteAppend :
        FB_DBWrite_Append(
            sNetID          := ,
            hDBID           := 1,
            hAdsID          := 1,
            sVarName        := 'MAIN.lrTestValueVar',
            nIGroup         := ,
            nIOffset        := ,
            nVarSize        := ,
            sVarType        := ,
            sDBVarName      := 'lrTestValueVar',
            eDBWriteMode    := eDBWriteMode_Append,
            tRingBufferTime := ,
            nRingBufferCount:= ,
            bExecute        := TRUE,
            tTimeout        := T#15s,
            bBusy            => bBusy,
            bError           => bErr,
            nErrID           => bErrid,

130 Version: 1.4 TC3 Database Server

 Samples

            sSQLState        => stSqlstate);

        IF NOT bBusy THEN

            FB_DBWrite_Append(bExecute := FALSE);
            eState          := eIdle;
        END_IF
    (*Add a new record to the table tbl_Update if it not exist
     else the existing record will be updated*)
    eWriteUpdate :
        FB_DBWrite_Update(
            sNetID           := ,
            hDBID            := 2,
            hAdsID           := 1,
            sVarName         := 'MAIN.lrTestValueVar',
            nIGroup          := ,
            nIOffset         := ,
            nVarSize         := ,
            sVarType         := ,
            sDBVarName       := 'lrTestValueVar',
            eDBWriteMode     := eDBWriteMode_Update,
            tRingBufferTime  := ,
            nRingBufferCount := ,
            bExecute         := TRUE,
            tTimeout         := T#15s,
            bBusy             => bBusy,
            bError            => bErr,
            nErrID            => bErrid,
            sSQLState         => stSqlstate);

        IF NOT bBusy THEN

            FB_DBWrite_Update(bExecute := FALSE);
            eState           := eIdle;
        END_IF
    (*Add a new record to the table tbl_RingBuffer.
     If the maximum count is reached the records will be deleted in a FIFO process*)
    eWriteRingBuffer :
        FB_DBWrite_RingBuffer(
            sNetID           := ,
            hDBID            := 3,
            hAdsID           := 1,
            sVarName         := 'MAIN.lrTestValueVar',
            nIGroup          := ,
            nIOffset         := ,
            nVarSize         := ,
            sVarType         := ,
            sDBVarName       := 'lrTestValueVar',
            eDBWriteMode     := eDBWriteMode_RingBuffer_Count,
            tRingBufferTime  := ,
            nRingBufferCount := 10,
            bExecute         := TRUE,
            tTimeout         := T#15s,
            bBusy             => bBusy,
            bError            => bErr,
            nErrID            => bErrid,
            sSQLState         => stSqlstate);

        IF NOT bBusy THEN

            FB_DBWrite_RingBuffer(bExecute := FALSE);
            eState           := eIdle;
        END_IF
END_CASE

TwinCAT Database Server

All Microsoft SQL Compact databases, which are declared in the XML configuration file,
must exist. They are not generated automatically.
Note The declared ASCII files, on the other hand, are generated automatically if they do not ex-
ist.

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

TC3 Database Server Version: 1.4 131

Samples

7.5 Example with the FB_DBRecordInsert and

FB_DBRecordSelect block
This sample illustrates logging of several values in a database from the PLC with the function block
FB_DBRecordInsert.
In this sample, several PLC variables are logged in a single data set. In addition, the function block
FB_DBRecordSelect can be used to read a data set from this database.
Download: TcDBSrv_InfoSysSamples.zip

Database type used MS Access

Compatible database types MS SQL, MS Compact SQL, MS Access, MySQL,
PostgreSQL, DB2, Oracle, InterBase/Firebird, XML
Function blocks used FB_DBRecordInsert, FB_DBRecordSelect
Libraries to be integrated "Tc2_Database", "Tc2_System", "Tc2_Standard",
"Tc2_Utilities"
Download file list TcDBSrv_InfoSysSamples.tszip,
CurrentConfigDataBase.xml, TestDB_Access.mdb

The following table structure is used for writing:

Column name Data type

Timestamp datetime
PLC_TestValue1 float
PLC_TestValue2 float
PLC_TestValue3 float
PLC_TestValue4 String

Variable Declaration
(* Declaration *)PROGRAMMAIN
VAR
    eState            : E_SQLStatement;

132 Version: 1.4 TC3 Database Server

 Samples

    NT_GetTime1       : NT_GetTime;
    bTimestart        : BOOL;
    tTime             : TIMESTRUCT;

    FB_FormatStringDateTime: FB_FormatString;
    sDateTimeString   : T_MaxString;

    TestValue1        : REAL := 123.456;

    TestValue2        : REAL := 234.567;
    TestValue3        : REAL := 345.678;
    TestValue4        : STRING(255) := 'No error occurred';

    FB_FormatString1  : FB_FormatString;
    sInsertString     : T_MaxString;
    bError            : BOOL;
    nErrid            : UDINT;

    FB_DBRecordInsert1: FB_DBRecordInsert;
    bStartstopInsert  : BOOL;
    bBusyInsert       : BOOL;
    bErrInsert        : BOOL;
    nErridInsert      : UDINT;
    stSQLStateInsert  : ST_DBSQLError;

    stRecord          : ST_Record;

    FB_DBRecordSelect1: FB_DBRecordSelect;
    nRecIndex         : UDINT := 0;
    bStartstopSelect  : BOOL;
    bBusySelect       : BOOL;
    bErrorSelect      : BOOL;
    nErrIDSelect      : UDINT;
    stSQLStateSelect  : ST_DBSQLError;
    nRecordCount      : UDINT;
END_VAR

Enum E_SQLStatement
TYPEE_SQLStatement:(
    eSQL_INSERT  := 0,
    eSQL_SELECT  := 1
);
END_TYPE

Struct ST_Record
TYPEST_Record :
STRUCT
    Timestamp : DT;
    PLC_Value1: REAL;
    PLC_Value2: REAL;
    PLC_Value3: REAL;
    PLC_Value4: STRING;
END_STRUCT
END_TYPE

PLC program
CASEeState OF
    eSQL_INSERT:
        (*Create the timestamp*)
        NT_GetTime1( START:= bTimestart, TIMESTR=> tTime);
        IF NOT NT_GetTime1.BUSY THEN
            bTimestart:= NOT bTimestart;
        END_IF

        FB_FormatStringDateTime(
            sFormat   := '%D.%D.%D %D:%D:%D',
            arg1      := F_WORD(tTime.wYear),
            arg2      := F_WORD(tTime.wMonth),
            arg3      := F_WORD(tTime.wDay),
            arg4      := F_WORD(tTime.wHour),
            arg5      := F_WORD(tTime.wMinute),
            arg6      := F_WORD(tTime.wSecond),
            sOut       => sDateTimeString);
        
        (*Create the SQL-INSERT command*)

TC3 Database Server Version: 1.4 133

Samples

        FB_FormatString1(
            sFormat   := 'INSERT INTO tbl_Test VALUES($'%S$',%F,%F,%F,$'%S$')',
            arg1      := F_STRING(sDateTimeString),
            arg2      := F_REAL(TestValue1),
            arg3      := F_REAL(TestValue2),
            arg4      := F_REAL(TestValue3),
            arg5      := F_STRING(TestValue4),
            sOut       => sInsertString,
            bError     => bError,
            nErrId     => nErrid);
        
        (*Write the record to the database*)
        FB_DBRecordInsert1(
            sNetID    := ,
            hDBID     := 1,
            sInsertCmd:= sInsertString,
            bExecute  := bStartstopInsert,
            tTimeout  := T#15s,
            bBusy      => bBusyInsert,
            bError     => bErrInsert,
            nErrID     => nErridInsert,
            sSQLState  => stSQLStateInsert);

    eSQL_SELECT:
        (*Read one record from the database*)
        FB_DBRecordSelect1(
            sNetID    := ,
            hDBID     := 1,
            sSelectCmd:= 'SELECT * FROM tbl_Test',
            nRecordIndex:= nRecIndex,
            cbRecordSize:= SIZEOF(stRecord),
            pDestAddr := ADR(stRecord),
            bExecute  := bStartstopSelect,
            tTimeout  := T#15s,
            bBusy      => bBusySelect,
            bError     => bErrorSelect,
            nErrID     => nErrIDSelect,
            sSQLState  => stSQLStateSelect,
            nRecords   => nRecordCount);
END_CASE

To use this sample, you have to declare the Access database "Sample7.mdb" in the XML configuration file.
A data set with the four PLC values and the timestamp is created in the database by generating a positive
edge at the variable "bStartstopInsert".

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

134 Version: 1.4 TC3 Database Server

 Samples

7.6 Stored procedures with

FB_DBStoredProceduresRecordArray
The function block FB_DBStoredProceduresRecordArray can be used to declare parameters as INPUT,
OUTPUT or INOUT and transfer them to the stored procedures. In this way complex SQL commands can be
preprogrammed in the database server and then triggered by the TwinCAT Database Server. In contrast to
the FB_DBStoredProceduresRecordReturn block, this block can be used to return several datasets with a
single call.

Download: TcDBSrv_InfoSysSamples.zip

Database type used MS SQL (MS SQL Server 2008)

Compatible database types MS SQL, MySQL, Oracle
Function blocks used FB_DBStoredProceduresRecordArray
Libraries to be integrated "Tc2_Database", "Tc2_System", "Tc2_Base",
"Tc2_Utilities"
Download file list TcDBSrv_InfoSysSamples.tszip,
CurrentConfigDataBase.xml

The following sample illustrates the call in a simple stored procedure with an input parameter and return data
set. The procedure was created on a Microsoft SQL Server 2008.

Code of the stored procedure SP_GetAddressByCustomerID

REATE PROCEDURE [SP_GetAddressByCustomerID]
    @Customer_ID bigint
AS
BEGIN
    SELECT tbl_Customer.ID, tbl_Customer.Name, tbl_Customer.Customer, tbl_Products.SerNum,
     tbl_Products.Product, tbl_Products.Info, tbl_Pos.Timestamp
    FROM

TC3 Database Server Version: 1.4 135

Samples

        tbl_Pos JOIN tbl_Customer ON tbl_Pos.CustomerNum = tbl_Customer.ID

        JOIN tbl_Products ON tbl_Pos.ProductNum = tbl_Products.SerNum
    WHERE
        tbl_Pos.CustomerNum = @Customer_ID;
END

Variable declaration in the PLC

PROGRAMMAIN
VAR
    R_TRIG1          : R_TRIG;
    bREAD            : BOOL := FALSE;

    nState           : BYTE;

    arrParaList      : ARRAY [0..0] OF ST_DBParameter;

    nCustomerID      : DINT := 12345;

    FB_DBStoredProceduresRecordArray1: FB_DBStoredProceduresRecordArray;

    nCustomerID: DINT:= 12345;

    nRecordStartIndex: UDINT;
    stRecordArr      : ARRAY [1..25] OF ST_Record;
    nRecs            : UDINT;

    bBusy            : BOOL;
    bErr             : BOOL;
    nErrid           : UDINT;
    stSqlstate       : ST_DBSQLError;
END_VAR

Dataset structure in the PLC (ST_Record)

TYPE ST_Record :
STRUCT
    nID         : T_ULARGE_INTEGER;
    sCustomer   : STRING(50);
    sName       : STRING(50);
    nProductNum : DINT;
    sProductName: STRING(50);
    sProductInfo: T_MaxString;
    tTimestamp  : DT;
END_STRUCT
END_TYPE

PLC program
R_TRIG1(CLK:=bREAD);
IF R_TRIG1.Q AND NOT bBusy THEN
    nState := 1;
END_IF

CASE nState OF
0:
     ;
1:(*Init of the parameters*)
     arrParaList[0].sParameterName    := '@Customer_ID';
     arrParaList[0].eParameterDataType:= eDBColumn_Integer;
     arrParaList[0].eParameterType    := eDBParameter_Input;
     arrParaList[0].cbParameterValue  := SIZEOF(nCustomerID);
     arrParaList[0].pParameterValue   := ADR(nCustomerID);

     nState := 2;
2:(*Start the stored procedure "SP_GetCustomerPosition"*)
     FB_DBStoredProceduresRecordArray1(
         sNetID:= ,
         hDBID:= 1,
         sProcedureName   := 'SP_GetCustomerPositions',
         cbParameterList  := SIZEOF(arrParaList),
         pParameterList   := ADR(arrParaList),
         nStartIndex      := nRecordStartIndex,
         nRecordCount     := 25,
         cbRecordArraySize:= SIZEOF(stRecordArr),
         pDestAddr        := ADR(stRecordArr),
         bExecute         := TRUE,
         tTimeout         := T#15s,
         bBusy             => bBusy,

136 Version: 1.4 TC3 Database Server

 Samples

         bError            => bErr,

         nErrID            => nErrid,
         sSQLState         => stSqlstate,
         nRecords          => nRecs);
        
     IF NOT bBusy THEN
         FB_DBStoredProceduresRecordReturn1(bExecute:= FALSE);
         nState := 0;
     END_IF
END_CASE

Visualization

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

7.7 Use XML as Database

The TwinCAT Database Server provides the ability to use an XML file as a database. Apart from the Stored
Procedure functions, the XML database type supports all known function blocks for reading and writing in a
database. Even SQL commands that can be issued with the function blocks FB_DBRecordInsert or
FB_DBRecordSelect are interpreted by the TwinCAT Database Server and applied to the XML file.

This sample demonstrates how an XML database is created, filled with the block FB_DBWrite and
subsequently read with an SQL SELECT command and the FB_DBRecordSelect block.

Download: TcDBSrv_InfoSysSamples.zip

TC3 Database Server Version: 1.4 137

Samples

Database type used XML

Compatible database types MS SQL, MS Compact SQL, MS Access, XML
Function blocks used FB_DBCreate, FB_DBConnectionAdd,
FB_DBTableCreate, FB_DBWrite,
FB_DBRecordSelect
Libraries to be integrated "Tc2_Database", "Tc2_System", "Tc2_Standard",
"Tc2_Utilities"
Download file list TcDBSrv_InfoSysSamples.tszip

MAIN program
PROGRAM MAIN
VAR
    nState             :BYTE := 0;

    R_TRIG1            : R_TRIG;
    bSTART             : BOOL;

    nCounter           : INT;

    FB_FileDelete1     : FB_FileDelete;
    FB_DBCreate1       : FB_DBCreate;
    FB_DBConnectionAdd1: FB_DBConnectionAdd;

138 Version: 1.4 TC3 Database Server

 Samples

    FB_DBTableCreate1  : FB_DBTableCreate;
    FB_DBWrite1        : FB_DBWrite;
    FB_DBRecordSelect1 : FB_DBRecordSelect;

    bBusy_Delete       : BOOL;
    bBusy_CreateDB     : BOOL;
    bBusy_ConnAdd      : BOOL;
    bBusy_CreateTable  : BOOL;
    bBusy_WriteDB      : BOOL;
    bBusy_SelectRecord : BOOL;

    bErr               : BOOL;
    nErrid             : UDINT;
    stSQLState         : ST_DBSQLError;
    nRecs              : UDINT;

    nDBid              : UDINT;

    arrTablestrc       : ARRAY [0..3] OF ST_DBColumnCfg :=

     [(sColumnName:='ID',sColumnProperty:='IDENTITY(1,1)',eColumnType:=EDBCOLUMN_BIGINT),
     (sColumnName:='Timestamp',eColumnType:=EDBCOLUMN_DATETIME),
     (sColumnName:='Name',sColumnProperty:='80',eColumnType:=EDBCOLUMN_NTEXT),
     (sColumnName:='Value',eColumnType:=EDBCOLUMN_FLOAT)];

    rTestValue         : LREAL := 1234.56789;

    stRecord           : ST_Record;
END_VAR
CASE nState OF
    0:
        (*To start this sample you have to set a rising edge to the variable bSTART*)
        R_TRIG1(CLK:=bSTART);
        IF R_TRIG1.Q THEN
            nState  := 1;
            FB_FileDelete1(bExecute:=FALSE);
            FB_DBCreate1(bExecute:=FALSE);
            FB_DBConnectionAdd1(bExecute:=FALSE);
            FB_DBTableCreate1(bExecute:=FALSE);
            FB_DBWrite1(bExecute:=FALSE);
            FB_DBRecordSelect1(bExecute:=FALSE);
            bSTART  := FALSE;
            nCounter:= 0;
        END_IF
    1:
        (*It isn't possible to overwrite an existing database file.
         If the database file exist the FB_FileDelete block will delete the file*)
        FB_FileDelete1(
            sNetId   := ,
            sPathName:= 'C:\TwinCAT\TcDatabaseSrv\Samples\XMLTestDB.xml',
            ePath    := PATH_GENERIC,
            bExecute := TRUE,
            tTimeout := T#5s,
            bBusy     => bBusy_Delete,
            bError    => ,
            nErrId    => );

        IF NOT bBusy_Delete THEN

            nState   := 10;
        END_IF
    10:
        (*It isn't possible to overwrite an existing database file.
         If the database file exist the FB_FileDelete block will delete the file*)
        FB_FileDelete1(
            sNetId   := ,
            sPathName:= 'C:\TwinCAT\TcDatabaseSrv\Samples\XMLTestDB.xsd',
            ePath    := PATH_GENERIC,
            bExecute := TRUE,
            tTimeout := T#5s,
            bBusy     => bBusy_Delete,
            bError    => ,
            nErrId    => );

        IF NOT bBusy_Delete THEN

            FB_FileDelete1(bExecute:=FALSE);
            nState   := 2;
        END_IF
    2:
        (*The FB_DBCreate block will create the database file
         "C:\TwinCAT\TcDatabaseSrv\Samples\XMLTestDB.xml" and
         C:\TwinCAT\TcDatabaseSrv\Samples\XMLTestDB.xsd "*)
        FB_DBCreate1(

TC3 Database Server Version: 1.4 139

Samples

            sNetID   := ,
            sPathName:= 'C:\TwinCAT\TcDatabaseSrv\Samples',
            sDBName  := 'XMLTestDB',
            eDBType  := eDBType_XML,
            bExecute := TRUE,
            tTimeout := T#15s,
            bBusy     => bBusy_CreateDB,
            bError    => bErr,
            nErrID    => nErrid);

        IF NOT bBusy_CreateDB AND NOT bErr THEN

            nState   := 3;
        END_IF
    3:
        (*The FB_DBConnectionAdd adds the connection information to the
         XML configuration file*)
        (*ATTENTION: Each database type has his own connection information*)
        FB_DBConnectionAdd1(
            sNetID      := ,
            eDBType     := eDBType_XML,
            eDBValueType:= eDBValue_Double,
            sDBServer   := 'XMLTestDB',
            sDBProvider := ,
            sDBUrl      := 'C:\TwinCAT\TcDatabaseSrv\Samples\XMLTestDB.xml',
            sDBTable    := 'myTable',
            bExecute    := TRUE,
            tTimeout    := T#15s,
            bBusy        => bBusy_ConnAdd,
            bError       => bErr,
            nErrID       => nErrid,
            hDBID        => nDBid);

        IF NOT bBusy_ConnAdd AND NOT bErr THEN

            nState := 4;
        END_IF
    4:
        (*The FB_DBTableCreate create the table "myTable"*)
        FB_DBTableCreate1(
            sNetID      := ,
            hDBID       := nDBid,
            sTableName  := 'myTable',
            cbTableCfg  := SIZEOF(arrTablestrc),
            pTableCfg   := ADR(arrTablestrc),
            bExecute    := TRUE,
            tTimeout    := T#15s,
            bBusy        => bBusy_CreateTable,
            bError       => bErr,
            nErrID       => nErrid);

        IF NOTbBusy_CreateTable AND NOT bErr THEN

            nState      := 5;
        END_IF
    5:
        (*The FB_DBWrite write five times the value of the plc variable "rTestValue" to
         the database table "myTable"*)
        FB_DBWrite1(
            sNetID          := ,
            hDBID           := nDBid,
            hAdsID          := 1,
            sVarName        := 'MAIN.rTestValue',
            nIGroup         := ,
            nIOffset        := ,
            nVarSize        := ,
            sVarType        := ,
            sDBVarName      := 'rTestValue',
            eDBWriteMode    := eDBWriteMode_Append,
            tRingBufferTime := ,
            nRingBufferCount:= ,
            bExecute        := TRUE,
            tTimeout        := T#15s,
            bBusy            => bBusy_WriteDB,
            bError           => bErr,
            nErrID           => nErrid,
            sSQLState        => stSQLState);

        IF NOT bBusy_WriteDB AND NOT bErr THEN

            FB_DBWrite1(bExecute := FALSE);
            nCounter        := nCounter + 1;
            IFnCounter = 5 THEN
                nState     := 6;

140 Version: 1.4 TC3 Database Server

 Samples

            END_IF
        END_IF
    6:
        (*The FB_DBRecordSelect select one record of the database table "myTable""*)
        FB_DBRecordSelect1(
            sNetID          := ,
            hDBID           := nDBid,
            sSelectCmd      := 'SELECT * FROM myTable WHERE Name = $'rTestValue$'',
            nRecordIndex    := 0,
            cbRecordSize    := SIZEOF(stRecord),
            pDestAddr       := ADR(stRecord),
            bExecute        := TRUE,
            tTimeout        := T#15s,
            bBusy            => bBusy_SelectRecord,
            bError           => bErr,
            nErrID           => nErrid,
            sSQLState        => stSQLState,
            nRecords         => nRecs);

        IF NOT bBusy_SelectRecord AND NOT bErr THEN

            nState          := 0;
        END_IF
    END_CASE

The process is started with a positive edge at the toggle variable bSTART.

The following files are created:

XMLTestDB.xml (XML database file)

<?xmlversion="1.0"encoding="UTF-8"?>
<XMLTestDBxmlns:xs="http://www.w3.org/2001/XMLSchema-instance"xs:noNamespaceSchemaLocation="XML-
TestDB.xsd">
  <myTable>
    <rowID="1"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
    <rowID="2"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
    <rowID="3"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
    <rowID="4"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
    <rowID="5"Timestamp="2012-05-10T13:48:47"Name="rTestValue"Value="1234.56789" />
  </myTable>
</XMLTestDB>

XMLTestDB.xsd (XML Schema)

<?xmlversion="1.0"?>
<xsd:schemaxmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:simpleTypename="bigint">
    <xsd:restrictionbase="xsd:long" />
  </xsd:simpleType>
  <xsd:simpleTypename="datetime">
    <xsd:restrictionbase="xsd:dateTime" />
  </xsd:simpleType>
  <xsd:simpleTypename="ntext_80">
    <xsd:restrictionbase="xsd:string">
      <xsd:maxLengthvalue="80" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:simpleTypename="float">
    <xsd:restrictionbase="xsd:double" />
  </xsd:simpleType>
  <xsd:complexTypename="myTable_Type">
    <xsd:sequence>
      <xsd:elementminOccurs="0"maxOccurs="unbounded"name="row">
        <xsd:complexType>
          <xsd:attributename="ID"type="bigint" />
          <xsd:attributename="Timestamp"type="datetime" />
          <xsd:attributename="Name"type="ntext_80" />
          <xsd:attributename="Value" type="float" />
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:elementname="XMLTestDB">
    <xsd:complexType>
      <xsd:sequenceminOccurs="1"maxOccurs="1">
        <xsd:elementname="myTable"type="myTable_Type" />
      </xsd:sequence>

TC3 Database Server Version: 1.4 141

Samples

    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

7.8 XML Database - XPath Sample to demonstrate the

different SELECT types
The function block FB_DBRecordArraySelect/FB_DBRecordSelect can be used to issue XPath commands
and read XML tags from any XML file. This sample demonstrates how different entries can be read from
XML files with the aid of the TwinCAT Database Server. Individual tags, subtags and reading of attributes is
supported, and these are displayed.

Download: TcDBSrv_InfoSysSamples.zip

Database type used XML

Compatible database types XML
Function blocks used FB_DBRecordArraySelect
Libraries to be integrated "Tc2_Database", "Tc2_System", "Tc2_Standard",
"Tc2_Utilities"
Download file list TcDBSrv_InfoSysSamples.tszip

Sample XML file (XMLFactoryXY.xml)

<?xmlversion="1.0" encoding="utf-8" ?>
<Factory_XY>
<Name>Sample Factory XY</Name>
<Factory_Info>
<Street>Samplestreet 25</Street>
<City>33415 Verl</City>
<Country>Germany</Country>
<Office_Count>1</Office_Count>

142 Version: 1.4 TC3 Database Server

 Samples

<Employe_Count>6</Employe_Count>
<Manager>Max Mustermann</Manager>
</Factory_Info>
<Employees>
<Employeeid="10001" name="Julia Kingston" department="Development" position="Worker"
hired="2001-08-01" />
<Employeeid="10002" name="Jens Marx" department="Import" position="Worker" hired="2003-08-01" />
<Employeeid="10003" name="Justus Kaiser" department="Export" position="Worker" hired="2003-08-01" />
<Employeeid="10004" name="Marc Klein" department="Production" position="Worker" hired="2005-08-01" /
>
<Employeeid="10005" name="Matt Bloomberg" department="Production" position="Worker"
hired="2005-08-01" />
<Employeeid="10006" name="Frida Hundt" department="Production" position="Worker"
hired="2010-08-01" />
</Employees>
</Factory_XY>

ST_FactoryInfo structure
TYPEST_FactoryInfo :
STRUCT
sStreet       : T_MaxString;
sCity         : T_MaxString;
sCountry      : T_MaxString;
sOffice_Count : T_MaxString;
sEmploye_Count: T_MaxString;
sManager      : T_MaxString;
END_STRUCT
END_TYPE

ST_Employee structure
TYPEST_Employee :
STRUCT
sID          : T_MaxString;
sName        : T_MaxString;
sDepartment  : T_MaxString;
sPosition    : T_MaxString;
sHired       : T_MaxString;
END_STRUCT
END_TYPE

MAIN program
PROGRAMMAIN
VAR
     bSTART    : BOOL;
     R_TRIG1   : R_TRIG;
    
     nState    : INT;

     sXPath    : T_MaxString;

    
     fbDBRecordArraySelect : FB_DBRecordArraySelect;
    
     bBusy_ReadFactoryName : BOOL;
     bError_ReadFactoryName: BOOL;
     nErrID_ReadFactoryName: UDINT;
    
     bBusy_ReadFactoryInfo : BOOL;
     bError_ReadFactoryInfo: BOOL;
     nErrID_ReadFactoryInfo: UDINT;
    
     bBusy_ReadEmployee    : BOOL;
     bError_ReadEmployee   : BOOL;
     nErrID_ReadEmployee   : UDINT;
    
     stSQLState            : ST_DBSQLError;
    
     sFactoryName          : T_MaxString;
     stFactoryInfo         : ST_FactoryInfo;
     aEmployees            : ARRAY [1..10] OF ST_Employee;
END_VAR

R_TRIG1(CLK:=bSTART);
IF R_TRIG1.Q THEN
     bSTART:=FALSE;
     fbDBRecordArraySelect(bExecute:=FALSE);

TC3 Database Server Version: 1.4 143

Samples

     nState:=1;
END_IFCASE nState OF
     0://IDLE
         ;
     1://Read Factory Name
         sXPath:= 'XPATH#Factory_XY/Name';
         fbDBRecordArraySelect(
             sNetID           := ,
             hDBID            := 7,
             pCmdAddr         := ADR(sXPath),
             cbCmdSize        := SIZEOF(sXPath),
             nStartIndex      := 0,
             nRecordCount     := 1,
             pDestAddr        := ADR(sFactoryName),
             cbRecordArraySize:= SIZEOF(sFactoryName),
             bExecute         := TRUE,
             tTimeout         := T#15S,
             bBusy             => bBusy_ReadFactoryName,
             bError            => bError_ReadFactoryName,
             nErrID            => nErrID_ReadFactoryName,
             sSQLState         => stSQLState,
             nRecords          => );
            
         IF NOT bBusy_ReadFactoryName THEN
             fbDBRecordArraySelect(bExecute:=FALSE);
             IF NOT bError_ReadFactoryName THEN
                 nState        :=2;
             ELSE
                 nState        :=255;
             END_IFEND_IF
     2://Read Factory Info
         sXPath                 := 'XPATH#Factory_XY/Factory_Info';
         fbDBRecordArraySelect(
             sNetID            := ,
             hDBID             := 7,
             pCmdAddr          := ADR(sXPath),
             cbCmdSize         := SIZEOF(sXPath),
             nStartIndex       := 0,
             nRecordCount      := 1,
             pDestAddr         := ADR(stFactoryInfo),
             cbRecordArraySize := SIZEOF(stFactoryInfo),
             bExecute          := TRUE,
             tTimeout          := T#15S,
             bBusy              => bBusy_ReadFactoryInfo,
             bError             => bError_ReadFactoryInfo,
             nErrID             => nErrID_ReadFactoryInfo,
             sSQLState          => stSQLState,
             nRecords           => );
            
         IF NOT bBusy_ReadFactoryInfo THEN
             fbDBRecordArraySelect(bExecute:=FALSE);
             IF NOT bError_ReadFactoryInfo THEN
                 nState         :=3;
             ELSE
                 nState         :=255;
             END_IF
         END_IF
     3://Read Employees
         sXPath                  := 'XPATH#Factory_XY/Employees/Employee';
         fbDBRecordArraySelect(
             sNetID             := ,
             hDBID              := 7,
             pCmdAddr           := ADR(sXPath),
             cbCmdSize          := SIZEOF(sXPath),
             nStartIndex        := 0,
             nRecordCount       := 10,
             pDestAddr          := ADR(aEmployees),
             cbRecordArraySize  := SIZEOF(aEmployees),
             bExecute           := TRUE,
             tTimeout           := T#15S,
             bBusy               => bBusy_ReadEmployee,
             bError              => bError_ReadEmployee,
             nErrID              => nErrID_ReadEmployee,
             sSQLState           => stSQLState,
             nRecords            => );
            
         IF NOT bBusy_ReadEmployee THEN
             fbDBRecordArraySelect(bExecute:=FALSE);
             IF NOT bError_ReadEmployee THEN
                 nState          :=0;

144 Version: 1.4 TC3 Database Server

 Samples

             ELSE
                 nState          :=255;
             END_IFEND_IF
     255://Error State
         ;
END_CASE

A positive edge at the variable "bStart" triggers issuing of the XPath commands and reading of the individual
elements from the XML file. The results will then be in the variables "sFactoryName", "stFactoryInfo" and
"aEmployees".

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

7.9 XML Database - XPath sample with XML-Schema

The function blocks FB_DBRecordSelect or FB_DBRecordArraySelect can be used to issue XPath
commands and to read XML tags, XML subtags or XML attributes from any XML file. If a suitable XML
schema exists for the XML file to be read, the content of the tags or attributes is converted to the
corresponding data types, as defined in the schema.

Further information about XML schemas can be found here: http://www.edition-w3.de/TR/2001/REC-

xmlschema-0-20010502/

In this sample, FB_DBRecordArraySelect is used to read two different subtags from an XML file with
corresponding XML schema.

Download: TcDBSrv_InfoSysSamples.zip

TC3 Database Server Version: 1.4 145

Samples

Database type used XML

Compatible database types XML
Function blocks used FB_DBRecordSelect
Libraries to be integrated "Tc2_Database", "Tc2_System", "Tc2_Standard",
"Tc2_Utilities"
Download file list TcDBSrv_InfoSysSamples.tszip,
CurrentConfigDatabase.xml, PLC_Structs.xml,
PLC_Structs.xsd

Sample XML file (PLC_Structs.xml)

<? xml version = "1.0"encoding="utf-8"?>
< Beckhoff_PLC >
< PLC_Structs >
< PLC_Struct Name = "ST_TestStruct">
< Struct Instance = "1">
< nINT64 > 123456789</nINT64>
< nUINT16 > 1234</nUINT16>
< rREAL64 > 1234.5678</rREAL64>
< sSTRING > This is instance one of ST_TestStruct</sSTRING>
< bBOOL > true</bBOOL>
< nINT32 > -100</nINT32>
</ Struct >
< Struct Instance = "2">
< nINT64 > 234567890</nINT64>
< nUINT16 > 2345</nUINT16>
< rREAL64 > 234.56789</rREAL64>
< sSTRING > This is instance two of ST_TestStruct</sSTRING>
< bBOOL > false</bBOOL>
< nINT32 > -50</nINT32>
</ Struct >
< < < Struct Instance = "3">
< nINT64 > 345678901</nINT64>
< nUINT16 > 3456</nUINT16>
< rREAL64 > 3456.78901</rREAL64>
< sSTRING > This is instance three of ST_TestStruct</sSTRING>
< bBOOL > true</bBOOL>
< nINT32 > -150</nINT32>
</ Struct >
</ PLC_Struct >
< PLC_Struct Name = "ST_TestStruct2">
< Struct2 Instance = "1">
< sSTRING > This is instance one of ST_TestStruct2</sSTRING>
< bBOOL > false</bBOOL>
< nINT32 > -88</nINT32>
</ Struct2 >
< Struct2 Instance = "2">
< sSTRING > This is instance two of ST_TestStruct2</sSTRING>
< bBOOL > true</bBOOL>
< nINT32 > -9</nINT32>
</ Struct2 >
</ PLC_Struct >
</ PLC_Structs >
</ Beckhoff_PLC>

Corresponding XML schema (PLC_Structs.xsd)

<? xmlversion="1.0" encoding="utf-8"?>
< xs:schemaattributeFormDefault="unqualified" elementFormDefault="qualified"xmlns:xs="http://
www.w3.org/2001/XMLSchema">
< xs:elementname="Beckhoff_PLC">
< xs:complexType >
< xs:sequence >
< xs:element name = "PLC_Structs">
< xs:complexType >
< xs:sequence >
< xs:element maxOccurs = "unbounded"name="PLC_Struct">
< xs:complexType >
< xs:sequence >
< xs:element minOccurs = "0"maxOccurs="unbounded"name="Struct">
< xs:complexType >
< xs:sequence >
< xs:element name = "nINT64"type="xs:long" />
< xs:element name = "nUINT16"type="xs:unsignedShort" />
< xs:element name = "rREAL64"type="xs:double" />

146 Version: 1.4 TC3 Database Server

 Samples

< xs:element name = "sSTRING"type="xs:string" />

< xs:element name = "bBOOL" type="xs:boolean" />
< xs:element name = "nINT32"type="xs:int" />
</ xs:sequence >
< xs:attribute name = "Instance"type="xs:unsignedByte"use="required" />
</ xs:complexType >
</ xs:element >
< xs:element minOccurs = "0"maxOccurs="unbounded"name="Struct2">
< xs:complexType >
< xs:sequence >
< xs:element name = "sSTRING"type="xs:string" />
< xs:element name = "bBOOL" type="xs:boolean" />
< xs:element name = "nINT32"type="xs:int" />
</ xs:sequence >
< xs:attribute name = "Instance"type="xs:unsignedByte"use="required" />
</ xs:complexType >
</ xs:element >
</ xs:sequence >
< xs:attribute name = "Name"type="xs:string"use="required" />
</ xs:complexType >
</ xs:element >
</ xs:sequence >
</ xs:complexType >
</ xs:element >
</ xs:sequence >
</ xs:complexType>
</ xs:element>
</ xs:schema>

Structure 1 ST_TestStruct
TYPE ST_TestStruct :
STRUCT
    nINT64 : T_LARGE_INTEGER;
    nUINT16: UINT;
    rREAL64: LREAL;
    sSTRING: T_MaxString;
    bBOOL  : BOOL;
    nINT32 : DINT;
END_STRUCT
END_TYPE

Structure 2 ST_TestStruct2
TYPE ST_TestStruct2 :
STRUCT
    sSTRING: T_MaxString;
    bBOOL  : BOOL;
    nINT32 : DINT;
END_STRUCT
END_TYPE

MAIN program
PROGRAMMAIN
VAR
nState                 : BYTE;

R_TRIG1                : R_TRIG;
bStartStop             : BOOL;

sCmd                   : T_MaxString;

FB_DBRecordArraySelect1: FB_DBRecordArraySelect;
arrTestStruct          : ARRAY [0..3] OF ST_TestStruct;
arrTestStruct2         : ARRAY [0..3] OF ST_TestStruct2;

bBusy                  : BOOL;
bError                 : BOOL;
nErrID                 : UDINT;
stSQLState             : ST_DBSQLError;

nRecs1                 : UDINT;
nRecs2                 : UDINT;
END_VAR

TC3 Database Server Version: 1.4 147

Samples

R_TRIG1(CLK:=bStartStop);
IF R_TRIG1.Q THEN
FB_DBRecordArraySelect1(bExecute:=FALSE);
nState                 := 1;
END_IF
CASE nState OF
0:(*Idle*)
;
1:
sCmd:='XPATH<SUBTAG>#/Beckhoff_PLC/PLC_Structs/PLC_Struct[@Name=$'ST_TestStruct$']/Struct';

FB_DBRecordArraySelect1(
     sNetID            := ,
     hDBID             := 1,
     cbCmdSize         := SIZEOF(sCmd),
     pCmdAddr          := ADR(sCmd),
     nStartIndex       := 0,
     nRecordCount      := 4,
     cbRecordArraySize := SIZEOF(arrTestStruct),
     pDestAddr         := ADR(arrTestStruct),
     bExecute          := TRUE,
     tTimeout          := T#15s,
     bBusy              => bBusy,
     bError             => bError,
     nErrID             => nErrID,
     sSQLState          => stSQLState,
     nRecords           => nRecs1);

IF NOT bBusy THEN

    FB_DBRecordArraySelect1(bExecute:=FALSE);
    IF NOT bError THEN
     nState            := 2;
    ELSE
     nState            := 255;
    END_IFEND_IF
2:
sCmd:='XPATH<SUBTAG>#Beckhoff_PLC/PLC_Structs/PLC_Struct[@Name=$'ST_TestStruct2$']/Struct2';

FB_DBRecordArraySelect1(
     sNetID            := ,
     hDBID             := 1,
     cbCmdSize         := SIZEOF(sCmd),
     pCmdAddr          := ADR(sCmd),
     nStartIndex       := 0,
     nRecordCount      := 4,
     cbRecordArraySize := SIZEOF(arrTestStruct2),
     pDestAddr         := ADR(arrTestStruct2),
     bExecute          := TRUE,
     tTimeout          := T#15s,
     bBusy              => bBusy,
     bError             => bError,
     nErrID             => nErrID,
     sSQLState          => stSQLState,
     nRecords           => nRecs2);

IF NOT bBusy THEN

    FB_DBRecordArraySelect1(bExecute:=FALSE);
    IF NOT bError THEN
     nState            := 0;
    ELSE
     nState            := 255;
    END_IFEND_IF
255: (* Error Step*)
;
END_CASE

Reading is started with a positive edge at the toggle variable bStartStop.

148 Version: 1.4 TC3 Database Server

 Samples

Requirements
Development environment Target system type PLC libraries to be linked
TwinCAT v3.0.0 PC or CX (x86) Tc2_Database

TC3 Database Server Version: 1.4 149

Appendix

8 Appendix

8.1 Errorcodes

8.1.1 ADS Return Codes

Error codes: 0x000 [} 150]..., 0x500 [} 151]..., 0x700 [} 152]..., 0x1000 [} 154]...

Global Error Codes

Hex Dec Description
0x0 0 no error
0x1 1 Internal error
0x2 2 No Rtime
0x3 3 Allocation locked memory error
0x4 4 Insert mailbox error
0x5 5 Wrong receive HMSG
0x6 6 target port not found
0x7 7 target machine not found
0x8 8 Unknown command ID
0x9 9 Bad task ID
0xA 10 No IO
0xB 11 Unknown ADS command
0xC 12 Win 32 error
0xD 13 Port not connected
0xE 14 Invalid ADS length
0xF 15 Invalid ADS Net ID
0x10 16 Low Installation level
0x11 17 No debug available
0x12 18 Port disabled
0x13 19 Port already connected
0x14 20 ADS Sync Win32 error
0x15 21 ADS Sync Timeout
0x16 22 ADS Sync AMS error
0x17 23 ADS Sync no index map
0x18 24 Invalid ADS port
0x19 25 No memory
0x1A 26 TCP send error
0x1B 27 Host unreachable
0x1C 28 Invalid AMS fragment

150 Version: 1.4 TC3 Database Server

 Appendix

Router Error Codes

Hex Dec Name Description
0x500 1280 ROUTERERR_NOLOCKEDMEMORY No locked memory can be allocated
0x501 1281 ROUTERERR_RESIZEMEMORY The size of the router memory could not be changed
0x502 1282 ROUTERERR_MAILBOXFULL The mailbox has reached the maximum number of possible
messages. The current sent message was rejected
0x503 1283 ROUTERERR_DEBUGBOXFULL The mailbox has reached the maximum number of possible
messages.
The sent message will not be displayed in the debug monitor
0x504 1284 ROUTERERR_UNKNOWNPORTTYPE Unknown port type
0x505 1285 ROUTERERR_NOTINITIALIZED Router is not initialized
0x506 1286 ROUTERERR_PORTALREADYINUSE The desired port number is already assigned
0x507 1287 ROUTERERR_NOTREGISTERED Port not registered
0x508 1288 ROUTERERR_NOMOREQUEUES The maximum number of Ports reached
0x509 1289 ROUTERERR_INVALIDPORT Invalid port
0x50A 1290 ROUTERERR_NOTACTIVATED TwinCAT Router not active

TC3 Database Server Version: 1.4 151

Appendix

General ADS Error Codes

152 Version: 1.4 TC3 Database Server

 Appendix

Hex Dec Name Description

0x700 1792 ADSERR_DEVICE_ERROR error class <device error>
0x701 1793 ADSERR_DEVICE_SRVNOTSUPP Service is not supported by server
0x702 1794 ADSERR_DEVICE_INVALIDGRP invalid index group
0x703 1795 ADSERR_DEVICE_INVALIDOFFSET invalid index offset
0x704 1796 ADSERR_DEVICE_INVALIDACCESS reading/writing not permitted
0x705 1797 ADSERR_DEVICE_INVALIDSIZE parameter size not correct
0x706 1798 ADSERR_DEVICE_INVALIDDATA invalid parameter value(s)
0x707 1799 ADSERR_DEVICE_NOTREADY device is not in a ready state
0x708 1800 ADSERR_DEVICE_BUSY device is busy
0x709 1801 ADSERR_DEVICE_INVALIDCONTEXT invalid context (must be in Windows)
0x70A 1802 ADSERR_DEVICE_NOMEMORY out of memory
0x70B 1803 ADSERR_DEVICE_INVALIDPARM invalid parameter value(s)
0x70C 1804 ADSERR_DEVICE_NOTFOUND not found (files, ...)
0x70D 1805 ADSERR_DEVICE_SYNTAX syntax error in command or file
0x70E 1806 ADSERR_DEVICE_INCOMPATIBLE objects do not match
0x70F 1807 ADSERR_DEVICE_EXISTS object already exists
0x710 1808 ADSERR_DEVICE_SYMBOLNOTFOUND symbol not found
0x711 1809 ADSERR_DEVICE_SYMBOLVERSIONINVAL symbol version invalid

0x712 1810 ADSERR_DEVICE_INVALIDSTATE server is in invalid state

0x713 1811 ADSERR_DEVICE_TRANSMODENOTSUPP AdsTransMode not supported
0x714 1812 ADSERR_DEVICE_NOTIFYHNDINVALID Notification handle is invalid

0x715 1813 ADSERR_DEVICE_CLIENTUNKNOWN Notification client not registered

0x716 1814 ADSERR_DEVICE_NOMOREHDLS no more notification handles
0x717 1815 ADSERR_DEVICE_INVALIDWATCHSIZE size for watch too big
0x718 1816 ADSERR_DEVICE_NOTINIT device not initialized
0x719 1817 ADSERR_DEVICE_TIMEOUT device has a timeout
0x71A 1818 ADSERR_DEVICE_NOINTERFACE query interface failed
0x71B 1819 ADSERR_DEVICE_INVALIDINTERFACE wrong interface required
0x71C 1820 ADSERR_DEVICE_INVALIDCLSID class ID is invalid
0x71D 1821 ADSERR_DEVICE_INVALIDOBJID object ID is invalid
0x71E 1822 ADSERR_DEVICE_PENDING request is pending
0x71F 1823 ADSERR_DEVICE_ABORTED request is aborted
0x720 1824 ADSERR_DEVICE_WARNING signal warning
0x721 1825 ADSERR_DEVICE_INVALIDARRAYIDX invalid array index
0x722 1826 ADSERR_DEVICE_SYMBOLNOTACTIVE symbol not active
0x723 1827 ADSERR_DEVICE_ACCESSDENIED access denied
0x724 1828 ADSERR_DEVICE_LICENSENOTFOUND missing license
0x725 1829 ADSERR_DEVICE_LICENSEEXPIRED license expired
0x726 1830 ADSERR_DEVICE_LICENSEEXCEEDED license exceeded
0x727 1831 ADSERR_DEVICE_LICENSEINVALID license invalid
0x728 1832 ADSERR_DEVICE_LICENSESYSTEMID license invalid system id
0x729 1833 ADSERR_DEVICE_LICENSENOTIMELIMIT license not time limited
0x72A 1834 ADSERR_DEVICE_LICENSEFUTUREISSUE license issue time in the future
0x72B 1835 ADSERR_DEVICE_LICENSETIMETOLONG license time period to long
0x72c 1836 ADSERR_DEVICE_EXCEPTION exception occured during system start
0x72D 1837 ADSERR_DEVICE_LICENSEDUPLICATED License file read twice
0x72E 1838 ADSERR_DEVICE_SIGNATUREINVALID invalid signature
0x72F 1839 ADSERR_DEVICE_CERTIFICATEINVALID public key certificate
0x740 1856 ADSERR_CLIENT_ERROR Error class <client error>
0x741 1857 ADSERR_CLIENT_INVALIDPARM invalid parameter at service
0x742 1858 ADSERR_CLIENT_LISTEMPTY polling list is empty
0x743 1859 ADSERR_CLIENT_VARUSED var connection already in use
0x744 1860 ADSERR_CLIENT_DUPLINVOKEID invoke ID in use
0x745 1861 ADSERR_CLIENT_SYNCTIMEOUT timeout elapsed
0x746 1862 ADSERR_CLIENT_W32ERROR error in win32 subsystem
0x747 1863 ADSERR_CLIENT_TIMEOUTINVALID Invalid client timeout value

TC3 Database Server Version: 1.4 153

Appendix

Hex Dec Name Description

0x748 1864 ADSERR_CLIENT_PORTNOTOPEN ads-port not opened
0x750 1872 ADSERR_CLIENT_NOAMSADDR internal error in ads sync
0x751 1873 ADSERR_CLIENT_SYNCINTERNAL hash table overflow
0x752 1874 ADSERR_CLIENT_ADDHASH key not found in hash
0x753 1875 ADSERR_CLIENT_REMOVEHASH no more symbols in cache
0x754 1876 ADSERR_CLIENT_NOMORESYM invalid response received
0x755 1877 ADSERR_CLIENT_SYNCRESINVALID sync port is locked

RTime Error Codes

Hex Dec Name Description
0x1000 4096 RTERR_INTERNAL Internal fatal error in the TwinCAT real-time system
0x1001 4097 RTERR_BADTIMERPERIODS Timer value not vaild
0x1002 4098 RTERR_INVALIDTASKPTR Task pointer has the invalid value ZERO
0x1003 4099 RTERR_INVALIDSTACKPTR Task stack pointer has the invalid value ZERO
0x1004 4100 RTERR_PRIOEXISTS The demand task priority is already assigned
0x1005 4101 RTERR_NOMORETCB No more free TCB (Task Control Block) available. Maximum
number of TCBs is 64
0x1006 4102 RTERR_NOMORESEMAS No more free semaphores available. Maximum number of
semaphores is 64
0x1007 4103 RTERR_NOMOREQUEUES No more free queue available. Maximum number of queue is
64
0x100D 4109 RTERR_EXTIRQALREADYDEF An external synchronization interrupt is already applied
0x100E 4110 RTERR_EXTIRQNOTDEF No external synchronization interrupt applied
0x100F 4111 RTERR_EXTIRQINSTALLFAILED The apply of the external synchronization interrupt failed
0x1010 4112 RTERR_IRQLNOTLESSOREQUAL Call of a service function in the wrong context
0x1017 4119 RTERR_VMXNOTSUPPORTED Intel VT-x extension is not supported
0x1018 4120 RTERR_VMXDISABLED Intel VT-x extension is not enabled in system BIOS
0x1019 4121 RTERR_VMXCONTROLSMISSING Missing function in Intel VT-x extension
0x101A 4122 RTERR_VMXENABLEFAILS Enabling Intel VT-x fails

TCP Winsock Error Codes

Hex Dec Description
0x274d 10061 A connection attempt failed because the connected party did not properly respond after a period of time,
or established connection failed because connected host has failed to respond.
0x2751 10065 No connection could be made because the target machine actively refused it. This error normally occurs when
you try to connect to a service which is inactive on a different host - a service without a server application.
0x274c 10060 No route to a host.
A socket operation was attempted to an unreachable host
Further Winsock error codes: Win32 Error Codes

154 Version: 1.4 TC3 Database Server

 Appendix

8.1.2 Internal Errorcodes of the TwinCAT Database Server

Code (Hex) Code (Dez) Describtion
0x0001 + ADS-Errorcode 65537 - 131071 ADS Errorcode of the declared
ADS-device
0x00020001 131073 Microsoft SQL Compact Database
(Errorcode)
0x00040001 262145 Microsoft SQL Database
(Errorcode)
0x00080001 524289 Microsoft Access Database
(Errorcode)
0x00100001 1048577 MySQL Database (Errorcode)
0x00200001 2097153 Oracle Database (Errorcode)
0x00400001 4194305 DB2 Database (Errorcode)
0x00800001 8388609 PostgreSQL Database (Errorcode)
0x01000001 16777217 Interbase/Firebird Database
(Errorcode)
0x02000001 33554433 TwinCAT Database Server
Errorcode
0x04000001 67108865 XML Database (Errorcode)
0x08000001 134217729 ASCII Database (Errorcode)

If an error occurs at executing an SQL statement, one of the declared errorcodes from the top of this site will
be displayed at the output "nErrID". The specified errorcode of the database will be displayed at the
"sSQLState" Output of the function block. The output "sSQLState" has the data type ST_DBSQLError [} 107].
The "sSQLState" can supply an errorcode for each database type.

At the following link you can find a list of SQLStates und their discribtion: http://msdn.microsoft.com/en-us/
library/ms714687(VS.85).aspx (SQLStates)

Database Type Errorcodereference

Microsoft SQL Compact Database http://technet.microsoft.com/en-us/library/
ms171788.aspx / OleDB_Errorcodes.htm [} 156]
Microsoft SQL Database OleDB_Errorcodes.htm [} 156]
Microsoft Access Database OleDB_Errorcodes.htm [} 156]
MySQL Database http://dev.mysql.com/doc/refman/5.0/en/error-
handling.html
Oracle Database http://www.ora-code.com
DB2 Database http://publib.boulder.ibm.com/infocenter/dzichelp/
v2r2/topic/com.ibm.db2z9.doc.codes/src/tpc/
db2z_n.htm
PostgreSQL Database http://www.postgresql.org/docs/current/static/
errcodes-appendix.html
Interbase/Firebird Database http://www.firebirdsql.org/file/documentation/
reference_manuals/reference_material/Firebird-2.1-
ErrorCodes.pdf
XML Database TcDBServer_XML_Errorcodes.htm [} 162]
ASCII Database TcDBServer_ASCII_Errorcodes.htm [} 162]

TC3 Database Server Version: 1.4 155

Appendix

8.1.3 OleDB Errorcodes

156 Version: 1.4 TC3 Database Server

 Appendix

HRESULT Describtion
0x80040E00 Invalid accessor
0x80040E01 Creating another row would have exceeded the total
number of active rows supported by the rowset
0x80040E02 Unable to write with a read-only accessor
0x80040E03 Given values violate the database schema
0x80040E04 Invalid row handle
0x80040E05 An object was open
0x80040E06 Invalid chapter
0x80040E07 A literal value in the command could not be
converted to the correct type due to a reason other
than data overflow
0x80040E08 Invalid binding info
0x80040E09 Permission denied
0x80040E0A Specified column does not contain bookmarks or
chapters
0x80040E0B Some cost limits were rejected
0x80040E0C No command has been set for the command object
0x80040E0D Unable to find a query plan within the given cost limit
0x80040E0E Invalid bookmark
0x80040E0F Invalid lock mode
0x80040E10 No value given for one or more required parameters
0x80040E11 Invalid column ID
0x80040E12 Invalid ratio
0x80040E13 Invalid value
0x80040E14 The command contained one or more errors
0x80040E15 The executing command cannot be canceled
0x80040E16 The provider does not support the specified dialect
0x80040E17 A data source with the specified name already exists
0x80040E18 The rowset was builtover a live data feed and cannot
be restarted
0x80040E19 No key matching the described characteristics could
be found within the current range
0x80040E1A Ownership of this tree has been given to the provider
0x80040E1B The provider is unable to determine identity for newly
inserted rows
0x80040E1C No nonzero weights specified for any goals
supported, so goal was rejected; current goal was not
changed
0x80040E1D Requested conversion is not supported
0x80040E1E lRowsOffset would position you past either end of the
rowset, regardless of the cRows value specified;
cRowsObtained is 0
0x80040E1F Information was requested for a query, and the query
was not set
0x80040E20 Provider called a method from IRowsetNotify in the
consumer and NT
0x80040E21 Errors occurred
0x80040E22 A non-NULL controlling IUnknown was specified and
the object being created does not support
aggregation
0x80040E23 A given HROW referred to a hard- or soft-deleted row
0x80040E24 The rowset does not support fetching backwards

TC3 Database Server Version: 1.4 157

Appendix

HRESULT Describtion
0x80040E25 All HROWs must be released before new ones can
be obtained
0x80040E26 One of the specified storage flags was not supported
0x80040E27 The comparison operator was invalid
0x80040E28 The specified status flag was neither
DBCOLUMNSTATUS_OK nor
DBCOLUMNSTATUS_ISNUL
0x80040E29 The rowset cannot scroll backwards
0x80040E2A Invalid region handle
0x80040E2B The specified set of rows was not contiguous to or
overlapping the rows in the specified watch region
0x80040E2C A transition from ALL* to MOVE* or EXTEND* was
specified
0x80040E2D The specified region is not a proper subregion of the
region identified by the given watch region handle
0x80040E2E The provider does not support multi-statement
commands
0x80040E2F A specified value violated the integrity constraints for
a column or table
0x80040E30 The given type name was unrecognized
0x80040E31 Execution aborted because a resource limit has been
reached; no results have been returned
0x80040E32 Cannot clone a command object whose command
tree contains a rowset or rowsets
0x80040E33 Cannot represent the current tree as text
0x80040E34 The specified index already exists
0x80040E35 The specified index does not exist
0x80040E36 The specified index was in use
0x80040E37 The specified table does not exist
0x80040E38 The rowset was using optimistic concurrency and the
value of a column has been changed since it was last
read
0x80040E39 Errors were detected during the copy
0x80040E3A A specified precision was invalid
0x80040E3B A specified scale was invalid
0x80040E3C Invalid table ID
0x80040E3D A specified type was invalid
0x80040E3E A column ID was occurred more than once in the
specification
0x80040E3F The specified table already exists
0x80040E40 The specified table was in use
0x80040E41 The specified locale ID was not supported
0x80040E42 The specified record number is invalid
0x80040E43 Although the bookmark was validly formed, no row
could be found to match it
0x80040E44 The value of a property was invalid
0x80040E45 The rowset was not chaptered
0x80040E46 Invalid accessor
0x80040E47 Invalid storage flags
0x80040E48 By-ref accessors are not supported by this provider
0x80040E49 Null accessors are not supported by this provider

158 Version: 1.4 TC3 Database Server

 Appendix

HRESULT Describtion
0x80040E4A The command was not prepared
0x80040E4B The specified accessor was not a parameter
accessor
0x80040E4C The given accessor was write-only
0x80040E4D Authentication failed
0x80040E4E The change was canceled during notification; no
columns are changed
0x80040E4F The rowset was single-chaptered and the chapter
was not released
0x80040E50 Invalid source handle
0x80040E51 The provider cannot derive parameter info and
SetParameterInfo has not been called
0x80040E52 The data source object is already initialized
0x80040E53 The provider does not support this method
0x80040E54 The number of rows with pending changes has
exceeded the set limit
0x80040E55 The specified column did not exist
0x80040E56 There are pending changes on a row with a reference
count of zero
0x80040E57 A literal value in the command overflowed the range
of the type of the associated column
0x80040E58 The supplied HRESULT was invalid
0x80040E59 The supplied LookupID was invalid
0x80040E5A The supplied DynamicErrorID was invalid
0x80040E5B Unable to get visible data for a newly-inserted row
that has not yet been updated
0x80040E5C Invalid conversion flag
0x80040E5D The given parameter name was unrecognized
0x80040E5E Multiple storage objects can not be open
simultaneously
0x80040E5F The requested filter could not be opened
0x80040E60 The requested order could not be opened
0x80040E61 Bad tuple
0x80040E62 Bad coordinate
0x80040E63 The given axis was not valid for this Dataset
0x80040E64 One or more of the given cell ordinals was invalid
0x80040E65 The supplied columnID was invalid
0x80040E67 The supplied command does not have a DBID (Note:
DBID is SQL shorthand for Database ID.)
0x80040E68 The supplied DBID already exists
0x80040E69 The maximum number of Sessions supported by the
provider has already been created. The consumer
must release one or more currently held Sessions
before obtaining a new Session object
0x80040E72 The index ID is invalid
0x80040E73 The initialization string does not conform to
specification
0x80040E74 The OLE DB root enumerator did not return any
providers that matched an of the SOURCES_TYPEs
requested
0x80040E75 The initialization string specifies a provider which
does not match the currently active provider.

TC3 Database Server Version: 1.4 159

Appendix

HRESULT Describtion
0x80040E76 The specified DBID is invalid
0x80040E6A Invalid trustee value
0x80040E6B The trustee is not for the current data source
0x80040E6C The trustee does not support memberships/
collections
0x80040E6D The object is invalid or unknown to the provider
0x80040E6E No owner exists for the object
0x80040E6F The access entry list supplied is invalid
0x80040E70 The trustee supplied as owner is invalid or unknown
to the provider
0x80040E71 The permission supplied in the access entry list is
invalid
0x80040E77 The ConstraintType was invalid or not supported by
the provider.
0x80040E78 The ConstraintType was not
CONSTRAINTTYPE_FOREIGNKEY and
cForeignKeyColumns was not zero
0x80040E79 The Deferrability was invalid or the value was not
supported by the provider
0x80040E80 The MatchType was invalid or the value was not
supported by the provider
0x80040E8A The UpdateRule or DeleteRule was invalid or the
value was not supported by the provider
0x80040E8B The pConstraintID did not exist in the data source
0x80040E8C The dwFlags was invalid
0x80040E8D The rguidColumnType pointed to a GUID that does
not match the object type of this column or this
column was not set
0x80040E8E The requested URL was out-of-scope
0x80040E90 The provider could not drop the object
0x80040E91 There is no source row
0x80040E92 The OLE DB object represented by this URL is
locked by one or more other processes
0x80040E93 The client requested an object type that is only valid
for a collection
0x80040E94 The caller requested write access to a read-only
object
0x80040E95 The provider could not connect to the server for this
object
0x80040E96 The provider could not connect to the server for this
object
0x80040E97 The attempt to bind to the object timed out
0x80040E98 The provider was unable to create an object at this
URL because an object named by this URL already
exists
0x80040E99 The provider could not drop the object
0x80040E9A The provider was unable to create an object at this
URL because the server was out of physical storage
0x00040EC0 Fetching requested number of rows would have
exceeded total number of active rows supported by
the rowset
0x00040EC1 One or more column types are incompatible;
conversion errors will occur during copying

160 Version: 1.4 TC3 Database Server

 Appendix

HRESULT Describtion
0x00040EC2 Parameter type information has been overridden by
caller
0x00040EC3 Skipped bookmark for deleted or non-member row
0x00040EC4 Errors found in validating tree
0x00040EC5 There are no more rowsets
0x00040EC6 Reached start or end of rowset or chapter
0x00040EC7 The provider re-executed the command
0x00040EC8 Variable data buffer ful
0x00040EC9 There are no more results
0x00040ECA Server cannot release or downgrade a lock until the
end of the transaction
0x00040ECB Specified weight was not supported or exceeded the
supported limit and was set to 0 or the supported limit
0x00040ECC Consumer is uninterested in receiving further
notification calls for this reason
0x00040ECD Input dialect was ignored and text was returned in
different dialect
0x00040ECE Consumer is uninterested in receiving further
notification calls for this phase
0x00040ECF Consumer is uninterested in receiving further
notification calls for this reason
0x00040ED0 The operation is being processed asynchronously
0x00040ED1 In order to reposition to the start of the rowset, the
provider had to reexecute the query; either the order
of the columns changed or columns were added to or
removed from the rowset
0x00040ED2 The method had some errors; errors have been
returned in the error array
0x00040ED3 Invalid row handle
0x00040ED4 A given HROW referred to a hard-deleted row
0x00040ED5 The provider was unable to keep track of all the
changes; the client must refetch the data associated
with the watch region using another method
0x00040ED6 Execution stopped because a resource limit has been
reached; results obtained so far have been returned
but execution cannot be resumed
0x00040ED7 The bind failed because the provider was unable to
satisfy all of the bind flags or properties
0x00040ED8 A lock was upgraded from the value specified
0x00040ED9 One or more properties were changed as allowed by
provider
0x00040EDA Errors occurred
0x00040EDB A specified parameter was invalid
0x00040EDC Updating this row caused more than one row to be
updated in the data source
0x00040EDD The row has no row-specific columns

TC3 Database Server Version: 1.4 161

Appendix

8.1.4 ASCII Errorcodes

Code Description
1 Function not allowed.
2 Syntax error
3 File couldn't be open.

8.1.5 XML Errorcodes

Code Description
1 Function not allowed.
2 XML file couldn't be read
3 XML schema couldn't be read
4 Syntax error
5 Table couldn't be created.
6 The list of INSERT VALUES are not conform to the
collumn list
7 Memory of the PLC structure not big enough.
8 XML file couldn't be created.
9 XML database not found.
10 XML table not found.

8.2 FAQ - Frequently asked questions and their answers

In this area we answer frequently asked questions to help you to work with TF6420 Database Server.
If you have any further questions please contact our support (-157)

1. Which performance can be achieved by the TF6420 Database Server? [} 162]

2. Supports the TF6420 Database Server Stored Procedures? [} 163]

3. Which data types are supported by the TwinCAT 3 Database Server? [} 163]

4. Is it possible to log more than one variable of a symbolgroup in one data record? [} 163]

5. How do I write or read single variables out of an existing database structure? [} 163]

6. Is it possible to log several data records at the same time into a database? [} 163]

7. Which network topologies are supported by the TF6420 Database Server? [} 163]

8. Which functionalities of the TwinCAT 3 Database Server can be used for the database type "XML"? [} 163]

9. Why are some Function Blocks in an "Obsolete" folder? [} 163]

?Which performance can be achieved by the TF6420 Database Server?

! That's much too sweeping a statement. The performance depends on the used hardware, the used write
mode e.g. ringbuffer mode and the count of the variable which has to be logged. Another aspect is the used
database type.

162 Version: 1.4 TC3 Database Server

 Appendix

?Supports the TF6420 Database Server Stored Procedures?

! Yes, the TwinCAT 3 Database Server support Stored Procedures with the help of the PLC function blocks
FB_DBStoredProcedures [} 97] and FB_DBStoredProceduresRecordReturn [} 105]. These function blocks are
not supported by every database type.

?Which data types are supported by the TwinCAT 3 Database Server?

! This Link [} 44] shows all supported databases of the TF6420 Database Server.

?Is it possible to log more than one variable of a symbolgroup in one data record?
! Symbolgroups will be created at the TF6420 Database Server Configuration Editor. The declared symbols
can only be logged separately into the database. To log several variable into one data record, use the
function block FB_DBRecordInsert_Ex [} 94] out of the PLC.

?How do I write or read single variable out of an existing database structure?

! With the help of the PLC function block FB_DBRecordInsert_Ex [} 94] it is possible to write single variable
into an existing database structure. Reading of single variable can be done by the function block
FB_DBRecordSelect_Ex [} 104].

?Is it possible to log several data records at the same time into a database?
! This depends on the used database. The database type "Microsoft SQL Database" supports this in
conjunction with the function block FB_DBRecordInsert_Ex. You only have to separate the different SQL
INSERT commands with ";".

?Which network topologies are supported by the TF6420 Database Server?

! It exist several possibilities to use the TwinCAT 3 Database Server in a network. This Link [} 19] shows the
different supported network topologies und further information about this topic.

?Which functionalities of the TwinCAT 3 Database Server can be used for the database type "XML"?
! The database type "XML" supports the full functionality of the TF6420 Database Server. Only Stored
Procedures and Delete SQL commands are not supported. You can work with the XML file like every other
database with SQL commands, or with the cyclic write mode of the PLC values. An additional functionality is
the possibility to use XPath commands and read XML-Tags. Further information can be found here [} 66].

?Why are some Function Blocks in an "Obsolete" folder?

! During the product development it gaves new PLC function blocks which include the functionallity of older
function blocks. Specially in new projects it makes no sense to use the obsolete FB's. Of course the old
function blocks are still part of the product. Here are the details:
- FB_DBAuthentificationAdd could be replaced by FB_DBConnectionAdd
- FB_DBRecordInsert could be replaced by FB_DBRecordInsert_Ex
- FB_DBRecordSelect und FB_DBRecordSelect_Ex could be replaced by FB_DBRecordArraySelect
- FB_DBStoredProcedureRecordReturn could be replaced by FB_DBStoredProcedureRecordReturn

TC3 Database Server Version: 1.4 163