Skip to main content

Beckhoff TwinCAT V2 / SQL4TwinCAT

Requirements​

The SQL4TwinCat.lib library uses the sockets functions of the Beckhoff library TcpIp.lib, which must also be connected in the project in the library administration.

The sample project can be executed on the Soft-PLC “TwinCAT PLC”, or a hardware-PLC. However, it is required that the control system has a TCP/IP connection, and that it supports the TcpIp.lib. To allow for the Tcplp.lib to function, install the TwinCAT TCP/IP Server (CE) from Beckhoff on the control system. This TwinCAT TCP/IP server is subject to licensing, or it runs in a 30 days demo mode.

Function Block​

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_SQL4TwinCAT). Upon applying changes here, the complete project must be retranslated.

Some global constants can be adjusted for project-specific purposes. Depending on the size of the data to be read or…

VAR_GLOBAL CONSTANT

diMaxRows : DINT := 50; ( max. Number Datasets, from Table-Array ) diMaxColumns : DINT := 15; ( max. Number Columns, from Table-Array ) diStringLength : DINT := 40; ( String-Length in the Table-Array*) diUBoundRequestArray : DINT := 20; (* UpperBoundary Request-String-Array ) diRequestStringLength : DINT := 80; ( String-Length Request-String-Array ) diReadDataBuffer : DINT := 4096; ( max. Number Bytes of Read-Buffer )

diSendDataBuffer : DINT := 2048; ( max. Number Bytes of Send-Buffer )

END_VAR

Global Variables​

VAR_GLOBAL

saRequest : ARRAY[1..diUBoundRequestArray] OF STRING(diRequestStringLength); ( Request-Array ) saColumnsName : ARRAY[1..diMaxColumns] OF STRING(diStringLength); ( ColumnsName-Array ) saTableValues : ARRAY[1..diMaxRows, 1..diMaxColumns] OF STRING(diStringLength); ( Values-Array )

END_VAR

Instance / Interface​

Creating the instance of the SQL4TwinCAT-Target-Library and creating local variables.

DB_Connection : SQL4TwinCAT; ( Instance of SQL4CoDeSys Function Block*

xExecute : BOOL; ( Start Database request )

xAbort : BOOL; ( Reset )

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

iPort : UINT; ( Set Port-Number of the SQL4automation-Connector )

xHoldConnection : BOOL; ( Holds Socket connection open after Database request )

timTimeOut : 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 )

Opening the instance in the program and assigning local variables.

sIPAddress := '192.168.1.20'; ( IP address which is set in the Connector )

iPort := 11001;

DB_Connection( xExecute:= xExecute,

xAbort:= xAbort,

sIPAddress:= sIPAddress,

iPort:= iPort,

xHoldConnection:= xHoldConnection,

tTimeOut:= timTimeOut,

pRequest:= ADR(saRequest),

pColumnName:= ADR(saColumnsName),

pTableValue:= ADR(saTableValues),

xReady=> xReady,

xBusy=> xBusy,

xDone=> xDone,

xError=> xError,

diResultState=> diResultState,

diResultRows=> diResultRows,

diResultColumns=> diResultColumns);

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 NumberError Description
1Unknown SQL Command
2Request returns more datasets than defined by MaxRows, modify request or increase MaxRows
3Request returns more columns than defined by MaxColumns, , modify request or increase MaxColumns
5Request returns minimal one value that is bigger than defined by MaxStringLength, modify request or increase MaxStringLength
10Internal Connector error
11Internal Connector error, cannot open database
21No IP-address defined
22No port number defined
23Request string is empty
24Number MaxRows not defined
25Number MaxColumns not defined
26Pointer to TableValues-Array is not defined
27Pointer to ColumnsName-Array is not defined
28Pointer to Request-Array is not defined
29Size MaxStringLength not defined / diStringLength=0
41

No connection to SQL4automation-Connector established.

Turn off Firewall.

51Error during sending of request
61No data available for receipt
71Connection to the SQL4automation-Connector cannot be closed
99Timeout
>100Error numbers of the ODBC database connection (some known error numbers are attached)
40002General error during SQL request, request string is invalid

Sample Project SQL4TwinCATSample​

In the sample project SQL4TwinCATSample, the SQL4TwinCAT library has already been integrated.

The project can be tested on the Soft-PLC „TwinCAT PLC“, for example. 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.

By forcing the variable xStartRequest, an automated execution of a database request can be performed

As soon as the request has been successfully completed, the received data records are located in the global variables.

As soon as the request has been successfully completed, the received data records are located in the global variables