Using node-odbc on Windows to talk to IBM i

worksofliam commented 5 years ago

This post will explain how to:

Install the IBM i ODBC driver on Windows
How to build node-odbc on Windows
Execute an SQL statement on a remote IBM i server from Windows

For these posts, I will be using Node.js 10.16.0 on Windows.

Building node-odbc

Feeling lazy and don't want to build it manually? Check the bottom of this post for the pre-built binaries for Windows.

Unlike Mac and Linux, the node-odbc repository does not provide the runtime binaries for Windows so they have to be built manually. Luckily the tools we need to build it can be installed with just two commands.

npm i -g windows-build-tools
npm i -g node-gyp

Windows build tools comes with Python and some Visual Studio Tools (msbuild) to compile the C++ from the node-odbc repository. Now we can install odbc and it will build the binary for us automatically to use in our Node.js script.

$ mkdir nodeodbc && cd nodeodbc
$ npm i odbc

> odbc@2.0.0-4 install C:\Users\liam.allan\nodeodbc\node_modules\odbc
> node-gyp configure build

C:\Users\liam.allan\nodeodbc\node_modules\odbc>if not defined npm_config_node_gyp (node "C:\Program Files\nodejs\node_modules\npm\node_modules\npm-lifecycle\node-gyp-bin\\..\..\node_modules\node-gyp\bin\node-gyp.js" configure build )  else (node "C:\Program Files\nodejs\node_modules\npm\node_modules\node-gyp\bin\node-gyp.js" configure build )
Building the projects in this solution one at a time. To enable parallel build, please add the "/m" switch.
  odbc.cpp
  odbc_connection.cpp
  odbc_statement.cpp
  dynodbc.cpp
  win_delay_load_hook.cc
  strptime.c
    Creating library C:\Users\liam.allan\nodeodbc\node_modules\odbc\build\Release\odbc.lib and object C:\Users\liam.al
  lan\nodeodbc\node_modules\odbc\build\Release\odbc.exp
  Generating code
  All 857 functions were compiled because no usable IPDB/IOBJ from previous compilation was found.
  Finished generating code
  odbc.vcxproj -> C:\Users\liam.allan\nodeodbc\node_modules\odbc\build\Release\\odbc.node
npm WARN saveError ENOENT: no such file or directory, open 'C:\Users\liam.allan\nodeodbc\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 'C:\Users\liam.allan\nodeodbc\package.json'
npm WARN nodeodbc No description
npm WARN nodeodbc No repository field.
npm WARN nodeodbc No README data
npm WARN nodeodbc No license field.
+ odbc@2.0.0-4
added 3 packages from 43 contributors and audited 3 packages in 6.037s
found 0 vulnerabilities

Installing the IBM i ODBC driver for Windows

Feeling lazy or don't have an IBM ID? I am hosting the IBMiAccess_v1r1_WindowsAP_English.zip on my website. You can download it here instead.

The driver currently requires you to have an IBM ID to download it, it was kind of hard to find it previously but Dawn May created this blog post and Kevin Adler also tweeted this:

Hey #IBMi people, did you know that you can download the IBM i Access ODBC driver without going through ESS? Just go to http://ibm.biz/ibmi-odbc-download, sign in with an IBMid, and agree to the license. Download for Windows, Linux, and now for IBM i/PASE!

Following that short link will give you an IBM ID sign on, which will then show you this page:

You want the third item from the bottom (ACS Windows App Pkg English (64bit)). This will give you a .zip file which you can extract. Inside the zip you can find Image64a/setup.exe which you can execute to install the driver:

Now, if you open the ODBC Data Source Administrator and head to the Drivers tab you should see the new drivers show up:

A Node.js example

In this example, for the purpose of testing, I am not using a DSN. That will come in the next blog post. Here is my example Node.js script that uses the odbc module with Promises.

const odbc = require('odbc');

// can only use await keyword in an async function
async function example() {
    const connection = await odbc.connect(`Driver=IBM i Access ODBC Driver;System=MYSYSTEM;UID=LALLAN;Password=passwordhere`);
    const result = await connection.query('SELECT * FROM QIWS.QCUSTCDT');
    console.log(result);
}

example();

And when we run this script, we get the expected output:

Pre-built `odbc` binaries:

To install odbc without building it, run npm i odbc --ignore-scripts. The contents of the zip file go inside ./mode_modules/odbc/build/.

odbc, Version 2.0.0-4, Windows outdated

worksofliam commented 5 years ago

Tomorrow I will post the blog about using a DSN, which would allow for shorter connection strings like:

const connection = await odbc.connect(`DSN=DEVSERV;Password=password`);

sonnguyenmaxava commented 2 years ago

windows-build-tools has been deprecated. It has be included in node.js. Can we safely skip the step npm i -g windows-build-tools?

sonnguyenmaxava commented 2 years ago

My old IBM Access Client Solutions does not have IBM i Access ODBC Driver. However, using the old name iSeries Access ODBC Driver or Client Access ODBC Driver (32-bit) works well.

worksofliam / blog