How to Transfer Data Via SQL Gateway Between EcoStruxure Machine Expert and Any Database

How Does It Work?

System Requirements

The following system requirements are needed:

oServer (for example, MySQL, not supplied by Schneider Electric)

oSQL Client (for example, MySQL Workbench, not supplied by Schneider Electric)

oDatabase, created with the SQL Client (for example, MySQL Workbench)

oSQL Gateway (is a separate installation on EcoStruxure Machine Expert DVD)

oSqlRemoteAccess Library (is part of the EcoStruxure Machine Expert installation)

How to Transfer Data Via SQL Gateway from EcoStruxure Machine Expert to 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.

In order to interface with the SQL Gateway, you may need information about the SQL Gateway and about the database from the administrator of the SQL environment. You need the IP address of the SQL Gateway, the connection name configured in the SQL Gateway, and the name of the database.

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 EcoStruxure Machine Expert to the Database

Declaration:

PROGRAM WriteData
VAR
     fbWrite           : SE_SQL.FB_SqlDbWrite;
     stConnSettings    : SE_SQL.ST_ConnectionSettings;
     refRequestWstring : SE_SQL.RequestWstring;
END_VAR

Implementation:

stConnSettings.wsDbConnectionName    := "ConnectionCountry";
stConnSettings.wGwPort               := 3457;
stConnSettings.wsGwIpAddress         := "10.128.154.47";
stConnSettings.timSqlTimeout         := T#20S;
//SQL Command to insert a new Country - MySQL-Syntax: INSERT INTO <database name>.<table name> (column1...columnN) values (value1...valueN)
//SQLite-Syntax: without "<database name>.")
refRequestWstring[1]                 := "INSERT INTO world.countries (Country, CapitalCity, Language)";
refRequestWstring[2]                 := "values ('Germany', 'Berlin', 'german')";

fbWrite(    i_xExecute             := TRUE,
iq_stConnSettings                  := stConnSettings,
i_refRequestWstring                := refRequestWstring,
i_uiNumOfWstrings                  := 2
);

Step	Action
1	In the ST_ConnectionSettings, set the IP address (wsGwIpAddress) of your PC where the gateway is running, for example, 10.128.154.47.
2	In the ST_ConnectionSettings, set the port (wGwPort) of the gateway (default: 3457).
3	In the ST_ConnectionSettings, set the connection name (wsDbConnectionName) which is configured in the SQL Gateway, for example, ConnectionCountry.
4	In the ST_ConnectionSettings, set the time-out (timSqlTimeout) for example, T#20S.
5	Write your request for writing in the refRequestWstring, for example, INSERT INTO world.countries (Country) values ('France').
6	In the i_uiNumOfWstrings, set your lines in the refRequestWstring, for example, 2.
7	Call the FB_SqlDbWrite with the settings/parameters/variables above.

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

Read Data from EcoStruxure Machine Expert to the Database

Declaration:

PROGRAM ReadData
VAR
        fbRead            : SE_SQL.FB_SqlDbRead;
        stConnSettings    : SE_SQL.ST_ConnectionSettings;
        refRequestWstring : SE_SQL.RequestWstring;
        refUserData       : SE_SQL.UserData;
END_VAR

Implementation:

stConnSettings.wsDbConnectionName  := "ConnectionCountry";
stConnSettings.wGwPort             := 3457;
stConnSettings.wsGwIpAddress       := "10.128.154.47";
stConnSettings.timSqlTimeout       := T#1 M;

//SQL Command to read all Countries in the database - MySQL-Syntax: SELECT (column1...columnN) FROM <database name>.<table name> [where <column>=<value>]
//SQLite-Syntax: without "<database name>."
refRequestWstring[1]               := "SELECT * FROM world.countries";

fbRead  (      i_xExecute            := TRUE,
               iq_stConnSettings     := stConnSettings,
               i_refRequestWstring   := refRequestWstring,
               i_uiNumOfWstrings     := 1,
               i_refUserData         := refUserData
);

Step	Action
1	In the ST_ConnectionSettings, set the IP address (wsGwIpAddress) of your PC where the SQL Gateway is running, for example, 10.128.154.47.
2	In the ST_ConnectionSettings, set the port (wGwPort) of the gateway (default: 3457).
3	In the ST_ConnectionSettings,, set the connection name (wsDbConnectionName) which is configured in the SQL Gateway, e.g ConnectionCountry.
4	In the ST_ConnectionSettings, set the time-out (timSqlTimeout), for example, T#20S.
5	Write your request for reading in the refRequestWstring, for example, SELECT * FROM world.countries.
6	In the i_uiNumOfWstrings, set your lines in the refRequestWstring, for example, 1.
7	In the i_refUserData, set your UserData Array.
8	Call the FB_SqlDbRead 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.