odbc driver version 4 user guide - delta controls inc

Delta ODBC Driver Version 4 User Guide Edition 1.20

Contents Overview .....................................................................................................................................5

What is ODBC .............................................................................................................................. 5

Why Use Delta Controls ODBC Driver ..................................................................................... 6

Who Uses the Delta Controls ODBC Driver ............................................................................. 6

Typical Applications ..................................................................................................................... 6

How Does Delta Controls ODBC Driver Function .................................................................... 6

Main Differences between ODBC 3 and ODBC 4 Drivers .............................................................8

What is New in ODBC Driver V4 .................................................................................................. 8

Moving from ODBC 3 to ODBC 4 .................................................................................................. 9

Resources and Documents ...................................................................................................... 9

Tables .....................................................................................................................................10

Multiple Sites Support ............................................................................................................11

Property Compatibility ............................................................................................................15

V4 Objects ...............................................................................................................................17

Setting Up the ODBC Driver ...................................................................................................... 18

System Requirements ............................................................................................................18

Installing the ODBC Driver .....................................................................................................18

Verifying Installation ..................................................................................................................21

Troubleshooting Installation ..................................................................................................22

A: Unlicensed Driver ...............................................................................................................22

B: Wrong Login Username/Password ....................................................................................23

C: Wrong Site Name was entered during ODBC Driver Install ..............................................23

D: Incorrect ODBC Data Source Administrator Dialog Was Configured ................................24

E: Licensing Problem with Click-To-Run Version of Microsoft Office ...................................24

F: Use a 32 bit (NOT 64 bit) Microsoft Excel installation with the ODBC driver .....................25

Date/Time Format ..................................................................................................................25

Convert Driver V3 Applications to V4 ..........................................................................................27

ODBC Driver Version 4 User Guide Page 2 of 88 Document Edition 1.20

Example: Update Application ODBC Driver V3 to V4 ..............................................................28

Connecting to a Site with ODBC Driver V4: ................................................................................29

Getting Started with the ODBC Driver ...................................................................................... 32

V4 Table Naming Conventions ...................................................................................................32

1: V4 Object Tables .................................................................................................................32

2: V4 ARRAY Tables .................................................................................................................33

3: V4 ENUM Tables .................................................................................................................33

4: V4 BIT Tables ......................................................................................................................33

5: SYSTEM Tables (Common to both V4 and V3) .....................................................................34

V4 OBJECT TABLE_V4 Query Example ......................................................................................35

V3 ENUM TABLE_V4 Query Example .........................................................................................36

Understanding Delta Controls ODBC Driver 4 Information .......................................................36

OBJECT Tables .......................................................................................................................36

ARRAY Tables .........................................................................................................................37

ENUM Tables ..........................................................................................................................38

BIT Tables ...............................................................................................................................38

SYSTEM Tables (Common to V4 and V3) .................................................................................38

Configuring Delta ODBC Driver V4 ............................................................................................ 43

Creating a New Data Source ..................................................................................................44

Data Source Naming ...............................................................................................................46

Standard Use ............................................................................................................................ 47

Introduction ................................................................................................................................47

Respond to "ODBC--Connection failed" Error Message ............................................................47

Query a Database with Microsoft Excel ......................................................................................48

Advanced Use ........................................................................................................................... 53

ODBC Driver V4 Structured Query Language.............................................................................53

SQL Reserved Words ..............................................................................................................53

Avoid Unintended Writes ............................................................................................................54

SELECT Statement .....................................................................................................................54

Overview

Page 3 of 88 ODBC Driver Version 4 User Guide Document Edition 1.20

JOIN Syntax .............................................................................................................................56

INSERT Statement......................................................................................................................57

Syntax .....................................................................................................................................57

UPDATE Statement ....................................................................................................................58

Syntax .....................................................................................................................................58

DELETE Statement .....................................................................................................................59

Syntax .....................................................................................................................................60

Operators....................................................................................................................................60

Scalar Functions ........................................................................................................................61

String Functions .....................................................................................................................62

Numeric Functions .................................................................................................................64

Time, Date, and Interval Functions.........................................................................................65

System Functions ...................................................................................................................67

Explicit Data Type Conversion Function .................................................................................67

Aggregate (GROUP BY) Function Descriptions .......................................................................68

Using SQL with Custom Programs ............................................................................................ 69

Visual Basic (VB) With ADO ......................................................................................................69

Visual C++ ...................................................................................................................................70

Java with JDBC-ODBC Bridge ....................................................................................................72

Troubleshooting ....................................................................................................................... 73

Section Contents ........................................................................................................................73

Typical Error Messages ..............................................................................................................74

Expected lexical element not found: <identifier> ...................................................................74

Column ‘<identifier>’ not found ..............................................................................................74

Interpreting Common Error Messages ......................................................................................75

Table 1: Invalid Query Error Messages ......................................................................................75

A: Spelling Mistake in SQL Keyword .......................................................................................75

B: Statement Containing Incorrect Extra Character(s) at the End.........................................75

C: Attempt to Update a Read-Only Table Column ..................................................................76

D: Referenced Object that Does Not Exist on Device .............................................................76


E: Insert statement with Unequal Number of Columns .........................................................77

Table 2: Not Found / Unknown Error Messages ........................................................................77

C: Unknown Property (controller not understand the property) ............................................78

Table 3: Unsupported Error Messages ......................................................................................79

A: Multi row insert is not supported: ......................................................................................79

B: Table Deletion Not Supported by Driver ............................................................................79

Table 4: ISAM Error Examples for INSERT ................................................................................80

A: An ISAM error shows when try to insert a new array entry into ARRAY_V4_EVC_Destination_List. .........................................................................................80

B: An ISAM error shows when try to insert a new entry into ARRAY_V4_SCH_Weekly_Schedule .......................................................................................81

Query Performance Considerations ...........................................................................................82

Add Restriction to a Query .........................................................................................................83

A: Query Takes Much Longer than Expected ..........................................................................84

B: Query is Complex (OR used) Needs Further Restriction as Still Takes Longer than Expected .................................................................................................................................85

C: Trend Log Query Takes Longer than Expected ..................................................................85

Why is the Object Value Unchanged after an UPDATE Query? ..................................................86

Copyright .................................................................................................................................. 87

Document Revision History ...................................................................................................... 88

Overview


Overview The Delta Controls ODBC Driver is designed to make it easy for users to access real-time data, in an industry standard fashion, using third-party software. Open Database Connectivity (ODBC) is a multi-platform, standard database access method developed by Microsoft to assist users in managing multiple databases.

With Delta Controls’ ODBC Driver, customers can create reports quickly and easily by utilizing off-the-shelf applications such as Microsoft Access® and Microsoft Excel®. Advanced users can interface with this product when developing their own applications, or integrate it with enterprise software.

The objectives of this document are as follows:

• Identify what information the Driver provides. • List the main differences between versions 3 and 4 of the ODBC driver. • Explain how to find data. • Explain how to set up and configure the Driver. • Describe the common table naming conventions, and how the tables are categorized. • Provide examples, in the form of sample reports, for both standard and advanced use of

the Driver.

The structure of this document is as follows:

1. This first section provides basic information on using the ODBC Driver. It contains an overview of ODBC, a list of the differences between ODBC Driver V3 and V4, information on installing, configuring and troubleshooting the ODBC Driver installation. It also contains a description of the table naming conventions.

2. The Standard Use section contains information on creating queries and reports with real-time data, using third-party software.

3. The Advanced Use section contains information for programmers and database specialists. If you are already familiar with ODBC, and want information on SQL and custom applications, go to the Advanced Use section.

What is ODBC ODBC is a widely accepted Application Programming Interface (API) for database access. ODBC is an industry standard for communication that allows user programs to access data from a variety of Database Management Systems (DBMS) through a single interface.


Why Use Delta Controls ODBC Driver By using the ODBC Driver, a wide range of real-time data and database information from the Building Automation System (BAS) network and controllers can be accessed. This information can then be used to create unlimited types of reports using third-party software such as Microsoft Access, and Microsoft Excel.

Who Uses the Delta Controls ODBC Driver? Partner technicians use the driver to improve the speed of the workflow while building owners use the driver for integration purposes. Partner technicians create tools to create and edit controller databases, generate reports and create applications that improve productivity. Building owners focus on the integration of the BACnet information with business systems. The web services API in enteliWEB® BACnet® frontend provides another approach to system integration but require a higher degree of developer expertise than the ODBC driver (SQL, MS Access).

Typical Applications The Delta Controls ODBC Driver V4 works with many third-party programs including the following:

• Many good commercial and open source ODBC tools are available. The QT ODBC Query Tool (32 bit QTODBC 6.1) is the recommended tool and provides excellent function at a reasonable cost.

• MS Office 2010 and 2013 (For Excel only, the Visual Basic ? parameter is not supported) • Microsoft Visual Studio • Python • Java (Java 7+ requires separate JDBC-ODBC bridge, older versions include bridge) • Other commercial or custom applications

All the applications listed in the Typical Applications topic needs to be 32 bit version. Delta Controls ODBC Driver V3 or V4 does not work with a 64 bit version.

How Does Delta Controls ODBC Driver Function? The Delta Controls ODBC Driver resides between third-party ODBC-compliant applications and the Building Automation System controller network. SQL is an industry standard language that allows users to select, insert, update and delete data from a DBMS. When the ODBC Driver

Overview


receives an SQL request from a program, it issues network requests to perform specific operations on the controller objects.

The current ODBC Driver V4 uses the BACnet Server included with the enteliWEB BACnet frontend. The ODBC Driver V4 driver sees a site as a database which supports Structured Query Language (SQL).


Main Differences between ODBC 3 and ODBC 4 Drivers The intention is to provide an ODBC V4 driver that handles ODBC V3 applications directly without any changes. In the ODBC Driver, older ODBC 3 queries continue to work in the vast majority of cases. Some older queries may need simple modifications to incorporate multi-site access. For more information, see the Convert Driver V3 Applications to V4 topic.

The ODBC Driver V4 incorporates all the features of the previous driver, plus new capabilities provided by the Delta BACnet server, such as support for multiple sites, version independence, and table changes. The ODBC Driver V4 works with enteliWEB and the driver must be installed on the same server as enteliWEB. The driver relies on enteliWEB to create and manage sites. The driver can talk to the Delta Version 3.33, 3.40 and enteliSYSTEM™ (enteliWEB, V 4 firmware) products that enteliWEB supports. The driver does not support Delta 3.30 or Version 2 products.

The ODBC Driver V4 table structure has several differences from the ODBC Driver V3 ORCA table structure. While BACnet Standard Revision 6 (ORCA) and BACnet Standard Revision 14 (enteliWEB) have significant differences in property naming conventions and sometimes associated data type, the tool aims to handle these cases in a seamless manner. The enteliWEB 4.0+ / Delta BACnet Server combination uses the new BACnet property names and sometimes the tool has to make some reasonable assumptions when dealing with previous ODBC 3 applications.

What is New in ODBC Driver V4 New Features supported in ODBC 4:

• Parallel v3 and v4 driver table structure that assists the migration of queries from ODBC 3 to ODBC 4.

• SYSTEM tables now include SYSTEM_ALARMLIST, SYSTEM_DEVICELIST, SYSTEM_OBJECTLIST, SYSTEM_OBJECTSYSTEMS, SYSTEM_OBJECTTYPES and SYSTEM_SITES system table. The SYSTEM table uses information that enteliWEB caches locally and does not require network traffic to generate a response to a query. See the SYSTEM Tables (Common to V4 and V3) section.

• Version independence provides support for multiple firmware versions, including V3.33, V3.40 and upcoming V4.x

• Faster driver connection time using a cache file (19 sec for initial connection, 4 sec for subsequent connection)

• LIMIT, BETWEEN SQL keywords (Retrieve a subset of the result from a query.)

Main Differences between ODBC 3 and ODBC 4 Drivers


• 20 kB GCL programs, size is device dependent (Read, insert and edit). • GWT Object (Add, edit or remove gateway data transfer entries). See KBA-2286 (V4 ODBC

GWT Object Support Example) for more information. • Read/Write support for multiple sites (Use UPDATE and especially DELETE with care!)

Moving from ODBC 3 to ODBC 4 Partners can:

• Transition customers who use the ODBC Driver V3, to the ODBC Driver V4, without losing any capabilities or integrations.

• Port their existing ODBC tools, to the V4 enteliSYSTEM™ (enteliWEB, V4 firmware), and gain new capabilities such as multi-site and version independence.

