How to read columns of type doubles in MDB files in node?

Problem

I'm querying some MDB files in nodejs on linux using MDBTools, unixodbc and the node odbc package.

Using this code

db.query("select my_str_col, my_dbl_col from my_table", function (err, rows) {
    if (err) return console.log(err);
    console.log(rows);
    db.close();
});

I can query the my_str_col string column but I can't decipher the my_dbl_col Double column, I get something like this :

[ { my_str_col: 'bla',     my_dbl_col: '{\u0014�Gai�@' },
  { my_str_col: 'bla bla', my_dbl_col: '' },
  { my_str_col: 'bla', my_dbl_col: '�G�z\u0014NF@' } ]

All not empty strings are 7 or 8 bytes but what bothers me most is the second row of this example where I get an empty string while I know there is a not null number in the MDB : it means I can't try to build the numbers from the string bytes.

So, how can I read numbers of type Double in a MDB file in node on linux ?

I precise that

  • a tool like MDBViewer (using MDBTools) correctly reads those numbers
  • JavaScript numbers will be precise enough for me : those numbers would all fit in float32
  • I can't apply lengthy conversions on the MDB files : I must make fast queries on a few hundred frequently changed files...
  • a solution in which I can't really issue queries but which lets me read the whole table would be acceptable too
Problem courtesy of: Denys Séguret

Solution

As I couldn't get node-odbc to correctly decipher numbers, I wrote a function calling mdb-export (which is very fast) and reading the whole table.

var fs   = require("fs"),
    spawn  = require('child_process').spawn,
    byline = require('byline'); // npm install byline   

// Streaming reading of choosen columns in a table in a MDB file. 
// parameters :
//   args :
//     path : mdb file complete path
//     table : name of the table
//     columns : names of the desired columns
//   read : a callback accepting a row (an array of strings)
//   done : an optional callback called when everything is finished with an error code or 0 as argument
function queryMdbFile(args, read, done) {
    var cmd = spawn('/usr/bin/mdb-export', [args.path, args.table]);
    var rowIndex = 0, colIndexes;
    byline(cmd.stdout).on('data', function (line) {
        var cells = line.toString().split(',');
        if (!rowIndex++) { // first line, let's find the col indexes
            var lc = function(s){ return s.toLowerCase() };
            colIndexes = args.columns.map(lc).map(function(name) {
                return cells.map(lc).indexOf(name);
            });
        } else { // other lines, let's give to the callback the required cells
            read(colIndexes.map(function(index){ return ~index ? cells[index] : null }));
        }
    });
    cmd.on('exit', function (code) {
        if (done) done(code);
    });
}

Here's an example in which I build an array with all rows of the question's example :

var rows = [];
queryMdbFile({
    path: "mydatabase.MDB",
    table: 'my_table',
    columns : ['my_str_col', 'my_dbl_col']
},function(row) {
    rows.push(row);
},function(errorCode) {
    console.log(errorCode ? ('error:'+errorCode) : 'done');
});

Everything is read as strings but easy to parse :

[ ['bla',     '1324'  ],
  ['bla bla', '332e+5'],
  ['bla',     '43138' ] ]

Surprisingly enough, this is faster than querying using node-odbc and linuxodbc.

Solution courtesy of: Denys Séguret

Discussion

There is currently no discussion for this recipe.

This recipe can be found in it's original form on Stack Over Flow.