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.