The ODBC Driver V3 works with V3 ORCA Tables and these tables are included in the Driver V4 for compatibility. The V3 ORCA Tables are deprecated and mainly provide a means to migrate an application over to ODBC 4. It is recommended to use the ODBC Driver V4 for new applications and also to upgrade existing applications.

For more information, see the Convert Driver V3 Applications to V4 topic.

Resources and Documents These links require a Delta Partner Passport login:

• The Delta George Support site has a useful ODBC V4 webinar that is about 1.5 hours long: https://support.deltacontrols.com/Support/Webinar/OdbcVersion4Webinar

• KbA1711_Using the ODBC Driver to speed up repetitive tasks https://support.deltacontrols.com/Support/Kbase/KbA1711

• Delta Tech Forums allows our partner to post questions or to report problems: https://forums.deltacontrols.com/viewforum.php?f=11

https://support.deltacontrols.com/Support/Webinar/OdbcVersion4Webinar

https://support.deltacontrols.com/Support/Kbase/KbA1711

https://forums.deltacontrols.com/viewforum.php?f=11


Tables In the ODBC 4 Driver, there are two sets of tables for: OBJECT, ARRAY, BIT and ENUM, one for Delta V3 ORCA sites and the other for V4 enteliSYSTEM sites. Only one set of SYSTEM tables exists.

Each table for V3 systems has a name that starts with: <OBJECT or ARRAY or BIT or ENUM>_BAC_<Object Types>. e.g. OBJECT_BAC_AI

Each table for V4 systems has a name that starts with: <OBJECT or ARRAY or BIT or ENUM>_V4_<Object Types>. E.g. OBJECT_V4_AV

V3 and V4 Table Differences:

The V3 ORCA and V4 tables have differences. So, to read/write properties that only exist in certain version of a device, the appropriate table must be used. However, if a user just wants to SELECT basic things such as object ID and name, you can use either the V3 ORCA or V4 set of tables.

For example, consider using OBJECT_V4_DER for all the data exchange for both V3 ORCA and V4 enteliSYSTEM devices. Some table columns such as "local_reference" have no values for ORCA V3 devices because ORCA V3 does not have this object property defined in DER.

V3 ORCA Tables V4 enteliSYSTEM Tables

All tables for V3 ORCA systems have a name: OBJECT_BAC_<Object Types>

All tables for V4 systems have a name: OBJECT_V4_<Object Types>

V3 tables are located in a table folder called Aliases/Synonyms or a similar name but this depends on the query tool used.

Tables: enum V4 names changed according to the BACnet Standard Revision 14 (all lower case and no spaces).

In ORCA ODBC 3 Driver, ARRAY, BIT and ENUM tables are hidden by default.

In ODBC 4 Driver, ARRAY, BIT and ENUM tables are enabled by default and cannot be hidden. Upon startup, several thousand tables are shown.



Example: Different Result for Same Query on V3 and V4 ENUM Tables The following graphic shows the same query done on both a Version 3 ENUM table and then on a Version 4 ENUM table with the latest ODBC Driver V4.

ENUM_BAC ENUM_V4

In the previous figure, notice that the BAC_AI_EventState and V4_AI_EventState value lists differ.

Multiple Sites Support The following table deals with multiple site support.

The V4 driver supports multiple site access. This powerful feature allows the ODBC 4 driver to read or write to devices / objects on different sites.

With multiple site support, be aware that you can do operations such as write or delete involving multiple sites. If you are connected to multiple sites, a query can do read/ write/insert/delete actions on all the instances of an object.

Enter an enteliWEB site name in the Site field. Use caution with multi-site UPDATE and especially DELETE operations!

If the wildcard character * was entered in the site name field during the ODBC driver installation, you can change it to a specific site name. The site name can be changed from the Windows ODBC Data


Source Administrator prior to starting an ODBC query program. Replace the wildcard * in the Site field with the name of the site defined in enteliWEB and then click OK.

The Site Name wildcard * for multiple sites is a powerful feature but you must use it with care! Generally, avoid using the * wildcard unless you have a specific need.

Before any Update or especially DELETE operation, use a SELECT statement to assess the data demands and then restrict and limit the query as much as possible. Try the statement on a local test network that does not control a facility. There is NO UNDO and you cannot reverse the effects using the driver.

Do a database backup of the target devices on the network immediately before you run any DELETE or UPDATE statements. You can also divide the intended changes into a series of statements and verify the changes as you run the individual scripts.

Another technique to help avoid accidents is to have multiple enteliWEB logins with a range of permissions. In many cases, Read-Only permissions are sufficient. See the Avoid Unintended Writes content in the Connecting to a Site with ODBC Driver 4 topic.

ObjRef is in ORCA short V3 format and the new V4 field FullRef contains the fullref including the site. SITE_ID was number 1 or 0 (always) in ORCA Version 3. It becomes "MainSite" string or whatever Site name that is used in in V4. For backward compatibility ObjRef can remain in ORCA short V3 format and the new field FullRef contains the fullref.



Consider an ORCA ODBC site example. (SITE_ID, ObjRef)

With an ORCA ODBC example, the probability is very high that it only has one site. The SITE_ID is either 1 or 0 in V3. It becomes whatever the Site name is called in V4 (e.g. MainSite).

V3 ORCA Reference V4 enteliSYSTEM Reference

<devID>.<ObjectType><ObjectInstance> 1011.AV1

//<SiteID><devID>.<ObjectType><ObjectInstance> //MainSite/1011.AV1 //Site2/1011.AV1

V4 can have the same object instance (e.g. 1011.AV1) on multiple sites. If you are connected to multiple sites, then a query can do read/write actions on all the 1011.AV1 instances on the multiple sites.

Multiple Sites Example The following enteliWEB example shows how to get certain data from multiple devices communicating in different sites. The enteliWEB frontend is logged into 3 sites: Main Building, North Building and South Building.

The following ODBC Driver V4 figure shows a SELECT statement for all the AV object values used for an OAT setpoint on the three sites that are logged in. The following figure is from the open source ODBC query tool v1.38.


We can compare what the response of the ODBC 3 driver is to the same SQL query. The following ORCAview graphic shows the OAT Setpoint 700.AV1 object.



The following ODBC 3 Driver graphic shows that ORCAview is only logged into a single site (Main Building) and the query result is then only from the devices on that site.

Property Compatibility With the Delta Controls enteliSYSTEM 4 product line, many property names are changed from ORCA V3.

Check the table to see the correct name or the value type.

For example, the commissioned flag in ORCA V3 systems was either 1 or 0 but in V4 enteliSYSTEM the commissioned property is either ‘True’ or ‘False’. An ORCA V3 Select statement would need modification to work in V4.

Some common units such as seconds differ based on the BACnet Standard followed. See the product Release Notes.

Old / new property names:

The driver handles new V4 BACnet property names and also still handles the older ORCA V3 property names.

Properties:

1. Some object property types have changed in V4. For instance, the value type of an AV object used to be real in V3, but now it is double in V4.

2. All V4 enum properties are now lower case and separated by a dash – without any space unlike ORCA V3. For example, ORCA V3 ‘Alarm’ with an initial capital letter prefixed by a space is now ‘alarm’ in V4


3. Expect some Delta Proprietary such as Manual Override or 3rd party inconsistencies.

V3 Objects (ORCA) V4 Objects (enteliSYSTEM)

ORCA ObjectType was:

"Analog Value" with a space

V4 ObjectType is:

"AnalogValue" without a space

Properties Names:

spaces in properties; sub-props

Properties Names:

underscores in properties; sub-props

For BACnet objects, the BACnet standard naming conventions are followed.

For example, InputRef from OBJECT_BAC_TL in ODBC Driver V3 becomes Input_Ref in ODBC Driver V4. The property InputRef for version 3 (ORCAview) devices also become Input_Ref in version 4 (enteliWEB).

For some examples on how to set alarming related sub properties, see KBA1711 Using the ODBC Driver to speed up repetitive tasks on the George support site.

Property Behaviors:

Boolean states are: "0" or "1" in V3

Property Behaviors:

States are: "false" or "true" in V4

See KbA1711_Using the ODBC Driver to speed up repetitive tasks https://support.deltacontrols.com/Support/Kbase/KbA171

https://support.deltacontrols.com/Support/Kbase/KbA1711



V4 Objects The conventions for property naming are changed and you need to be aware of some changes in table structure.

The ODBC 4 driver supports V4 Delta enteliSYSTEM products including BACnet objects and Delta proprietary objects. Version 4 objects follow the BACnet Standard Protocol Revision 14 that defines new objects and adds extended function to existing objects. The ORCA products supported the BACnet Standard Protocol Revision 6.

Objects: Specific

1. Objects such as AIC or MIC are more difficult to work with as they contain arrays. Some objects such as SCH contain an array within an array.

2. DER has many exceptions in the dictionary and the driver handles them.

3. CAL has unique proprietary properties.

For example, the BACnet property Date_List is in both ORCA V3 and V4 Calendar objects and contains only a list of dates marked in the Calendar. A datelist entry does not have any description so it is not direct to find out why each date is marked.

In the V4 Calendar Object, Delta added a proprietary property called Date_List_Ext that contains both the datelist and associated description information for each date.

Objects: General

1. Objects: Watch for queries involving multiple Trend Log objects as many operations may be required for a query.


Setting Up the ODBC Driver This section covers basic information on how to install, verify, and configure the Delta Controls ODBC Driver.

The ODBC Driver V4 version number must match the enteliWEB version number.

System Requirements enteliWEB must be installed on the PC before setting up the ODBC Driver V4. If the recommended system requirements are met for enteliWEB, then the Delta Controls ODBC Driver will run fine. The ODBC Driver V4 is available with the –API option on all enteliWEB licenses. The –API is included by default with the enteliWEB Tech license.

Refer to the Release Notes of the ODBC Driver V4. The ODBC Driver requires that enteliWEB is installed. See also the enteliWEB Release Notes.

Installing the ODBC Driver You need enteliWEB 4.1+ with the -API option to run the ODBC Driver. You can install the driver but it will not run without the –API option. During the initial installation, the system is automatically configured. Once the ODBC Driver is installed, the settings can be changed if needed. enteliWEB must be installed prior to configuring the ODBC Driver.

For security purposes, it is recommended to not use the same login information as the enteliWEB default Admin account for the ODBC Driver V4 login. This approach also prevents lockout of the Admin account.

To install the ODBC Driver on your computer:

1) Exit other programs. 2) On the same server that enteliWEB is installed on, run odbc_setup.msi. The user does

not have to be logged on as an Administrator but the system asks for an Administrator password.

Setting Up the ODBC Driver


3) Confirm that the license is correct. Accept the license terms.

4) Enter the username and password of a valid enteliWEB user for the enteliWEB account that ODBC uses. It is recommended to use a separate user and unique password for ODBC for improved security. If you do three consecutive incorrect login attempts, the system prevents further logins for one hour.


5) Enter the enteliWEB site name for the site you want the ODBC driver to use. Click Next 6) On the Delta BACnet ODBC Setup dialog, Click Install. 7) Installation is finished.



8) Finish the installation process and reboot the PC.

9) If needed, see the Configuring Delta ODBC Driver V4 section. See KBA-1834 for more information.

The ODBC Driver V4 is a 32 bit driver application just like Version 3.40. If a 64 bit windows machine is used, then the correct ODBC Data Source Administrator is found in C:\Windows\SysWOW64\odbcad32.exe

Another ODBC Data Source Administrator is used to create and maintain data source names for 64-bit applications but remember that the Delta ODBC Driver V4 is a 32 bit application. Do NOT use: windows\system32\odbcad32.exe.

Verifying Installation After installing the Delta Controls ODBC Driver, you can verify that it has been correctly installed by following this procedure. The Delta ODBC v4 driver uses a 32-bit Windows driver.

A 64-bit Windows operating system has two odbcad32.exe files:

• %SystemRoot%\SysWOW64\odbcad32.exe is used to create and maintain data source names for 32-bit applications, including 32-bit applications that run on 64-bit operating systems. [Use this one]

• %SystemRoot%\system32\odbcad32.exe is used to create and maintain data source names for 64-bit applications. [Do NOT Use!]

To verify correct installation of the driver: 1. Open the ODBC Data Source Administrator that is found in:

C:\Windows\SysWOW64\odbcad32.exe 2. Click Administrative Tools. 3. In the ODBC Data Source Administrator Dialog, verify that ‘Delta ODBC 4’ appears in the

