Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.4 / Application Development / Using the InterSystems ODBC Driver / Overview
Previous section   Next section

Overview

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.
This chapter discusses the following topics:

Installation

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:
  • The InterSystems standard installation installs ODBC driver components by default (as described in the Installation Guide).
  • If you perform a custom installation, you can select the SQL client only option to install only the ODBC client driver.
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 “Defining an ODBC Data Source on Windows” or “Defining an ODBC Data Source on UNIX®” for OS-specific instructions on how to perform these tasks.
Note:
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:
  • On Windows: the Microsoft Windows driver manager provided with the operating system.
  • On UNIX®: the iODBC driver manager (for use with the Unicode and 8–bit ODBC APIs) and the unixODBC driver manager (for use with the 8–bit ODBC API). See “ODBC Installation and Validation on UNIX® Systems” for more information.
    For questions about other driver managers, contact the InterSystems WorldWide Response Center (WRC).
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 GitHub site.

Installation

There are several sites with installation information, both for Windows and for Linux and related operating systems:
The installation process is simple:
  • Install Python 2 or 3 (which supports Unicode) via the Python download:
  • From a console with Python in the path:
       pip install pyodbc
    

MacOS X Installation

MacOS X installation is similar to UNIX platforms (see Python Releases for Mac OS X):
  • install homebrew
  • install unixODBC
  • run pip install:
       pip install --upgrade --global-option=build_ext 
       --global-option="-I/usr/local/include"  --global-option="-L/usr/local/lib" 
       --allow-external pyodbc --allow-unverified pyodbc pyodbc
    

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.
test.py
import pyodbc 
import time

input("Hit any key to start")

dsn = 'IRIS Samples'
server = '127.0.0.1' 
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+';')
lowptr=cnxn.getinfo(127)
highptr=cnxn.getinfo(136)
#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.

Support for Node.js Relational Access

