PLC to SQL, Why Would I?

29Jun2029Dec21 LucidEngineer

https://www.linkedin.com/pulse/plc-sql-why-would-i-bill-mueller/

There exists a gap between the process historians and ODBC-enabled batch/SCADA services to get PLC tag data into a SQL database.

One-time-use data, such as lot numbers or dispensed totals, are not quite suitable for continuously monitoring in the historian, or hacking the Alarm/Event server to insert a custom string, or writing a periodic ADO script buried into the SCADA header display. But the data is valuable in a report for the end user.

We have ran into this gap in a couple recent projects focused on bringing various sources of plant floor data into a single report. In these cases, the stand-alone manufacturing units were designed with a single batching function and relied on the operator to record by hand process values for reporting. Hooking these systems up to the plant historian was fairly straight forward. The issue was defining a frame of meaningful data for the report.

We ended up using Rockwell’s Transaction Manager to fill this gap.

An existing flag in the PLC to start the machine sequence was used to trigger Transaction Manager to read data from the PLC, format the event record, and insert it into an existing SQL database. When the machine sequence completed, a second event record was inserted into the database.

These two records defined the time frame for the reports. Using FT Vantage Point, we were able to combine three data sources into a single report:

SQL Time Frame Database for Lot #, Start time, End time
OSI Pi Process Data for Temperature, Pressure, Aggregate Functions, etc…
SQL Alarm and Event Data for Operator Actions and Alarms

While quality and consistency were increased, it also made life easier for the operators by eliminating the need to periodically record the data by hand.

The ability to asynchronously grab PLC data and insert it into a database enabled the existing data infrastructure to produce meaningful and actionable reports.