Oil & Gas Solutions ODBC Interface
The VTScada ODBC interface may be used to access all data recorded by the child tags of the Flow Computer tag. This includes process data tags, hourly data tags, daily data tags, alarm and event data, and RTU configuration. The following sections detail how data from these tags can be accessed from the ODBC interface.
Process Tags
Data values from process tags will be logged to the historian in the same format as standard VTScada tags and can be accessed via ODBC queries using the table and column names described in the VTScada user documentation.
Hourly & Daily Log Format
Data values from hourly and daily log tags will be logged to the historian in the same format as standard VTScada tags (they are Analog Status tags) and can be accessed via ODBC queries using the table and column names described in the VTScada user documentation.
Alarm and Event Log Format
Alarm and event tags use a special table name combined with custom field names to store and retrieve the alarm and event data. The standard VTScada historian table “History” will only provide a single record per tag for a single timestamp. With alarm and event data, multiple alarms and events can have the same timestamp so queries of this data must use a different mechanism to retrieve this data. To allow reporting of all alarm and event logs, all queries of alarm and event data must use the table “HistoryWithDuplicates”.
Unlike most VTScada tags, the Alarm and Event tag in the Flow Computer has a number of fields that may be read from the historian table. These fields and their contents are listed in the following table.
| Field Name | Data Type | Contains |
|---|---|---|
| IsAlarm | Boolean | True (1) if the record is an alarm, false (0) if it's an event |
| IsEvent | Boolean | True (1) if the record is an alarm, false (0) if it's an event |
| EnronBitmap | Integer | The original "Bitmap" value read from the Enron device for both alarms and events |
| MeterRunNum | Integer | The meter run number that the alarm or event is associated with. Null for alarm and events not associated with a specific run number |
| ROCAlarmCode | Integer | The original "Alarm Code" value read from the ROC protocol device on alarm reports |
| ROCAlarmType | Integer | The original "Alarm Type" value read from the ROC protocol device on alarm reports |
| ROCFSTNum | Integer | The original "FST" value read from the ROC protocol device on event reports |
| ROCCalInfo | Integer | The original "Call Info" value read from the ROC protocol device on event reports |
| ROCCalType | Integer | The original "Cal Type" value read from the ROC protocol device on event reports |
| ROCParamNum | Integer | The original "Parameter Number" value read from the ROC protocol device on event reports |
| ROCPointType | Integer | The original "Point Type" value read from the ROC protocol device on event reports |
| ROCPointNum | Integer | The original "Point Number" value read from the ROC protocol device on event reports |
| AlarmValue | Float | The value of the alarm that was exceeded. Not supported (i.e. null) for ROC and Enron field I/O devices |
| EventValue | Float | The value for the event, sometimes known as the "Current value" |
| PrevValue | Float | The previous value for an event |
| ROCTimePerEvent | Float | The original "Time Per Event" value read from the ROC protocol device on event reports |
| AlarmAction | Text |
The alarm action being reported. Possible values are device dependent but may be one of the following: • Clear • Set • Pulse Input Clear • Pulse Input Set • SRBX Clear • SRBX Set |
| AlarmType | Text |
The alarm action being reported. Possible values are device dependent but may be one of the following: • Lo • LoLo • Hi • HiHi • Rate • Status Change • A/D Failure • Manual Mode • Redundant Total Count • Redundant Flow • No Flow • Logic • Input Freeze • EIA-485 Fail • Sensor Comm Fail • Off Scan Mode • Sequence Out of Order • Phase Discrepancy Detected • Inconsistent Pulse Count • Frequency Discrepancy • Channel A Failure • Channel B Failure |
| Descriptor | Text | A human readable description of the alarm or event |
| DeviceTypeCode | Text |
A code indicating the type of device that generated the alarm. Possible values are: • 0 (RealFlo) • 1 (TFlo) • 16 (ROC800) • 18 (FloBoss 103) • 20 (FloBoss 107) • 29 (FloBoss 107 with Alternate ROCNOC addressing) |
| IOAddress | Text | The address for the value |
| ROCEventText | Text | The original "Event Text" value read from the ROC protocol device on event reports |
| ROCOperatorID | Text | The original "Operator ID" value read from the ROC protocol device on event reports |
Example Queries of Alarms and Events
For a Flow Computer tag with the name “Well 27-102569”, the following query will return a list of all alarms for the month of October 2014:
SELECT Timestamp, 'Well 27-102569\Alarm and Event Log:MeterRunNum', 'Well 27-102569\Alarm and Event Log:Descriptor', 'Well 27-102569\Alarm and Event Log:EventValue', 'Well 27-102569\Alarm and Event Log:AlarmType', 'Well 27-102569\Alarm and Event Log:AlarmAction', 'Well 27-102569\Alarm and Event Log:IOAddress' FROM HistoryWithDuplicates WHERE 'Well 27-102569\Alarm and Event Log:IsAlarm' = 1 AND Timestamp >= '2014-10-01 03:00:00' AND Timestamp < '2014-10-15 03:00:00' ORDER BY Timestamp ASC
Note that the field names are a concatenation of the Flow Computer’s alarm and event tag name and the field name from the table above. This is the standard format for the VTScada ODBC interface, refer to SQL Queries.
To query alarms or events for specific flow meter run within the flow computer, include the meter run number in the Where clause of the ODBC query. The example below shows a query for all events from meter run #1 from Flow Computer tag “Well 27-102569” for the month of October 2014:
SELECT Timestamp, 'Well 27-102569\Alarm and Event Log:MeterRunNum', 'Well 27-102569\Alarm and Event Log:Descriptor', 'Well 27-102569\Alarm and Event Log:EventValue', 'Well 27-102569\Alarm and Event Log:PrevValue', 'Well 27-102569\Alarm and Event Log:IOAddress' FROM HistoryWithDuplicates WHERE 'Well 27-102569\Alarm and Event Log:IsEvent' = 1 AND 'Well 27-102569\Alarm and Event Log:MeterRunNum' = 1 AND Timestamp >= '2014-10-01 03:00:00' AND Timestamp < '2014-10-15 03:00:00' ORDER BY Timestamp ASC
Note the use of the “IsEvent” field in the event query vs. the use of the “IsAlarm” field in the alarm query. To get a re-port of all recorded alarms and events, leave the IsAlarm and IsEvent fields out of the where clause but add them to the selected fields to show what the report contains.
The RTU configuration log is recorded by the individual Meter Run tags primarily for the purpose of producing CFX file type reports and any others requiring detailed information regarding the configuration of the RTU. The configuration information recorded by VTScada consists of over 300 individual readings and settings, the details of which are beyond the scope of this manual. For a complete list of the fields available, use your ODBC reporting tool to explore the available values. The display below shows a small number of these as discovered by 3rd party ODBC query tool.
Dynacard Interface
Dynacard data can be retrieved by querying the DYNACARD_RECORDS table. The table fields are as follows:
| Field Name | Data Type | Contains |
|---|---|---|
| NAME | Text | Name of the Dynacard tag |
| TIMESTAMP | Timestamp | The Dynagraph timestamp |
| PARAMETER | Text | Name of the parameter whose value is contained in the row |
| VALUE1 | Float | Parameter value for summary data, X value for a XY data pair |
| VALUE2 | Float | Y value for an XY data pair |
