This time, we will use TF6420’s Library to connect to PostgreSQL DB, and update and retrieve DB data using the API provided by TwinCAT.

Let’s get started!

Reference Link

PostgreSQL#Install PostgreSQL in Raspberry Pi4

Beckhoff#TwinCAT3 TF6420 x MongoDB_Part1

Beckhoff#TwinCAT3 TF6420 x MongoDB_Part2

Function Block

In this article, we will introduce the Function Block used in the article. This time, we will use the “UPDATE” and “SELECT” commands for the PostgreSQL DB, so we will create a program in the red frame Flow.

For commands that do not get data from SQL DB Server, such as UPDATE,
- Use FB_SQLDatabase.Connect() to connect to Database
- Use FB_SQLDatabase.CreateCmd() to init the FB_SQLCommand FB
- Use FB_SQLCommand.Execute() to send the SQLDB command
- Use FB_SQLDatabase.Disconnected() to disconnect the connection with SQLDB
For commands that retrieve data from SQL DB Server, such as SELECT,
- Use FB_SQLDatabase.Connect() to connect to Database
- Use FB_SQLDatabase.CreateCmd() to init the FB_SQLCommand FB
- Use FB_SQLCommand.ExecuteDataRetrun() to send the SQLDB command
- Use FB_SQL_Result.Read() to read the data from SQLDB
- Use FB_SQL_Result.Release() to clear the data from buffer
- Use FB_SQLDatabase.Disconnected() to disconnect the connection with SQLDB

FB_SQLDatabaseEvt

This Function Block can be used to manage SQL database connections.

VAR_INPUT

Variable	Type	Description
sNetID	T_AmsNetID	AMS Network ID of the device subject to ADS Command
tTimeout	TIME	Sets the time until the execution of the Function is canceled.

VAR_OUTPUT

Variable	Type	Description
bBusy	BOOL	1=Function Block is executing
bError	BOOL	1=Function Block error exists
ipTcResult	Tc3_EventLogger.I_TcMessage	TwinCAT 3 EventLogger Messsage Interface that can provide execution status of Function Blocks

Method:Connect

Connects to the database.

METHOD Connect : BOOL
VAR_INPUT
hDBID: UDINT := 1;
END_VAR

VAR_INPUT

Variable	Type	Description
hDBID	UDINT	Set the ID of the database to be used

RetureValue

Variable	Type	Description
Connect	BOOL	True=Method has completed execution (also true if there is an error)

Method:CreateCmd

This Method is used to initialize the Interface of FB_SQLCommandEvt, such as command generation, in a database connection that has already been opened.

The manual says that the initialization of Function Block FB_SQLCommand can be completed in the same cycle, but to prevent bugs, it is better to monitor the change in the rising and falling of the Busy Flag.

VAR_INPUT
pSQLCommand: POINTER TO FB_SQLCommandEvt;
END_VAR

VAR_INPUTS

Variable	Type	Description
pSQLCommand	POINTER TO FB_SQLCommand	Obtains a new Function Block FB_SQLCommandEvt Instance that has been initialized.

ReturnValue

Variable	Type	Description
CreateCmd	BOOL	True=Method has completed execution (also true if there is an error)

FB_SQLCommandEvt

Function block for executing SQL commands. Before it can be used it has to be initialized with the function block FB_SQLDatabaseEvt.

VAR_INPUT

Variable	Type	Description
sNetID	T_AmsNetID	AMS Network ID of the device subject to ADS Command
tTimeout	TIME	Sets the time until the execution of the Function is canceled.

VAR_OUTPUT

Variable	Type	Description
bBusy	BOOL	1=Function Block is executing
bError	BOOL	1=Function Block error exists
ipTcResult	Tc3_EventLogger.I_TcMessage	TwinCAT 3 EventLogger Messsage Interface that can provide execution status of Function Blocks

Method:Execute

Sends SQL commands to the database through a database connection already opened with FB_SQLDatabase.

METHOD Execute : BOOL
VAR_INPUT
pSQLCmd: POINTER TO BYTE;
cbSQLCmd: UDINT;
END_VAR

VAR_INPUT

Variable	Type	Description
pSQLCmd	POINTER TO BYTE	String variable Memory Pointer for SQL command to execute (you can use ADR function)
cbSQLCmd	UDINT	Length of SQL command to execute (you can use the SIZEOF function)

ReturnValue

Variable	Type	Description
Execute	POINTER TO BYTE	The return value indicates the execution Status of the Method.

Method:ExecuteDataReturn

Sends SQL commands to the database through a database connection that has already been opened with FB_SQLDatabase. Instance of Function Block FB_SQLResult is transferred to read the returned records.

METHOD ExecuteDataReturn : BOOL
VAR_INPUT
pSQLCmd: POINTER TO BYTE;
cbSQLCmd: UDINT;
pSQLDBResult: POINTER TO FB_SQLResult;
END_VAR

VAR_INPUT

Variable	Type	Description
pSQLCmd	POINTER TO BYTE	String variable Memory Pointer for SQL command to execute (you can use ADR function)
cbSQLCmd	UDINT	Length of SQL command to execute (you can use the SIZEOF function)
pSQLDBResult	POINTER TO FB_SQLResult	Returns an instance of Function Block FB_SQLResult