System DSN tab, and then click OK.


4. On the Drivers tab, confirm the version and build of the driver.

Troubleshooting Installation The following table covers basic license and login issues.

Table: License and Login Issues

A: Unlicensed Driver

Invalid License

ODBC connection failed: Function DBconnect - SQLDriverConnect() failed: Error 30 S0000[Delta Controls][BACnet ODBC Driver][ISAM]Invalid License

The ODBC 4 Driver is available with the –API option on all enteliWEB licenses. The –API is included by default with the enteliWEB Tech license.

Situation Actions Result

Open the License Manager.

a) Check the status of the license in the Delta License Manager.

With the license active, queries no longer return an "Invalid License" message.



Table: License and Login Issues b) Confirm that the enteliWEB –API option is active. c) Renew your subscription. d) If the subscription has expired, then you cannot use a driver released after the subscription expiry date. You can install the latest driver but it will not work. An option is to uninstall the newer driver and then install the older one again.

See the Software License Manager User Guide PDF file for more information.

Do a Passport login to https://passport.deltacontrols.com

Then, click on the Licensing Portal option.

B: Wrong Login Username/Password Invalid Authorization Specification


Incorrect Username and/ or Password.

a) Ensure that the correct username and password are entered in the DSN or connection string.

The message shows a correct login and a "Database schema retrieved" message.

C: Wrong Site Name was entered during ODBC Driver Install Query failed: DBSQLSelect() SQLExecuteDirect - Error 24 S0000 [Delta Controls][BACnet ODBC Driver][ISAM]Site cannot be open For any query with a wrong site name in query: SELECT FullRef, IDX, DEV_ID, Object_List, objRef FROM "WRONG Site"."ARRAY_V4_DEV_Object_List"


A "Site cannot be open" message displays for any valid query.

a) Check the sitename in enteliWEB. b) Check the site name that was entered during the driver installation. The site name is not case sensitive. c) Check the Site Name in the DSN using the ODBC Data Source Administrator for 32 bit

Valid queries now function as expected. For example: SELECT FullRef, IDX, DEV_ID, Object_List, objRef FROM "MainSite"."ARRAY_V4_DEV_Object_List"


Table: License and Login Issues applications. d) For multiple sites, include the site names in the script. The * wildcard can be used for multi-site connections.

D: Incorrect ODBC Data Source Administrator Dialog Was Configured Driver or DSN not found message The Delta ODBC Driver Version 4 is a 32 bit application that typically runs on a 64 bit PC. Use: C:\Windows\SysWOW64\odbcad32.exe

The ODBC Driver V4 is a 32 bit driver application just like Version 3.40. If a 64 bit windows machine is used, the correct ODBC Data Source Administrator is found in C:\Windows\SysWOW64\odbcad32.exe Refer to KbA1834: Historian and ODBC Driver Setup on a 64 Bit Operating System.


The wrong ODBC Data Source Administrator Dialog was configured.

a) Check the correct ODBC Data Source Administrator was used. b) If necessary, use the ODBC Data Source Administrator for 32 bit applications found in C:\Windows\SysWOW64\odbcad32.exe

For a 64 bit machine, Do NOT use: windows\system32\odbcad32.exe.

E: Licensing Problem with Click-To-Run Version of Microsoft Office

Microsoft Office has a streamed deployment mechanism called "Click To Run" which allows Office to launch before all files have been downloaded and installed. When Office is running as a Click-To-Run application, the executable is running within Microsoft's Application Virtualization (App-V) environment that has its own segregated registry and file store locations. When licensing executes within Delta ODBC and it is within the virtual Excel environment, the registry writes are all redirected to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Delta Controls\Licenses



Table: License and Login Issues When a virtual ClickToRun registry key is created, the default system registry path at the corresponding location is hidden.


The ODBC driver indicates a problem with licensing.

Delete the ClickToRun registry entry. This is the current workaround.

Excel can now reference the correct System registry entry after the ClickToRun registry entry was deleted.

F: Use a 32 bit (NOT 64 bit) Microsoft Excel installation with the ODBC driver

The Delta ODBC Driver Version 4 is a 32 bit application that typically runs on a 64 bit PC. Use a 32 bit Excel application with the driver. Do NOT use a 64 bit Excel installation with the driver, as the ‘Delta ODBC 4’ does not display in the DNS list.


'Delta ODBC 4’ is not listed in the DNS list when trying to set up MS Excel to use Delta ODBC V4 driver.

Check the version of Excel. If it’s a 64 bit version, install 32 bit version of Excel.

’Delta ODBC 4’ is in the DNS list when trying to setup V4 excel to use Delta ODBC driver.

Date/Time Format The V4 Driver uses a different Date/Time format from the V3 Driver because this choice allows enhanced date/time queries.

Unlike the V3 driver, the V4 driver supports partial or complete wildcard date/time values like ‘every other week’, or ‘every Tuesday’. Because of this format change, Date/Time queries that worked in V3 do not work in V4. You need to edit them so that they work with the V4 Driver.

The_V4 properties have AsString columns defined for all columns where it makes sense. Only some of the _BAC V3 Properties have AsString columns defined. The date part of the format for the V4 Time/Date is changed.

V3 Example: V3 Driver Query with Date/Time Does Not Work in V4 Driver

The following query works in V3 but would need changes to work in V4.


UPDATE ARRAY_BAC_CAL_DateList set DateListDateAsString='16-*-2010' where DateListDateAsString='*-*-*'

The V4 Driver now has additional string type table columns for all Date/Time types (i.e. DayScheduleTimeAsString for DayScheduleTime table column) and these columns should be used in the query.

However, the Date/Time format depends on the device, and the driver cannot predict the format used by a particular device. Actually, the time format HH:MM:SS is unchanged from the V3 Driver but the date format varies for different devices. Due to this limitation, your query may result in an error if you assume an incorrect Date/Time format.

The suggested approach is to run a SELECT query for any date/time type table columns on a particular device to confirm the format that the device actually uses.

V4 Example: Change Calendar Date List

The following query changes the date list of CAL objects that match the selection criteria using the new DateListDateAsString column.

The query targets each entry that is ‘4th of July in 2016’ and updates each entry to ‘4th of July every year’.

For a V3 table, the following query works: UPDATE ARRAY_BAC_CAL_DateList set DateListDateAsString='04-Jul-*' where DateListDateAsString='04-Jul-2016'

For a V4 table, the following query works: (Note the use of underscores as separators and also _V4 in the names and properties) UPDATE ARRAY_V4_CAL_Date_List set Date_List_DateAsString='*/07/04/*' where Date_List_DateAsString='2016/07/04/1'

V4 Example: Find Reset Devices Based on Timestamp

The following query finds devices that were reset within the specified range of date time: SELECT objref, LastResetTime FROM OBJECT_BAC_DEV where LastResetTimeAsString BETWEEN '2016/06/02/4 06:00:00.0000' AND '2016/06/16/4 09:14:59.9999'

The number /4 is for Thursday in the BETWEEN '2016/06/02/4 06:00:00.0000' AND '2016/06/16/4 09:14:59.9999' part of the query. The format in the example contains the day of the week.

Example: Results for Device Reset on Thursday within the specified Date/Time



SELECT objref, LastResetTime FROM OBJECT_BAC_DEV where LastResetTimeAsString BETWEEN '2016/06/02/4 06:00:00.0000' AND '2016/06/16/4 09:14:59.9999'

The result shows one Device reset on Thursday within the specified Date/Time:

Convert Driver V3 Applications to V4

The main cases for conversion are:

1. Runs with no modifications (expect 80%+ of cases) 2. Modify to get to work. Check the table column names. 3. Extend an application by using SITE_ID as a site name allowing multiple site support. 4. Switch to V4 tables for better compatibility with BACnet and new Delta devices. Delta has

added useful proprietary fields.

If a V3 Application does not function with the ODBC Driver V4:

1. Check the DSN Name used in the application. In ODBC V3, the name was ‘Delta Network’ but the name is ‘Delta ODBC 4’ in the current driver.

2. Confirm that the application uses the SITE_ID if it needs the multiple site function. You need to create any sites in enteliWEB.

3. Confirm that the syntax and column / property names are valid for the ODBC Driver V4.

For more information, see the Main Differences between ODBC 3 and ODBC 4 Drivers section.


Example: Update Application ODBC Driver V3 to V4 The following code worked for a partner using the ODBC Driver Version 3 but does not work in V4.

//Works with ODBC Driver Version 3 but NOT with V4 Code: Select all INSERT INTO OBJECT_BAC_TL (DEV_ID,INSTANCE,NAME,InputRefEx,LogInterval) VALUES (100,1,'TL Room_Temp','BAC.100.AI1.Value',90000) UPDATE OBJECT_BAC_TL SET LogEnable = 0 WHERE DEV_ID=100 AND Instance=1 UPDATE OBJECT_BAC_TL SET BufferSize = 500 , StartTime = {ts '2000-01-01 00:00:00.000'} WHERE DEV_ID=100 AND Instance=1 UPDATE OBJECT_BAC_TL SET LogEnable = 1 WHERE DEV_ID=100 AND Instance=1

The solution was to make two changes in the code:

• In the third statement, change so that the query writes to the StartTimeAsString column instead of StartTime based on information in the Main Differences between ODBC 3 and ODBC 4 Drivers section of this document. See also the previous Date/Time Format topic.

After the changes, the following SQL statements work with the ODBC Driver Version 4:

//Revised code works with ODBC Driver Version 4 Code: Select all INSERT INTO OBJECT_BAC_TL (DEV_ID,INSTANCE,NAME,InputRefEx,LogInterval) VALUES (100,1,'TL Room_Temp','BAC.100.AI1.Value',90000) UPDATE OBJECT_BAC_TL SET LogEnable = 0 WHERE DEV_ID=100 AND Instance=1 UPDATE OBJECT_BAC_TL SET BufferSize = 500 , StartTimeAsString = '2000/01/01 00:00:00.000' WHERE DEV_ID=100 AND Instance=1 UPDATE OBJECT_BAC_TL SET LogEnable = 1 WHERE DEV_ID=100 AND Instance=1



Connecting to a Site with ODBC Driver V4: The following topic shows how to connect the ODBC Driver 4 to the enteliWEB site named International Airport Terminal 2 using the QT ODBC6.1 query tool.

1. Select File>New>Query with New Connection>Data Sources… in the upper right area of the dialog. Click. The Select Data Source dialog opens.

2. On the Machine Data Source tab, select Delta ODBC 4.


3. Click OK. The DSN was defined during the install and contains the username and password entered. If a sitename was entered, it displays in the tool.

4. Expand the Aliases/Synonyms folder. Select an entry and expand it to see the properties.

The ODBC Driver V4 has table aliases that implement the ODBC Driver V3 schema and provides backward compatibility with the V3 driver. Most existing applications can seamlessly migrate to the new driver without any query modifications. In some cases, minor modifications are needed. See the Convert Driver V3 Applications to V4 topic.



The tables that contain _V4 in their names conform to ODBC Driver V4 which works with the enteliWEB BACnet frontend.


Getting Started with the ODBC Driver This section provides a summary of the table structures and properties used in the Delta ODBC Driver Version 4.

This section includes:

1. Table naming conventions 2. List of available tables and their structures 3. Examples of simple V4 queries 4. Show and explain common table columns

For backward compatibility with the V3 driver, the ODBC Driver V4 provides table aliases that implement the ODBC Driver V3 schema. Most existing application can seamlessly migrate to the new driver without any query modifications. In some cases, minor modifications are needed.

Our convention is to use the terms table column and table property in an effort to provide clear and consistent terms usage.

V4 Table Naming Conventions There are five types of tables: OBJECT, ARRAY, ENUM, BIT and SYSTEM tables.

1: V4 Object Tables OBJECT tables show information from object non-array properties. Each table represents a single object type.

OBJECT Table Description OBJECT_V4_AI Where: table type = OBJECT, object system = V4, object abbreviation = AI

V4 Example SELECT * FROM "South Building".OBJECT_V4_AI

The previous query includes the specific site name "South Building" which can be omitted. This approach is useful when you are logged into multiple sites and are only interested in data from a specific site.

Getting Started with the ODBC Driver


V3 Example SELECT * FROM OBJECT_BAC_AI

2: V4 ARRAY Tables ARRAY tables show information from object array properties. Each table represents a single array property of a single object type.

