HowTo make Node.js ODBC connections using OpenLink ODBC and Virtuoso ODBC Drivers

Data Access Drivers & Connectors Tips, Tricks, and HowTos
, , ,
1

What

You are a Data Scientist, working primarily with Node.js, with a need for data access that’s independent of specific Database Management Systems (DBMS).

Why

You don’t want to duplicate effort (e.g., re-writing code) per each target DBMS.

How

This document details how to make ODBC connections from Node.js to ODBC compliant databases using the OpenLink ODBC and Virtuoso ODBC Drivers. This can be done using the Node.js node-odbc module which is an asynchronous ODBC database interface for Node.js .

Install the latest version of Node.js (version 14+) available from the Node.js Download Server. Note nvm (Node Version Manager) can be used to manually update the version of Node.js.

Install unixODBC

Install unixODBC and unixODBC-devel: Compilation of node-odbc on your system requires these packages to provide the correct headers.

  • Ubuntu/Debian : sudo apt-get install unixodbc unixodbc-dev
  • RedHat/CentOS : sudo yum install unixODBC unixODBC-devel
  • macOS:

Install the node-odbc module with the npm command:

npm install odbc

and a list of installed modules can be obtained with the command:

npm list

Install the necessary ODBC Driver(s), configure DSNs for connection to the target databases and set the ODBCINI environment variable to point to the location of the odbc.ini file being used such that it can be located by unixODBC.

For the OpenLink ODBC and Virtuoso ODBC Drivers the WideAsUTF16 = Y ( which is the Use UTF-16 for ODBC unicode calls option in Setup Dialog) driver connection attribute must be set to work with unixODBC.

Once in place the following sample node-odbc program can be used to make an ODBC connection from Node.js to a target database with suitably configured DSN:

const odbc = require('odbc');
 
async function queryData() {
const connection = await odbc.connect('DSN={DSN-name};UID={user-name};PWD={password}');
const data = await connection.query('SELECT * FROM table');
console.log(data);
}
 
queryData();

and can be executed with the command:

node file-name.js

Sample output for OpenLink ODBC and Virtuoso ODBC connections on macOS

Installation and setup

De-iMac:node hwilliams$ open ~/Downloads node-v14.15.4.pkg
De-iMac:~ hwilliams$ mkdir nodejs
De-iMac:~ hwilliams$ cd nodejs
De-iMac:nodejs hwilliams$ npm install odbc

> odbc@2.3.5 install /Users/hwilliams/node_modules/odbc
> node-pre-gyp install --fallback-to-build

node-pre-gyp WARN Using needle for node-pre-gyp https download 
node-pre-gyp WARN Tried to download(404): https://github.com/markdirish/node-odbc/releases/download/v2.3.5/odbc-v2.3.5-darwin-x64-napi-v3.tar.gz 
node-pre-gyp WARN Pre-built binaries not found for odbc@2.3.5 and node@14.15.4 (node-v83 ABI, unknown) (falling back to source compile with node-gyp) 
  CXX(target) Release/obj.target/odbc/src/odbc.o
  CXX(target) Release/obj.target/odbc/src/odbc_connection.o
  CXX(target) Release/obj.target/odbc/src/odbc_statement.o
../src/odbc_statement.cpp:83:20: warning: private field 'odbcStatement' is not
      used [-Wunused-private-field]
    ODBCStatement *odbcStatement;
                   ^
../src/odbc_statement.cpp:84:21: warning: private field 'odbcConnection' is not
      used [-Wunused-private-field]
    ODBCConnection *odbcConnection;
                    ^
../src/odbc_statement.cpp:221:20: warning: private field 'odbcStatement' is not
      used [-Wunused-private-field]
    ODBCStatement *odbcStatement;
                   ^
../src/odbc_statement.cpp:222:21: warning: private field 'odbcConnection' is not
      used [-Wunused-private-field]
    ODBCConnection *odbcConnection;
                    ^
4 warnings generated.
  CXX(target) Release/obj.target/odbc/src/dynodbc.o
  SOLINK_MODULE(target) Release/odbc.node
  COPY /Users/hwilliams/node_modules/odbc/lib/bindings/napi-v3/odbc.node
  TOUCH Release/obj.target/action_after_build.stamp
npm WARN saveError ENOENT: no such file or directory, open '/Users/hwilliams/package.json'
npm notice created a lockfile as package-lock.json. You should commit this file.
npm WARN enoent ENOENT: no such file or directory, open '/Users/hwilliams/package.json'
npm WARN hwilliams No description
npm WARN hwilliams No repository field.
npm WARN hwilliams No README data
npm WARN hwilliams No license field.

+ odbc@2.3.5
added 69 packages from 74 contributors and audited 70 packages in 21.982s

1 package is looking for funding
  run `npm fund` for details

found 0 vulnerabilities

De-iMac:nodejs hwilliams$ npm list
/Users/hwilliams
├─┬ odbc@2.3.5
│ ├── async@3.2.0
│ ├── node-addon-api@1.7.2
│ └─┬ node-pre-gyp@0.14.0
│   ├── detect-libc@1.0.3
│   ├─┬ mkdirp@0.5.5
│   │ └── minimist@1.2.5
│   ├─┬ needle@2.6.0
│   │ ├─┬ debug@3.2.7
│   │ │ └── ms@2.1.3
│   │ ├─┬ iconv-lite@0.4.24
│   │ │ └── safer-buffer@2.1.2
│   │ └── sax@1.2.4
│   ├─┬ nopt@4.0.3
│   │ ├── abbrev@1.1.1
│   │ └─┬ osenv@0.1.5
│   │   ├── os-homedir@1.0.2
│   │   └── os-tmpdir@1.0.2
│   ├─┬ npm-packlist@1.4.8
│   │ ├─┬ ignore-walk@3.0.3
│   │ │ └─┬ minimatch@3.0.4
│   │ │   └─┬ brace-expansion@1.1.11
│   │ │     ├── balanced-match@1.0.0
│   │ │     └── concat-map@0.0.1
│   │ ├─┬ npm-bundled@1.1.1
│   │ │ └── npm-normalize-package-bin@1.0.1 deduped
│   │ └── npm-normalize-package-bin@1.0.1
│   ├─┬ npmlog@4.1.2
│   │ ├─┬ are-we-there-yet@1.1.5
│   │ │ ├── delegates@1.0.0
│   │ │ └─┬ readable-stream@2.3.7
│   │ │   ├── core-util-is@1.0.2
│   │ │   ├── inherits@2.0.4 deduped
│   │ │   ├── isarray@1.0.0
│   │ │   ├── process-nextick-args@2.0.1
│   │ │   ├── safe-buffer@5.1.2 deduped
│   │ │   ├─┬ string_decoder@1.1.1
│   │ │   │ └── safe-buffer@5.1.2 deduped
│   │ │   └── util-deprecate@1.0.2
│   │ ├── console-control-strings@1.1.0
│   │ ├─┬ gauge@2.7.4
│   │ │ ├── aproba@1.2.0
│   │ │ ├── console-control-strings@1.1.0 deduped
│   │ │ ├── has-unicode@2.0.1
│   │ │ ├── object-assign@4.1.1
│   │ │ ├── signal-exit@3.0.3
│   │ │ ├─┬ string-width@1.0.2
│   │ │ │ ├── code-point-at@1.1.0
│   │ │ │ ├─┬ is-fullwidth-code-point@1.0.0
│   │ │ │ │ └── number-is-nan@1.0.1
│   │ │ │ └── strip-ansi@3.0.1 deduped
│   │ │ ├─┬ strip-ansi@3.0.1
│   │ │ │ └── ansi-regex@2.1.1
│   │ │ └─┬ wide-align@1.1.3
│   │ │   └── string-width@1.0.2 deduped
│   │ └── set-blocking@2.0.0
│   ├─┬ rc@1.2.8
│   │ ├── deep-extend@0.6.0
│   │ ├── ini@1.3.8
│   │ ├── minimist@1.2.5 deduped
│   │ └── strip-json-comments@2.0.1
│   ├─┬ rimraf@2.7.1
│   │ └─┬ glob@7.1.6
│   │   ├── fs.realpath@1.0.0
│   │   ├─┬ inflight@1.0.6
│   │   │ ├── once@1.4.0 deduped
│   │   │ └── wrappy@1.0.2
│   │   ├── inherits@2.0.4
│   │   ├── minimatch@3.0.4 deduped
│   │   ├─┬ once@1.4.0
│   │   │ └── wrappy@1.0.2 deduped
│   │   └── path-is-absolute@1.0.1
│   ├── semver@5.7.1
│   └─┬ tar@4.4.13
│     ├── chownr@1.1.4
│     ├─┬ fs-minipass@1.2.7
│     │ └── minipass@2.9.0 deduped
│     ├─┬ minipass@2.9.0
│     │ ├── safe-buffer@5.1.2 deduped
│     │ └── yallist@3.1.1 deduped
│     ├─┬ minizlib@1.3.3
│     │ └── minipass@2.9.0 deduped
│     ├── mkdirp@0.5.5 deduped
│     ├── safe-buffer@5.1.2
│     └── yallist@3.1.1
└── zeppelin-solidity@1.3.0

