FB_SqlDbRequest
Task
The FB_SqlDbRequest function block is used to:
-
Establish a permanent and either secured or unsecured TCP connection to the SQL Gateway. For more details, refer to ST_ExtendedConnectionSettings.
-
Perform SQL requests that read data from the SQL database. The return data is provided in a two-dimensional array of data whose size is defined with global parameters.
-
Perform SQL requests that update or modify the SQL database. The requests do not return any data.
Functional Description
After the function block has been enabled (i_xEnable is set to TRUE), a TCP connection (either secured via TLS V1.2 or unsecured) to the SQL Gateway is established using the settings defined in the variable iq_stExtendedConnSettings.
As soon as the connection has been established, the output q_xReady is set to TRUE, and the function block is able to send SQL requests to the SQL Gateway, which forwards the requests to the SQL database configured by using the connection name.
After a rising edge on i_xExecute has been detected, the function block is sending the SQL request to the SQL Gateway and is processing the response.
Status messages and diagnostic information are provided using the outputs q_xError (TRUE if an error has been detected), q_etResult, and q_etResultMsg.
Diagnostic messages in case of an unsuccessful command execution can be acknowledged by setting the input i_xErrorQuit to TRUE. The function block remains active and the TCP connection is not closed.
After the data exchange with the SQL database is completed, disable the function block. Set i_xEnable to FALSE to close the connection.
Whether a connection using TLS is supported depends on the controller where the FB_SqlDbRequest is used. Refer to the specific manual of your controller to verify if TCP communication using TLS is supported.
Interface
|
Input |
Data type |
Description |
|---|---|---|
|
i_xEnable |
BOOL |
The function block establishes a connection to the SQL Gateway upon a rising edge of this input. If the input is set to FALSE, the function block is reset and an existing connection is closed or the connection establishment is aborted. For more information, also refer to Behavior of Function Blocks with the Inputs i_xEnable and i_xExecute and i_xErrorQuit. |
|
i_xExecute |
BOOL |
The function block performs an SQL request in order to read or write data from or to the SQL database upon rising edge of this input. For more information, also refer to Behavior of Function Blocks with the Inputs i_xEnable and i_xExecute and i_xErrorQuit. |
|
i_xErrorQuit |
BOOL |
The function block acknowledges a detected error indicated by q_xError upon a rising edge of this input. For more information, also refer to Behavior of Function Blocks with the Inputs i_xEnable and i_xExecute and i_xErrorQuit. |
|
i_etSqlRequest |
Defines which type of SQL request (read or write) is to be executed. Default: ET_SqlRequest.Read |
|
|
i_refRequestWstring |
REFERENCE TO [RequestWstring] |
Reference to the request data that contains one SQL query request (either for read or write). Any SQL request must be divided into individual strings that do not exceed a length of 200 characters each. Adapt the size of the global parameters Gc_uiMaxRequest and Gc_uiRequestWstringLength according to the length of the SQL requests that you use in your application.
NOTE: To concatenate WSTRINGS, use the WCONCAT function of Standard64 library.
|
|
i_uiNumOfWstrings |
UINT |
The number of needed WSTRINGS that contain the split SQL request. The maximum number is limited by the global parameter Gc_uiMaxRequest. |
|
i_refUserData |
REFERENCE TO [UserData] |
Reference to the UserData that must be available on the controller for storing the SQL data read from the database. |
|
In_Out |
Data type |
Description |
|---|---|---|
|
iq_stExtendedConnSettings |
ST_ExtendedConnectionSettings |
Contains the information for connecting to an SQL Gateway. |
|
Output |
Data type |
Description |
|---|---|---|
|
q_xActive |
BOOL |
Indicates that the execution of the function block is active. As long this output is TRUE, the function block must be executed cyclically. |
|
q_xReady |
BOOL |
Indicates that the initialization was successful and the TCP connection is established. This output signals a TRUE as long as the function block is capable of accepting inputs. |
|
q_xBusy |
BOOL |
If this output is set to TRUE, the function block execution is in progress. |
|
q_xDone |
BOOL |
If this output is set to TRUE, the execution has been completed successfully. |
|
q_xError |
BOOL |
If this output is set to TRUE, an error has been detected. For details, refer to q_etResult and q_etResultMsg. |
|
q_etResult |
ET_Result |
Provides diagnostic and status information. |
|
q_sResultMsg |
STRING[255] |
Provides additional diagnostic and status information. |
|
q_uiRetNumOfRows |
UINT |
Number of rows in the returning data. This output is updated with the number of records which was received from the SQL database.
NOTE: Only valid after a successful read request.
|
|
q_uiRetNumOfColumns |
UINT |
Number of columns in the returning data. This output is updated with the number of records which was received from the SQL database.
NOTE: Only valid after a successful read request.
|
For more information, also refer to Common Inputs and Outputs.
Defining an ARRAY of User Data
A two-dimensional ARRAY must be available on the controller for intermediate storage of SQL data read from the database. The two-dimensional ARRAY is defined in ALIAS UserData.
The size of the ARRAY can be adapted via the global parameters Gc_uiMaxRows, Gc_uiMaxColumns, and Gc_uiTableWstringLength.
When you configure these parameters, consider the amount of SQL data that you expect to be received. Before data transfer is started, SQL data is segmented according to the size of this buffer.
If the SQL data that is received exceeds the size of the ARRAY, the SQL data transfer is stopped and the function block signals an error.