ARRAY Table Description ARRAY_V4_AI_Last_Write_Time Where: Table Type = ARRAY, Object System = V4, Object Type = MT, Column Name = Trend_Log_List

V4 Example SELECT * FROM ARRAY_V4_MT_Trend_Log_List

V3 Example SELECT * FROM ARRAY_BAC_MT_TrendLogList

3: V4 ENUM Tables ENUM tables show information from object properties’ enumerated values and its actual meaning. Each table represents a single enum property of a single object. The ENUM table contains the enumerated states only and is not specific to a particular object. For example, 100.MI1 and 200.MI2 are identical.

Table Description ENUM_V4_AI_Commission_Flag Where: Table Type = ENUM, Object System = V4, Object Type =AI, Property Name = Commission_Flag

V4 Example SELECT ID, Value FROM ENUM_V4_AI_Commission_Flag

V3 Example SELECT ID, Value FROM ENUM_BAC_AI_CommissionFlag

4: V4 BIT Tables BIT tables show the OBJECT property’s Boolean state and its actual meaning. Each table represents a single bit property. The BIT table documents the bits only and is not specific to a particular object. For example, 100.EVC1 and 200.EVC2 are identical. The bit names in V3 and V4 have changed.


Table Description BIT_V4_EVC_FLAGS Where: Table Type = BIT, Object System = V4, Object Type = AI, Property Name = FLAGS

V4 Example SELECT ID, Value FROM BIT_V4_AI_Flags

V3 Example SELECT ID, Value FROM BIT_BAC_AI_Flags

5: SYSTEM Tables (Common to both V4 and V3) SYSTEM tables show information about objects, sites, object types, object systems, devices and alarms. The SYSTEM table uses information that enteliWEB caches locally and does not require network traffic to generate a response to a query.

Table Description SYSTEM_ALARMLIST Where: Table Type = SYSTEM, Information Type = ALARMLIST

V4 and V3 Example SELECT DEV_ID, INSTANCE, NAME, OBJECTSYSTEM, OBJECTTYPE, OBJREF, SITE_ID FROM SYSTEM_OBJECTLIST



V4 OBJECT TABLE_V4 Query Example The general format of an SQL SELECT statement is as follows:

SELECT columns from table, separated by commas FROM table name WHERE some condition

The fields listed right after the word SELECT are shown in the data displayed. The * can be used to indicate that all fields in the table should be shown in the output, but for many objects this is overkill.

Note that in SQL, a wildcard is done with % not *, and that text string values are enclosed in 'single quotes' not "double quotes"

The following figure shows the output for a V4 query on the V4 ARRAY table:

SELECT SITE_ID, INSTANCE, FullRef FROM OBJECT_V4_DEV


V3 ENUM TABLE_V4 Query Example An enumeration table provides a list of possible values and the meaning of these values.

The following graphic shows the output for a V4 query on the V3 ENUM table:

SELECT Value from ENUM_BAC_AI_Reliability

Understanding Delta Controls ODBC Driver 4 Information Every table contains basic default columns. Some of the common columns seen within the tables are shown and explained.

OBJECT Tables SELECT Site_ID, Instance, DEV_ID, Object_Name, Object_Identifier, Present_Value, FullRef FROM OBJECT_V4_AI

SITE_ID The site name assigned to the site that is connected to the ODBC Driver. The site name and the corresponding site number can be found in the SYSTEM_SITES table.



INSTANCE The object instance number.

DEV_ID The device address of the object.

Objectldentifier The object’s type and instance together in text format.

Present_Value The value of the object.

FullRef The object’s device site name, address, type and instance, together in text format. With support for multiple sites, often you use the FullRef instead of the ObjRef as this avoids making an unintended change throughout all sites.

ARRAY Tables SELECT Site_ID, DEV_ID, Instance, IDX, ObjRef, Event_Time_Stamps_dateTimeAsString, FullRef FROM ARRAY_V4_EV_Event_Time_Stamps


DEV_ID The device address of the object array property.

INSTANCE The instance number of the object with the array.

ObjRef The device number, object type, and the instance of the object that contains the array.

IDX The index identifying each row in the array.

FullRef The object’s device site name, address, type and instance, together in text format. With support for multiple sites, often you use the FullRef instead of the ObjRef as this avoids making an unintended change throughout all sites.

Event_Time_Stamps_dateTimeAsString

Provides full access to the timestamp information for events.


ENUM Tables SELECT ID, Value FROM ENUM_V4_BI_Event_State

ID The numeric value of the object property.

Value The text representation of the ID.

BIT Tables SELECT ID, Value FROM BIT_V4_SUA_HAL_Flags

ID The numeric value of the object property.

Value The text representation of the ID.

SYSTEM Tables (Common to V4 and V3) This section provides information on the five different system tables. The SYSTEM table uses information that enteliWEB caches locally and does not require network traffic to generate a response to a query.

SYSTEM_ALARMLIST The SYSTEM_ALARMLIST table provides a way to read the Active Alarm List.



SELECT EventObject, InputRef, ToState, TimeStamp, Priority, NotifyType FROM SYSTEM_ALARMLIST

EventObject The reference of the event object that produced an alarm.

InputRef The reference of the monitored value.

ToState The transition of the alarm from one state to this state.

TimeStamp The date/time stamp of this alarm transition.

Priority The priority value of the alarm.

NotifyType The type of notification that results when an alarm occurs.

SYSTEM_OBJECTLIST The SYSTEM_OBJECTLIST table lists all of the objects in the system, from all of the devices.

SELECT OBJECTSYSTEM, DEV_ID, OBJECTTYPE, Instance, OBJREF, NAME, SITE_ID FROM SYSTEM_OBJECTLIST

OBJECTSYSTEM The network type, either BAC or V4.

DEV_ID The device address of the object.

OBJECTTYPE The object type in full text (e.g. LINKnet, DEVICE, FILE).

INSTANCE The object instance number.

OBJREF The device number, object type (e.g. DEV, IPS, FIL), and the instance of the object.

NAME The name of the object.



SYSTEM_OBJECTSYSTEMS The SYSTEM_OBJECTSYSTEMS table shows the numeric to text mapping of object systems.

SELECT OBJECTSYSTEM, OBJECTSYSTEM_ID FROM SYSTEM_OBJECTSYSTEMS

OBJECTSYSTEM The name of the object system.

OBJECTSYSTEM_ID The numeric value of the object system.

SYSTEM_OBJECTTYPES The SYSTEM_OBJECTTYPES table shows the numeric to text mapping of all object types in the system. The graphic shows a small segment of the total output.

SELECT NAME, OBJECTSYSTEM, OBJECTTYPE, OBJECTTYPE_ID FROM SYSTEM_OBJECTTYPES

NAME The name of the object type in full text.

OBJECTSYSTEM The object system that the object type belongs to.

OBJECTTYPE The text value of the object type.

OBJECTTYPE_ID The numeric value of the object type.



SYSTEM_SITES The SYSTEM_SITES table provides a list of all the sites created on the system.

NAME The name of the site.


SYSTEM_DEVICELIST The SYSTEM_DEVICELIST table provides a list of information about known controllers. System tables collect up information from different tables and provide rapid response to SELECT * types queries. The SYSTEM_DEVICELIST provides a way to quickly read controller information that enteliWEB already knows and does not have to query the network to get. This table is new in Driver V4.

SELECT * FROM SYSTEM_DEVICELIST LIMIT 10;

Descriptor Shows the Site_ID, Device Number and object reference.

DeviceNumber The number of each device that is operational.

DeviceStatus The current status of the device. Typical values are operational, non-operational or offline. Non-operational does not mean that the controller is not functioning, or that it is offline. Non-operational indicates that there is a network configuration problem. With enteliWEB 4.14, the driver returns operational and non-operational values as before except when the device is offline. For each device, the driver checks the deviceFlags column in the Device_info table. If the deviceFlags is offline, then the device status column value is replaced with a value of offline.

DeviceType The type of device. Each type includes a family of device models.


FirmwareVersion Displays the version identifier assigned by the vendor to the firmware present in the BACnet device.

ModelName The name of a particular device,

Name A descriptive name of the device such as AHU3.

ProtocolRevision The level of BACnet Standard Revision supported.

SiteName The site name assigned to the site that is connected to the ODBC Driver. The site name and the corresponding site number can be found in the SYSTEM_SITES table.

SoftwareVersion Indicates the specific version of the installed software.

VendorId The official BACnet ID of the device manufacturer. Delta Controls Inc. is 8.

The following query provides a list of operational devices:

SELECT Name FROM SYSTEM_DEVICELIST where DeviceStatus = 'operational'

Configuring Delta ODBC Driver V4


Configuring Delta ODBC Driver V4 The following section provides a description of the fields used to configure the Delta Controls ODBC Driver.

The ODBC Driver V4 is a 32 bit driver application just like Version 3.40. If a 64 bit windows machine is used, then the correct ODBC Data Source Administrator is found in C:\Windows\SysWOW64\odbcad32.exe

Another ODBC Data Source Administrator is used to create and maintain data source names for 64-bit applications but remember that the Delta ODBC Driver V4 is a 32 bit application. Do NOT use: windows\system32\odbcad32.exe.

To configure the 32 bit ODBC Driver: 1. On a 64 bit PC, make sure that you open the correct ODBC Data Source Administrator. Run

the 32 bit ODBC driver that is found at: C:\Windows\SysWOW64\odbcad32.exe

2. In the ODBC Data Source Administrator dialog, select the System DSN tab.


3. Select Delta ODBC 4 and then click the Configure button. A popup Setup menu appears.

4. Enter text for the Data Source, Site, User and Password fields. 5. Click Ok

Data Source Name: This field represents the unique identifier for each Delta Controls data source that is configured on the system.

Site: Displays the enteliWEB site name that the ODBC Driver logs onto.

User: Specifies the enteliWEB user. It is recommended to use a separate user with ODBC for improved security. Avoid using the same enteliWEB Admin user and password for the ODBC login.

Password: Specifies the password for logging onto the enteliWEB system. It is recommended to use a unique password for ODBC for improved security. A separate user and unique password for ODBC provides improved security.

Creating a New Data Source In addition to the default Data Source created by installing the Delta Controls ODBC Driver, users can also create new ODBC data sources. A new data source can be used to connect to other sites with a different user name and password.

To create a new data source: 1. On a 64 bit PC, make sure that you open the correct ODBC Data Source Administrator.

Run the 32 bit ODBC driver that is found at: C:\Windows\SysWOW64\odbcad32.exe

2. In the ODBC Data Source Administrator dialog, select the System DSN tab.

Configuring Delta ODBC Driver V4


3. Click Add. The Create New Data Source dialog opens.

4. Select Delta Network. 5. Click Finish. A popup Setup menu appears.

6. Enter text for the Data Source, Site, User and Password. 7. Click OK


Data Source Naming The DSN name for Driver 4 is "Delta ODBC 4" but the DSN name of Driver 3 was "Delta Network". In some cases, you may need to modify V3 ODBC applications for the ODBC Driver 4.

You can also create a parallel DSN called "DeltaNetwork" to preserve V3 application compatibility.

The recommended approach is to change v3 code to use the v4 DSN name and also the v4 username and password. It is not recommended to just rename the Driver 4 DSN source.

Standard Use


Standard Use Introduction The Delta Controls ODBC Driver can be used to create queries and reports with real-time data, using third-party software.

The basic function of a query is to list and sort data, and then link the data as defined by a user’s needs. Queries can also be used to view and retrieve data from one or more sources, analyze that data in a variety of ways, and then display it in the order needed for records and reports.

The following sections describe how to use the ODBC Driver with Microsoft Excel.

Respond to "ODBC--Connection failed" Error Message When you link tables to a Microsoft Access database, using an ODBC Data Source, information about that connection is stored in the Description property of the table. If the database is moved to another computer that does not contain the same Data Source Name (DSN) for the ODBC connection to the linked table, an ODBC – connection to ‘Delta Network’ failed error message may appear.

Some common reasons for this "connection to ‘Delta Network’ failed" message might be:

1. The database file was moved to another computer, 2. The Delta Controls ODBC Driver is not installed or was installed incorrectly. 3. The DSN has been removed or its name has been changed.

With a failed connection, do the following:

• Verify that the Delta Controls ODBC Driver is installed and then re-establish a link to the DSN to resolve the issue. See Verifying Installation.

• Verify that a Delta Controls ODBC Driver is installed on the computer and confirm that a DSN was created.

