PortuguêsHOW TO VISUALIZE DATA IN AN MSSQL DATABASE THROUGH YOUR CP
NOTE: This tutorial was validated in version 3.20 of MasterTool. For the correct development of the tutorial below, your software version must be equal to or greater than this one.
If you want to know more about the Database communication with your PLC and how to use the writing and query functions in the Nexto Series, watch the Webinar below:
The LibSQLClient library allows a direct connection to be established between Nexto Series PLCs and a Microsoft SQL Server database. After establishing this connection, it is possible to perform operations of writing and reading information in a SQL Server database, using the structured query language SQL.
The PLC connection to the MSSQL server is performed via Tabular-Data-Stream Protocol (TDS), a typical TCP/IP protocol that uses port 1433.
Input Parameters
EXECUTE: Enables the function block on rising edge.
DB_LOGIN_PARAMS: Parameters for logging into the database. Description of parameters below.
SQL_COMMAND: String that houses the SQL command to be sent to the database.
QUERY_DATA: Variable used to store the data returned by the database after executing a SELECT command.
Output Parameters
DONE: Indicates whether the operation was completed.
BUSY: Indicates if there is an operation taking place.
ERROR: Indicates if there is an error executing the command.
STATUS: String that indicates the block working status.
Parameters DB_LOGIN_PARAMS
QUERY_DATA
Variable used to store the data returned by the database after a SELECT command.
In this case, the user must instantiate a variable of type C_QUERY_DATA and place it in the QUERY_DATA input so it does not return an invalid parameter error (MSSQL_INVALID_PARAMETERS).
SQL_STATUS_CODES error codes
Global Constants
It is also possible to resize the data table adjusting the maximum number of rows and columns that the QUERY_DATA structure will store. To do this the user must change the value of the global constants gc_dwMSSQLMaxRows and gc_dwMSSQLMaxColumns, described in the table below. They are found in the MSSQL_Client library in the Library Manager.
Furthermore, the user also has the option to change the maximum length of the strings that store the column names, as well as the strings that store the data returned by the server. The constants are set to store 15 rows, 10 columns, 200 character strings within the data array (asTableQuery) and 40 characters in the array with column names by default.
Read Code Demo
The code below exemplifies a read operation in the database through Nexto using the ST language. In the reading operation, it is important to highlight the addition of the QD variable of type QUERY_DATA to store the data read by the query.
After setting all the parameters and compiling the application, the operation in the database will take place on the rising edge of the variable "xExec", and the variable "xError" will remain in FALSE. If it becomes TRUE, an error occurred in the operation, which can be seen in the "msgError" variable.
Writing To An SQL Database
In this section, we will write to a database created in Microsoft SQL, using the INSERT command.
First of all, open Microsoft SQL Server and at the ‘Authentication’ option choose ‘SQL Server Authentication’ and fill in your Login and Password and click ‘Connect’.
Connected to the server, click on ‘New Query’ to create the table that will be written using MasterTool IEC XE commands.
In the tab that will open, write the program as the image below and click ‘Run’ so the table will be created.
With the table properly created, open a new project in MasterTool IEC XE 3.20, include the ‘LibSQLClient’ library as seen at the beginning of this tutorial, create a new POU in Structured Text and then write the program from the image below in this POU:
Note: In the sServer variable (underlined in red) the computer IP where the Microsoft SQL database is operating must be placed.
Set the ‘xExec’ variable to TRUE, as it will activate the ‘EXECUTE’ variable sending the INSERT command to the SQL database.
Lastly, open a ‘New Query’ in Microsoft SQL Server and write the program below and then click ‘Run’. The SELECT command will display the table updated.
