Academic Company Events NI Developer Zone Support Solutions Products & Services Contact NI MyNI

Document Type: Tutorial
NI Supported: Yes
Publish Date: Oct 23, 2007

Subscribe

Add to Del.icio.us

DIGG this!


Feedback


Yes No

Related Categories

Products

Development Topic

Industry

Technology

Related Links - Developer Zone

Related Links - Products and Services

Accessing Citadel 5 Data from Other Software

2 ratings | 2.00 out of 5
Print

Overview

This document describes Structured Query Language (SQL), Open Database Connectivity (ODBC), and how to access Citadel data using both SQL and ODBC. There are separate documents that explain how to set up Citadel as an ODBC data source and accessing the data from various ODBC clients.

Table of Contents

  1. Introduction
  2. Accessing Citadel Data
  3. Data Transform
  4. SQL Examples

Introduction

The Citadel historical database includes an Open Database Connectivity (ODBC) driver, which enables other applications to directly retrieve data from Citadel using Structured Query Language (SQL) queries.

What is ODBC?
ODBC is a standard developed by Microsoft. It defines the mechanisms for accessing data residing in database management systems (DBMSs). Nearly all Windows-based applications that can retrieve data from a database support ODBC. For example, you can use SQL from Microsoft Access, Visual Basic, Microsoft Query, and Microsoft Excel. See the related links for information on using SQL from these specific applications. This document gives more general SQL information.

What is SQL?
Structured Query Language (SQL) is a text based industry-standard language used for retrieving, updating, and managing data. In Lookout, you can use SQL to build queries to extract data from any ODBC compliant database. You can also use SQL from an ODBC client to retrieve data from a Citadel database. The Citadel ODBC driver also includes many built-in data transforms to simplify statistical analysis of retrieved data.


See Also:
Accessing Citadel 5 data with Microsoft Excel
Setting up Lookout's Citadel Database as an ODBC Data Source

Accessing Citadel Data

IntData Table
The ODBC driver presents Citadel data to other applications as a IntData table. The table contains a field or column for each data member logged to the Citadel database and three fields you can use to specify query criteria and to time stamp retrieved data: IntInterval, LocalTime, and UTCTime.

IntInterval specifies the query value sample rate. IntInterval can range from 10 ms to several years. IntInterval defaults to 1 (one day). WEEK is a standard seven days, but MONTH and YEAR account for different month lengths and leap years.

Because Citadel is event-driven, it only logs a value when the value changes. Using IntInterval, you can query Citadel for values evenly spaced over a period of time.

LocalTime and UTCTime are timestamps that indicate when values are logged. Citadel stores the time in UTCTime format and derives LocalTime from the stored time.

The following where clause query uses IntInterval and LocalTime to select data over a specified time at one-minute intervals. Notice that time and date formats are the same as those used in Lookout.
SELECT * FROM IntData
WHERE LocalTime>12/1 10:00 AND LocalTime<12/2 13:00 AND IntInterval=“1:00”

RawData Table
The RawData table is used to retrieve the actual values logged by Lookout for a data member and the times they were logged. As Lookout logs values for data members asynchronously, there is no correlation between the timestamps for one data member and another. For this reason, when querying the points table, you may only query one data member at a time.

The where clause using LocalTime and UTCTime is supported for the points table; however, IntInterval is not relevant to the RawData table. The data transforms are also not relevant to the RawData table and are not supported. An example of a query using the points table could be as follows:
SELECT LocalTime, Pot1 FROM RawData
WHERE LocalTime > "12/1 10:00" AND
LocalTime < "12/2 10:00"

Data Transform

Your queries can include special commands that perform data transforms to manipulate and analyze historical data. The following table lists data transform commands.
Data Transform Commands
Command
Transformation
MATH_MIN(Datapoint) Returns the minimum for Datapoint across the interval.
MATH_Max(Datapoint) Returns the maximum for Datapoint across the interval.
MATH_Avg(Datapoint) Returns the average for Datapoint across the interval.
MATH_STDEV(Datapoint) Returns the standard deviation for Datapoint across the interval.
MATH_STARTS(Datapoint) Returns the number of starts (that is, the number of transitions from OFF to ON) for Datapoint across the interval. For numeric points, 0.0 is interpreted as OFF, and all other numbers are treated as ON.
MATH_STOPS(Datapoint) Returns the number of stops (that is, the number of transitions from ON to OFF) for Datapoint across the interval.
MATH_ETM(Datapoint) Returns the amount of time Datapoint was in the ON state across the interval.
MATH_QUAL(Datapoint) There might be gaps in the historical data traces in Citadel because of machine shutdown, Lookout shutdown, or a similar occurrence. Qual returns the ratio of time for which valid data exists for Datapoint across the interval to the length of the interval itself. If valid data exists for only one-half of the interval, Qual returns 0.5.

Using these data transforms, you can directly calculate and retrieve complex information from the database such as averages and standard deviations, so you do not need to extract raw data and then manipulate it in another application.

For example, you need to know how many times a compressor motor started in December. You also need to know its total run time for the month. Use the following query to get your answers:
SELECT MATH_STARTS(PLC.MotorRun),MATH_ETM(PLC.MotorRun)
FROM IntData
WHERE LocalTime>=12/1/95 AND LocalTime<1/1/96 AND IntInterval=31

SQL Examples

The following examples are typical query statements; however, your queries might be much more involved, depending on your system requirements.

SELECT *
FROM IntData		 Retrieves the current value of every data member logged to Citadel. Because your query does not occur at the same moment in time as a PLC poll, signals scanned from PLCs are not included in the retrieved data.
SELECT *
FROM IntData
WHERE IntInterval=0:01		 Retrieves the value of every data member logged today in one-second increments. Notice that the interval value is enclosed in quotation marks.
SELECT LocalTime, Pot1
FROM IntData
WHERE LocalTime>8:50
AND IntInterval=0:01		 Retrieves and time stamps the value of Pot1 in one-second increments from 8:50 this morning to now. Names are enclosed by quotes.
SELECT LocalTime, AB1.I:3, MATH_MAX(AB1.I:3)
FROM IntData
WHERE LocalTime>10/1/95
AND LocalTime<11/1/95
AND IntInterval=1:00		 Retrieves and time stamps an Allen-Bradley PLC input in one-minute intervals for the month of October, 1995. This query also indicates the highest occurring input value of each minute.
SELECT LocalTime, OVEN1_SP, PLC.OVEN1_PV,
MATH_MAX(PLC.OVEN1_PV), MATH_MIN(PLC.OVEN1_PV),
MATH_AVG(PLC.OVEN1_PV)
FROM IntData
WHERE LocalTime>=14:00
AND LocalTime <15:00
AND IntInterval=1:00:00		 Retrieves an oven temperature at 3:00 p.m. and shows the highest, lowest, and average temperatures between 2 p.m. and 3 p.m.
2 ratings | 2.00 out of 5
Print

Reader Comments | Submit a comment »

 

Legal
This tutorial (this "tutorial") was developed by National Instruments ("NI"). Although technical support of this tutorial may be made available by National Instruments, the content in this tutorial may not be completely tested and verified, and NI does not guarantee its quality in any way or that NI will continue to support this content with each new revision of related products and drivers. THIS TUTORIAL IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND AND SUBJECT TO CERTAIN RESTRICTIONS AS MORE SPECIFICALLY SET FORTH IN NI.COM'S TERMS OF USE (http://ni.com/legal/termsofuse/unitedstates/us/).