• Open the Microsoft Access database file from the new computer, use Tools > Add-ins > Linked Table Manager command to re-establish a link to the DSN.

To re-establish a link to the DSN: 1. Open the Microsoft Access database file from the new computer, use Tools > Database

Utilities > Linked Table Manager (MS Access) command to re-establish a Link to the DSN - or – Tools > Add-Ins > Linked Table Manager (MS Access).

2. In the Linked Table Manager dialog, select the checkbox for the DSN and then click OK.


3. In the Select Data Source dialog, click the Machine Data Source tab, and then select the Data Source Name to re-establish the link.

Query a Database with Microsoft Excel Microsoft provides a way to get ODBC data from the Delta ODBC Driver V4 into a Microsoft Excel spreadsheet. Many applications can import data exported from Excel.

To create a database query with Microsoft Excel: 1. Run Microsoft Excel. The ODBC driver requires a 32 bit version of the Excel application.

Do not use a 64 bit installation of the Excel application. 2. On the Excel ‘Data’ menu, click ‘From Other Sources’ and then click ‘From Data

Connection Wizard’. The data Connection Wizard dialog appears.

Standard Use


3. Select ‘ODBC DSN’ for the kind of data source. Click Next.

4. In the Choose Data Source dialog, select Delta ODBC 4 and then click Next.


5. The Query Wizard opens, and then guides you through the rest of the procedure.

If you get an ‘Unable to obtain list of tables from the data source’ error, it may be indicating that you are using a Click-To-Run version of Excel. Please refer to item E: Licensing Problem with Click-To-Run Version of Microsoft Office in the Troubleshooting Installation section.

7. Click Finish. If you click Next, the Select Table dialog opens. 8. Select the required table(s) from the list.

Standard Use


9. Click Ok. The Import Data dialog appears.

10. Select how you want to view the data. 11. Click OK. The data displays in an Excel spreadsheet.


12. Enter a Name and description for the data connection. Click the Finish button to save the connection file.

Advanced Use


Advanced Use This Advanced Use section contains information for programmers and database specialists who use SQL and custom applications, and provides samples of some common SQL queries. There are four main parts to this section: SQL, Visual Basic, VC++ and Java with a JDBC-ODBC bridge.

ODBC Driver V4 Structured Query Language The Structured Query Language (SQL) section provides information on queries, query examples, and definitions of the arguments. Delta ODBC Driver V4 aligns with the following specifications and standards that deal with the Call-Level Interface (CLI). (The ODBC features are a superset of each of these standards).

• The Open Group CAE Specification "Data Management: SQL Call-Level Interface (CLI)" • ISO/IEC 9075-3:1995 (E) Call-Level Interface (SQL/CLI)

The Delta ODBC Driver V4 supports all the ODBC 3.0 Driver standard CORE, Level 1, and Level 2 API functions. The SQL core grammar generally conforms to the X/Open SQL CLI, implements the SQL language specifications contained in the ANSI SQL-92 Database Language-SQL standard and implements the call-level interface specifications contained in the ISO/IEC-95 Call-Level Interface (SQL/CLI) standard.

SQL Reserved Words The following words are reserved for use by SQL:

SQL Reserved Words

ALL AND ANY AS ASC AVG BETWEEN BY COUNT CREATE DELETE DESC

DISTINCT DROP EXISTS FROM GROUP BY HAVING IN INDEX INNER INSERT INTO IS

JOIN LEFT LIKE LIMIT MAX MIN NOT NULL OFFSET ON OR ORDER BY

OUTER SELECT SET SQL SUM TABLE TOP UNIQUE UPDATE USER VALUES WHERE

The reserved words do not have to be capitalized, but are displayed in the SQL grammar examples to make them more readily identifiable.

This Advanced Use section defines the SQL grammar for SQL statements supported by the Delta ODBC Driver V4. SQL reserved words are written in uppercase to make them more readily identifiable.


Avoid Unintended Writes As a safety precaution, you can create multiple enteliWEB accounts with different permissions. For example, one account might have Read Only permissions, another account might have Read and limited Write permissions and another account might have full Read and Write permissions. The intention is to avoid doing unintended writes especially with the power of the multiple site feature supported in ODBC Driver V4. You can do a site backup before running scripts that do write operations on the network controllers but there is no way to undo an accidental write.

Use UPDATE and especially DELETE with care! It is possible to alter every object in the system by mistake. There is NO UNDO and you cannot reverse the effects using the driver.

SELECT Statement The SELECT statement retrieves data from one or more tables.

Syntax SELECT [TOP n] [ALL | DISTINCT] select_expr[AS alias_name][, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... ] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}]

• The TOP or LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

• The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set.

• Each select_expr indicates a column that you want to retrieve. There must be at least one select_expr.

• table_references indicates the table or tables from which to retrieve rows. See JOIN syntax in description.

Advanced Use


• The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause. In the WHERE expression, you can use any of the functions and operators that are supported by the ODBC Driver V4, except for aggregate (summary) functions.

• With the LIKE operator, string columns can be wildcarded in two ways:

o % This wildcard (percent) matches one or more characters, similar to the typical * in other languages.

o _ This wildcard matches a single character only (underscore).

• A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses.

• The FROM table_references clause indicates the table or tables from which to retrieve rows. If named more than one table, join will be performed.

• Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names or aliases.

• The default is ascending order; this can be specified explicitly using the ASC keyword. To sort in reverse order, add the DESC keyword to the name of the column in the ORDER BY clause that the query is sorting by.

Sample Statement SELECT SITE_ID, Name, ObjRef, Value FROM OBJECT_BAC_AI WHERE DEV_ID=59 and SITE_ID = 'MainSite'


JOIN Syntax ODBC Driver V4 supports the following JOIN syntaxes for the table_references part of SELECT statements and multiple-table DELETE and UPDATE statements:

• table_references: escaped_table_reference [, escaped_table_reference] ...

• escaped_table_reference: table_reference | { OJ table_reference }

• table_reference: table_factor|join_table

• table_factor: tbl_name [[AS] alias] | table_subquery [AS] alias | ( table_references )

• join_table: table_reference [INNER] JOIN table_factor [join_condition] | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition

• join_condition: ON conditional_expr

The following syntax shows a typical SELECT syntax using an INNER JOIN:

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name; where

This example uses the structure shown in the previous syntax:

Advanced Use


SELECT ES.Exception_Schedule_period_calendarEntry_dateAsString, SCH.Object_Name, SCH.Instance FROM ARRAY_V4_SCH_Exception_Schedule ES INNER Join OBJECT_V4_SCH SCH on ES.Objref = SCH.Objref where ES.dev_id = 100 and ES.instance =1

INSERT Statement The INSERT statement adds a record to the end of a table that contains the specified field values.

Syntax INSERT INTO tbl_name [(col_name,...)] [VALUES (expr,...) | SELECT ... ]

INSERT inserts new rows into an existing table.

• The INSERT ... VALUES forms of the statement insert rows based on explicitly specified values.

• The INSERT ... SELECT form inserts rows selected from another table or tables. See SELECT Statement.

Sample Statement INSERT INTO OBJECT_BAC_AI(SITE_ID, DEV_ID, NAME)VALUES ('MainSite', 59, 'New AI')

