Database Integration Tutorial#

Estimated time to read: 13 minutes

This tutorial integrates a legacy test station with MES using the Connect IoT Database driver. Many stations on a shop floor never speak an equipment protocol. They write their results into a relational database, and that table is the only integration point available.

The Database driver turns such a table into an equipment interface. It polls the table for you, builds the SQL from what you configure on the Automation Driver Definition, and raises an Automation Event for each row it finds. It also runs commands, so a workflow can read reference data or write back into the database.

flowchart TD
    Station[Test Station] -->|writes rows| DB[(StationDb)]
    DB <-->|polls, reads, writes| Driver(Database driver)
    Driver -->|new measurement event| Workflow(Automation workflow)
    Workflow -->|posts measurement| MES[(MES)]
    Workflow -->|confirms row processed| Driver

The test station never talks to MES directly. The driver watches the table on its behalf, and the workflow is what decides what a new row means and where it goes.

By the end you will have an Automation Controller that:

• Detects each new measurement row written by the test station.
• Posts the measurement to MES, one row at a time, without losing rows or reprocessing them.
• Writes the processing result back into the station database.
• Reads the recipe limits for a material on demand.

Database Overview#

Why a database integration#

A database integration fits when the equipment, or the software that drives it, already persists what you need:

• Legacy testers and inspection stations that log results to a local SQL Server or MySQL instance.
• Third-party line software with a documented database schema but no API.
• Intermediate staging tables written by a supplier gateway.

You do not change the station. You read what it already writes.

Key concepts#

• Trigger query - the query the driver runs on every polling cycle to detect that something changed. It is built from the event configuration, or supplied as a raw query.
• Result query - the query the driver runs after the trigger detects a change, to fetch the columns the event carries.
• Processing mode - decides whether the driver keeps polling immediately after raising an event, or waits for the workflow to confirm that the row was handled.
• Command - a query the workflow runs on demand: a select, an insert, an update, a delete, a stored procedure, or a raw statement.

Scenario#

Test station TEST-01 writes one row per measured unit into the table dbo.TestResults of the StationDb database:

Table: Structure of the TestResults table

A second table, dbo.RecipeLimits, holds the acceptance limits per recipe:

Table: Structure of the RecipeLimits table

The integration goals are:

• Flow A - detect each new row of TestResults, post the measurement to MES, and mark the row as processed.
• Flow B - read the limits of a recipe from RecipeLimits on demand, so the workflow can classify the measurement before posting it.

Prerequisites#

• A running Automation Manager and access to the GUI. If you are new to controllers and protocols, complete the Connect IoT - Basic Configuration Tutorial first.
• A reachable Microsoft SQL Server or MySQL server, and a database account that can read the two tables and update TestResults.
• The two tables created, with a few rows in RecipeLimits.
• A Resource in MES named TEST-01, marked as Connect IoT enabled. See How To: Mark an Entity Type as Connect IoT Enabled.

Demo environment (containers)#

For a self-contained trial, the following docker-compose.yml starts a single Microsoft SQL Server instance. The Automation Manager connects to it on localhost:1435. Host port 1435 is used instead of the default 1433 so the demo server does not clash with an existing SQL Server already using 1433.

services:
  sqlserver-station:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sqlserver-station
    ports:
      - "1435:1433"                      # host 1435 -> container 1433
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "Station#Passw0rd"
      MSSQL_PID: Developer

Start it with docker compose up -d, then create the database and the tables:

CREATE DATABASE StationDb;
GO
USE StationDb;
GO

CREATE TABLE dbo.TestResults (
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    MaterialName    NVARCHAR(50)   NOT NULL,
    RecipeName      NVARCHAR(50)   NOT NULL,
    Value           DECIMAL(18,3)  NOT NULL,
    MeasuredAt      DATETIME       NOT NULL DEFAULT GETDATE(),
    ProcessedStatus NVARCHAR(20)   NULL
);

CREATE TABLE dbo.RecipeLimits (
    RecipeName NVARCHAR(50)  NOT NULL PRIMARY KEY,
    LowerLimit DECIMAL(18,3) NOT NULL,
    UpperLimit DECIMAL(18,3) NOT NULL
);

INSERT INTO dbo.RecipeLimits (RecipeName, LowerLimit, UpperLimit)
VALUES ('RECIPE-A', 9.500, 10.500);

Simulate the station by inserting a row whenever you want to trigger the integration:

INSERT INTO dbo.TestResults (MaterialName, RecipeName, Value)
VALUES ('LOT-4471', 'RECIPE-A', 10.120);

Automation Protocol#

Create an Automation Protocol and select the package @criticalmanufacturing/connect-iot-driver-database. Configure the connection to the station database:

Table: Database protocol parameters used in this tutorial

Automation Driver Definition#

Create an Automation Driver Definition for the protocol above, and associate it with the Resource TEST-01.

