SQL query editor

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

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

With the editor you can 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.

The Menu Bar

SQL query editor 1:

 

 

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 can create arbitrary 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.

SQL query editor 2:

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 table schema with the help of the button "Get Tableschema".

SQL query editor 3:

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 must press the "Execute" button to send the command to the database. This operation will be executed internally with the function block FB_DBRecordInsert_EX

SQL query editor 4:

SQL "SELECT" commands

With the tab "SELECT" datasets of a table could be read. Therefore, you must 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.

SQL query editor 5:

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

SQL query editor 6:

DELETE tables

SQL query editor 7: