ODBC Support for Python and Node.js
The open source modules described here provide language-specific ODBC interfaces for Python and Node.js:
-
Support for pyodbc Python ODBC Bridge — pyodbc implements the Python DB API 2.0 specification.
-
Support for Node.js Relational Access — node-odbc enables ODBC database access for Node.js client applications.
Support for pyodbc Python ODBC Bridge
pyodbc is an open source Python module that implements the DB API 2.0 specification (PEP 249 -- Python Database API Specification v2.0Opens in a new tab), 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. This module can also be used with earlier versions of InterSystems IRIS. For general information, see the pyodbc GitHub siteOpens in a new tab.
An InterSystems-specific implementation of DB API 2.0 was introduced with InterSystems IRIS 2022.1 (see “Using the Python DB-API” in Using the Native SDK for Python), and is recommended for all new Python development. The pyodbc implementation is still supported, and may be required for client applications that connect to older versions of InterSystems IRIS.
Installation
There are several sites with installation information, both for Windows and for Linux and related operating systems:
-
pyodbc GitHub site: pyodbc Python ODBC bridgeOpens in a new tab
-
pyodbc Wiki: Wiki HomeOpens in a new tab
-
Microsoft pyodbc installation: Python SQL Driver - pyodbcOpens in a new tab
-
General Python documentation: PythonOpens in a new tab
The installation process is simple:
-
Install Python 2 or 3 (which supports Unicode) via the Python downloadOpens in a new tab:
-
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 XOpens in a new tab):
-
install homebrewOpens in a new tab
-
install unixODBCOpens in a new tab
-
run pip install:
pip install --upgrade --global-option=build_ext --global-option="-I/usr/local/include" --global-option="-L/usr/local/lib"
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 = '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.perf_counter()
#Sample select query
cursor.execute("SELECT * from Sample.Person")
row = cursor.fetchone()
#while row:
# print(row)
# row = cursor.fetchone()
end= time.perf_counter()
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='latin1')
cnxn.setencoding(str, encoding='latin1')
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 the Unicode section of the pyodbc WikiOpens in a new tab.
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-odbcOpens in a new tab), 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-odbcOpens in a new tab) 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-gypOpens in a new tab) 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/Opens in a new tab).
Installation and Setup
-
Make sure all dependencies are installed:
-
Node.js and npm (https://nodejs.org/en/download/Opens in a new tab) — 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-gypOpens in a new tab) — 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-odbcOpens in a new tab) — 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.
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$
~/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$
/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.
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.
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.
~/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$