ReturnValue

Variable	Type	Description
ExecuteDataReturn	POINTER TO BYTE	The return value indicates the execution Status of the Method.

FB_SQLResultEvt

This Function Block is used to read cached records.

VAR_INPUT

Variable	Type	Description
sNetID	T_AmsNetID	AMS Network ID of the device subject to ADS Command
tTimeout	TIME	Sets the time until the execution of the Function is canceled.

VAR_OUTPUT

Variable	Type	Description
bBusy	BOOL	1=Function Block is executing
bError	BOOL	1=Function Block error exists
ipTcResult	Tc3_EventLogger.I_TcMessage	TwinCAT 3 EventLogger Messsage Interface that can provide execution status of Function Blocks

Method:Read

This Method reads a specified number of records from data cached in the TwinCAT Database Server.

VAR_INPUT

Variable	Type	Description
nStartIndex	UDINT	Set the Index of the first record to be read
nRecordCount	UDINT	Set the number of records to be read
pData	POINTER TO BYTE	Memory Pointer of the structure array to write the record (can be obtained from the ADR function)
cbData	UDINT	Sets the size of the structure array in bytes (can be obtained from the SIZEOF function)
bWithVerifying	BOOL	True=returned data is compared to the pData structure array and adjusted
bDataRelease	BOOL	Release cached data

ReturnValue

Variable	Type	Description
Read	BOOL	True=Method has completed execution (also true if there is an error)

Implementation

Here is the configuration for this project: the PostgreSQL Server is accessed on the Raspberry Pi4 and is accessed from the Beckhoff C6920 using the TF6420.

Configuration

Configure the Data Server for TF6420.

Add New DataServer Project

From the TwinCAT IDE or Visual Studio, right-click>Add>New Project.

Select the Empty TwinCAT Database Server Project and Next.

Set a name for the TwinCAT Database Server Project and proceed with Create.

TcDbServer is created.

Add PostgreSQL Database

Right click on TcDbServer>Add New Database.

DB is created.

DBID=1, this ID is a very important parameter when using the PLC’s API.

Database Type

Set the database type from the Database Type drop-list.

In this case, PostgreSQL will be used.

Done!

Host

Host is set to the IP of the device on which the PostgreSQL DB is installed.

This time it is 192.168.5.144.

Database

Database should match the DB name in PostgreSQL.

You can also check it from VSCODE’s PostgreSQL Plugin, but this time we will use xa5.

Done!

Port

Default communication Port for PostgreSQL is 5432.

Authentication

The next step is to set the authentication method; Default is None.

Select Username/Password from the drop-list in Authentication.

When you set the Username/Password, the Username and Password input fields will be displayed.

Please enter your Login information.

Connecton String

The Connection String displays the connection path according to the parameters you have just set.

CHECK Connection

Click the CHECK button to test the connection between TwinCAT and Database.

IF NO License..

If you see error 0x724, generate a license.

SYSTEM>License.

Check on TF6420.

Click the 7 Days Trial License button to generate a Trial License.

Enter the Magic code.

Done!

Download your project with Activate Configuration.

OK to proceed.

Switch to Run Mode.

RESULT

Perform another connection test. This time Configuration check succeded is displayed. In other words, the connection is OK!

Activate Configuration

Download the DB configuration to the TwinCAT Database Server.

DB>Right click>Activate Configuration.

ADD PLC

Next, to add a PLC project, go to PLC>right click>Add New Item.

Select Standard PLC Project>Add.

Add Library

To add the Database library of TF6420 to the project, go to References>Right click>Add library.

Add Tc3_Database.

Done!

Program

The next step is to create a program.

DUT_SQL

This structure here matches the data in the tags table in XA5 of Database.

TYPE DUT_SQL :
STRUCT
tagname:STRING(255);
tagvalue:STRING(255);
END_STRUCT
END_TYPE

There are also strings in tagname and tagvalue from VSCode’s PostgreSQL Plug-in.

How to get the size?

You can check the variable size of each Row with the following command.

SELECT tagvalue ,char_length(tagvalue) FROM tags;

Thus, the number of Bytes will be in line with the number of characters, such as 505=3.

FC_SQL_UpdateCommands

This Function automatically generates SQL Update commands.

FUNCTION FC_SQL_UpdateCommands : STRING(255)
VAR_INPUT
iValue:INT;
iTag:STRING;
END_VAR
VAR
END_VAR

FC_SQL_UpdateCommands:=’UPDATE tags SET tagvalue = ‘;
FC_SQL_UpdateCommands:=CONCAT(STR1:=FC_SQL_UpdateCommands,STR2:=INT_TO_STRING(iValue));
FC_SQL_UpdateCommands:=CONCAT(STR1:=FC_SQL_UpdateCommands,STR2:=’ ‘);
FC_SQL_UpdateCommands:=CONCAT(STR1:=FC_SQL_UpdateCommands,STR2:=’WHERE tagname = ‘);
FC_SQL_UpdateCommands:=CONCAT(STR1:=FC_SQL_UpdateCommands,STR2:=iTag);

