用nodejs访问ActiveX对象,以操作Access数据库为例。

起因
有人提问“如果用nodejs访问sql server?”
找了找资料,发现有两类解决方法,使用第三方nodejs插件:https://github.com/orenmazor/node-tds、使用ADODB.ConnectionActiveX对象
参考:
如果用ActiveX那么在Windows下nodejs将会无所不能,类似写asp。那它们怎么通信?得动手试试
经过
思路
nodejs通过cscript.exe(windows脚本进程)间接访问ActiveX
cscript能解析jscriptvbscript两种脚本,无疑为方便维护选jscript开发。
需解决的问题
1、跨进程通信
新版的nodejs里增加了对子进程的操作,跨进程通信不是问题。
var util = require('util'),
    exec = require('child_process').exec,
    child;

child = exec('cat *.js bad_file | wc -l',
  function (error, stdout, stderr) {
    console.log('stdout: ' + stdout);
    console.log('stderr: ' + stderr);
    if (error !== null) {
      console.log('exec error: ' + error);
    }
});
如例我们可以拿到控制台的输出内容stdout 

2、数据库访问相关ActiveX,ADODB.Connection
var connection = new ActiveXObject("ADODB.Connection");
var result = 'ok';
try{
    connection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + params.accessfile);
    connection.Execute(params.sql);
} catch(ex){
    result = ex.message;
}
return {
    result: result
};
connection.Open(connectionString),链接字符串参数可以设置访问sql server。
3、为方便维护,特别将cscript和nodejs的脚本合并,用typeof exports判断当前运行环境。
4、字符编码cscript代码使用ascii编码
非ascii码字符进行“\uHHHH”Unicode编码。
5、命令行字符需转义,双引号、百分号在命令行有特殊意义。
参数传递使用base64编码,避免冲突
cscript环境MSXML2.DOMDocument可以做base64编解码
function base64Decode(base64){
    var xmldom = new ActiveXObject("MSXML2.DOMDocument");
    var adostream = new ActiveXObject("ADODB.Stream");
    var temp = xmldom.createElement("temp");
    temp.dataType = "bin.base64";
    temp.text = base64;

    adostream.Charset = "utf-8";
    adostream.Type = 1; // 1=adTypeBinary 2=adTypeText
    adostream.Open();
    adostream.Write(temp.nodeTypedValue);
    adostream.Position = 0;
    adostream.Type = 2; // 1=adTypeBinary 2=adTypeText
    var result = adostream.ReadText(-1); // -1=adReadAll
    adostream.Close();
    adostream = null;
    xmldom = null;
    return result;
}

总结
调用流程
1、创建子进程,传递经过编码的参数;
2、子进程处理完毕将数据JSON格式化输出到控制台;(子进程自动结束)
3、读取控制台的数据,执行回调函数。

优势
1、使nodejs拥有访问ActiveX对象的能力;
2、实现简单,开发维护方便。

劣势
1、只能运行在Windows平台;
2、数据编解码会消耗更多cpu;
3、每次调用需要创建一个子进程重新连接。(可改进)
总结
1、具有一定实用性;
2、跨进程通信性能可继续探索。


模块代码:

