Skip to main content

Schneider Electric / SQL4SE

Requirements​

The target is built upon the Codesys-library for SQL4automation and uses the fbSQL4Codesys function block.

The function block requires the following libraries:

  • Memory V3.5.7.0

  • Network V3.5.7.0

  • Standard V3.5.9.0

  • SysMem 3.5.5.0

  • SysTypes2 Interfaces 3.5.4.0

Installing the library​

The library is built upon the SQL4CodesysV3 library «S4A_SQL4CodesysV3.compiled-library», which can be installed via the project archive «SQL4SE_ProjectArchive – V1.0.0.0.projectarchive».

Calling up fbSQL4CODESYS​

Calling up fbSQL4CODESYS

Parameters of fbSQL4CODESYS​

Description of the interface signals:

xExecute : BOOL; // Start Database request

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

iPort : INT; // Set Port-Number of the SQL4automation-Connector

tTimeout : TIME; // Timeout

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

dwRequestBufferSize : DWORD; // Size of the request databuffer

dwResponseBufferSize : DWORD; // Size oft he response databuffer

dwResponseMaxReturnParams : DWORD; // Maximum number of return parameters

// (Connector version >= 4.0, default 0)

dwResponseMaxRows : DWORD; // Maximum number of rows in the database query response

dwResponseMaxColumns : DWORD; // Maximum number of columns in the database query response

dwResponseMaxStringLen : DWORD; // Maximum string length in the database query response

dwResponseCutStringLen : DWORD; // Strings in the response are truncated to the appropriate length

// (0=inactive)

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

stResult.diResultState : DINT; // Status Database request / Error number stResult.dwResultRows : DWORD; // Num. returned Rows from the Database request

stResult.dwResultColumns : DWORD; // Num. returned Columns from the Database request

Error Codes​

The variable “diResultState” describes the error.

Error NumberError Description
1Unknown SQL Command
2Request returns more datasets than defined by diMaxRows, modify request or increase diMaxRows
3Request returns more columns than defined by diMaxColumns, modify request or increase diMaxColumns
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/ sIPAddress=’’
22No port number defined / iPort=0
23Request string is empty
24Number MaxRows not defined / dwResponseMaxRows=0
25Number MaxColumns not defined / dwResponseMaxColumns=0
41

No connection to SQL4automation-Connector established.

Turn off Firewall.

51Request string is greater than the send buffer. (diSendDataBuffer)
99Timeout
>100Error numbers of the ODBC database connection (some known error numbers are attached)
40002General error during SQL request, request string is invalid

The sample project​

The sample project can be obtained from the download area of SQL4automation (http://www.SQL4automation.com).

The sample program should be started prior to making adaptations to the own application.

This makes finding possible error sources easier.

Adapt the IP address and the remote port to your system while opening the function block «fbSQL4CODESYS» in the program «prgUncritical».

Adapt the IP address and the remote port to your system while opening the function block «fbSQL4CODESYS» in the program…

The Connector must be running and the connection to the sample database «S4A_Test_DB.mdb» must be

configured.

The configuration of the connection to the database is described in chapter 4.1.2 Setting up the connection to the test database.

The sample programs «basic examples» show the 4 basic SQL commands (SELECT, INSERT, UPDATE, DELETE) as well as the handling of the SQL data.

The sample programs «basic examples» show the 4 basic SQL commands (SELECT, INSERT, UPDATE, DELETE) as well as the…

All function blocks have in common that they have an execute interface and a reference to a Sql4Codesys instance:

All function blocks have in common that they have an execute interface and a reference to a Sql4Codesys instance

The "xExecute" signal triggers the communication with the database. The status is returned via the signals "xReady", "xBusy", "xDone" and "xError".

The logic within the block is controlled by a small step sequence (variable «iStep»):

  • iStep = 0 (Inactive): No communication to the database is active

  • iStep = 1 (Create the request): This step waits until the interface is free. As soon as this is the case, the SQL query is created according to the customer application and the signal «xExecute» is set. The SQL4automation framework sends the telegram to the database.

  • iStep = 2 (Parse the response): This step waits for a response from the connector. If data is returned, it can be evaluated on successful completion. In the event of an error or if no response from the connector arrives, a corresponding error message is output.

  • iStep = 3 (Reset execute signal): In this step, the system waits for the "xExecute" signal to be reset, whereupon the sequencer is returned to the "inactive" initial state.

The communication principle is the same for all example blocks. The PLC sends a request to the database and receives a response. The data flow is secondary - data can be read from the database (SELECT) or written (INSERT, UPDATE). The exact syntax of the SQL command is determined on the one hand by the database model and, on the other hand, by the task to be executed.

Creating a request (request to the database):

  • Example of a SELECT command (fbExample1_Select).

    Example of a SELECT command (fbExample1_Select)

  • Example of an INSERT command (fbExample1_Insert). The variable values ​​are converted with run-time convert functions.

    Example of an INSERT command (fbExample1_Insert). The variable values ​​are converted with run-time convert functions

Parsing the Response (Response from the Database):

  • The response telegram is checked for correctness on receipt. Important information is stored internally and the user has to read out the data:

    • inSQL4SE.stResult.diResultState: 0: no error > 0: error code

    • inSQL4SE.stResult.dwResultRows: Number of data records in the response telegram

    • inSQL4SE.stResult.dwResultColums: Number of data columns in the response telegram

  • To read the data, the following methods are available:

    • inSQL4SE.GetData(): Returns the corresponding data field. The row index and the column index are 0 based.

    • inSQL4SE.GetColumnName(): Returns the column name based on the corresponding column index. The column index is 0 based. For example, the column names can be used to validate whether the data is returned in the expected order.

  • Example (fbExample1_Select)

Example (fbExample1_Select)