iQ-R MES Interface RD81MES96N - using stored procedures

iQ-R MES Interface RD81MES96N - using stored procedures

Info
This article explains how to execute stored procedures with a RD81MES96N MES Interface module based on an example using MS SQL Server 2017. Files used in the example can be found in attachments.

A stored procedure is a prepared SQL code that you can save in your database, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed. The iQ-R MES Interface module RD81MES96N can access both input and output parameters of the procedure.

Prepare a stored procedure

Stored procedures can be quite complex and do a lot of things. For a simple example in this article we use a procedure that takes a table name as an input parameter, counts the number of rows in that table and returns it as an output parameter:


It can be executed directly on the server from SQL Server Management Studio using a query like this:


Configure database connection

The first necessary steps to configure MES Interface (preparation on database side, installation of MES Interface Function Configuration Tool, connecting to the module, configuring network settings and adding a database connection) are explained in this article: Configure RD81MES96N(new version) module with MS SQL Server 2017

Add stored procedure configuration

To be able to use a stored procedure in your MES Interface application, it needs to be added to the project similar to a table. To do this navigate to Access Table/Proc. Settings in the MES Interface Function Configuration Tool, select a free row and click Edit:


In the configuration window set the name of your procedure (1), select the target database server (2) and switch from Access Table to Access Procedure (3). Then click Browse DB Procedure Information (4):


Another window will pop up. If you have a working connection to the database, it will display all available stored procedures:


After selecting the procedure, input and output parameters will be read automatically:


Add tags and jobs

Tags configuration

To use the configured procedure, first create tags to be used in your job:


In this example 4 tags are used:
  1. JobStartupRequest (Bit M0): used to trigger the job
  2. JobCompletionNotification (Bit M1): confirmation that the job was executed
  3. StoredProcedureParameter (Unicode String D0-D19): input parameter - name of the DB table we want to count
  4. StoredProcedureReturnedValue (Unsigned Word D20): output parameter - number of rows from the table

Job configuration

Add a job to execute the procedure:


Tags JobStartupRequest and JobCompletionNotification are used in the Trigger Conditions tab:


In Main-Processing tab add a DB Communication Action:


In the popup window select the Stored Procedure type (1), choose your procedure (2) and assign previosly configured tags to the procedure parameters (3):


After the configuration upload the project to your MES Interface module and the PLC project to your iQ-R CPU.

Testing the application


No additional programming is required in the PLC, all that is included in the sample is resetting the trigger variable after job completion:


To test your application, first write your database table name to the StoredProcedureParameter variable, then set the JobStartupRequest bit to trigger the job. After a successful execution the number of rows in that table should appear in the StoredProcedureReturnedValue variable.




    • Related Articles

    • iQ-R - FTP Server using SD card

      This article explains how to prepare the iQ-R PLC to use the FTP Server function with access to files on the SD card. 1. FTP Server configuration in Module Parameter First, go to the Module Parameter window to enable the FTP Server. You can also ...

    • iQ-R - built-in database

      This video shows how to configure and manage built-in database in iQ-R controller, how to access to using internal function blocks and also external software

    • Iconics Suite - Melsoft connection with iQ-R

      Detailed description how to configure connection between Iconics Suite SCADA and iQ-R PLC controller using Melsoft Connection Remember that your PC has to have the static IP address in the same subnet as PLC. iQ-R PLC default address is 192.168.3.39 ...

    • Configure GOT2000 MES Interface function with MS SQL 2017

      This article will show You how to configure GOT2000 panel to log data to MS SQL 2017. Prepare before configuration - Install Microsoft SQL Server Express 2017 (called MS SQL Server). - Install Microsoft SQL Server Management Studio. - Install DB ...

    • INV - FR-E800 - How to inverter control by iQ-R over CC-Link IE Field Basic

      This article how to set up CC-Link IE Field Basic connection between iQ-R PLC and FR-E800 inverter. It also presents ready to use function blocks to realize basic control, read inverter monitors as well as read and write FR-800 parameters through the ...