Supporting Relational Databases in LabVIEW Real-Time

A real-time application typically involves a balance between deterministic and non-deterministic processes. A common non-deterministic operation is the ability to access a relational database. This article will discuss how to incorporate relational databases into a real-time target application.

Introduction

Databases are a large topic with domain nomenclature and caveats.  In order to make this article effective at presenting options for database support on real-time targets we will begin defining common terms used in the rest of the article and then present three possible scenarios which will cover most application needs.

Relational Database

A relational database uses the relationship of similar data to increase the speed and versatility of the database. The idea is that data is represented very similar to a spreadsheet.  You have a table which is the sheet, fields that are columns, and records which are the rows.  The data in the fields are of a similar type (i.e. numbers, text, dates), where as the relationships occur in the records.  An example record would be Last name, First name, Address, where each comma signifies a field.  

SQL

Typically you search a database using SQL (pronounced sequel), which is a computer language specifically designed for database queries. For more information about SQL please refer to the third-party web site w3cschools.com

National Instruments Database Connectivity Toolkit

National Instruments provides the Database Connectivity Toolkit (DCT) to aid with reading and editing databases that have an  ODBC driver.  Because ODBC is a technology based on Microsoft's™ ActiveX Data Object (ADO), the DCT cannot be used on real-time targets.  

National Instruments Citadel Database

The LabVIEW Datalogging and Supervisory Control (DSC) Module uses the National Instruments Citadel historical database to log shared variable data from the Shared Variable Engine. The DSC Module also includes the Citadel ODBC driver, which contains special commands to perform data transforms, so you can retrieve, manipulate, and analyze historical data automatically from outside the LabVIEW environment.  The Citadel database is ideal for logging alarm and event based data from shared variables.  For more customized solutions, a lower-level of database implementation will be needed.  In particular the ability to easily run SQL commands on a database is desirable.

The rest of this article does not deal with the Citadel Database, but it should be noted that for most data logging applications DSC is a very attractive solution.  For more information please refer to the Datalogging Supervisory Control Module Help

How to Support Relational Database Functionality on Real-Time targets

National Instruments real-time targets run either the PharLap or VxWorks real-time operating systems.  Because PharLap and VxWorks does not support ActiveX we cannot use the Database Connectivity Toolkit or any common database providers like Microsoft Access™.  The following are three possible scenarios to support relational database functionality on real-time targets.  After each example the advantages and disadvantages of each will be discussed, along with an example.

Host-Target Database Connection

To provide a real-time application with a user interface typically there is a host-side executable running, with a consistent connection to the real-time target.  The connection is a common communication protocol such as TCP or UDP.  When the real-time target needs to write/read from a database, a formatted message will be sent with the necessary information to the host.  The host receives the information, and because it is an OS that supports database connections, can relay that information to the database.

Advantages

• Simple.  Most real-time application already have some form of structured communicate between the host and target.
• If your host is able to make an ODBC connection (Windows-based) then the host side can be implemented using the Database Connectivity Toolkit, which improves simplicity.

Disadvantages

• Requires the maintenance of two connections, one between the Real-Time Target and host and another from the host to the database.
• If the connection between the host and target become intermittent, then a buffering scheme will be needed to store database operations.

Example of Host-Target Database Connection

The rt_stm_db.zip example requires the Database Connectivity Toolkit, and the Simple Messaging reference library to be installed.

ADO Schema XML Recordsets

The Database Connectivity Toolkit offers additional features such as being able to read/write recordsets (SQL queries) to XML files using the "DB Tools Save Recordset to File" and "DB Tools Load Recordset from File" VIs.  Originally these VIs were intended to facilitate putting LabVIEW data/datatypes into 3rd party databases, but since the schema is a standardized by ADO we can use it to write recordsets to file on the real-time target.  Similar to the "Host-Target Database Connection" scenario:

1.  Now we save recordsets to local disk on the real-time target, 
2. after some specified amount of time the Host initiates an FTP command of the XML Recordsets,
3. and finally the recordsets are entered into the database using the Database Connectivity Toolkit VIs.

Advantages

• Since we always save the data to file first, intermittent connection problems are not a large problem.
• Integration into the database is very simple because of the DCT.

Disadvantages

• File I/O is typically slower than sending information back to the host over ethernet.
• Smaller targets may need larger drive space if FTPing is at a slow rate.
• Only takes care of the situation where the RT target sends information to the Host, not where the RT target queries the database.  To support that you would need another communication connection.

Example of ADO Schema XML Recordset

The xml_recordset.zip example requires the Database Connectivity Toolkit.  This examples shows how we can support Strings, Long and Double datatype into the ADO XML file.

SQLite for VxWorks

Up till now we have discussed how to communicate data from the real-time target to the host, and have the host entirely manage the database connection.  An alternative is to have all database operations occur on the real-time target.  SQLite is a software library that implements a serverless, zero-configuration, transactional SQL database engine.  Compiling SQLite for real-time operating systems allows for self-contained relational database support on National Instruments real-time targets (currently VxWorks only).

Advantages

• Entirely self-contained on the real-time target.
• SQLite fully supports SQL
• SQLite  is free source code
• The SQLite Engine is small and compact making it perfect for embedded applications

Disadvantages

• The SQLite Engine is connectionless. To query data from the database and return it to the host requires a host-target connection
• SQLite is maintained by by the SQLite community and not National Instruments
• File I/O and String operations will increase CPU and memory requirements

Example of SQLite for VxWorks

The lv_sqlite.zip example includes a readme with instructions on how to install the SQLite Engine onto your VxWorks target.  For more information about SQLite please refer to www.sqlite.org.

Feedback

These examples were created by the NI Systems Engineering group.

You can give us feedback by posting questions and comments through the Supporting Databases in LabVIEW Real-time discussion thread.

We do not regularly monitor Reader Comments posted on this page.

Requirements

Filename: xml_recordset.zip

Software Requirements

Application Software: LabVIEW Professional Development System

Filename: lv_sqlite_103.zip

Software Requirements

Application Software: LabVIEW Development System 2009

Filename: rt_stm_db.zip

Software Requirements

Application Software: LabVIEW Professional Development System 2009

Reader Comments | Submit a comment »

Downloads for LV 8.2
Please add downloads for Labview 8.2 also.
- shourya singh, Logic ++. shouryasingh1@gmail.com - Sep 20, 2009