Cause
Someone asked, "What if you use Nodejs to access SQL Server?" ”
Looking for information, found that there are two types of solutions, using Third-party Nodejs plug-ins: Https://github.com/orenmazor/node-tds, using Adodb.connectionactivex objects.
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 then under Windows Nodejs will be omnipotent, similar to write ASP. So how do they communicate? We have to try.
After
Ideas
Indirect access to ActiveX via Cscript.exe (Windows scripting process) with Nodejs
Cscript can parse two scripts for JScript and VBScript, and is no doubt for easy maintenance of selected JScript development.
Reference: Http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/cscript_overview.mspx?mfr=true
Issues to be addressed
1. Cross-process communication
The new version of Nodejs adds to the operation of the subprocess, and communication across processes is not a problem.
Http://nodejs.org/docs/latest/api/all.html#child_Processes
Copy Code code 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 ');
}
});
As an example, we can get the output of the console stdout!
2, database access related Activex,adodb. Connection
Reference: Http://msdn.microsoft.com/en-us/library/windows/desktop/aa746471%28v=vs.85%29.aspx
Copy Code code 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 link string parameter can be set to access SQL Server.
Reference: http://www.connectionstrings.com/sql-server-2005
3, in order to facilitate maintenance, especially the Cscript and Nodejs scripts merged, with typeof exports to determine the current operating environment.
4, character encoding cscript code using ASCII encoding
Non-ASCII characters are "\uhhhh" Unicode encodings.
5, command line characters to escape, double quotes, percent semicolon in the command line has special meaning.
Parameter passing uses base64 encoding to avoid conflicts
cscript environment msxml2.domdocument can do base64 codec
Copy Code code 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;
}
Summarize
Call Process
1, the creation of child processes, passing encoded parameters;
2, the child process finished processing data JSON format output to the console; (child process automatically ends)
3, read the console data, execute the callback function.
Advantage
1, so that Nodejs have access to ActiveX objects ability;
2, simple to achieve, easy to develop and maintain.
Disadvantage
1, can only run on the Windows platform;
2, data codec will consume more CPU;
3. Each call requires the creation of a child process reconnect. (Can be improved)
Summarize
1, has certain practicality;
2, cross-process communication performance can continue to explore.
Module Code:
Copy Code code 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 chart to be escaped when 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). toString (16);
});
}
Return ' "' + source + '";
}
/**
* Serialization of arrays
* @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 ("");
}
/**
* 0 for processing date serialization
* @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. 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 (The 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);
}
}();
}
Calling code:
Copy Code code as follows:
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 passing by! %s ') ", +new Date)
}, function (data) {
Console.log (data);
});
Access.query ({
Accessfile: ' Demo.mdb ',
SQL: "SELECT * from Demo"
}, function (data) {
Console.log (data);
});
Latest Code: http://code.google.com/p/nodejs-demo/source/browse/
