Beckhoff TwinCAT V3 / SQL4TwinCAT3

Requirements

The SQL4TwinCAT3 function block requires the following libraries: Tc2_TcpIp.lib, Tc2_Standard, Tc2_System. These libraries must be added in the project under references. The Tc2_TcpIp.lib is part of the TCP/IP server license, which must be ordered from Beckhoff (order-nr. TF6310 - TC3 TCP/IP). For testing purposes, a 7-days testing license can be activated under System License. This activation can be performed several times.

The sample project can be executed on the Soft-PLC of TwinCAT3, or on a hardware-PLC.

However, it is required that the control system has a TCP/IP connection, and that it supports the Tc2_TcpIp.lib. Also for the Soft-PLC of TwinCAT3 (order-nr. TC1200- TC3 PLC), a 7-days testing license can be activated. This activation can be performed several times.

Function Block

Global Constants

Some global constants can be adjusted for project-specific purposes. Depending on the size of the data to be read or written, the array sizes are configured here. These global parameters are declared in your project (GVL_SQL4TwinCAT3). Upon applying changes here, the complete project must be retranslated.

**

VAR_GLOBAL CONSTANT

gc_diMaxRows : DINT := 200; // max. Number Datasets (Table-Array)

gc_diMaxColumns : DINT := 15; // max. Number Columns (Table-Array)

gc_diStringLength : DINT := 40; // String-Length in the Table-Array

gc_diUBoundRequestArray : DINT := 20; // Arraysize Request-Array

gc_diRequestStringLength : DINT := 80; // String-Length Request-Array

gc_diReadDataBuffer : DINT := DINT#8192; // max. Number Bytes of Read-Buffer

gc_diSendDataBuffer : DINT := DINT#8192; // max. Number Bytes of Write-Buffer

gc_xSetOptionsAllowed : BOOL := TRUE; // Activates / deactivates Socket Setting Options, must be deactivated with some control systems.

END_VAR

Global Variables

The global variables for the data are declared in your project (GVL_SQL4TwinCAT3).

VAR_GLOBAL

g_saRequest : ARRAY[1..diUBoundRequestArray] OF STRING(diRequestStringLength); // Request-Array

g_saColumnName : ARRAY[1..diMaxColumns] OF STRING(diStringLength); // ColumnsName-Array

g_saTableValue : ARRAY[1..diMaxRows, 1..diMaxColumns] OF STRING(diStringLength); // Values-Array

END_VAR

Instance / Interface

Creating the instance of the SQL4TwinCAT3 function block and creating locale variables:

VAR

DB_Connection : SQL4TwinCAT3; // Instance of SQL4TwinCAT3 Function Block

xExecute : BOOL; // Start Database request

xAbort : BOOL; // Reset

sIPAddress : STRING(15); // Set IP-Address of the SQL4automation-Connector

uiPort : UINT; // Set Port-Number of the SQL4automation-Connector

xHoldConnection : BOOL; // Holds Socket connection open after Database request

tTimeOut : TIME; // Timeout

xReady : BOOL; // Ready for Database request

xBusy : BOOL; // Database request in process

xDone : BOOL; // Database request completed

xError : BOOL; // Database request resulted in an Error

diResultState : DINT; // Status Database request / Error number

diResultRows : DINT; // Num. returned Rows from the Database request

diResultColumns : DINT; // Num. returned Columns from the Database request

END_VAR

**

Opening the instance in the program and assigning local variables.

sIPAddress := '192.168.1.20'; // also 'localhost' possible

uiPort := 11001;

DB_Connection( xExecute:= xExecute,

xAbort:= xAbort,

sIPAddress:= sIPAddress,

uiPort:= uiPort,

xHoldConnection:= xHoldConnection,

tTimeOut:= T#30S,

pRequest:= ADR(g_saRequest),

pColumnName:= ADR(g_saColumnName),

pTableValue:= ADR(g_saTableValue),

xReady=> xReady,

xBusy=> xBusy,

xDone=> xDone,

xError=> xError,

diResultState=> diResultState,

diResultRows=> diResultRows,

diResultColumns=> diResultColumns,

Socket=> );

Handshake

The handshake is conducted in accordance with the handshake of the StandardProtocol. The StandardProtocol is described under 9.1 Standard Protocol.

Error Codes Return-Value

The variable “diResultState” describes the error.

Error Number	Error Description
1	Unknown SQL Command
2	Request returns more datasets than defined by gc_diMaxRows, modify request or increase gc_diMaxRows
3	Request returns more columns than defined by gc_diMaxColumns, modify request or increase gc_diMaxColumns
5	Request returns minimal one value that is bigger than defined by MaxStringLength, modify request or increase MaxStringLength
10	Internal Connector error
11	Internal Connector error, cannot open database
21	No IP-address defined/ sIPAddress=’’
22	No port number defined / uiPort=0
23	Request string is empty/ pRequest(1)=’’
24	Number MaxRows not defined / gc_diMaxRows=0
25	Number MaxColumns not defined / gc_diMaxColumns=0
26	Pointer to TableValue-Array is not defined
27	Pointer to ColumnName-Array is not defined
28	Pointer to Request-Array is not defined
29	Send buffer too small (gc_diSendDataBuffer<=gc_diRequestStringLength)
30	Send buffer too small ( gc_diSendDataBuffer<5)
51	Request string is greater than the send buffer. (gc_diSendDataBuffer)
71	Connection to the SQL4automation-Connector cannot be closed
99	Timeout
>100	Error numbers of the ODBC database connection (some known error numbers are attached)
40002	General error during SQL request, request string is invalid

Sample Project SQL4TwinCAT3 Sample program

As a sample project, a Microsoft Visual Studio Solution was created. It is located in the folder “SQL4TwinCAT3 Sample program”. There is also a PLC project archive and a Solution project archive in this folder.

However, it is also possible to add the function block “SQL4TwinCAT3” and the global list of variables “GVL_SQL4TwinCAT3” into an existing project.

The project can be tested on the TC3 Soft-PLC. The connector must be started, and the connection to the sample database “S4A_Test_DB.mdb” must be configured.

How to configure the connection to the test database is described under 4.1.2 Setting up the connection to the test database.

By forcing xExecute, the program can be tested. In the case of an error, the connection can be reset by forcing xAbort. By forcing the variable xStartRequest, an automated execution of a database request can be performed.