connecting to sql server from node dot js

Fri, Apr 18, 2014

I’ve been hearing some good things about Node.js recently so I thought I’d give it a go. Not on something easy like a quickie server but on something much more gnarly. Getting it to talk to MSSQL via ODBC.

First things first, you need to install the ODBC/FreeTDS infrastructure and I’ve already blogged about that here.

Once that lot is set up, I’ll be using a DSN to define the connection so some things need to match. The Servername in odbc.ini:


[SQL Server]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = Yes
TraceFile       = /tmp/sqlserver
Servername      = SQL Server
Database        = TestDatabase
Port            = 1433

must match the [] tag in freetds.conf:


[SQL Server]
host = ip.address.of.server
port = 1433
tds version = 8.0
client charset = UTF-8

To test it works give it a blast with tsql, as the Javascript app will be using the same connection details:

tsql -S SQL Server -U username -P password

Once it’s working, it’s time to install ODBC for Node.js. This is a little tricky if you haven’t installed unixODBC in the standard location, so I’ll cover doing it that way. First get a copy of ODBC for Node.js:

git clone https://github.com/wankdanker/node-odbc

then we need to faff around with the config:

vi node-odbc/binding.gyp

make binding.gyp look like this (changing your paths and/or putting them in the OS == “mac” section if you’re doing this on a Mac):


[ 'OS == "linux"', {
  'libraries' : [ 
    '-L/home/vagrant/apps/unixODBC-2.3.2/lib',
    '-lodbc' 
  ],
  'cflags' : [
    '-g',
    '-I/home/vagrant/apps/unixODBC-2.3.2/include'
  ]

then install it manually. This will make sure it finds the non standard install of unixODBC:

npm install ./node-odbc

Now for the good stuff. The Javascript code for connecting to SQL Server:


var db = require("odbc")()
    , cn = "DRIVER={FreeTDS};DSN=SQL Server;UID=USERNAME;PWD=PASSWORD;DATABASE=TestDatabase"
    ;

db.open(cn, function (err) {
  if (err) return console.log(err);

    db.query('select top 10 * from Something', function (err, rows, moreResultSets) {
        var noOfRows = rows.length;
        for (var count=0; count < noOfRows; count++) {
            console.log(rows[count]['FIELD']);
        }
        
        db.close(function (err) {
      });
    });
});

One last thing to do is make sure it knows where the non standard unixODBC libraries are, otherwise you’ll see this if you just run it:

Error: libodbc.so.2: cannot open shared object file: No such file or directory

So run it from a script:


export LD_LIBRARY_PATH=/home/vagrant/apps/unixODBC-2.3.2/lib
node odbc.js

Viola, Javascript talking to SQL Server.

References:

comments powered by Disqus