INSERT INTO OBJECT_V4_BI(SITE_ID, DEV_ID, NAME)VALUES ('MainSite', 72, 'Lights Master Switch)

For object tables, the INSERT statement creates objects in the controllers. For array tables, the INSERT statement appends an entry at the end of the array. For system, bitmask and enumeration tables, the INSERT statement is not allowed. For ARRAY tables representing fixed length array properties, the INSERT statement is not allowed.

See the Table 4 ISAM Error Examples for INSERT in the Troubleshooting section.


UPDATE Statement The UPDATE statement alters the value of an existing object property.


One technique is to first do a database backup of the devices on the network immediately before you run a statement. Use a SELECT statement to assess the data demands and then restrict and limit it as much as possible so that you focus on just what you want to alter.

Try the statement on a local test network that does not control a facility. You can also divide the intended changes into a series of statements and verify the changes as you run the scripts. Be conscious of the multi-site function available with the ODBC Driver V4. Avoid using the * wildcard to specify the Site Name.

Another technique to help avoid accidents is to have multiple enteliWEB logins with a range of permissions. See the Avoid Unintended Writes topic.

Syntax UPDATE table_reference SET col_name1={expr1} [, col_name2={expr2}] ... [WHERE where_condition] [LIMIT row_count]

• The UPDATE statement in SQL language can only update records in a single table.

• The SET clause indicates which columns to modify and the values they should be given as an expression.

• The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated.

• If the ORDER BY clause is specified, the rows are updated in the order that is specified.

• The LIMIT clause places a limit on the number of rows that can be updated.

Advanced Use


Sample Statement UPDATE OBJECT_V4_AV SET present_value = 33 WHERE Fullref = '//MainSite/59.AV157'

The previous statement alters the present value to 33 for the Analog Variable instance 157 on Device 59 of MainSite. The statement uses FullRef in the WHERE clause to specify the site, device and the object type and instance.

The UPDATE statement cannot be used to change BIT_*, ENUM_*, SYSTEM_* tables or any table columns for read-only properties including, but not limited to:

• SITE_ID • DEV_ID • IDX • INSTANCE • OBJREF • FullRef • ObjectType

Refer to the Why is the Object Value not updated after an UPDATE query is executed? topic located in the Troubleshooting section.

DELETE Statement The DELETE statement removes records from a table and either deletes objects from controllers or array elements from variable length array properties.


One technique is to first do a database backup of the devices on the network immediately before you run a statement. Use a SELECT statement to assess the data demands and then restrict and limit it as much as possible so that you focus on just what you want to alter.

Try the statement on a local test network that does not control a facility. You can also divide the intended changes into a series of statements and verify the changes as you run the scripts. Be conscious of the multi-site function available with the ODBC Driver V4. Avoid using the * wildcard to specify the Site Name.

Another technique to help avoid accidents is to have multiple enteliWEB logins with a range of permissions. See the Avoid Unintended Writes topic.


Syntax DELETE Statement

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

• The DELETE statement deletes rows from tbl_name and returns the number of deleted rows.

• The conditions in the optional WHERE clause identify which rows to delete. With no WHERE clause, all rows are deleted.

• where_condition is an expression that evaluates to true for each row to be deleted.

• If the ORDER BY clause is specified, the rows are deleted in the order that is specified.

• The LIMIT clause places a limit on the number of rows that can be deleted.

• These clauses apply to single-table deletes, but not multi-table deletes.

Sample Statement DELETE FROM object_BAC_AI Where site_ID = 'MainSite' AND OBJref = '59.AI1'

The DELETE statement cannot be used to remove records from following tables or objects:

• ARRAY-based tables with a fixed length

• Bitmask tables

• Enumeration tables

• System tables

Operators AND Logical AND = Assign a value (as part of a SET statement, or as part of the SET clause

in an UPDATE statement) BETWEEN ... AND ...

Check whether a value is within a range of values

Advanced Use


/ Division operator = Equal operator > Greater than operator >=, !< Greater than or equal operator IS Test a value against a Boolean IS NOT Test a value against a Boolean IS NOT NULL NOT NULL value test IS NULL NULL value test < Less than operator <=, !> Less than or equal operator LIKE Simple pattern matching - Minus operator NOT Negates value NOT BETWEEN ... AND ...

Check whether a value is not within a range of values

!=, <> Not equal operator NOT LIKE Negation of simple pattern matching OR Logical OR + Addition operator * Multiplication operator - Change the sign of the argument

Scalar Functions ODBC specifies the following types of scalar functions, with detailed information about each of these function types provided in the corresponding topics of this section. The function descriptions include associated syntax. The tables include functions that have been added in Delta ODBC Driver v4 to align with SQL-92.

The content is based on: https://msdn.microsoft.com/en-us/library/ms711813(v=vs.85).aspx

This Scalar Function section contains the following topics:

• String Functions • Numeric Functions • Time, Date, and Interval Functions

https://msdn.microsoft.com/en-us/library/ms711813(v=vs.85).aspx

https://msdn.microsoft.com/en-us/library/ms710249%28v=vs.85%29.aspx




• System Functions • Explicit Data Type Conversion Function

String Functions

Name Description ASCII(string_exp) Returns the ASCII code value of the leftmost

character of string_exp as an integer. BIT_LENGTH(string_exp) Returns the length in bits of the string expression.

Does not work only for string data types, therefore will not implicitly convert string_exp to string but instead will return the (internal) size of whatever datatype it is given.

CHAR(code) Returns the character that has the ASCII code value specified by code. The value of code should be between 0 and 255; otherwise, the return value is data source–dependent.

CHAR_LENGTH(string_exp) Returns the length in characters of the string expression, if the string expression is of a character data type; otherwise, returns the length in bytes of the string expression (the smallest integer not less than the number of bits divided by 8). This function is the same as the CHARACTER_LENGTH function.

CHARACTER_LENGTH(string_exp) Returns the length in characters of the string expression, if the string expression is of a character data type; otherwise, returns the length in bytes of the string expression (the smallest integer not less than the number of bits divided by 8). This function is the same as the CHAR_LENGTH function.

CONCAT(string_exp1,string_exp2) Returns a character string that is the result of concatenating string_exp2 to string_exp1.

DIFFERENCE(string_exp1,string_exp2) Returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.

INSERT(string_exp1, start, length, string_exp2)

Returns a character string where length characters have been deleted from string_exp1, beginning at start, and where string_exp2 has been inserted into string_exp, beginning at start.

LCASE(string_exp) Returns a string equal to that in string_exp, with all uppercase characters converted to lowercase.



Advanced Use


Name Description LEFT(string_exp, count) Returns the leftmost count characters of

string_exp. LENGTH(string_exp) Returns the number of characters in string_exp,

excluding trailing blanks. LENGTH only accepts strings. Therefore, it will implicitly convert string_exp to a string, and return the length of this string (not the internal size of the datatype).

LOCATE(string_exp1, string_exp2[, start])

Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first character position in string_exp2 unless the optional argument, start, is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned.

LTRIM(string_exp) Returns the characters of string_exp, with leading blanks removed.

OCTET_LENGTH(string_exp) Returns the length in bytes of the string expression. The result is the smallest integer not less than the number of bits divided by 8. Does not work only for string data types, therefore it will not implicitly convert string_exp to string but instead will return the (internal) size of whatever data type it is given.

POSITION(character_exp IN character_exp) Returns the position of the first character expression in the second character expression. The result is an exact numeric with an implementation-defined precision and a scale of 0.

REPEAT(string_exp, count) Returns a character string composed of string_exp repeated count times.

REPLACE(string_exp1, string_exp2, string_exp3)

Search string_exp1 for occurrences of string_exp2, and replace with string_exp3.

RIGHT(string_exp, count) Returns the rightmost count characters of string_exp.

RTRIM(string_exp) Returns the characters of string_exp with trailing blanks removed.

SOUNDEX(string_exp) Returns a data source–dependent character string representing the sound of the words in string_exp.

SPACE(count) Returns a character string consisting of count spaces.


Name Description SUBSTRING(string_exp, start, length) Returns a character string that is derived from

string_exp, beginning at the character position specified by start for length characters.

UCASE(string_exp) Returns a string equal to that in string_exp, with all lowercase characters converted to uppercase.

Numeric Functions The most common numeric functions are listed in the following table:

Name (Most Common Numeric Functions)

Description

EXP(float_exp) Returns the exponential value of float_exp. LOG(float_exp) Returns the natural logarithm of float_exp. POWER(numeric_exp, integer_exp) Returns the value of numeric_exp to the power of

integer_exp. SQRT(float_exp) Returns the square root of float_exp.

The following table contains all the numeric functions organized in alphabetic order:

Name Description ABS(numeric_exp) Returns the absolute value of numeric_exp. ACOS(float_exp) Returns the arccosine of float_exp as an angle, expressed in radians. ASIN(float_exp) Returns the arcsine of float_exp as an angle, expressed in radians. ATAN(float_exp) Returns the arctangent of float_exp as an angle, expressed in radians. ATAN2(float_exp1, float_exp2)

Returns the arctangent of the x and y coordinates, specified by float_exp1 and float_exp2, respectively, as an angle, expressed in radians.

CEILING(numeric_exp) Returns the smallest integer greater than or equal to numeric_exp. The return value is of the same data type as the input parameter.

COS(float_exp) Returns the cosine of float_exp, where float_exp is an angle expressed in radians.

COT(float_exp) Returns the cotangent of float_exp, where float_exp is an angle expressed in radians.

DEGREES(numeric_exp) Returns the number of degrees converted from numeric_exp radians. EXP(float_exp) Returns the exponential value of float_exp. FLOOR(numeric_exp) Returns the largest integer less than or equal to numeric_exp. The

return value is of the same data type as the input parameter. LOG(float_exp) Returns the natural logarithm of float_exp.

Advanced Use


Name Description LOG10(float_exp) Returns the base 10 logarithm of float_exp. MOD(integer_exp1, integer_exp2)

Returns the remainder (modulus) of integer_exp1 divided by integer_exp2.

PI() Returns the constant value of pi as a floating-point value. POWER(numeric_exp, integer_exp)

Returns the value of numeric_exp to the power of integer_exp.

RADIANS(numeric_exp) Returns the number of radians converted from numeric_exp degrees. RAND([integer_exp]) Returns a random floating-point value using integer_exp as the optional

seed value. ROUND(numeric_exp, integer_exp)

Returns numeric_exp rounded to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to |integer_exp| places to the left of the decimal point.

SIGN(numeric_exp) Returns an indicator of the sign of numeric_exp. If numeric_exp is less than zero, –1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned.

SIN(float_exp) Returns the sine of float_exp, where float_exp is an angle expressed in radians.

SQRT(float_exp) Returns the square root of float_exp. TAN(float_exp) Returns the tangent of float_exp, where float_exp is an angle expressed

in radians. TRUNCATE(numeric_exp, integer_exp)

Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to |integer_exp| places to the left of the decimal point.

Time, Date, and Interval Functions

Name Description CURRENT_DATE( ) Returns the current date. CURRENT_TIME[(time-precision)]

Returns the current local time. The time-precision argument determines the seconds precision of the returned value.

CURRENT_TIMESTAMP [(timestamp-precision)]

Returns the current local date and local time as a timestamp value. The timestamp-precision argument determines the seconds precision of the returned timestamp.

CURDATE( ) Returns the current date. CURTIME( ) Returns the current local time. DAYNAME(date_exp) Returns a character string containing English name of the day (Sunday

through Saturday or Sun. through Sat) for the day portion of date_exp. DAYOFMONTH(date_exp) Returns the day of the month based on the month field in date_exp as an

integer value in the range of 1–31.


Name Description DAYOFWEEK(date_exp) Returns the day of the week based on the week field in date_exp as an

integer value in the range of 1–7, where 1 represents Sunday. DAYOFYEAR(date_exp) Returns the day of the year based on the year field in date_exp as an

integer value in the range of 1–366.

EXTRACT(extract-field FROM extract-source)

Returns the extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression. The extract-field argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND The scale is 0 unless SECOND is specified, in which case the scale is not less than the fractional seconds precision of the extract-source field.

HOUR(time_exp) Returns the hour based on the hour field in time_exp as an integer value in the range of 0–23.

MINUTE(time_exp) Returns the minute based on the minute field in time_exp as an integer value in the range of 0–59.

MONTH(date_exp) Returns the month based on the month field in date_exp as an integer value in the range of 1–12.

MONTHNAME(date_exp) Returns a character string containing English name of the month (January through December or Jan through Dec) for the month portion of date_exp.

NOW( ) Returns current date and time as a timestamp value. QUARTER(date_exp) Returns the quarter in date_exp as an integer value in the range of 1–4,

where 1 represents January 1 through March 31. SECOND(time_exp) Returns the second based on the second field in time_exp as an integer

value in the range of 0–59. Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR Where fractional seconds are expressed in billionths of a second.

TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2)

Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR Where fractional seconds are expressed in billionths of a second.

WEEK(date_exp) Returns the week of the year based on the week field in date_exp as an integer value in the range of 1–53.

YEAR(date_exp) Returns the year based on the year field in date_exp as an integer value. The range is data source–dependent.

Advanced Use


System Functions

Name Description DATABASE( ) Returns the name of the database corresponding to the connection

handle. The name of the database is also available by calling SQLGetConnectOption with the SQL_CURRENT_QUALIFIER connection option

USER( ) Returns the logged user name. The user name is also available by way of SQLGetInfo by specifying the information type: SQL_USER_NAME.

Explicit Data Type Conversion Function

Name Description CONVERT(value_exp, data_type) The CAST Function is not supported but you can use CONVERT instead. For more information, see the SQL-92 specification.

The function returns the value specified by value_exp converted to the specified data_type, where data_type is one of the following keywords: SQL_BIGINT, SQL_BINARY, SQL_VARBINARY, SQL_BIT, SQL_CHAR, SQL_VARCHAR, SQL_DATE, SQL_TYPE_DATE, SQL_DECIMAL, SQL_DOUBLE, SQL_FLOAT, SQL_INTEGER, SQL_NUMERIC, SQL_REAL, SQL_SMALLINT, SQL_TIME, SQL_TYPE_TIME, SQL_TIMESTAMP, SQL_TYPE_TIMESTAMP, SQL_TINYINT

The following table provides range information for SQL_INTEGER and SQL_BIGINT.

SQL type Identifier

Typical SQL data

Typical Type Description Range

SQL_INTEGER INTEGER Exact numeric value with precision 10 and scale 0

Signed range is from (2^31 <= n <= 2^31 – 1) -2147483648 to 2147483647 Unsigned range is (0 <= n <= 2^32 - 1). 0 to 4294967295

SQL_BIGINT BIGINT A large integer. Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0

Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615.

For further information on data types, see the SQL 92 Specification is here:

https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types?view=sql-server-ver15


Aggregate (GROUP BY) Function Descriptions

Name Description and Example Query AVG() Return the average value of the argument SELECT AVG(value) FROM object_bac_ai WHERE Name LIKE '%Room Temp%'

COUNT() Return a count of the number of rows returned SELECT COUNT(objref) FROM object_bac_ai WHERE name LIKE ‘%OAT’

MAX() Return the maximum value SELECT MAX(value) from object_bac_ai WHERE Name LIKE '%Room Temp%'

MIN() Return the minimum value SELECT MIN(value) from object_bac_ai WHERE Name LIKE '%Room Temp%'

SUM() Return the sum SELECT SUM(value) from object_bac_ai WHERE Name LIKE '%Airflow%'

Using SQL with Custom Programs


Using SQL with Custom Programs This section contains information for programmers and database specialists who use SQL with custom applications. Typical applications may use Visual Basic, VC++ and Java with a JDBC-ODBC bridge.

Visual Basic (VB) With ADO To connect to a Delta Controls ODBC Driver Data Source using ADO, use the OLE DB provider for ODBC. The information used to establish a connection to the data source should be specified through the ConnectionString property of a Connection object. The following code examples show how to establish a DSN, or a DSN-less connection. The Object is a Visual Basic Object with the type ADODB.Connection.

To use a DSN connection, the Connection String has this format:

Object.ConnectionString = "Provider=MSDASQL.1;Data Source=Delta ODBC 4;"

To use a DSN-less connection, the Connection String has this format:

Object.ConnectionString = "driver={Delta ODBC 4};DBQ={%Site Name%};UID={%ODBC User Name%};pwd={%Password%};"

The following example shows how to retrieve a set of DEV objects and then add them into a ListBox object with name lbList.

Dim ado Conn As New ADODB. Connection Dim adoRS As new Adodb. Record SET adoConn.ConnectionString = "....." adoConn.Open Set adoRS.ActiveConnection = adoConn adoRS.Open "SELECT * FROM OBJECT_BAC_DEV" While Not adoRS.EOF lbList.AddItem CStr(adoRS("instance")), lbList.ListCount adoRS.MoveNext Wend adoRS.Close Set adoRS = nothing ado Conn. Close Set adoConn = nothing


Visual C++ The VC++ client is a client based on the set of ODBC classes: CDatabase and CRecordset. The traditional way to use database classes is as follows:

1. Open Connection 2. Bind a Recordset and Connection 3. Open the Recordset 4. Work with the Recordset 5. Close the Recordset 6. Close Connection

A developer can open two types of connections: a DSN connection and a DSN-less connection. For a DSN connection, provide the DSN name and logon information. When the DSN is a Data Source Name from the ODBC Administrator, UID and PWD are the user logon information. For example:

"DSN=Delta ODBC 4;"

For a DSN-less connection the developer needs to provide the full driver name, and all required information to open a connection. For example:

