How Does It Work?

System Requirements

The following system requirements are needed:

SQL Server (for example, MySQL, not supplied by Schneider Electric)
SQL Client (for example, MySQL Workbench, not supplied by Schneider Electric)
Database, created with the SQL Client (for example, MySQL Workbench)
SQL Gateway (is a separate installation on EcoStruxure Machine Expert DVD)
SqlRemoteAccess Library (is part of the EcoStruxure Machine Expert installation)

How to Transfer Data Via SQL Gateway Between Controller and Any Database

Before data can be transferred, the SQL connection must be built with the SQL Gateway. For detailed information, refer to the SQL Gateway User Guide.

The function blocks for data exchange require the IP address of the SQL Gateway and the port where it is listening. In addition, the connection name, by which the connection to the SQL database is configured inside the gateway, must be provided to the function block.

NOTE: The SQL syntax in code examples in this description depends on a MySQL server, if you use another SQL server, the commands can be different.

Write Data from a Controller to the Database Using a Secured TCP Connection

Declaration:

PROGRAM WriteData_Secured
VAR
    fbSqlDbRequest          : SE_SQL.FB_SqlDbRequest;
    stExtConnectionSettings : SE_SQL.ST_ExtendedConnectionSettings;
    refRequestWstring       : SE_SQL.RequestWstring;
    refUserData             : SE_SQL.UserData;
    etSqlRequest            : SE_SQL.ET_SqlRequest;
END_VAR

Implementation:

stExtConnectionSettings.wsDbConnectionName      := "ConnectionCountry";
stExtConnectionSettings.wsGwIpAddress           := "10.128.154.47";
stExtConnectionSettings.wGwPort                 := 3458;
stExtConnectionSettings.timSqlTimeout           := T#5S;
stExtConnectionSettings.timSqlConnectionTimeout := T#10S;
etSqlRequest                            := SE_SQL.ET_SqlRequest.Write;

// SQL Command to insert a new Country
refRequestWstring[1] := "INSERT INTO world.countries (Country, CapitalCity, Language)";
refRequestWstring[2] := "values ('Germany', 'Berlin', 'german')";

IF NOT fbSqlDbRequest.q_xReady THEN
  fbSqlDbRequest(i_xEnable:= TRUE,
                 i_xExecute:= FALSE,
                 i_etSqlRequest:= etSqlRequest,
                 i_refRequestWstring:= refRequestWstring,
                 i_uiNumOfWstrings:= 2,
                 i_refUserData:= refUserData,
                 iq_stExtendedConnSettings:= stExtConnectionSettings);
ELSE
  fbSqlDbRequest(i_xEnable:= TRUE,
                 i_xExecute:= TRUE,
                 i_etSqlRequest:= etSqlRequest,
                 i_refRequestWstring:= refRequestWstring,
                 i_uiNumOfWstrings:= 2,
                 i_refUserData:= refUserData,
                 iq_stExtendedConnSettings:= stExtConnectionSettings);
END_IF

Description of the parameters used

Step	Action
1	In the ST_ExtendedConnectionSettings, set the connection name (wsDbConnectionName) which is configured in the SQL Gateway, for example, ConnectionCountry.
2	In the ST_ExtendedConnectionSettings, set the IP address (wsGwIpAddress) of your PC where the gateway is running, for example, 10.128.154.47.
3	In the ST_ExtendedConnectionSettings, set the port (wGwPort) of the gateway (default: 3458).
4	In the ST_ExtendedConnectionSettings, set the time-out (timSqlTimeout) for example, T#5s.
5	In the ST_ExtendedConnectionSettings, set the time-out (timSqlConnectionTimeout) for example, T#10s.
6	Set the input i_etSqlRequest to SE_SQL.ET_SqlRequest.Write.
7	Write your request for writing in the refRequestWstring, for example, INSERT INTO world.countries (Country) values ('France').
8	In the i_uiNumOfWstrings, set your lines in the refRequestWstring, for example, 2.
9	Call the FB_SqlDbRequest with the settings/parameters/variables above.

NOTE: It is only possible to send one request per function block call.