Configure the new measurement event#

Add an event that detects each new row of TestResults.

Event configuration:

Table: New measurement event configuration

Add one event property per column the workflow needs. The columnName extended data binds the property to the database column:

Table: New measurement event properties

Configure the confirmation command#

Add a command that marks the row as handled and confirms the event, so the driver resumes polling.

Command configuration:

Table: Confirmation command configuration

Command parameters:

Table: Confirmation command parameters

With Status set to Processed and RowId set to 18, the driver runs:

Update [TestResults] Set ProcessedStatus = 'Processed' Where Id = 18

Add a second command of type ConfirmationDataProcessing, with a single parameter of type EventReply holding the event name. This is the command that tells the driver the row was handled and that it can raise the next event.

Configure the recipe limits command#

Add a command that reads the limits of one recipe.

Command configuration:

Table: Recipe limits command configuration

Command parameters:

Table: Recipe limits command parameters

With Recipe set to RECIPE-A, the driver runs:

Select LowerLimit, UpperLimit From [RecipeLimits] Where RecipeName = 'RECIPE-A'

and returns a single object:

{ "LowerLimit": 9.5, "UpperLimit": 10.5 }

Automation Controller#

Create an Automation Controller for the Resource TEST-01, associate the Automation Driver Definition with it, and create the setup workflow so the driver connects on start. See Create the Setup Workflow.

Flow A - Post each measurement to MES#

Overview#

A Data Flow workflow that reacts to the new measurement event, classifies the value against the recipe limits, posts it to MES, and confirms the row back to the driver.

When to use#

Use this pattern whenever the station database is the source of truth and every row must be processed exactly once, in order.

Build the workflow#

1. Add an On Equipment Event task and select the new measurement event. The event properties appear as outputs of the task.
2. Add an Execute Equipment Command task for the recipe limits command. Connect RecipeName from the event to its Recipe parameter.
3. Add a Condition or an Expression Evaluator task that compares Value against LowerLimit and UpperLimit, and produces a Passed or Failed result.
4. Add a Data Collection task, or an Execute Service Call task, that posts the measurement to MES for the Material named in MaterialName.
5. Add an Execute Equipment Command task for the confirmation command. Connect RowId from the event to its RowId parameter, and the classification result to its Status parameter.
6. Add an Execute Equipment Command task for the ConfirmationDataProcessing command, and run it after the update succeeds.

How it works at runtime#

• Every two seconds the driver runs the trigger query, which detects that the highest row number changed.
• The driver runs the result query for the next row and raises one event with the five configured properties.
• Because the processing mode is ContinueOnProcessingConfirmation, the driver stops raising further events for this table and waits.
• The workflow classifies the value, posts it to MES, updates ProcessedStatus, and runs the ConfirmationDataProcessing command.
• The driver resumes polling and raises the next event.

Expected outcomes#

• One MES record per row of TestResults, in the order the station wrote them.
• ProcessedStatus filled with Processed or Failed for every processed row.
• No row processed twice, and no row skipped while the workflow is busy.

Troubleshooting#

Table: Flow A troubleshooting

Flow B - Read reference data on demand#

Overview#

The recipe limits command is also useful on its own, in a Control Flow that answers a question from MES rather than reacting to the station.

When to use#

Use it when a workflow, or an operator action in MES, needs a value that only the station database holds.

Build the workflow#

1. Add a trigger that starts the flow, for example an On Timer task or a Message Bus Listener task.
2. Add an Execute Equipment Command task for the recipe limits command, and set its Recipe parameter.
3. Use the returned object in the rest of the flow.

How it works at runtime#

The command is a plain query. It is executed when the task runs, and nothing is polled. The resultType of SingleResult returns one object, so a recipe that does not exist returns an empty result rather than an error. Handle that case explicitly in the workflow.

Tuning the polling#

The polling interval and the batch size decide the load the integration puts on the database:

• Raise pollingInterval when the station writes rows infrequently. Every cycle is a query against the table.
• Set maxRowsToProcess when the station can write a burst of rows, so each event carries a bounded batch instead of everything found at once. It only applies when resultDataFormat is AllNew.
• Set databasePollingInterval on the protocol to control how often the driver checks the database for structural changes.
• Use rawTriggerQuery and rawResultQuery when the generated queries cannot express what you need, for example when the trigger needs a join or an index hint. A raw query replaces the generated one entirely.

Summary#

You configured a Database Automation Protocol, described a station table as an Automation Driver Definition, and built two workflows on top of it.

The pattern generalizes. Any table a machine writes to can become an Automation Event, and any query that machine's database can answer can become a command. The parts worth carrying over are the confirmation loop, which is what keeps rows from being lost or reprocessed, the write back into the station database, which makes the integration state visible to whoever maintains the station, and the polling settings, which is where a database integration succeeds or fails in production.