Home / Using the InterSystems ODBC Driver / Overview

Using the InterSystems ODBC Driver
Previous section           Next section
InterSystems: The power behind what matters   

InterSystems provides ODBC drivers to enable you to access InterSystems databases via an ODBC connection. To use ODBC, install and configure the InterSystems ODBC client driver, then define one or more DSNs to refer to InterSystems databases. Your application can use an InterSystems DSN in the same way it would use any other DSN.
To use an InterSystems database as an ODBC data source, you should first ensure that the InterSystems ODBC client driver has been installed. The following options are available:
You must also define DSNs (Data Source Names) to provide your ODBC-aware applications with information needed to connect to InterSystems databases. Each InterSystems database can be represented by multiple DSNs, each of which can support multiple connections. See Using an InterSystems Database as an ODBC Data Source on Windows or Using an InterSystems Database as an ODBC Data Source on UNIX® for OS-specific instructions on how to perform these tasks.
On Windows, InterSystems IRIS IDs use the Large Number (BigInt) datatype, so ODBC client applications must have Large Number support. For example, instances of Access 2016 previous to build 16.0.7812 will display row data as #Deleted. This may also happen if Large Number support is not turned on in the Access Settings for the current database.
Supported ODBC Driver Managers
The InterSystems ODBC drivers are compliant with ODBC 3.5.
InterSystems ODBC supports the following ODBC driver managers:
For more complete information, including specific supported databases, see the online InterSystems Supported Platforms document for this release.
Support for pyodbc Python ODBC bridge
pyodbc is an open source Python module which implements the DB API 2.0 specification (PEP 249 -- Python Database API Specification v2.0), leveraging ODBC to access the underlying database. InterSystems supports use of pyodbc as a way to access the database from Python using the relational paradigm. For general information, see the pyodbc GetHub site.
There are several sites with installation information, both for Windows and for Linux and related operating systems:
The installation process is simple:
MacOS X Installation
MacOS X installation is similar to UNIX platforms (see Python Releases for Mac OS X):
Test Program
The following test program demonstrates using pyodbc to access an InterSystems IRIS database. See Structure of the ODBC Initialization File for an example listing the connection keywords supported by the InterSystems ODBC driver.
import pyodbc 
import time

input("Hit any key to start")

dsn = 'IRIS Samples'
server = '' 
database = 'USER' 
username = '_SYSTEM' 
password = 'SYS' 
#cnxn = pyodbc.connect('DRIVER={InterSystems ODBC35};SERVER='+server+'; PORT='+port+'; DATABASE='+database+';UID='+username+';PWD='+ password)
cnxn = pyodbc.connect('DSN='+dsn+';')
#value = PyLong_FromUnsignedLongLong(lowptr)
#print("%#5.8x"% (value))

print ("Connection high pointer: ")
print (format(highptr, '02x'))
print ("Connection high pointer: ")
print("%#5.8x"% (highptr))
print ("Connection low pointer: ")
print("%#5.8x"% (lowptr))
cursor = cnxn.cursor()
start= time.clock()

#Sample select query
cursor.execute("SELECT * from User.Person") 
row = cursor.fetchone() 
#while row: 
#    print(row) 
#    row = cursor.fetchone()

end= time.clock()
print ("Total elapsed time: ")
print (end-start)
input("Hit any key to end")
The following changes avoid returning Unicode data specifically and just directly return UTF-8 data.
   cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='raw')
   cnxn.setencoding(str, encoding='raw')
This uses the narrow driver, which avoids driver managers using UCS-2 or UCS-4 Unicode and the complications of providing a driver that matches how a particular driver manager was built. For other Unicode options, see Unicode in the pyodbc Wiki.
An Overview of ODBC
An ODBC system has the following components:
For a particular vendor database, that vendor may offer its own version of the ODBC client driver for that platform. Oracle, for example, supplies its own ODBC driver for use with Oracle databases on Windows. This may be preferred in some cases because the vendor driver may take advantage of its knowledge of how the database works internally to optimize performance or enhance reliability.
ODBC Connection Details
For an application to connect to a database via ODBC, the application must generally provide the following connection details:
In most cases, this information is stored within a DSN, which has a logical name for use within the client application. The DSN may or may not include login credentials, which can also be stored in the database initialization file, or not stored at all.
The DSNs must be registered with the ODBC driver manager.
In practice, a connection is established as follows:
  1. A client application includes ODBC calls that attempt to connect to a particular DSN. A client application is linked to an ODBC driver manager, which accepts the calls.
  2. The ODBC driver manager reads the initialization file to obtain the location of the ODBC client driver and load the client driver into memory.
  3. Once loaded into memory, the ODBC client driver uses the ODBC initialization file to locate connection information for the DSN, as well as other information. Using this information, the client driver connects to the specified database.
  4. Having established the connection, the client driver maintains communications with the database server.

Previous section           Next section
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-04-23 13:43:21