var Access = {
    create: function(params){
        var fso = new ActiveXObject("Scripting.FileSystemObject");
        var result = 'ok';
        if (!fso.FileExists(params.accessfile)){
            var adoxcatalog = new ActiveXObject("ADOX.Catalog");
            try {
                adoxcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + params.accessfile);
            } catch(ex) {
                result = ex.message;
                return;
            }
            adoxcatalog = null;
        } else {
            result = 'exists';
        }
        return {
            result: result
        };
    },
    existsTable: function(params){
        var connection = new ActiveXObject("ADODB.Connection");
        var result = 'ok', exists = false;
        try{
            connection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + params.accessfile);
            var recordset = connection.OpenSchema(20/*adSchemaTables*/);
            recordset.MoveFirst();
            while (!recordset.EOF){
                if (recordset("TABLE_TYPE") == "TABLE" && recordset("TABLE_NAME") == params.tablename){
                    exists = true;
                    break;
                }
                recordset.MoveNext();
            }
            recordset.Close();
            recordset = null;
        } catch(ex){
            result = ex.message;
        }
        return {
            "result": result,
            "exists": exists
        };
    },
    execute: function(params){
        var connection = new ActiveXObject("ADODB.Connection");
        var result = 'ok';
        try{
            connection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + params.accessfile);
            connection.Execute(params.sql);
        } catch(ex){
            result = ex.message;
        }
        return {
            result: result
        };
    },
    query: function(params){
        var connection = new ActiveXObject("ADODB.Connection");
        var result = 'ok', records = [];
        try{
            connection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + params.accessfile);
            var recordset = new ActiveXObject("ADODB.Recordset");
            recordset.Open(params.sql, connection);
            var fields = [];
            var enumer = new Enumerator(recordset.Fields);
            for (; !enumer.atEnd(); enumer.moveNext()){
                fields.push(enumer.item().name);
            }

            recordset.MoveFirst();
            while (!recordset.EOF) {
                var item = {};
                for (var i = 0; i < fields.length; i++){
                    var fieldname = fields[i];
                    item[fieldname] = recordset(fieldname).value;
                }
                records.push(item);
                recordset.MoveNext();
            }
            recordset.Close();
            recordset = null;
        } catch(ex){
            result = ex.message;
        }
        return {
            result: result,
            records: records
        };
    }
};