The node-odbc open source Node.js module enables ODBC database access for Node.js client applications. According to the node-odbc site (https://github.com/wankdanker/node-odbc), the module is intended to be “an asynchronous/synchronous interface for node.js to unixODBC and its supported drivers” but it also works in Windows with the Windows driver manager. InterSystems IRIS supports node-odbc on both platforms.

Dependencies

  • InterSystems ODBC driver
    This is installed by default when you install InterSystems IRIS.
  • Node.js and npm
    Make sure Node.js version 8 or later is installed. npm is typically installed with Node.js.
  • node-odbc
    The node-odbc package is available using npm, or it can be installed locally from Github. Refer to the Github node-odbc site (https://github.com/wankdanker/node-odbc) for more information.
    The following packages are required to build node-odbc:
    • node-gyp
      node-odbc is delivered as source and is built by npm commands using node-gyp. If you use npm to install node-odbc you may also get node-gyp installed. If not, refer to the node-gyp site (https://www.npmjs.com/package/node-gyp) for information about how to install it.
      Depending on the OS or Linux distribution, it may be necessary to install development tools that are required by node-gyp to build the node-odbc module. No attempt is made here to document the tools required or how to install them. Refer to node-gyp and node-odbc installation instructions for more information.
    • Python and related development tools
      Python is a requirement for node-gyp. At the time of this writing, node-gyp depends on Python 2.7 but that could change in the future as new versions of node-gyp become available.
  • unixODBC (Linux/UNIX only)
    The unixODBC driver manager is required to use node-odbc on Linux, and is provided as a standard part of most Linux distributions. If not already installed on your system, see the installation instructions for your distribution. It is also available for download from the unixODBC site (http://www.unixodbc.org/).

Installation and Setup

  • Make sure all dependencies are installed:
    • Node.js and npm (https://nodejs.org/en/download/) — Make sure Node.js version 8 or later is installed. npm is also required and typically installed with Node.js. Decide whether to install node modules using npm locally or globally. First step for local installation is to define a project folder, go to that folder and run 'npm init' (see the example in the following section).
    • node-gyp (https://www.npmjs.com/package/node-gyp) — This package is required to build node-odbc. It makes sense to install node-gyp globally, but a local installation will work. In either case, node-gyp will also require Python 2.7.
    • node-odbc (https://github.com/wankdanker/node-odbc) — Install on your system using instructions included on the linked page. This should probably be installed locally since it needs to be rebuilt for IRIS ODBC.
  • Remove UNICODE support and rebuild node-odbc. Edit ./node_modules/odbc/binding.gyp to comment out 'UNICODE' in the 'defines' array. Save the modified binding.gyp and then in the project folder execute 'npm rebuild'.
  • Make sure the appropriate InterSystems ODBC DSNs are defined. On Windows, you can use the Data Source Administrator (see “Defining an ODBC Data Source on Windows”). On non-Windows platforms, define the ODBCINI environment variable to the location of the desired odbc.ini file (see “Defining an ODBC Data Source on UNIX®”). It is also possible to define this in JavaScript before loading the node-odbc module.

Sample Installation and Setup on Ubuntu

This sample assumes that Node.js and npm have been installed on your system. If you use npm to install node-odbc you may also get node-gyp installed. The node-gyp module and its dependencies are required before you can build node-odbc.
Set up a project folder with npm init
It is okay to just take the defaults for the npm init options.
  ~$ mkdir my_odbc
  ~$ cd my_odbc
  ~/my_odbc$ npm init

  This utility will walk you through creating a package.json file.
  It only covers the most common items, and tries to guess sensible defaults.

  See `npm help json` for definitive documentation on these fields and exactly what they do.

  Use `npm install <pkg>` afterwards to install a package and   save it as a dependency in the package.json file.

  Press ^C at any time to quit.
  package name: (my_odbc) 
  version: (1.0.0) 
  description: 
  entry point: (index.js) 
  test command: 
  git repository: 
  keywords: 
  author: 
  license: (ISC) 
  About to write to /home/your_home/my_odbc/package.json:

  {
  "name": "my_odbc",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC"
  }

  Is this OK? (yes)
  ~/my_odbc$

Install node-gyp (if not already installed globally)
  ~/my_odbc$ npm ls node-gypnpm ls node-gyp
  my_odbc@1.0.0 /home/your_home/my_odbc
  └── (empty)

  ~/my_odbc$ npm install node-gyp --save

  npm notice created a lockfile as package-lock.json. You should commit this file.
  npm WARN my_odbc@1.0.0 No description
  npm WARN my_odbc@1.0.0 No repository field.

  + node-gyp@3.8.0
  added 97 packages from 67 contributors and audited 183 packages in 6.749s
  found 0 vulnerabilities

  ~/my_odbc$ 
Install node-odbc
  /my_odbc$ npm install odbc

  > odbc@1.4.5 install /home/your_home/my_odbc/node_modules/odbc
  > node-gyp configure build

  make: Entering directory '/home/your_home/my_odbc/node_modules/odbc/build'
  CXX(target) Release/obj.target/odbc_bindings/src/dynodbc.o
  SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node
  COPY Release/odbc_bindings.node
  make: Leaving directory '/home/your_home/my_odbc/node_modules/odbc/build'
  npm WARN my_odbc@1.0.0 No description
  npm WARN my_odbc@1.0.0 No repository field.

  + odbc@1.4.5
  added 4 packages from 10 contributors and audited 187 packages in 6.187s
  found 0 vulnerabilities

  ~/my_odbc$ 

The above may generate a large number of warnings but they can be ignored so long as the package was added successfully.
Remove UNICODE support and rebuild node-odbc
Edit ./node_modules/odbc/binding.gyp to remove UNICODE support:
  $/my_odbc$ nano ./node_modules/odbc/binding.gyp
Make sure the defines section looks like this:
  'defines' : [
  # 'UNICODE'
  ],

Now rebuild node-odbc:
  ~/my_odbc$ npm rebuild
Again, this command may generate a number of warnings that can be ignored. Review to make sure that no errors were encountered and the new module was successfully linked.
Set up the sample program
You can use the following Javascript code to test the ODBC connection. This step requires a running InterSystems IRIS Server and a properly defined DSN.
// update this line to reference the location of irisodbc.ini on your system
  process.env.ODBCINI = process.env.ODBCINI || '/opt/isc/iris/inat/mgr/irisodbc.ini';

  var db = require("odbc")();

  let cn = 'DSN=';

  if (process.platform == "win32") {
    // Windows
    cn += 'Sampleodbc;';
  } else if (process.platform == "darwin") {
    // Mac OS
    cn += 'Userunixodbc;';
  } else if (process.platform == "linux") {
    cn += 'Userunixodbc;';
  }
  console.log(cn);

  db.open(cn, function (err) {
    if (err) {
      return console.log(err);
    }
    console.log('I am connected')    ;

    db.query('select * from sample.person where id<3',  function cb(err, data) {
      if (err) {
        console.error(err);
      } else {
        console.log(data);
      }
    });
    db.close(function () { });
  });

This code assumes that the Sample.Person class is defined and compiled in the namespace specified by the DSN and that it has data with ID values less than 3.
Run the sample
  ~/my_odbc$ node index.js
  ~/my_odbc$ node sample.js

  DSN=Userunixodbc;
  I am connected
  [ { ID: 1,
    Age: 3,
    DOB: '2015-09-28',
    FavoriteColors: '',
    Name: 'Ulman,George L.',
    SSN: '293-31-5406',
    Spouse: 0,
    Home_City: 'Newton',
    Home_State: 'MI',
    Home_Street: '6958 Main Avenue',
    Home_Zip: '20649',
    Office_City: 'Xavier',
    Office_State: 'NY',
    Office_Street: '7313 Madison Avenue',
    Office_Zip: '73226' },
  { ID: 2,
    Age: 16,
    DOB: '2002-04-07',
    FavoriteColors: 'Green',
    Name: 'Pascal,Vincent A.',
    SSN: '973-94-3185',
    Spouse: 0,
    Home_City: 'Xavier',
    Home_State: 'ND',
    Home_Street: '3788 Madison Drive',
    Home_Zip: '80569',
    Office_City: 'Washington',
    Office_State: 'SC',
    Office_Street: '1206 Second Place',
    Office_Zip: '37389' } ]

  ~/my_odbc$ 

An Overview of ODBC

An ODBC system has the following components:
  • The client application — An application makes calls according to the Microsoft ODBC API. ODBC calls establish a connection from the client to a data source (see the section on “ODBC Connection Details”).
  • The ODBC driver manager — The driver manager accepts calls from applications using the ODBC API and hands them off to a registered ODBC client driver. The driver manager also performs any necessary tasks so that the client application can communicate with the client driver and, ultimately, the database server.
  • The ODBC client driver — A database-specific application that accepts calls from a client application through the ODBC driver manager and provides communication to the database server. It also performs any ODBC-related data conversions that the application requests.
  • The database server — The actual database ultimately receiving the calls from the client application. It can be on the same or a different machine than the client driver from which it is receiving calls.
  • An initialization file — A set of configuration information for the driver manager; depending on the operating system, it may also contain client driver information. On UNIX®, this is an actual file, frequently called odbc.ini. On Windows, it is a registry entry.
Note:
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:
  • Information about the ODBC client driver to use.
  • Information on locating and accessing the database. For example, this may include the server on which the database resides and the port to use when connecting to it. The details needed depend upon the database technology.
  • Login credentials to access the database, if the database is protected by a password.
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