"driver={Delta ODBC 4};DBQ={%Site Name%};UID={%ODBC User Name%};PWD={%Password%}"

Where: DBQ is the Site Name, UID is the enteliWeb user name, PWD is password.

Make sure that CDatabase::useCursorLib is specified when you open a connection to the Delta Network. The cursor library masks some functionality of the underlying ODBC driver, effectively preventing the use of dynaset (if the driver supports them).

The only cursors supported, if the cursor library is loaded, are static snapshots and forward only. If you plan to create a recordset object directly from CRecordset without deriving from it, you should not load the cursor library.

Using SQL with Custom Programs


C Database m_Database; TRY { m_Database.OpenEx(_T(" driver={Delta Controls ODBC Driver}; DBQ=Delta Controls;UID=MYUSER; pwd=MYPASSWORD;"), CDatabase::noOdbcDialog CDatabase::useCursorLib); } CATCH(CDBException, pE) { AfxMessageBox(pE->m_strError); } END_CATCH

The following example shows how to retrieve a set of DEV objects. For the unbound recordset (when a developer does not derive classes from CRecordset), forwardOnly is mandatory.

CRecordset rsDevices; CString strResult; TRY { rsDevices.m_pDatabase = &m_database; rsDevices.Open( CRecordset::forwardOnly, _T("SELECT * FROM OBJECT_BAC_DEV")); while (!rsDevices.IsEOF()) { rsDevices.GetFieldValue(_T("INSTANCE"), strResult); //strResult will contain string representation //for numeric value of INSTANCE rsDevices.MoveNext(); } } CATCH(CDBException, pE) { AfxMessageBox(pE->m_strError); } END_CATCH if (rsDevices.IsOpen()) rsDevices.Close(); m.Database. Closed();


Java with JDBC-ODBC Bridge You can still use the Java code that you used for the ODBC Driver 3 with ORCAview, but you must use the 32 bit JDK6 and lower (the latest JDK is 8). That is the version that ODBC 3 supported also. Note that the DSN name in ODBC Driver 3 used to be "Delta Network" but version 4 uses "Delta ODBC 4" so you need to revise the code where necessary.

To connect to a Delta Controls ODBC Driver Data Source using JDBC, use the Sun JDBC-ODBC Bridge. The information used to establish a connection to the data source should be specified through the getConnection method of a DriverManager object. The following code example shows how to load a Sun Java-ODBC Bridge Driver, and then establish a DSN connection. The dataConnection is an instance of class Connection. Delta ODBC 4 is the DSN name.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); dataConnection = DriverManager.getConnection("jdbc:odbc:Delta ODBC 4");

The following example shows how to retrieve a set of DEV objects and then print them to "standard" output stream.

//--------------------------------------------------- import java.sql.*; //Necessary package for SQL routines //--------------------------------------------------- Connection dataConnection = null; Statement stmt = null; //loading JDBC-ODBC Bridge driver into memory Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //Making a connection to database //"Delta ODBC 4" is ODBC DSN dataConnection = DriverManager.getConnection("jdbc:odbc:Delta ODBC 4"); //Making statement object stmt = dataConnection.createStatement(); String sql = "SELECT * FROM OBJECT_BAC_DEV WHERE DEV_ID<2000"; //run the query ResultSet rs = stmt.executeQuery(sql); //go through the recordset and print DEV_ID and device name while (rs.next()) { System.out.println("Device [" + rs.getObject("DEV_ID").toString() + "]: "+ rs.getObject("NAME").toString()); } rs.close(); dataConnection.close();

Troubleshooting


Troubleshooting Section Contents

Typical Error Messages ..............................................................................................................74

Interpreting Common Error Messages ......................................................................................75

Table 1: Invalid Query Error Messages ......................................................................................75

Table 2: Not Found / Unknown Error Messages ........................................................................77

Table 3: Unsupported Error Messages ......................................................................................79

Table 4: ISAM Error Examples for INSERT ................................................................................80

Query Performance Considerations ...........................................................................................82

Add Restriction to a Query .........................................................................................................83

For installation issues, see the Setting up the ODBC Driver section which contains troubleshooting information.

This section provides examples of how to deal with some common issues that may arise when using the Delta Controls ODBC Driver V4. Typically, scripts are created and used to query data. Most cases involve error messages and some may involve addressing an issue that does not generate an error message. Some cases involve a query that takes longer than expected to generate a result.

When you use the driver, error messages can come from multiple sources:

• Delta Controls BACnet ODBC Driver (most common) • Microsoft ODBC Manager • Application that you are using • BACnet controller (Delta Controls or third party devices)

The order of the components within an error message may vary depending on the query tool used. With some ODBC tools, if you use a small window, the ODBC error message is sometimes truncated by the ODBC tool to fit within the area available for error messages.


Typical Error Messages This topic presents basic content that aims to help a user interpret and act on the feedback provided by the driver. After this brief initial explanation of error messages, troubleshooting content is presented in tables. The performance information follows and provides issues and possible responses. The following topics show two typical error messages with some suggested responses based on an interpretation of the message.

For information about issues that prevent you from logging in or running the driver, see the License or Login Issues table in the Troubleshooting Installation topic located in the Setting Up the ODBC Driver section of this document.

Expected lexical element not found: <identifier> The following query has the keyword FROM spelled incorrectly as FROMMM.

SELECT OBJECTSYSTEM, OBJECTTYPE, Name, OBJECTTYPE_ID FROMMM SYSTEM_OBJECTTYPES

The following message appears:

Column ‘<identifier>’ not found SELECT Object_System, Name, FROM SYSTEM_OBJECTTYPES

The following message appears:

The previous message indicates that the Object_System column is not in the table queried. First, you would check the spelling / syntax of the table name and then check that the column is actually present in this table. The correct column is: ObjectSystem without an underscore. One quick way to check for the columns in a table is to run a SELECT * query for a particular table and then inspect the results.

Troubleshooting


Interpreting Common Error Messages The following table deals with queries that have simple typing mistakes or incorrect syntax. Other tables cover the case of a valid query that is too complex, poorly formed or requires a huge number of database operations.

Table 1: Invalid Query Error Messages

A: Spelling Mistake in SQL Keyword Invalid SQL statement: SELEKT

The previous message indicates a problem with the syntax of the statement. Check for a typing error especially in a SQL keyword or for a missing element in the query. In the following query, the SELECT keyword is misspelled. Example Bad Query: SELEKT DEV_ID, Name, Value, Units FROM OBJECT_BAC_AV Situation Actions Result The message indicates a spelling mistake or an unrecognized keyword.

a) Check and fix any spelling mistakes. b) Check the keywords used in the query.

The corrected query runs normally and no error message is displayed. SELECT DEV_ID, Name, Value, Units FROM OBJECT_BAC_AV

B: Statement Containing Incorrect Extra Character(s) at the End Unexpected extra token

In the following query, the statement has an extra brace bracket at the end.

Example Bad Query: SELECT * FROM OBJECT_BAC_AV) Situation Actions Result


Table 1: Invalid Query Error Messages The message indicates an incorrect character(s) such as a brace bracket at the end of the statement.

a) Remove the incorrect character(s) and run the query again.

The query runs and the "Unexpected extra token" message no longer displays.

C: Attempt to Update a Read-Only Table Column Attempt to write read-only

Read only columns cannot be updated.

UPDATE OBJECT_bac_AI SET objType= 'BinaryValue' where objref = '1011.AI83' If '1011.AI83' did not exist, then a "0 rows affected" messages would appear. See the following D: Referenced Object that Does Not Exist on Device entry in this table.

Situation Actions Result The message indicates an attempt to write to a read only property.

a) Fix the statement to write to the correct property that is writable.

The query runs and the "Attempt to write read-only"message no longer displays.

D: Referenced Object that Does Not Exist on Device 0 rows affected

Cannot update an object if it does not exist on the device.

UPDATE OBJECT_bac_AV SET Value = 68 where objref = '100.AV1' If '100.AI' does not exist, then an "Attempt to write read-only" message would appear. See the previous C: entry in this table. Good practice is to do a SELECT first to verify the WHERE clause and to see how many objects/ rows etc. are involved. Modify the WHERE clause if needed. Then, you can copy the WHERE clause into an INSERT statement.


Troubleshooting


Table 1: Invalid Query Error Messages The query syntax is valid but the 0 rows message indicates that the object does not exist on the device.

a) Fix the statement to write to an object that exists on the device.

The query runs and the "0 rows affected" message no longer displays.

E: Insert statement with Unequal Number of Columns Unequal number of insert columns and insert values

Bad Query Example INSERT INTO OBJECT_BAC_BV (DEV_ID, instance, name) VALUES (100, 103) Situation Actions Result The message indicates that the number of columns and values do not match.

a) Fix the number of columns and insert values so that they match. In this example, the name is missing. For example, Fan1Status.BV100 can be added to the query.

The query runs without any error message. The revised query might be: INSERT INTO OBJECT_BAC_BV (DEV_ID, instance, name ) VALUES (100, 103, Fan1Status.BV100)

Table 2: Not Found / Unknown Error Messages

A: Invalid Table Name Table‘xxx’ not found

The correct table name is SYSTEM_OBJECTTYPES and NOT SYSTEM_OBJECTTYPESS where SYSTEM_OBJECTTYPES has an extra "S" at the end. Bad Query Example



SELECT OBJECTSYSTEM, OBJECTTYPE, Name, OBJECTTYPE_ID FROM SYSTEM_OBJECTTYPESS Situation Actions Result

Every query must use a valid table name. The name could be misspelled or refer to a non-existent table.

a) Fix the table name so that the name is valid. b) Run the query to confirm that the table name is valid.

The query now has a valid table name and produces a result without any error message.

B: Invalid Table Column Column ‘test’ not found

In the following query, test is not a valid table column in the AV object. SELECT test FROM OBJECT_BAC_AV Situation Actions Result

Every query must use a valid table column(s) that is in the specified table.

a) Fix the table column name in the query so that the name is valid. b) Run the query to confirm that the table column name is valid.

The query now has a valid table column and produces a result without any error message.

C: Unknown Property (controller not understand the property)

Column <identifier> not found

If a controller does not understand a BACnet, Delta proprietary or third party property, the error message shows the name of the column that it does not understand. The following query includes the Delta proprietary table column HAL_Flags of the AI object but the targeted Device 4100 does not understand these properties. Bad Query Example

Troubleshooting



SELECT Name, Value, HAL_Flags from OBJECT_BAC_AI WHERE Dev_ID =4100 Situation Actions Result

Query device does not understand the property (third party).

a) Check the result for null entries. b) Remove the Delta proprietary property from the query for Device 4100.

The modified query with HAL_Flags in this case, now removed generates a result for the modified query.

Table 3: Unsupported Error Messages

A: Multi row insert is not supported:

The current driver does not support multiple row inserts.

INSERT into ARRAY_BAC_SCH_Schedule (DEV_ID, INSTANCE, DAY, Value_Type) VALUES (5600, 2, 3, 'enumerated'), (5600, 2, 3, 'Null') Comment Action Result

The "Multi-row INSERTs are not supported," message indicates an additional unexpected row insert.

a) Check the syntax of the statement. b) Fix the INSERT so that only one row is inserted. INSERT does not support multiple row inserts.

The simplified INSERT query runs without any error.

B: Table Deletion Not Supported by Driver Not supported

DROP would delete the whole table which the driver does not allow. The DELETE statement deletes data in a table but NOT the table itself.


Table 3: Unsupported Error Messages Bad Query Example DROP TABLE OBJECT_BAC_AIC Comment Action Result

Supported SQL statement that the driver does not allow by design.

a) Fix the statement by not using DROP. b) Run query again.

The query now runs. By design, the driver does not support deleting or changing existing tables.

Table 4: ISAM Error Examples for INSERT

A: An ISAM error shows when try to insert a new array entry into ARRAY_V4_EVC_Destination_List.

An INSERT like the following gives an ISAM error as all the properties in the internal union must be included in the INSERT statement. The BACnet server does not support entering default values for the missing union data. ISAM

V4 ODBC Bad – This statement produces an ISAM error INSERT into ARRAY_V4_EVC_Destination_List (DEV_ID, Instance, SITE_ID, Destination_List_ValidDays, Destination_List_FromTimeAsString, Destination_List_ToTimeAsString, Destination_List_recipient_address_network_number,Destination_List_ProcessIdentifier, Destination_List_IssueConfirmedNotifications, Destination_List_Transitions,Destination_List_Retry) VALUES (30000, 165, 'UDP_IP', 'friday|saturday|sunday', '00:15:00.00', '04:00:00.00', 0, 0, 1, 'to-fault', 1); Comment Action Result

