SQL Expert mode

SQL Expert Mode

In SQL Expert mode users can assemble the SQL commands for Insert, Select or Update, for example, in the PLC and send them to the database via the TwinCAT Database Server. This is a very flexible and powerful option. Stored Procedures - in database - can also be called from the PLC.

Build Project

The TwinCAT Connectivity extension for Visual Studio provides a new project template. When a new project is created, the TwinCAT Connectivity Project category appears as an option.

To create a new TwinCAT Connectivity project, select Empty TwinCAT Connectivity Project, specify the project name and the storage location and click OK to add it to the solution. In this way, TwinCAT Connectivity projects or TwinCAT Database Server projects can conveniently be created in parallel with TwinCAT or other Visual Studio projects.

A new project node appears in the solution. Below the Connectivity project node you can add subprojects for the supported connectivity functions.

Use Add to add a new TwinCAT Database Server project to the TwinCAT Connectivity project. The TwinCAT Database Server project can be found in the list of existing Item Templates.

A new TwinCAT Database Server project is created under the TwinCAT Connectivity node.

This is now used as the basis for the pending configuration of a TwinCAT Database Server. The document can be edited either via the Properties window or via an editor.

A Connectivity project can be associated with any number of TwinCAT Database Server projects or other projects, and it may therefore contain several configurations.

Editor for server settings

The Server Settings editor can be used to edit the settings for the TwinCAT Database Server. These are general settings relating to the corresponding server. In the drop-down menu (1) you can select the target system via the Ams NetID. To this end you have to create a route to the target system via TwinCAT. When a finished configuration is transferred, the settings are stored in the TwinCAT Database Server for this target system.

The settings for logging faults or errors can be configured under Log settings. In the event of a fault or error, the Database Server generates a detailed entry in a log file. The log file can be read with the Information Log Viewer. Under Log Settings you can specify a path to the file location and the maximum file size. You can also influence the accuracy of the log. For performance reasons we recommend that logging is deactivated again after the error analysis, once it is no longer required.

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. This feature is currently not supported in Windows CE.

Further configuration settings are available to control the read process from the database. These settings refer to the TwinCAT Database Server on the target system:

Supported database types

The installed database types can be selected in the server settings. All installed databases are selected by default. The TwinCAT 3 Database Server will load the corresponding database interfaces. In this way, unused databases on the target system can be deselected.

A new database configuration can be added via the command Add New Database in the context menu of a Database Server project or via the corresponding command in the toolbar.

A new database configuration is added in the form of a file in the project folder and integrated in the project. As with all Visual Studio projects, the information on the new files is stored in the Connectivity project.

Editor for database configurations

The database ID, which is required for some function blocks in the PLC, is shown in the upper part of the editor (1). The database types of the target database can be selected from the drop-down menu (2). Another option is the ODBC interface for a database, although this is not yet supported. Note that not all functions of the TwinCAT Database Server can be guaranteed, depending on the database.

As a further option you can select a so-called failover database (3), which is triggered when an error is encountered in Configure mode. In the event of a network disconnection, this feature can automatically ensure that data are stored elsewhere and not lost.

For each database additional adjustable parameters are available. Depending on the database a connection string (5) is created, which describes the connection to the database. The intention is to make the parameters you have set more transparent.

The CREATE (4) button can be used to create a new database. This function is only displayed if the respective database supports it.

Unknown databases can be configured via an ODBC interface. In the ODBC Type drop-down list select "Unknown Database" and add parameters via the commands in the context menu. They may contain passwords, which are stored in encrypted form. The required connection string can be assembled from these parameters. Note that only limited functions of the TwinCAT Database Server can be used. Only the explicit function blocks of the SQL Expert mode are supported.

Activating a project

To activate a configured project on the TwinCAT Database Server, use the command Activate Configuration in the context menu of the TwinCAT Database Server project.

The SQL Query Editor is a Database Server tool that supports the development of your application. The tool can be used to test connections and SQL commands and to check the compatibility between PLC and databases.

First select the target system from the routes of your TwinCAT system (1). The TwinCAT Database Server must be installed on the target system. If a NoSQL database is stored in the configuration, an additional NoSQL tab is visible. You will find the documentation in a subitem below.

All configured databases (2) are displayed, once you have activated the database configurations on the target system. You can also select one of the available tables (3) from the database. Based on this table, you can generate SQL commands from the SQL Query Editor and send them to the database. The SQL commands have different syntax, depending on database type.

Three commands are available for generating the individual SQL commands:

• Get Table Schema: Calls up the structure of the selected table.
• Information such as the column name, PLC data type and size of variables is displayed. The retrieved structure can also be prepared for your PLC application via the commands Copy for PLC (4) or Export TC3 (5).
• Create Cmd: An SQL command is generated in the command text box, depending on the selected tab. The command syntax may differ, depending on the database type. The previously read table schema is used here.
• The created SQL command can optionally be modified.
• Execute: The SQL command shown in the text box is executed and returns values, if applicable.

The differences in the individual SQL commands are explained below.

Create Table command

The CREATE TABLE tab can be used to create tables within the database. Further columns can be added to the table with (+), as required. Once you have specified the column name and type, you can specify additional properties, in order to generate automatic IDs, for example.

The table name can be determined by executing the command. The table with the configured table structure is created.

Insert command

The Insert command gives the opportunity to write records into the table. The values under "Value" can be modified once the table structure has been retrieved. If the command is then generated, the values in the Insert command will automatically be in the right format. These values are written into the table when the command is executed.

This value cannot be customized if automatic ID generation is used.

Select command

Select commands can be created and sent via the SELECT tab. Select commands give the opportunity to read records from the databases. After executing the command, values are returned if they exist in the table. They are listed under "Value" in the table structure display. Use the arrows under the display to navigate through the individual records.

Delete command

The Delete command has two functions.

1. DELETE Records: Deletes the contents of a table.
2. DROP table: Deletes the whole table.

This SQL command can also be customized, in order to delete only a particular section of the table, for example.

Stored Procedures

The TwinCAT Database Server supports "Stored Procedures", which provide numerous databases for processing more complex queries at the database level or to make a simplified interface available.

If Stored Procedures are available in the database and the table, you can list and select them (1). The input and output parameters can be picked up automatically (2) and transferred to the tables in the display (3)(4).

The parameter type, name and data type are displayed there. In addition you can insert values here, in order to execute the Stored Procedures with the input values via "Execute". The result is displayed in the output values (4). If several records are returned, the arrow keys can be used to switch between them. This functionality serves as development aid for the call in the PLC. The results are returned there by calling the corresponding function block.

InformationLog View is a tool for reading log files from the TwinCAT Database Server. Recorded information is displayed with a timestamp, IDs and error messages in plain text.

The log files can not only be viewed or emptied via direct file access, but also directly via the target. This is particularly advantageous with distributed Database Servers in a network, for quick and easy access to the log file. For this access a route to the target device must exist.