SQL Query Editor

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.

After the TwinCAT Database Server of the target system is selected, the SQL Query Editor loads the current database configuration and the tables of the successfully connected databases. Depending on whether the database supports the SQL and the NoSQL interface (from the TwinCAT Database Server), it is listed under the respective category.

Below the selection of the target system there is a status bar with the available commands:

Table level
Insert working area	Opens the Insert working area to write data sets to the selected table with SQL.
Select working area	Opens the Select working area to read data sets from the selected table with SQL.
Delete/Drop working area	Opens the Delete/Drop working area to delete data sets with SQL from the selected table or to delete entire tables.
NoSQL working area	Opens the NoSQL working area to execute NoSQL-specific queries.
Database level
Stored Procedure working area	Opens the Stored Procedure working area to execute stored procedures of the database.
Tables working area	Opens the Tables working area to create new tables in the selected database.
Update tables	Updates the available tables of the selected database.
General
Update databases	Updates the entire database tree.

The working areas are opened to the right of the tree under the corresponding tab. Also from the same table several tabs can be opened at one time.

Insert working area

The Insert working area enables data to be written to the selected table via the TwinCAT Database Server interface for SQL function blocks.

In the lower area (2) there is a table with the individual data symbols in the data set to be written. The name, PLC data type, the byte length as well as the value can be determined here. The entered values are then used via the command to generate the SQL statement.

This SQL statement is then made available in a text field (3). Depending on the syntax of the database, the content may vary.

The upper status bar contains the commands for interacting with the TwinCAT Database Server (1).

Command	Description
Read tables schema	Reads out the table schema of the table of the working area.
Generate SQL statement	Generates the SQL statement from the present table depending on the database syntax.
Execution	Executes the statement in the text field (3) via the respective interface of the TwinCAT Database Server.
Copy the statement	Copies the statement in the text field (3) as TwinCAT compatible syntax.
Export as structure	Exports the structure of the table of input values to a TwinCAT 3 compatible DUT.

Select working area

The Select working area allows reading data into the selected table via the TwinCAT Database Server interface for SQL function blocks.

In the lower area (2) there is a table with the individual data symbols in the data set to be read. The name, PLC data type, as well as the byte length can be determined here. This information is then needed to interpret the data.

This SQL statement is then made available in a text field (3). Depending on the syntax of the database, the content may vary.

The result field (4) displays the data after the statement has been executed. If several results are returned, they can be switched through the pages.

The upper status bar contains the commands for interacting with the TwinCAT Database Server (1).

Command	Description
Read tables schema	Reads out the table schema of the table of the working area.
Generate SQL statement	Generates the SQL statement from the present table depending on the database syntax.
Execution	Executes the statement in the text field (3) via the respective interface of the TwinCAT Database Server.
Copy the statement	Copies the statement in the text field (3) as TwinCAT compatible syntax.
Export as structure	Exports the structure of the table of input values to a TwinCAT 3 compatible DUT.

Delete/Drop working area

The Delete/Drop working area provides the option to issue SQL statements to delete either data from a table or the entire table from the database.

For this purpose, you can choose between the two options in the status bar. The syntax corresponding to the database is then generated in the statement field. To execute this with the TwinCAT Database Server interface, the switch FB_SQLCommandEvt.Execute is available.

NoSql working area

The NoSql working area supports the special functions of NoSql databases or the TwinCAT Database Server NoSQL interface.

ID	Name	Function
1	Filter/Document	Depending on which function is used, this input field acts as a document or as a filter in JSON format. If you want to execute a Find operation and also carry out a projection or sort operation, you can fill these fields with Options(Find) below.
2	Options (Find)	Describes additional parameters for the Find function, such as the projection or sorting.
3	Control elements	Control elements for interaction with the TwinCAT Database Server interface for NoSQL.
4	Data display	List of returned data. Navigation allows iteration through the available pages.

Find: executes a search query with the filter entered in the text field (1). Optionally, a projection or sorting operation can also be executed via the Options(Find) fields. Data is returned and listed in the data display (4). The syntax of the filters is database-specific.

Aggregate: executes an aggregation with the parameters entered in the text field (1). Data is returned and listed in the data display (4). The syntax of the filters is database-specific.

Insert: executes an insert query of the (JSON) document or document array entered in the text field (1). These are then written to the collection.

Delete: executes a delete query on the data found with the filter in the text field (1). Any data that is found is deleted from the collection.

Validate: if this option is selected, the data queries are not automatically parsed according to their own schema, but an attempt is made to map these data to the structure of the symbol from the PLC, which was specified via these parameters.

With the latter function, a Find query may lead to conflicts. In contrast to structures in the PLC process image, data sets in NoSQL databases do not have to follow a fixed schema. It is possible that queried documents do not have data for a specific element in the PLC structure. Or the data set carries data that does not occur in the PLC structure. These data are assigned via the name or the attribute "ElementName" in the PLC.

The differences in the data can be examined via the Schema Compare tab. In the above example it can be seen that in the case of the returned document in the PLC structure, the variable "Name" has a different data type length than that of the database. The corresponding colors show the weighting of the conflict:

Red: too many or too few data available.

Yellow: the byte length of the data set does not match, or underlying data sets are left over or missing.

Green: no conflicts

These conflicts are also listed under the Issue Tracker tab. It can also be read into the PLC as a string array, if required.

The Remaining Json tab returns any remaining data sets as JSON. This information can also be read into the PLC as a string.

The control elements in the status bar can be used to iterate through the data, as known from the other displayed data. The number of data sets displayed simultaneously can be specified.

Stored Procedure working area

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 present in the database, they will be listed in the dropdown list of the status bar (4).

Below is the table for the input parameters (1), and for the output schema (2). In addition, there is a view for the output results (3). If the Stored Procedure is executed successfully, the results are displayed here.

The status bar has the following commands:

Command	Description
Read stored procedure input schema	Reads out the input parameter schema. The results are shown in Table 1.
Read Stored Procedure output schema	Reads out the output parameter schema. The results are shown in Table 2. Info: This requires the execution of the Stored Procedure. Depending on the programming, data can be changed here.
Design	Executes the Stored Procedure via the respective interface of the TwinCAT Database Server.
Export as structure	Exports the structure of the table to a TwinCAT 3 compatible DUT.

Table working area

The Table working area is used to create new tables.

Here, the table structure (1) can be created and an SQL statement can be generated from it in the corresponding field (2). The status bar (3) with the following commands can be used for this purpose:

Command	Description
Table name	Specifies the table name of the new table.
Generate SQL statement	Generates the SQL statement from the present table depending on the database syntax.
Execution	Executes the Stored Procedure via the respective interface of the TwinCAT Database Server.
Copy the statement	Copies the statement in the text field (2) as TwinCAT compatible syntax.