OPC ODBC
How to connect OPC to relational databases
Many companies today use ODBC compliant databases, such as SQL Server and
Oracle, to store business data. They also have a requirement to store and use
process data from the plant floor, lab systems, etc.
Rather than invest in a separate process historian, they can leverage their
existing assets by simply adding the ability to directly collect and share
process data from their existing database.
Relational Databases and Connection Methods
Most relational databases out there support ODBC (Open Database Communication).
This includes Microsoft SQL, Microsoft Access, Oracle, as well as many of the
open source relational databases like MySQL and PostgreSQL. Sometimes, the ODBC
driver comes with the database, other times, such as with MySQL, it must be
downloaded separately. With ODBC, we have a common interface that we can use to
speak to a multitude of databases. So we’re set right? Well, not necessarily.
For many databases, a direct or native driver will provide higher performance.
As an example, Oracle’s OCI (Oracle Call Interface) must be used for top
interface performance.
Table Structures
With table structures, there are two distinct methods of storing OPC Data.
Like a historian (time based)
Like a relational database
What’s the difference? It’s easier to show the difference in use:
First, how a historian would store the data:
| Tagname |
Value |
Quality |
Timestamp |
| Tag1 |
34 |
Good, non-specific |
11/13/2008 14:23 |
| Tag2 |
15.9473 |
Good, non-specific |
11/13/2008 14:23 |
| Tag1 |
34 |
Good, non-specific |
11/13/2008 14:25 |
| Tag2 |
15.8645 |
Good, non-specific |
11/13/2008 14:25 |
| Tag1 |
35 |
Good, non-specific |
11/13/2008 14:27 |
| Tag2 |
15.9824 |
Good, non-specific |
11/13/2008 14:27 |
| Tag1 |
35 |
Good, non-specific |
11/13/2008 14:29 |
| Tag2 |
15.7649 |
Good, non-specific |
11/13/2008 14:29 |
| Tag1 |
34 |
Good, non-specific |
11/13/2008 14:30 |
| Tag2 |
15.8318 |
Good, non-specific |
11/13/2008 14:30 |
| Tag1 |
34 |
Good, non-specific |
11/13/2008 14:32 |
| Tag2 |
15.6214 |
Good, non-specific |
11/13/2008 14:32 |
Now, a relational table:
| Sample# |
Timestamp |
Tag1 |
Tag2 |
| 1 |
11/13/2008 14:23 |
34 |
15.9473 |
| 2 |
11/13/2008 14:25 |
34 |
15.8645 |
| 3 |
11/13/2008 14:27 |
35 |
15.9824 |
| 4 |
11/13/2008 14:29 |
35 |
15.7649 |
| 5 |
11/13/2008 14:30 |
34 |
15.8318 |
| 6 |
11/13/2008 14:32 |
34 |
15.6214 |
Notice that the rows now correlate with a sample number.
Writing data into a database
Most historians on the market today come with OPC Clients. This is because an
OPC Client gives the historian the ability to support data collection from
thousands of devices. Simply add the necessary OPC Server, and you have
connectivity down to the device. This is great for the Historian vendors,
because they only have to make 1 interface, an OPC Client. Before, they had to
dedicate countless hours in developing device drivers for whatever devices
their customers were picking up. Relational databases have been developed in a
different direction than historians. Many people prefer to run their databases
on non-windows platforms. This can be a problem when trying to embed an OPC
Client, because OPC runs on DCOM, and DCOM is Microsoft technology. That’s not
to say that there aren’t 3rd party DCOM stacks out there, because there are.
It’s just that maintaining correct DCOM security, and utmost compatibility,
between OPC applications really does require Windows. What I’m trying to get at
is that you need to use a 3rd party application on a Windows PC to get
OPC data into a database.
|