MAIN

This is the actual program control flow.

Step0=Initialize parameters
Step10=Connect to PostgreSQL DB
Step20=Initialize FB_SQLCommandEvt.
Step30-45=Send Update command to PostgreSQL DB
Step50=Send Select command to PostgreSQL
Step60=Transfer the data retrieved from the Select command to a PLC variable
Step70=Wait 1 second and go back to Step20 again

PROGRAM MAIN
VAR
FB_SQLDatabaseEvt:FB_SQLDatabaseEvt(sNetID := ”, tTimeout := T#5S);
FB_SQLCommandEvt:FB_SQLCommandEvt(sNetID := ”, tTimeout := T#5S);
FB_SQLResultEvt :FB_SQLResultEvt(sNetID := ”, tTimeout := T#5S);

tags:ARRAY[0..99]OF DUT_SQL;
iStep:INT;
SQL_Commands:ARRAY[1..4]OF STRING(255);
iCounter:INT:=0;
i:INT;
isubstep:INT;
iErrorCounet :INT;
sSelectDataCommand :STRING(255);
TON_Process :TON;
END_VAR

CASE iStep OF

0:
TON_Process(IN:=FALSE);
iCounter:=1;
iStep:=10;

10:
FB_SQLDatabaseEvt.Connect(
hDBID:=1
);
IF FB_SQLDatabaseEvt.bError THEN
iErrorCounet:=iErrorCounet+1;
iStep:=9991;
END_IF
IF FB_SQLDatabaseEvt.bConnected THEN
iStep:=20;
END_IF
20:
FB_SQLDatabaseEvt.CreateCmd(
pSQLCommand:=ADR(FB_SQLCommandEvt)
);
IF FB_SQLDatabaseEvt.bError THEN
iErrorCounet:=iErrorCounet+1;
iStep:=9992;
END_IF
TON_Process(IN:=NOT FB_SQLDatabaseEvt.bBusy AND NOT FB_SQLDatabaseEvt.bError);
IF TON_Process.Q THEN
TON_Process(IN:=FALSE);
iStep:=30;
iCounter:=iCounter+1;
IF iCounter>100 THEN
iCounter:=1;
END_IF
END_IF
30:
SQL_Commands[1]:=FC_SQL_UpdateCommands(iCounter,’$’Application/GVL_FactoryIO/qCounter1$’;’);
SQL_Commands[2]:=FC_SQL_UpdateCommands(iCounter*10+1,’$’Application/GVL_FactoryIO/qCounter2$’;’);
SQL_Commands[3]:=FC_SQL_UpdateCommands(iCounter*10+2,’$’Application/GVL_FactoryIO/qCounter3$’;’);
SQL_Commands[4]:=FC_SQL_UpdateCommands(iCounter*10+3,’$’Application/GVL_FactoryIO/iFactoryIORunning$’;’);
iStep:=40;
isubstep:=0;
i:=1;
40:

IF FB_SQLCommandEvt.Execute(pSQLCmd:=ADR(SQL_Commands[i]),cbSQLCmd:=SIZEOF(SQL_Commands[i])) THEN
IF FB_SQLCommandEvt.bError THEN
iErrorCounet:=iErrorCounet+1;
iStep:=9994;
END_IF
END_IF;
IF FB_SQLCommandEvt.bBusy THEN
isubstep:=1;
END_IF
IF NOT FB_SQLCommandEvt.bBusy AND isubstep=1 THEN
iStep:=45;
isubstep:=0;
END_IF

45:
i:=i+1;
IF i >= 5 THEN
iStep:=50;
isubstep:=0;
ELSE
iStep:=40;
END_IF

50:
sSelectDataCommand:=’SELECT * FROM “tags” LIMIT 1000;’;
IF FB_SQLCommandEvt.ExecuteDataReturn(pSQLCmd:=ADR(sSelectDataCommand),cbSQLCmd:=SIZEOF(sSelectDataCommand),pSQLDBResult:=ADR(FB_SQLResultEvt)) THEN

IF FB_SQLCommandEvt.bError THEN
iErrorCounet:=iErrorCounet+1;
END_IF
IF FB_SQLResultEvt.nDataCount >0 AND NOT FB_SQLCommandEvt.bBusy THEN
iStep:=60;
END_IF

END_IF

60:

IF FB_SQLResultEvt.Read(
nStartIndex:=0
,nRecordCount:=4
,pData:=ADR(tags)
,cbData:=SIZEOF(tags)
,bWithVerifying:=TRUE
,bDataRelease:=TRUE
) THEN

IF FB_SQLResultEvt.bError THEN
iErrorCounet:=iErrorCounet+1;
iStep:=9995;
END_IF
IF FB_SQLResultEvt.nDataCount=0 THEN
iStep:=70;
END_IF;
END_IF;

70:
TON_Process(IN:=TRUE,PT:=T#1S);
IF TON_Process.Q THEN
TON_Process(IN:=FALSE);
iStep:=20;
END_IF

END_CASE