OPC Router Tutorial Backend

Following the eleventh part of the OPC Router tutorial, I’m presenting how to implement more advanced backend features for our recently created REST API. Remember, we’re configuring and running a project for “Papín”, a fictional potato bread factory.

Why an OPC Router tutorial with Backend?

Because “learning by doing” with examples is fun. Also, this functionality can provide us with a unique use case: the power to deploy the backend for a web-enabled application in a very intuitive visual way, which is an outstanding option when we want to implement some business logic simpler and faster. See below a list of posts that complete this tutorial:

Considerations

From this point on, we must download and install OPC Router v4.27.5016.75 or later to make this feature work appropriately. Also, OPC Router Config was renamed to OPC Router Management.

Prerequisites

Firstly, you’ll need:

Google Chrome, Mozilla Firefox, or Microsoft Edge web browser 🌐
Microsoft SQL Server database 🧱
Microsoft SQL Server Management Studio (SSMS) 🎙️ (or equivalent application)
FactoryTalk Linx Gateway as OPC DA server 🛤️
ControlLogix 1756-L61 as Programmable Automation Controller (PAC) 🤖
Schematic diagram 📝
OPC Router Management application 💻

User Story

We want to build a REST interface that allows us to take some calculated values from a SQL Server database, and send them back to an industrial automation controller through an OPC DA server.

Schematic Diagram

To illustrate this new approach better, look at this:

Additionally, make sure to complete the previous post here.

Updates to our REST API design

At this point, add the new resources that will be exposed by this service:

HTTP Method	Resource Name	Resource Action
GET	/AvgValueMCurrPln01	Average value of MCurr at PLN01.
GET	/MinValueMTorqPln02	Minimum value of MTorq at PLN02.
GET	/MaxValueMSpdPln03	Maximum value of MSpd at PLN03.
GET	/OptimalValueMSpdPln03	Optimal value of MSpd at PLN03.

Check your output data channel

At this instant, make sure to check OPC topic “P_PBF19_PA01” for 1756-L61 controller through Factory Talk Linx Gateway OPC DA interface.

In order to check the transfer, monitor variables within “Controller Tags” tab:

Create and Test Stored Procedures

sp_avg_mcurr_pln01

To begin with, consider saved MCurr values at PLN01:

Then, the idea I propose here is for the database engine to calculate the average value of MCurr at PLN01:

With this in mind, notice that you should expect an average value of 108.224545.

AVERAGE(MCurr) = 108.224545

At this point, let’s create and test our first database stored procedure as “dbo.sp_avg_mcurr_pln01“:

Initially, run SSMS and connect to the database server.

Press “Ctrl +N” or click the “New Query” button to start this process:

Enter the SQL code below:

USE [papinpbf19_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_avg_mcurr_pln01](
@cd_location1 bigint  OUT,
@cd_location2 bigint  OUT,
@cd_location3 bigint  OUT,
@cd_variable  bigint  OUT,
@avg          DECIMAL(18,5) OUT
)
AS
BEGIN

    SELECT @avg = AVG(qt_value)
      FROM pv.tb_rv_vsd_motor_i
     WHERE (
	   (pv.tb_rv_vsd_motor_i.cd_location1 = @cd_location1)
       AND (pv.tb_rv_vsd_motor_i.cd_location2 = @cd_location2)
       AND (pv.tb_rv_vsd_motor_i.cd_location3 = @cd_location3)
       AND (pv.tb_rv_vsd_motor_i.cd_variable = @cd_variable))
    return @avg
END

Press “F5” or click the “Execute” button to run the new SQL query:

Following this, refresh the “Stored Procedures” folder:

After that, notice the newly created stored procedure:

Right click the stored procedure and click the “Execute Stored Procedure…” to begin the test.

Later, enter the test data and click “OK”.

Lastly, watch the results window and make sure it matches the expected (rounded) value.

sp_min_mtorq_pln02

To begin with, consider saved MTorq values at PLN02:

Then, the idea I propose here is for the database engine to calculate the minimum value of MTorq at PLN02:

With this in mind, notice that you should expect a minimum value of 350.12000.

MINIMUM(MTorq) = 350.12000

At this point, let’s create and test our second database stored procedure as “dbo.sp_min_mtorq_pln02“:

Enter and run the SQL code below:

USE [papinpbf19_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_min_mtorq_pln02](
@cd_location1 bigint  OUT,
@cd_location2 bigint  OUT,
@cd_location3 bigint  OUT,
@cd_variable  bigint  OUT,
@min          DECIMAL(18,5) OUT
)
AS
BEGIN

    SELECT @min = MIN(qt_value)
      FROM pv.tb_rv_vsd_motor_t
     WHERE (
	   (pv.tb_rv_vsd_motor_t.cd_location1 = @cd_location1)
       AND (pv.tb_rv_vsd_motor_t.cd_location2 = @cd_location2)
       AND (pv.tb_rv_vsd_motor_t.cd_location3 = @cd_location3)
       AND (pv.tb_rv_vsd_motor_t.cd_variable = @cd_variable))
    return @min
END

Later, enter the test data for the procedure and click “OK”.

Lastly, watch the results window and make sure it matches the expected value.

sp_max_mspd_pln03

To begin with, consider saved MSpd values at PLN03:

Then, the idea I propose here is for the database engine to calculate the maximum value of MSpd at PLN03:

With this in mind, notice that you should expect a maximum value of 707.08000.

MAXIMUM(MSpd) = 707.08000

