Use nodejs to access ActiveX objects, taking the Access database as an example. _javascript skills

Cause
Someone asked "How to use nodejs to access sql server?"
After searching for information, I found that there are two types of solutions, using third-party nodejs plug-ins: https://github.com/orenmazor/node-tds, Use the ADODB.ConnectionActiveX object.
Reference:
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
If you use ActiveX, nodejs will be omnipotent under Windows, similar to writing asp. How do they communicate? Have to give it a try
Through
Thoughts
Use nodejs to access ActiveX indirectly through cscript.exe (windows script process)
cscript can parse both jscript and vbscript scripts, so jscript development is undoubtedly chosen for the convenience of maintenance.
Reference: http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/cscript_overview.mspx?mfr=true
Problems to be solved
1. Cross-process communication
The new version of nodejs adds operations on child processes, so cross-process communication is not a problem.
http://nodejs.org/docs/latest/api/all.html#child_Processes

Copy code The code is as follows :

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);
}
});

For example, we can get the output content of the console stdout!


2. ActiveX related to database access, ADODB.Connection
Reference: http://msdn.microsoft.com/en-us/library/windows/desktop/aa746471(v=vs. 85).aspx

Copy code The code is as follows:

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), the connection string parameter can be set to access sql server.
Reference: http://www.connectionstrings.com/sql-server-2005
3. To facilitate maintenance, the cscript and nodejs scripts are merged, and typeof exports is used to determine the current operating environment.
4. Character encoding cscript code uses ascii encoding
Non-ascii characters are encoded with "uHHHH" Unicode.
5. Command line characters need to be escaped. Double quotes and percent signs have special meanings in the command line.
Parameter transfer uses base64 encoding to avoid conflicts
cscript environment MSXML2.DOMDocument can do base64 encoding and decoding

Copy code The code is as follows:

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;
}

Summary
Calling process
1. Create a child process, Pass the encoded parameters;
2. After the child process is processed, the data will be output to the console in JSON format; (the child process automatically ends)
3. Read the data from the console and execute the callback function.


Advantages
1. Enable nodejs to have the ability to access ActiveX objects;
2. Simple implementation, easy development and maintenance.


Disadvantages
1. Can only run on Windows platform;
2. Data encoding and decoding will consume more CPU;
3. Each call needs to create a sub-process and restart it connect. (Can be improved)
Summary
1. It has certain practicality;
2. The cross-process communication performance can continue to be explored.
Module code:

Copy code The code is as follows:

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 () {
/**
* Character table that needs to be escaped during string processing
* @private
*/
var escapeMap = {
"b": '\b',
"t": '\t',
"n": '\n',
"f": '\f',
"r": '\r',
'"' : '\"',
"\": '\\'
};
/**
* String serialization
* @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 '"';
}
/**
* Array serialization
* @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("");
}
/**
* Handle zero padding when serializing dates
* @private
*/
function pad(source) {
return source < 10 ? '0' source : source;
}
/**
* Date serialization
* @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('');
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('');
}();
} else { // nodejs
void function(){
function json4stdout(stdout){
if (!stdout) return;
var result = null;
String(stdout).replace(/([sS] )/, 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/trunk/database