Read Data with a Controller from a Database Using a Secured TCP Connection

Declaration:

PROGRAM ReadData_Secured
VAR
    fbSqlDbRequest          : SE_SQL.FB_SqlDbRequest;
    stExtConnectionSettings : SE_SQL.ST_ExtendedConnectionSettings;
    refRequestWstring       : SE_SQL.RequestWstring;
    refUserData             : SE_SQL.UserData;
    etSqlRequest            : SE_SQL.ET_SqlRequest;
END_VAR

Implementation:

stExtConnectionSettings.wsDbConnectionName      := "ConnectionCountry";
stExtConnectionSettings.wsGwIpAddress           := "10.128.154.47";
stExtConnectionSettings.wGwPort                 := 3458;
stExtConnectionSettings.timSqlTimeout           := T#5S;
stExtConnectionSettings.timSqlConnectionTimeout := T#10S;
etSqlRequest                            := SE_SQL.ET_SqlRequest.Read;

// SQL Command to read all Countries in a database
refRequestWstring[1] := "SELECT * FROM world.countries";

IF NOT fbSqlDbRequest.q_xReady THEN
  fbSqlDbRequest(i_xEnable:= TRUE,
                 i_xExecute:= FALSE,
                 i_etSqlRequest:= etSqlRequest,
                 i_refRequestWstring:= refRequestWstring,
                 i_uiNumOfWstrings:= 1,
                 i_refUserData:= refUserData,
                 iq_stExtendedConnSettings:= stExtConnectionSettings);
ELSE
  fbSqlDbRequest(i_xEnable:= TRUE,
                 i_xExecute:= TRUE,
                 i_etSqlRequest:= etSqlRequest,
                 i_refRequestWstring:= refRequestWstring,
                 i_uiNumOfWstrings:= 1,
                 i_refUserData:= refUserData,
                 iq_stExtendedConnSettings:= stExtConnectionSettings);
END_IF

Description of the parameters used

Step	Action
1	In the ST_ExtendedConnectionSettings, set the connection name (wsDbConnectionName) which is configured in the SQL Gateway, for example, ConnectionCountry.
2	In the ST_ExtendedConnectionSettings, set the IP address (wsGwIpAddress) of your PC where the SQL Gateway is running, for example, 10.128.154.47.
3	In the ST_ExtendedConnectionSettings, set the port (wGwPort) of the gateway (default: 3458).
4	In the ST_ExtendedConnectionSettings, set the time-out (timSqlTimeout), for example, T#5s.
5	In the ST_ExtendedConnectionSettings, set the time-out (timSqlConnectionTimeout ) for example, T#10s.
6	Set the input i_etSqlRequest to SE_SQL.ET_SqlRequest.Read.
7	Write your request for reading in the refRequestWstring, for example, SELECT * FROM world.countries.
8	In the i_uiNumOfWstrings, set your lines in the refRequestWstring, for example, 1.
9	In the i_refUserData, set your UserData array.
10	Call the FB_SqlDbRequest with the settings/parameters/variables above.

Advanced Settings for the Data Buffer / Request with the Global Parameters

NOTE: The advanced settings can be overwritten specifically for your project in the Library Manager.

Step	Action
1	In the Gc_uiMaxRows, set the maximum number of rows for the ARRAY of UserData (only needed for FB_SqlDbRead), for example, 20. Result: 20 rows from the configured database can be read.
2	In the Gc_uiMaxColumns, set the maximum number of columns for the ARRAY of UserData (only needed for FB_SqlDbRead), for example, 10. Result: 10 columns from the configured database can be read.
3	In the Gc_uiTableWstringLength, set the maximum number of characters in the 2-dimensional ARRAY of UserData (only needed for FB_SqlDbRead), for example, 200. Result: One field with 200 characters from the configured database can be read.
4	In the Gc_uiMaxRequest, set the maximum lines of the ARRAY of RequestWstring, for example, 20. Result: The request can be split into 20 lines of WSTRINGs.
5	In the Gc_uiRequestWstringLength, set the maximum number of characters in the ARRAY of RequestWstring, for example, 200. Result: One request line can include 200 characters.