De-iMac:nodejs hwilliams$ export ODBCINI=/Library/ODBC/odbc.ini

OpenLink ODBC Driver connection

ODBC DSN
[sqllu-xs4all]
Driver                     = /Library/ODBC/OpenLink SQL Server Lite ODBC Driver v9.0.bundle/Contents/MacOS/sql_mt_lu.so
Database                   = Northwind
Cursor_Sensitivity         = Low
FetchBufferSize            = 60
JetFix                     = No
UserName                   = openlink
MaxRows                    = 0
NoAutoCommit               = No
NoLoginBox                 = No
NoRowsetSizeLimit          = No
ReadOnly                   = No
Service                    = MSSQL::(dev-gw.openlinksw.com:11433)
TDSHost                    = dev-gw.openlinksw.com
TDSPort                    = 11433
DeferLongFetch             = No
Options                    = -O 1
TDSVersion                 = 7.4
SQLServerCatalog           = Yes
TrustedConnection          = No
CharsetTranslationDisabled = No
MultipleActiveStmtsEmul    = No
IncludeViews               = No
IncludeAll                 = No
NoQuotedIdentifiers        = No
WideAsUTF16                = No
ReadonlyIntent             = No
Sample Connection
De-iMac:nodejs hwilliams$ cat node-uda.js 
const odbc = require('odbc');
 
async function queryData() {
const connection = await odbc.connect('DSN=sqllu-xs4all;UID=openlink;PWD=xxx');
const data = await connection.query('SELECT TOP 5 CustomerID, ContactName FROM Customers');
console.log(data);
}
 
queryData();
De-iMac:nodejs hwilliams$
De-iMac:nodejs hwilliams$ node node-uda.js
[
  { CustomerID: 'ALFKI', ContactName: 'Maria Anders' },
  { CustomerID: 'ANATR', ContactName: 'Ana Trujillo' },
  { CustomerID: 'ANTON', ContactName: 'Antonio Moreno' },
  { CustomerID: 'AROUT', ContactName: 'Thomas Hardy' },
  { CustomerID: 'BERGS', ContactName: 'Christina Berglund' },
  statement: 'SELECT TOP 5 CustomerID, ContactName FROM Customers',
  parameters: undefined,
  return: undefined,
  count: -1,
  columns: [
    {
      name: 'CustomerID',
      dataType: -8,
      columnSize: 5,
      decimalDigits: 0,
      nullable: false
    },
    {
      name: 'ContactName',
      dataType: -9,
      columnSize: 30,
      decimalDigits: 0,
      nullable: true
    }
  ]
]
De-iMac:nodejs hwilliams$

The OpenLink ODBC Drivers can be obtained form the Download Server.

Virtuoso ODBC Driver connection

ODBC DSN
[virtdemo]
Driver             = /Library/ODBC/OpenLink Virtuoso ODBC Driver (Unicode).bundle/Contents/MacOS/virtodbcu_r.so
Address            = demo.openlinksw.com:21118
PWDClearText       = 0 
LastUser           = demo
Database           = Demo
DayLight           = Yes
RoundRobin         = No
NoSystemTables     = No
TreatViewsAsTables = No
WideAsUTF16        = Yes
Sample Connection
De-iMac:nodejs hwilliams$ cat node-virt.js
const odbc = require('odbc');
async function queryData() {
const connection = await odbc.connect('DSN=virtdemo;UID=demo;PWD=demo');
const data = await connection.query('SELECT TOP 5 CustomerID, ContactName FROM Demo..Customers');
console.log(data);

}

queryData();
De-iMac:nodejs hwilliams$
De-iMac:nodejs hwilliams$ node node-virt.js 
[
  { CustomerID: 'ALFKI', ContactName: 'Maria Anders' },
  { CustomerID: 'ANATR', ContactName: 'Ana Trujillo' },
  { CustomerID: 'ANTON', ContactName: 'Antonio Moreno' },
  { CustomerID: 'AROUT', ContactName: 'Thomas Hardy' },
  { CustomerID: 'BERGS', ContactName: 'Christina Berglund' },
  statement: 'SELECT TOP 5 CustomerID, ContactName FROM Demo..Customers',
  parameters: undefined,
  return: undefined,
  count: -1,
  columns: [
    {
      name: 'CustomerID',
      dataType: 12,
      columnSize: 5,
      decimalDigits: 0,
      nullable: false
    },
    {
      name: 'ContactName',
      dataType: 12,
      columnSize: 30,
      decimalDigits: 0,
      nullable: true
    }
  ]
]
De-iMac:nodejs hwilliams$

The Virtuoso ODBC Driver installers can be obtained directly from the following links:

Related