> For the complete documentation index, see [llms.txt](https://docs.videc.de/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.videc.de/acron-9.3/en/acron_der_anlagenchronist/acron_odbc-treiber.md).
# ACRON ODBC driver
With the ACRON **ODBC driver**, ACRON provides an ODBC (**Open Database Connectivity** ) interface.
ODBC is a standardized application programming interface (API) developed by Microsoft which enables other applications to access data in ACRON. The application must have SQL (Structured Query Language) as its default data access language.
Applications offering an ODBC interface, such as Excel, can access the data created by ACRON by way of the ACRON ODBC driver.
Password protection as per user administration: Specifically this means that if user administration is enabled for the plant, the user concerned must exist as a minimum.
The following conditions and steps are necessary to be able to use the ACRON ODBC driver:
* The ACRON ODBC driver must be installed and configured.
To do this, run the ACRON ODBC Setup program and then select **Start menu>Control Panel>ODBC Data Sources** or under **Windows XP Start menu>Control Panel>Administrative Tools>Data Sources (ODBC)** to set the ACRON ODBC driver.
* The database application must support ODBC and SQL queries.
When entering the plant name, specify the **identification** of the plant, observing case-sensitive input.
* Password protection
When user administration is enabled the user must exist.
The ACRON ODBC driver supports ODBC Level 2.
Once you have entered the plant with the "ODBC Data Source Administrator", the ACRON ODBC driver gives you **read** access to the following virtual tables:
#### Access to standard tables
| | |
| -------------- | ----------------------------------------------- |
| "Process" | Process data |
| "Interval1" | Primary interval data |
| "Interval2" | Secondary interval data |
| "Day" | Daily data |
| "Week" | Weekly data |
| "Month" | Monthly data |
| "Year" | Annual data |
| "PV" | Process variables |
| "Messages” | All messages and malfunctions in message format |
| "Malfunctions" | Only malfunctions |
| "Maintenance" | Service data |
| "Event" | Event header data |
The unique key in all time-based tables is the time stamp and date of the record concerned.
The column names in the individual tables are formed as in the ACRON Form Designer, though the dot does not conform to ODBC format and is replaced by an underscore, e.g. variablename\_dval indicates the daily value.
Contrary to the normal conventions, a "select" without a "where" condition does not return all data, but only a record containing the current data from the database concerned. There are two reasons for this: Firstly, the system is not subjected to massive data volumes; and secondly, it is often only the current value which is of relevance, and no dynamic "where" condition is required.
The remaining column names are formed, as in all vertical reports, from the name of the process variable and a suffix for the property (e.g. **PV**\_pmax for the maximum process value).
The following example reads the time stamp and the process data of the process variables "am1" and "am2" for the time frame from December 14, 2011, 18:30:00 hours to January 14, 2012, 18:52:00 hours:
`SELECT Timestamp, am1_pval, am2_pval`
`FROM Process`
`WHERE (Timestamp>{ts '2011-12-14 18:30:00'} And Timestamp<{ts '2012-01-14 18:52:00'})`
The following example reads the date and the daily values of the process variables "am1" and "am2" for the time frame from January 1, 2012 until now:
`SELECT Date, am1_dval, am2_dval`
`FROM Day`
`WHERE (Date>={d '2012-01-01'})`
#### Access to Fast tables
To enable high-speed access to the key data, the Fast tables were implemented.
The following restrictions apply to Fast tables:
* The result is limited to 1 million lines.
* For comparison with the 'PVShortName' column only the operators '=' and 'LIKE' are possible.
* For comparison with the 'PVLongName' column only the operators '=' and 'LIKE' are possible.
* For comparison with the 'Quality' column only the operator '=' is possible. Use 'is ZERO' or ='M' or ='R' or ='O' or ='U'.
* For comparison with the 'PVType' column only the operator '=' is possible. Use ='A' or='M' or ='C'.
* The 'NumData' column can only be used once in the Where condition.
* The 'CharData' column cannot be used in the Where condition.
#### Names of Fast tables
All Fast tables have the same structure – the table name merely dictates the compression level of the data:
| Table name | Compression level |
| ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| FastProcess | Process data |
| FastInterval1. | Primary interval data |
| FastInterval2. | Secondary interval data |
| FastInterval3 | Additional interval 1 |
| FastInterval4 | Additional interval 2 |
| FastInterval5 | Additional interval 3 |
| FastInterval6 | Additional interval 4 |
| FastInterval7 | Additional interval 5 |
| FastInterval8 | Additional interval 6 |
| FastInterval\_Vxxx | Interval data with variable compression interval. The data are compressed to the desired interval from process data during the runtime. Accessing them takes longer as a result, of course. "xxx" stands for the desired interval in seconds, and the value may be between 3 and 43200 (3 seconds - 12 hours). So accessing the "FastInterval\_V120" table delivers 2-minute values. |
| FastDay | Daily data |
| FastWeek | Weekly data |
| FastMonth | Monthly data |
| FastYear | Annual data |
#### Layout of the Fast tables:
| Field no. | Column name | Explanation |
| --------- | ------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1 | Timestamp | Time stamp for process and interval data in local time |
| | TimestampUTC | Time stamp for process and interval data in UTC time |
| | Date | Date for daily, weekly and monthly data |
| | Year | Year for annual data |
| 2 | PVShortName | Unique identification of the process variable |
| 3 | PVLongName | Name of the process variable |
| 4 | NumData | Value for numerical process variables in floating point format with 18 significant digits |
| 5 | CharData | Either value of text process variables, assigned value key of numerical process variables or the formatted value of numerical process variables as ASCII |
| 6 | Quality | Quality information on the value: Blank entry=perfect 'M'=missing value 'R'=automatic substitute value 'O'=overwritten value 'U'=Unapproved value (only with approval management active) |
| 7 | PVType | Process variable type: 'A'=auto variable 'M'=manual variable 'C'=calculation variable |
`SELECT Timestamp, PVShortName, CharData, NumData FROM FastProcess`
`WHERE (Timestamp>{ts '2022-01-09 13:33:00'})`
`ORDER BY Timestamp, PVShortName`
**Result 1:**
| Timestamp | PVShortName | CharData | NumData |
| ------------------- | --------------------------- | ----------- | --------- |
| 2022-01-09 13:33:01 | am1 | 930.78 | 930.7789 |
| 2022-01-09 13:33:01 | am2. | 930.78 | 930.7789 |
| 2022-01-09 13:33:01 | Auto050197. | 588 | 587.7219 |
| 2022-01-09 13:33:01 | Auto50197\_1. | 1,523 | 1522.5008 |
| 2022-01-09 13:33:01 | Auto50197\_1. | 1,566 | 1566.3503 |
| 2022-01-09 13:33:01 | Autovariable\_num | -869.87 | -869.8732 |
| 2022-01-09 13:33:01 | Autovariable\_num | -609.12 | -609.1157 |
| 2022-01-09 13:33:01 | Autovariable\_Text\_Unicode | Sim text 48 | |
| 2022-01-09 13:33:01 | Event ID | 427 | 427 |
| 2022-01-09 13:33:01 | Event ID | 425 | 425 |
| 2012-01-09 13:33:01 | Event ID part | 364 | 364 |
| 2022-01-09 13:33:01 | Event key | 0 | 0 |
| 2022-01-09 13:33:01 | Event key | 5 | 5 |
| 2022-01-09 13:33:01 | Event average | 394 | 394 |
| 2022-01-09 13:33:01 | Event average | 393 | 393 |
| 2022-01-09 13:33:01 | Event count | 1 | 1 |
| 2022-01-09 13:33:01 | Event count | 1 | 1 |
| 2022-01-09 13:33:01 | Container | 44 | 43.8495 |
| 2022-01-09 13:33:01 | Dummy1. | 230 | 230.3013 |
| 2022-01-09 13:33:01 | Weather | Rain | 2 |
`SELECT Timestamp, PVShortName, CharData, NumData FROM FastProcess`
`WHERE (Timestamp>{ts '2012-01-09 13:33:00'})`
`AND (FastProcess.PVShortName Like 'A%')`
`ORDER BY Timestamp, PVShortName`
**Result 2:**
| Timestamp | PVShortName | CharData | NumData |
| ------------------- | --------------------------- | ----------- | --------- |
| 2012-01-09 13:33:01 | Auto050197. | 588 | 587.7219 |
| 2012-01-09 13:33:01 | Auto50197\_1. | 1,523 | 1522.5008 |
| 2012-01-09 13:33:01 | Auto50197\_1. | 1,566 | 1566.3503 |
| 2012-01-09 13:33:01 | Autovariable\_num | -869.87 | -869.8732 |
| 2012-01-09 13:33:01 | Autovariable\_num | -609.12 | -609.1157 |
| 2012-01-09 13:33:01 | Autovariable\_Text\_Unicode | Sim text 48 | |
`SELECT FastInterval4.Timestamp, FastInterval4.PVShortName, FastInterval4.PVLongName, FastInterval4.NumData, FastInterval4.CharData FROM FastInterval4 FastInterval4`
`WHERE (FastInterval4.Timestamp>{ts '2022-03-25 00:00:00'})`
`ORDER BY Timestamp, PVShortName`
## Definitions and mode of operation
ODBC **(Open DataBase Connectivity)** interfaces, developed by the Microsoft Corporation, was initially only available for Microsoft operating systems (MS-Windows, NT, Win32). VISIGENIC licensed the source code and ported ODBC to UNIX systems.
The ODBC interface is defined by:
* libraries with ODBC functions (C functions) which permit an application to connect to a database, send SQL commands and receive the query results
* SQL syntax, based on the specification of the X/OPEN and SQL Access Group (1992)
* a standard set of error codes
* a defined method of connecting to the DBMS (Data Base Management System)
* a standardized set of data types
## Architecture
The ODBC architecture comprises four layers:
1. ODBC Database Application
2. ODBC Driver Manager
3. ODBC Driver
4. Data Source
## Components
### ODBC application
The application executes SQL statements by means of ODBC calls and receives the results of the query.
### ODBC Driver Manager
The Driver Manager uses the .odbc.ini file (odbc.ini under MS-Windows) to activate the necessary driver for the application (multiple drivers may be active at the same time - simultaneous access to different Data Base Management Systems \[DBMS])
* processes ODBC calls
* provides parameter checking for ODBC calls
### ODBC Driver
* provides the connection to the data source
* sends queries to the data source
* converts data into different formats
* delivers the results of the query to the application
* formats native error messages of the DBMS into ODBC standard error messages
* initializes and manages the cursor actions
### Data Source
The Data Source is a special representative of a DBMS in combination with any operating system and/or network necessary to access the DBMS.
## API
### ODBC API
The ODBC API comprises three function groups (ODBC Conformance Levels):
* Core API
* Level 1 API
* Level 2 API
### API conformance core
Core functions are functions which every ODBC driver should support. It can be assumed that these functions can be used without restriction. Core functions have the following functionality:
* Set up the connection to the database
* Execute SQL statements
* Poll information via the result sets after a database query
* Standard error messages
### API conformance level 1
Level 1 functions are functions which provide more detailed information on the addressed tables and on result sets, as well as information on the connected data sources. These functions permit the existing database connection to be configured and database queries to be variably structured. They have the following functionality:
* Execute driver queries (query supported scope of functionality)
* Set query parameters
* Poll table descriptions (data types, accuracy)
#### API conformance level 2
Level 2 functions are functions to enhance performance and optimize the database statements. They are only supported to a limited extent by the drivers, but can largely be simulated by multiple level 1 functions.
## Preparing data sources
The first step in making a connection to an ODBC data source is to prepare the data source. This ensures that a connection is possible and the data can be used in the database application.
A data source is prepared outside of a database application, before you call the program, define the data source and then connect to the data source. The precise requirements vary from one data source to the next, but in general terms preparation of a data source ensures that:
* the necessary files and directories are properly installed on your computer;
* the configuration files (such as AUTOEXEC.BAT) are properly set up;
* the tables to be accessed have the correct names;
* the tables to be accessed are linked to unique indexes as appropriate.
## Defining data sources
To access an ODBC data source an appropriate ODBC driver is required. When configuring an ODBC data source, you need to prove information about the data source that the driver requires in order to make the connection.
After taking the necessary steps to use the data source, start the database application and define the data source. To do this, you make the necessary entries in the ODBC setup dialog box displayed for the driver in question.
The fields in the ODBC setup dialog box depend on the driver. In most setup dialog boxes you can enter the following information when defining an ODBC data source:
* the name of the data source
* (optionally) a description
* other required information about the DBMS
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.videc.de/acron-9.3/en/acron_der_anlagenchronist/acron_odbc-treiber.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.