Access Citadel Data using ODBC Queries

You can access Citadel data using ODBC queries from data tables.

Aliases Table

The Citadel ODBC driver automatically generates alias names for traces or data sets whose names are longer than the Maximum Column Name Length value specified in the Citadel 5 ODBC Setup dialog box.

The LabVIEW Datalogging and Supervisory Control (DSC) Module stores generated alias names in an Aliases table. The Aliases table has two columns: AliasName and FullName.

Database tables and column names can be up 126 characters long and cannot contain some special characters. Some ODBC clients support only names up to 62 characters long. Note that the database URL is not included in the trace or data set name.

The alias name consists of a prefix and the original trace or data set name substring, so the total length of the alias string is equal to the Maximum Column Name Length value. The alias prefix has the following format ~XXXXXXXXXXXXXXXX_ whereXXXXXXXXXXXXXXXX is a 64-bit trace or data set ID.

For example, if the original shared variable URL is \\computer\my_database\my_process_folder\my_process\my_folder_1\my_folder_2\pot1.value and Maximum Column Name Length is 32, the alias name is ~A012ABC4045FE43A_r_2/pot1@value where A012ABC4045FE43A is the internal trace ID. Note that database URL (\\computer\my_database\) is not used for the alias and that certain special characters were mapped to supported characters.

Some ODBC clients do not handle certain special characters in column and table names. The ODBC clients replace special characters in shared variable names and data set names as shown in the following table:

The special characters changed in ODBC 5. If you are converting SQL queries from an earlier version earlier of the ODBC driver, you might have to rewrite any SQL queries you set up in the earlier processes.

IntData Table

With Citadel 5, the IntData table replaces the Traces table. The ODBC driver presents Citadel data to other applications as an IntData table. The table contains a field or column for each shared variable 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.

Because Citadel is event-driven, Citadel logs a value only when the value changes. Using IntInterval, you can query Citadel for values evenly spaced over a period of time. The Citadel service stores the time in UTCTime format and derives LocalTime from the stored time. You can configure the time zone per database through the Citadel 5 ODBC Setup configuration dialog box.

The following table illustrates the Citadel 5 IntData table columns:

IntInterval specifies the query value sample rate and can range from 1 ns to several years. The default value of IntInterval is 1 (one day). IntInterval appears as a regular table column. Display format depends on the IntInterval value specified in the WHERE clause of the query. Fixed intervals appear as hh:mm:ss.ffffff. ffffff is the fractional part of a second. Special intervals WEEK, MONTH, YEAR appear in days.

Refer to the following examples for how to use the intervals:

RawData Table

With Citadel 5, the RawData table replaces the Points table of Citadel 4. Use the RawData table to retrieve the actual values logged for a shared variable and the times they were logged. Because logging to Citadel takes place asynchronously, there is no correlation between the time stamps for one shared variable and another. For this reason, when querying the RawData table, you can query only one shared variable at a time.

The following table illustrates the Citadel 5 RawData table columns:

The possible values for Quality are in the following table:

The WHERE clause using LocalTime, UTCTime and LoggingTime is supported for the RawData table. However, IntInterval is not relevant to the RawData table. The data transforms also are not relevant to the RawData table and are not supported. Note that standard set functions (MAX, MIN, AVG, COUNT) are supported.

Dataset Tables

IntData and RawData tables contain all shared variables available in given Citadel database. Data set tables contain only shared variables available in particular data sets. There are DS_IntData_dataset and DS_RawData_dataset tables per data set. In addition to IntData or RawData table columns, data set tables have RunID and RunName columns.

In addition to the example queries, the WHERE clause can restrict query to specified data set run(s).

The following table illustrates the Citadel 5 DS_IntData and DS_RawData table columns: