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

death114

起因有人提问“如果用nodejs访问sql server？”
找了找资料，发现有两类解决方法，使用第三方nodejs插件：https://github.com/orenmazor/node-tds、使用ADODB.ConnectionActiveX对象。
参考：
http://stackoverflow.com/questions/857670/how-to-connect-to-sql-server-database-from-javascript
http://stackoverflow.com/questions/4728385/connecting-to-a-remote-microsoft-sql-server-from-node-js


如果用ActiveX那么在Windows下nodejs将会无所不能，类似写asp。那它们怎么通信？得动手试试


经过
思路
用nodejs通过cscript.exe(windows脚本进程)间接访问ActiveX
cscript能解析jscript和vbscript两种脚本，无疑为方便维护选jscript开发。
参考：http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/cscript_overview.mspx?mfr=true


需解决的问题
1、跨进程通信
新版的nodejs里增加了对子进程的操作，跨进程通信不是问题。
http://nodejs.org/docs/latest/api/all.html#child_Processes
varutil=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('execerror: '+error);
}
});
如例我们可以拿到控制台的输出内容stdout！

2、数据库访问相关ActiveX，ADODB.Connection
参考：http://msdn.microsoft.com/en-us/library/windows/desktop/aa746471%28v=vs.85%29.aspx
varconnection=newActiveXObject("ADODB.Connection");
varresult='ok';
try{
connection.Open("Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+params.accessfile);
connection.Execute(params.sql);
}catch(ex){
result=ex.message;
}
return{
result:result
};

connection.Open(connectionString)，链接字符串参数可以设置访问sql server。
参考：http://www.connectionstrings.com/sql-server-2005


3、为方便维护，特别将cscript和nodejs的脚本合并，用typeof exports判断当前运行环境。


4、字符编码cscript代码使用ascii编码
非ascii码字符进行“\uHHHH”Unicode编码。


5、命令行字符需转义，双引号、百分号在命令行有特殊意义。
参数传递使用base64编码，避免冲突
cscript环境MSXML2.DOMDocument可以做base64编解码
functionbase64Decode(base64){
varxmldom=newActiveXObject("MSXML2.DOMDocument");
varadostream=newActiveXObject("ADODB.Stream");
vartemp=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
varresult=adostream.ReadText(-1);//-1=adReadAll
adostream.Close();
adostream=null;
xmldom=null;
returnresult;
}

总结
调用流程
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;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)){ // cscriptvoid function(){//from http://tangram.baidu.com/api.html#baidu.jsonvar 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;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/409468function 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=adTypeTextadostream.Open();adostream.Write(temp.nodeTypedValue);adostream.Position = 0;adostream.Type = 2; // 1=adTypeBinary 2=adTypeTextvar result = adostream.ReadText(-1); // -1=adReadAlladostream.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 { // nodejsvoid 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