At this point, let’s create and test our third database stored procedure as “dbo.sp_max_mspd_pln03“:

Enter and run the SQL code below:

USE [papinpbf19_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_max_mspd_pln03](
@cd_location1 bigint  OUT,
@cd_location2 bigint  OUT,
@cd_location3 bigint  OUT,
@cd_variable  bigint  OUT,
@max          DECIMAL(18,5) OUT
)
AS
BEGIN

    SELECT @max = MAX(qt_value)
      FROM pv.tb_rv_vsd_motor_s
     WHERE (
	   (pv.tb_rv_vsd_motor_s.cd_location1 = @cd_location1)
       AND (pv.tb_rv_vsd_motor_s.cd_location2 = @cd_location2)
       AND (pv.tb_rv_vsd_motor_s.cd_location3 = @cd_location3)
       AND (pv.tb_rv_vsd_motor_s.cd_variable = @cd_variable))
    return @max
END

Later, enter the test data for the procedure and click “OK”.

Lastly, watch the results window and make sure it matches the expected value.

Exploring Other Calculated Values

In this case, suppose you have these calculated MCurr, MTorq and MSpd values by a third-party algorithm within the “tb_cv_optimal_value” database table.

The idea I propose here is for OPC Router to select the most recent optimal value of MSpd at PLN03, but without the use of any stored procedure.

With this in mind, notice that you should expect an optimal value of 639.00000.

LAST_OPT(MSpd) = 639.00000

Creating Resources

/AvgValueMCurrPln01

At this time, let’s create the resource to return the average value of MCurr at PLN01 as “GET AvgValueMCurrPln01“:

Then, drag and drop these five blocks as shown here:

After that, edit the REST trigger block to include the new URL endpoint and the JSON response format:

At this point, edit the “Constant Values” block with a fixed location at PLN01 and the appropriate variable code:

Furthermore, edit the Database block and make sure it looks like this:

Also, edit the JSON Write block:

Finally, edit the OPC Data Access block with the specific controller tag:

Then, make sure all transfer objects look like these:

Following this, make sure to link blocks as shown here to complete the resource:

Following this, repeat the section “Compile, Save and Publish your OPC Router Tutorial Project Configuration with REST” to apply changes.

At this point, test the API with Swagger.

/MinValueMTorqPln02

At this time, let’s create the resource to return the minimum value of MTorq at PLN02 as “GET MinValueMTorqPln02“. Just copy, paste and rename from the “GET AvgValueMCurrPln01” connection.

After that, edit the REST trigger block to include the new URL endpoint and the JSON response format:

At this point, edit the “Constant Values” block with a fixed location for PLN02 and the appropriate variable code:

Furthermore, edit the Database block and make sure it looks like this:

Also, keep the JSON Write block:

Finally, edit the OPC Data Access block with the specific controller tag:

Following this, make sure to link blocks as shown here to complete the resource:

Following this, repeat the section “Compile, Save and Publish your OPC Router Tutorial Project Configuration with REST” to apply changes.

At this point, test the API with Swagger.

/MaxValueMSpdPln03

At this time, let’s create the resource to return the maximum value of MSpd at PLN03 as “GET MaxValueMSpdPln03“. Just copy, paste and rename from the “GET MinValueMTorqPln02” connection.

After that, edit the REST trigger block to include the new URL endpoint and the JSON response format:

At this point, edit the “Constant Values” block with a fixed location for PLN03 and the appropriate variable code:

Furthermore, edit the Database block and make sure it looks like this:

Also, keep the JSON Write block:

Finally, edit the OPC Data Access block with the specific controller tag:

Following this, make sure to link blocks as shown here to complete the resource:

Following this, repeat the section “Compile, Save and Publish your OPC Router Tutorial Project Configuration with REST” to apply changes.

At this point, test the API with Swagger.

/OptValueMSpdPln03

At this time, let’s create the resource to return the optimal value of MSpd at PLN03 as “GET OptValueMSpdPln03“. Just copy, paste and rename from the “GET MaxValueMSpdPln03” connection.

After that, edit the REST trigger block to include the new URL endpoint and the JSON response format:

At this point, edit the “Constant Values” block with a fixed location for PLN03:

Furthermore, edit the Database block so as to use a SELECT command instead of a stored procedure:

Also, keep the JSON Write block:

Finally, edit the OPC Data Access block with the specific controller tag:

Following this, make sure to link blocks as shown here to complete the resource:

Following this, repeat the section “Compile, Save and Publish your OPC Router Tutorial Project Configuration with REST” to apply changes.

At this point, test the API with Swagger.

Visualize Online Data in OPC Router

Also, you can watch live values here:

At this point, you should notice an execution time visually:

Verify Data Transfer to Controller Tags

Finally, watch how the OPC Router tutorial allows us to implement a light backend for a web-enabled application by sending calculated values from a SQL Server database to an industrial automation controller.

Firstly, watch the calculated average value of MCurr at PLN01 transferred to the controller.

Then, watch the calculated minimum value of MTorq at PLN02 transferred to the controller.

After that, watch the calculated maximum value of MSpd at PLN03 transferred to the controller.

Finally, watch the calculated optimal value of MSpd at PLN03 transferred to the controller.

Did you enjoy this OPC Router tutorial with Backend? I’d love to hear from you.

Stay tuned and follow part 13 of this tutorial! 😎

For other articles like this one in English, click here. To see them in Spanish, click here.

Share this post