if (/^u/.test(typeof exports)){ // cscript
    void function(){
        //from http://tangram.baidu.com/api.html#baidu.json
        var JSON = {
            stringify: (function () {
                /**
                 * 字符串处理时需要转义的字符表
                 * @private
                 
*/
                var escapeMap = {
                    "\b": '\\b',
                    "\t": '\\t',
                    "\n": '\\n',
                    "\f": '\\f',
                    "\r": '\\r',
                    '"' : '\\"',
                    "\\": '\\\\'
                };
                
                /**
                 * 字符串序列化
                 * @private
                 
*/
                function encodeString(source) {
                    if (/["\\\x00-\x1f]/.test(source)) {
                        source = source.replace(
                            /["\\\x00-\x1f]/g, 
                            function (match) {
                                var c = escapeMap[match];
                                if (c) {
                                    return c;
                                }
                                c = match.charCodeAt();
                                return "\\u00" 
                                        + Math.floor(c / 16).toString(16) 
                                        + (c % 16).toString(16);
                            });
                    }
                    return '"' + source + '"';
                }
                
                /**
                 * 数组序列化
                 * @private
                 
*/
                function encodeArray(source) {
                    var result = ["["], 
                        l = source.length,
                        preComma, i, item;
                        
                    for (i = 0; i < l; i++) {
                        item = source[i];
                        
                        switch (typeof item) {
                        case "undefined":
                        case "function":
                        case "unknown":
                            break;
                        default:
                            if(preComma) {
                                result.push(',');
                            }
                            result.push(JSON.stringify(item));
                            preComma = 1;
                        }
                    }
                    result.push("]");
                    return result.join("");
                }
                
                /**
                 * 处理日期序列化时的补零
                 * @private
                 
*/
                function pad(source) {
                    return source < 10 ? '0' + source : source;
                }
                
                /**
                 * 日期序列化
                 * @private
                 
*/
                function encodeDate(source){
                    return '"' + source.getFullYear() + "-" 
                        + pad(source.getMonth() + 1) + "-" 
                        + pad(source.getDate()) + "T" 
                        + pad(source.getHours()) + ":" 
                        + pad(source.getMinutes()) + ":" 
                        + pad(source.getSeconds()) + '"';
                }
                
                return function (value) {
                    switch (typeof value) {
                    case 'undefined':
                        return 'undefined';
                        
                    case 'number':
                        return isFinite(value) ? String(value) : "null";
                        
                    case 'string':
                        return encodeString(value).replace(/[^\x00-\xff]/g, function(all) {
                            return "\\u" + (0x10000 + all.charCodeAt(0)).toString(16).substring(1);
                        });
                    case 'boolean':
                        return String(value);
                        
                    default:
                        if (value === null) {
                            return 'null';
                        }
                        if (value instanceof Array) {
                            return encodeArray(value);
                        }
                        if (value instanceof Date) {
                            return encodeDate(value);
                        }
                        var result = ['{'],
                            encode = JSON.stringify,
                            preComma,
                            item;
                            
                        for (var key in value) {
                            if (Object.prototype.hasOwnProperty.call(value, key)) {
                                item = value[key];
                                switch (typeof item) {
                                case 'undefined':
                                case 'unknown':
                                case 'function':
                                    break;
                                default:
                                    if (preComma) {
                                        result.push(',');
                                    }
                                    preComma = 1;
                                    result.push(encode(key) + ':' + encode(item));
                                }
                            }
                        }
                        result.push('}');
                        return result.join('');
                    }
                };
            })(),
            parse: function (data) {
                return (new Function("return (" + data + ")"))();
            }
        }

        //http://blog.csdn.net/cuixiping/article/details/409468
        function base64Decode(base64){
            var xmldom = new ActiveXObject("MSXML2.DOMDocument");
            var adostream = new ActiveXObject("ADODB.Stream");
            var temp = xmldom.createElement("temp");
            temp.dataType = "bin.base64";
            temp.text = base64;

            adostream.Charset = "utf-8";
            adostream.Type = 1; // 1=adTypeBinary 2=adTypeText
            adostream.Open();
            adostream.Write(temp.nodeTypedValue);
            adostream.Position = 0;
            adostream.Type = 2; // 1=adTypeBinary 2=adTypeText
            var result = adostream.ReadText(-1); // -1=adReadAll
            adostream.Close();
            adostream = null;
            xmldom = null;
            return result;
        }
        WScript.StdOut.Write('<json>');
        var method = Access[WScript.Arguments(0)];
        var result = null;
        if (method){
            result = method(JSON.parse(base64Decode(WScript.Arguments(1))));
        }
        WScript.StdOut.Write(JSON.stringify(result));
        WScript.StdOut.Write('</json>');
    }();
else { // nodejs
    void function(){
        function json4stdout(stdout){
            if (!stdout) return;
            var result = null;
            
            String(stdout).replace(/<json>([\s\S]+)<\/json>/, function(){
                result = JSON.parse(arguments[1]);
            });
            return result;
        }
        var util = require('util'), exec = require('child_process').exec;
        for (var name in Access){
            exports[name] = (function(funcname){
                return function(params, callback){
                    console.log([funcname, params]);
                    exec(
                        util.format(
                            'cscript.exe /e:jscript "%s" %s "%s"', __filename,
                            funcname,
                            (new Buffer(JSON.stringify(params))).toString('base64')
                        ),
                        function (error, stdout, stderr) {
                            if (error != null) {
                                console.log('exec error: ' + error);
                                return;
                            }
                            console.log('stdout: ' + stdout);
                            callback && callback(json4stdout(stdout));
                        }
                    );
                }
            })(name);
        }
    }();
}

 

调用代码:
var access = require('./access.js');
var util = require('util');

var accessfile = 'demo.mdb';
access.create({ accessfile: accessfile }, function(data){
    console.log(data);
});
access.existsTable({ accessfile: accessfile, tablename: 'demo' }, function(data){
    if (data.result == 'ok' && !data.exists){
        access.execute({
            accessfile: 'demo.mdb',
            sql: "CREATE TABLE demo(id Counter Primary key, data Text(100))"
        });
    }
});
access.execute({
    accessfile: 'demo.mdb',
    sql: util.format("INSERT INTO demo(data) VALUES('zswang 路过!%s')", +new Date)
}, function(data){
    console.log(data);
});
access.query({
    accessfile: 'demo.mdb',
    sql: "SELECT * FROM demo"
}, function(data){
    console.log(data);
});
最新代码:http://code.google.com/p/nodejs-demo/source/browse/#svn%2Ftrunk%2Fdatabase
url:http://greatverve.cnblogs.com/archive/2011/12/14/nodejs-access.html
posted @ 2011-12-14 11:13  大气象  阅读(5348)  评论(0编辑  收藏  举报
http://www.tianqiweiqi.com