EVC_Destination_List is a complex structure, it is a list of group properties that has a union inside.

Verify that all the properties and values in the internal union are specified.

The INSERT statement now runs without any error.

V4 ODBC - This statement returns valid results.

Troubleshooting


Table 4: ISAM Error Examples for INSERT INSERT into ARRAY_V4_EVC_Destination_List (DEV_ID, Instance, Destination_List_ValidDays, Destination_List_FromTimeAsString, Destination_List_ToTimeAsString, Destination_list_recipient_type, Destination_List_recipient_address_network_number, Destination_List_recipient_address_mac_address, Destination_List_ProcessIdentifier, Destination_List_IssueConfirmedNotifications, Destination_List_Transitions,Destination_List_Retry) VALUES (5600, 90010, 'monday', '00:11:00.00', '05:55:00.00', 'address', 2, '0x', 0, 1, 'to-offnormal', 2);

V3 ODBC - This statement returns valid results. INSERT into ARRAY_BAC_EVC_DestinationList (Site_ID, DEV_ID, INSTANCE,DestinationListValidDays, DestinationListFromTime, DestinationListToTime, DestinationListDeviceType, DestinationListDeviceRefDeviceNo, DestinationListDeviceRefObjectType, DestinationListDeviceRefInstance, DestinationListRetry, DestinationListConfirmed, DestinationListTransitions) Values (1, 99509, 1,'Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday', { t '08:00:00'} , { t '16:00:00'} , 'Logical',100002, 284, 100002, 1, 1, 'To Alarm, To Fault, To Normal')

B: An ISAM error shows when try to insert a new entry into ARRAY_V4_SCH_Weekly_Schedule

An INSERT like the following gives an ISAM error as you must specify the day that you want to insert to and also the value type. ISAM Error

V4 ODBC Bad Query - This statement produces an ISAM error as it does not specify a day.


Table 4: ISAM Error Examples for INSERT INSERT into ARRAY_V4_SCH_Weekly_Schedule (DEV_ID, Instance, SITE_ID, day_schedule_timeAsString, day_schedule_value_enumerated) VALUES 30000, 1, 'UDP_IP', '03:00:00.00', '1')

Comment Action Result

To insert a new item in Weekly_Schedule, you must specify the day you want to insert to and also the value type.

Fix the statement by specifying the day that you want to insert and the value type.

The query now runs.

V4 ODBC – This statement returns valid results. INSERT into ARRAY_V4_SCH_Weekly_Schedule (DEV_ID, Instance, day, day_schedule_timeAsString, day_schedule_value_type, day_schedule_value_enumerated) VALUES (5600, 90010, 4, '09:22:00.00', 'enumerated','0'

To modify existing item: You have to specify the day you want to insert to and the value type and the item in the array to modify. V4 ODBC – This statement modifies an existing item. INSERT into ARRAY_V4_SCH_Weekly_Schedule (DEV_ID, Instance, day, day_schedule_timeAsString, day_schedule_value_type, day_schedule_value_enumerated, idx) VALUES (5600, 90010, 4, '07:22:00.00', 'enumerated','1', 2)

Query Performance Considerations Many performance issues do not generate an error message as the syntax is acceptable to the driver.

If the SQL query for data from tables takes longer than expected, the cause could be a lack of restrictions for the devices and or instances. Without enough restrictions, the ODBC Driver goes through most or all of the devices, and instances trying to match criteria. In some cases, even if you restrict the query as much as possible, it takes a long time to see the results. Queries with OR conditions often require a long time to complete as they require many database operations.

Troubleshooting


With a very large site or multiple sites, you need to be especially careful with queries and to restrict, limit or restructure problematic queries. Some queries require extensive database operations and you might think of them as doing a descriptor reload on a large site which can take many hours.

With any query, consider the following:

1. Watch what you ask for! Avoid SELECT * when you really only need a small subset of the requested information. Generally, SELECT * takes longer in ODBC Driver V4 than in V3 as many more columns are included in V4 and the driver handles requests and reads in a different way. With a query involving Trend Logs, the query either returns no data or all of the data in each trend. The TL cannot return part of the data.

2. Query the right objects and do it in an efficient way. Specify what you want as much as possible. Include specific names, sites and object types.

3. Use a WHERE clause to restrict a query. With a new query, you can first test it using LIMIT 10 as a way to get just get the first 10 records with a quick response. Examine these records and confirm that they are what you want.

4. LIKE is especially useful to restrict a query. Use LIKE as an operator inside a WHERE clause as it often speeds up a query. For example use LIKE within this where clause: WHERE ObjectName LIKE ‘%Setpoint’

5. In addition, the ODBC Driver Version 4 provides new features and also backward compatibility with the V3 Driver. These capabilities place more operational demands on the V4 driver in some situations.

Add Restriction to a Query If the SQL query for data from tables is very slow, the cause could be a lack of restrictions for the devices and or instances. Without enough restrictions, the ODBC Driver goes through all the devices, and instances trying to match criteria. For example:

SELECT DEV_ID, NAME, VALUE FROM OBJECT_BAC_AO WHERE VALUE > 20

To speed up an SQL query try adding criteria on DEV_ID and/ or, INSTANCE. The following table provides a few examples:

Column: Restrictions:

Add this criteria to the WHERE clause:

DEV_ID DEV_ID = 5900

DEV_ID BETWEEN 100 AND 200


INSTANCE INSTANCE = 1

INSTANCE < 5

If an SQL query is slow even if restrictions for DEV_ID, INSTANCE or IDX are specified, the cause may be an OR condition used in the WHERE clause. The OR statement affects the WHERE clause because it causes the ODBC Driver to evaluate each of the conditions individually for each object.

SELECT * FROM OBJECT_BAC_AO WHERE DEV_ID=5900 OR VALUE>5

For the type of query above there is no workaround. This query always goes through all objects in the table and tries to match VALUE and DEV_ID.

One possible solution, if the client application supports it, is to split this query into 2 separate queries with a single condition, and then combine the results.

A: Query Takes Much Longer than Expected No message is shown but the query may take longer than expected to execute especially on a large site or multiple sites.

Consider the following type of query that has no workaround. This query always goes through all objects in the table and tries to match VALUE and DEV_ID. You can add a Where clause to restrict the devices involved.

Initial Query: SELECT DEV_ID, NAME, VALUE FROM OBJECT_BAC_AO WHERE VALUE > 20

The query reads all the AO objects on the site(s) and checks if the value is greater than 20.

Try the following to speed up the query:

1. If possible, focus the search on just the site(s) of interest and also to the specific devices or device ranges.

2. Add a restriction clause to the WHERE component.

Troubleshooting


Modified Query with Additional Where Restriction Added: SELECT DEV_ID, NAME, VALUE FROM OBJECT_BAC_AO WHERE VALUE > 20 AND DEV_ID > 100 AND DEV_ID < 200

B: Query is Complex (OR used) Needs Further Restriction as Still Takes Longer than Expected No message is shown but the query may take a much longer time to execute on a large site or multiple sites.

For this type of query no workaround is available. This query always goes through all objects in the table and tries to match VALUE and DEV_ID.

SELECT * FROM OBJECT_BAC_AO WHERE DEV_ID=5900 OR VALUE>5

The query goes through all the objects in the table and tries to match the WHERE clause.

Try the following to speed up the query:

1. If possible, add further restrictions to focus the query more and reduce the time needed. 2. Consider using several queries each with a single condition to get the information and

then combine the results after.

Try to restrict the selection as much as possible as a good way to reduce the query time. This query is slow as it does many database operations to complete the query.

C: Trend Log Query Takes Longer than Expected Querying data from Trend Log may take a long time depending on how much data is involved in your query statement.

The key to reduce query time is to narrow down the data you obtain from a query result.

Troubleshooting Here are some tips to increase the speed of Trend Log Query:

1. Specify particular data, site, controller, trend log instance information to narrow down the data you are looking for.

For instance, the query below only returns 4 columns for a TL instances between 1-10 on DEV100 in the Main Building site:


SELECT fullref, Object_Name, Input_Ref, Logging_Type from OBJECT_V4_TL WHERE Site_ID = 'Main Building' and DEV_ID = 100 and instance between 1 and 10

2. Limit the number of TLs involved in the query.

For instance, the query below only returns the first 10 entries from TL’s on DEV100:

SELECT * from OBJECT_V4_TL WHERE DEV_ID = 100 LIMIT 10

With a query on TL objects, you always need to be aware of the amount of data involved. Your query may be very slow because it requires manipulation of huge amounts of data.

Why is the Object Value Unchanged after an UPDATE Query? If an object value is not updated after an UPDATE query, it probably relates to the object mode. In addition, an ORS object can restrict access and permissions for devices and objects. A writer with a higher priority can also override the ODBC driver object value.

Object Mode If an object is in Auto mode, modifications of the object value cannot be applied by the Delta Controls ODBC driver. To modify an object’s values, switch from Auto to Manual mode prior to executing the UPDATE query.

To execute an UPDATE query that changes the object mode:

1. Create an AV object (Analog Variable), on a BACnet controller, and make sure that it is in Auto mode.

2. Run the following two queries: the first one sets the object to Manual mode and then the second one assigns a value to the object. Change the DEV_ID and instance to match your test object

UPDATE OBJECT_BAC_AV SET VALUE OutOfService = 1 where DEV_ID = 100 and instance =1

UPDATE OBJECT_BAC_AV SET VALUE = 50 where DEV_ID = 100 and instance =1

3. After the first query, the object should change from Auto to Manual and after the second query the object value should change to 50.

Troubleshooting


Copyright Copyright © Delta Controls Inc. All rights reserved.

Document Title: ODBC Driver Version 4 User Guide

Edition: 1.20 Date of edition: November 22, 2019

No part of this document may be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any language (natural or computer), in any form or by any means, without the prior written permission of Delta Controls Inc.

Limited permission is granted to reproduce documents released in Adobe® Portable Document Format (PDF) electronic format in paper format. Documents released in PDF electronic format may be printed by end-users for their own use using a printer such as an inkjet or laser device. Authorized distributors of Delta Controls Inc. products (Delta Partners) may print PDF documents for their own internal use or for use by their customers. Authorized Delta Partners may engage a printing or copying company to produce copies of released PDF documents with the prior written permission of Delta Controls Inc.

Information in this document is subject to change without notice and does not represent a commitment to past versions of this document on the part of Delta Controls Inc. Delta Controls Inc. may make improvements and/or changes to this document/the associated software/or associated hardware at any time.

BACstat, the Delta logo, ORCAview, ORCAweb, Earthright, enteliWEB, enteliBUS, enteliMESH, enteliTOUCH, enteliVIZ, enteliZONE, enteliSTAT, and Virtual Stat are registered trademarks of Delta Controls Inc.

Microsoft, Microsoft Excel, Microsoft Access, Windows Server, ODBC, Windows 7, Windows 8 and Windows 10 are registered trademarks of Microsoft Corporation.

Java is a trademark of Sun Microsystems Inc.

BACnet is a registered trademark of the American Society of Heating, Refrigeration and Air-Conditioning Engineers (ASHRAE).

All other trademarks are the property of their respective owners.


Document Revision History Document Edition Number

Date Published Change Description

1.0 September 22, 2016 User Guide for initial full product release which is ODBC Version 4.1.0.461.

1.1 March 22, 2019 Added to avoid using Microsoft Office click to run applications. Also emphasized the use of 32 bit applications with the Delta ODBC Driver 4. For the CONVERT function, removed SQL_LONGVARBINARY and SQL_LONGVARCHAR from supported keywords. Added Avoid Unintended Writes topic.

1.15 July 11, 2019 Approximately page 42/43, remove the invalid query for offline devices in the SYSTEM_DEVICELIST content of the Getting Started with the ODBC Driver section.

1.16 July16, 2019 Further revisions to content for SYSTEM_DEVICELIST.

1.20 November 22, 2019 In Getting Started with ODBC Driver section, system devicelist tables now allow a user to query and find an offline device. In Troubleshooting, show ISAM error examples: ARRAY_V4_EVC_Destination_List ARRAY_V4_SCH_Weekly_Schedule. Include some data type range information in CONVERT.

www.deltacontrols.com