adosql.js

//
// adosql.js
//
if (typeof AdoDbUtil == 'undefined')
    var AdoDbUtil = { };

AdoDbUtil.PROVIDER_MICROSOFT_JET_OLEDB_4_0 = 'Microsoft.Jet.OLEDB.4.0';
AdoDbUtil.PROVIDER_MSDASQL_1 = 'MSDASQL.1';

// http://msdn.microsoft.com/en-us/library/ms711711(VS.85).aspx
AdoDbUtil.DRIVER_MICROSOFT_TEXT_DRIVER = '{Microsoft Text Driver (*.txt; *.csv)}';
AdoDbUtil.DRIVER_MICROSOFT_EXCEL_DRIVER = '{Microsoft Excel Driver (*.xls)}';
AdoDbUtil.DRIVER_MICROSOFT_ACCESS_DRIVER = '{Microsoft Access Driver (*.mdb)}';

AdoDbUtil.getConStr = function(path) {
    var driver;
    if (path.match(/\.xls$/)) {
        driver = AdoDbUtil.DRIVER_MICROSOFT_EXCEL_DRIVER;
    } else if (path.match(/\.mdb$/)) {
        driver = AdoDbUtil.DRIVER_MICROSOFT_ACCESS_DRIVER;
    } else if (new ActiveXObject('Scripting.FileSystemObject').FolderExists(path)) {
        driver = AdoDbUtil.DRIVER_MICROSOFT_TEXT_DRIVER;
    }

    if (driver == null) {
        throw new Error('Unknown file type');
    }

    var constr = [
        'DRIVER=' + driver,
        'DBQ=' + path,
        'READONLY=FALSE'
    ].join(';');

    return constr;
};

AdoDbUtil.connect = function(constr) {
    var con = new ActiveXObject('ADODB.Connection');
    con.Open(constr);
    return con;
};

AdoDbUtil.execute = function(con, sql) {
    var recs = [ ];
    var rs = con.Execute(sql);
    try {
        if (rs != null && rs.State != 0 /* CLOSE */) {
             while (!rs.EOF) {
                var rec = [ ];
                for (var i = 0, m = rs.Fields.Count; i < m; ++i) {
                    var f = rs.Fields(i);
                    rec.push({ name: f.Name, value: f.Value });
                }
                recs.push(rec);
                rs.MoveNext();
            }
        }
    } finally {
        if (rs != null && rs.State != 0 /* CLOSE */) {
            rs.Close();
        }
    }
    return recs;
};

AdoDbUtil.dump = function(recs) {
    if (recs == null || recs.length <= 0) {
        return;
    }
    var rec = recs[0];
    AdoDbUtil._print('#>', false);
    var buf = [ ];
    for (var k = 0, p = rec.length; k < p; ++k) {
        buf.push(rec[k].name);
    }
    AdoDbUtil._print(buf.join(','));
    for (var i = 0, m = recs.length; i < m; ++i) {
        var rec = recs[i];
        buf = [ ];
        for (var k = 0, p = rec.length; k < p; ++k) {
            buf.push(new String(rec[k].value));
        }
        AdoDbUtil._print((i + 1) + '>', false);
        AdoDbUtil._print(buf.join(','));
    }
};

AdoDbUtil.createMdb = function(path) {
    var adox = new ActiveXObject('ADOX.Catalog');
    adox.Create([
        'Provider=' + AdoDbUtil.PROVIDER_MICROSOFT_JET_OLEDB_4_0,
        'Data Source=' + path,
        'Jet OLEDB:Engine Type=5'
    ].join(';'));
};

AdoDbUtil.compactMdb = function(path) {
    var src = [
        'Provider=' + AdoDbUtil.PROVIDER_MICROSOFT_JET_OLEDB_4_0,
        'Data Source=' + path
    ].join(';');
    var dst = [
        'Provider=' + AdoDbUtil.PROVIDER_MICROSOFT_JET_OLEDB_4_0,
        'Data Source=' + path + '.new'
    ].join(';');

    var jro = new ActiveXObject('JRO.JetEngine');
    jro.CompactDatabase(src, dst);

    var fso = new ActiveXObject('Scripting.FileSystemObject');
    fso.DeleteFile(path);
    fso.MoveFile(path + '.new', path);
};

AdoDbUtil._print = function(msg, br) {
    if (br == null || br) {
        WScript.StdOut.WriteLine(msg);
    } else {
        WScript.StdOut.Write(msg);
    }
};

new function() {
    if (WScript.Arguments.Length <= 0) {
        throw new Error('Too Few Arguments');
    }
    var constr;
    for (var i = 0, m = WScript.Arguments.Length; i < m ; ++i) {
        var item = WScript.Arguments.Item(i);
        if (item == '-c') {
            constr = WScript.Arguments.Item(i + 1);
            break;
        }
    }
    if (constr == null) {
        constr = AdoDbUtil.getConStr(WScript.Arguments.Item(0));
    }
    var con = AdoDbUtil.connect(constr);
    try {
        while (true) {
            var line = read('? ');
            if (line == null || /^quit$/.test(line) || /^exit$/.test(line)) {
                break;
            }
            if (line.length <= 0) {
                continue;
            }
            try {
                if (/^!(.+)$/.test(line)) {
                    var ret = eval(RegExp.$1);
                    if (typeof ret != 'undefined') {
                        print(ret);
                    }
                } else if (/^\$(.+)$/.test(line)) {
                    var shell = new ActiveXObject('WScript.Shell');
                    var exec = shell.Exec(RegExp.$1);
                    while (exec.Status == 0) {
                        WScript.Sleep(100);
                    }
                    var out = exec.StdOut;
                    while (!out.AtEndOfStream) {
                        print(out.ReadLine());
                    }
                } else {
                    AdoDbUtil.dump(AdoDbUtil.execute(con, line));
                }
            } catch (e) {
                print(e.message);
            }
        }
    } finally {
        if (con != null) {
            con.Close();
        }
    }

    function print(msg) {
        WScript.StdOut.WriteLine(msg);
    }

    function read(msg) {
        WScript.StdOut.Write(msg);
        return WScript.StdIn.ReadLine();
    }
}();