Home
- What is this library for?
- What Platforms does it support?
- Is the library production quality?
- How to install
- Does this library support electron?
- How do I set the app name on connection string?
- How does the driver handle SQL_VARIANT types
- Errors when passing strings > 2k in length.
- Api
- Table Value Parameters TVP
- User Binding
- Async Patterns
- Connecting
- Executing A Query
- Cancel A Query
- Subscribing To Driver Events
- Capturing Print Output
- Executing Stored Procedures
- Table Binding
- Prepared Statements
- Easiest Way To See Library Being Used
- Testing The Driver
- Compiling The Driver
- Setup a development environment
This wiki describes NodeJS npm module msnodesqlv8. It has been forked from the Microsoft SQL server ODBC module msnodesql and has been reworked to be compatible with later versions of Node. New features are being introduced.
This npm module will allow a windows instance of Node JS to connect to a SQL server database and submit SQL queries via a javascript api.
The module is a mixture of C++ and javascript. Compiled binaries are supplied, therefore it should be up and running quickly for use against your database. Those familiar with the Microsoft original library will feel at home as the api is a super-set of existing functionality.
Please give the module a try and feel free to offer suggestions for improvement.
msnodesqlv8 will run on 32 bit and 64 bit versions of Node JS hosted on the Windows operating system. All major versions of Node are supported and tested i.e. Node 4, 5, 6, 7, 8 and 9. download node here. previous node releases. Node version 0 is not supported from 0.4.1.
The library is subjected to extensive testing in continuous test cycles to ensure stability and accuracy. It is being used in production environments.
ensure you have an up to date version of node installed on your computer first :-
npm install msnodesqlv8to use the code in your node javascript having installed :-
var sql = require('msnodesqlv8');Yes. Electron is a framework for creating native applications with web technologies. This library now ships with compiled binaries that are compatible with Electron.
Use the 'APP=my-application' such as in the example below. issue
Driver={SQL Server Native Client 11.0}; APP=msnodesqlv8; Server=np:\.\pipe\LOCALDB#8704E301\tsql\query; Database={scratch}; Trusted_Connection=Yes
c.query("select cast(10000 as sql_variant) as data;", (err, res) => {});the driver on detecting variant type will query underlying type information and proceed to interpret column as that underlying type. In the example above the column therefore will be presented as javascript int type.
see issue
use one of the user binding functions which will force the driver to use the specified binding type rather than guessing from the input data. In this case WLongVarChar should resolve the issue of binding large strings.
function largeText() {
var len = 2200;
var s = "A".repeat(len);
sql.open(connStr, (err, conn) => {
conn.query("declare @s NVARCHAR(MAX) = ?; select @s as s", [sql.WLongVarChar(s)],
(err, res) => {
assert.ifError(err);
var ss = res[0].s;
assert(ss.length == len);
});
});
}typescript type based information for this library. api
import {MsNodeSqlDriverApiModule as v8} from './lib/MsNodeSqlDriverApiModule'
import v8Connection = v8.v8Connection;
import v8PreparedStatement = v8.v8PreparedStatement;
import v8BindCb = v8.v8BindCb;
import v8BulkMgr = v8.v8BulkTableMgr;
import v8Error = v8.v8Error;
export const sql: v8.v8driver = require('msnodesqlv8');var sql = require('msnodesqlv8');
export const sql: v8.v8driver = require('msnodesqlv8'); // typescriptsql.open(conn_str, function (err, conn) { });sql.open( {conn_str : '', conn_timeout : 10}, function (err, conn) {});conn.close(function () { });When executing a query, the more flag may be used to indicate no further results will be returned. This optional paramater will be set true for example when queries of form 'select ....; select .....' are issued.
It is worth noting behaviour when a query such as below is executed.
let severity = 9; // or 14 for immediate terminate
RAISERROR(`'User JS Error', ${severity}, 1);SELECT 1+1;`;
here the first statement to run will be RAISEERROR. When submitting severity code less than 14, the driver has an oppertunity to read the error code and proceed to returning results on the follwing query. In this case the more flag will be set true and the error condition set. A further callback can be expected with the results and err code not set.
When error code is >= 14 the driver immediately terminates as odbc will not allow results to be obtained from further queries. In this case the more flag will be false and the err condition set to the RAISEERROR.
This behaviour is demonstrated in following test code which loops forever executing this example to prove the connection will recover from a termination of this kind.
tool\t-busy14.bat
node test\edge-case.js -t busy --delay=500 --severity=14
tool\t-busy9.bat
node test\edge-case.js -t busy --delay=500 --severity=9
var q = conn.query(sql, function (err, results, more) { });var q = conn.query(sql, [], function (err, results, more) { });sql.query(conn_str, q, [], function (err, results, more) { });conn.query({query_str: '',query_timeout: 10}, (err, results, more) => {});conn.prepare(select, function (err, ps) { });ps.preparedQuery([], function (err, res) { });ps.free(function () { });q.on('error', function(err) { }); // Error type i.e. e.message
q.on('warning', function(err) { });
q.on('meta', function(meta) { });
q.on('column', function(col) { });
q.on('info', function(e) { }); // Error type i.e. e.message
q.on('rowcount', function(count) { });
q.on('row', function(row) { });
q.on('done', function() { });
q.on('submitted', function() { });
q.on('open', function() { });
q.on('closed', function() { });var pm = conn.procedureMgr();pm.callproc(sp_name, [], function(err, results, output) { });var tm = conn.tableMgr();tm.bind(table_name, function (bm) {});bm.insertRows([], function () {});bm.selectRows([], function (err, res) {});bm.deleteRows([], function () {});bm.updateRows([], function () {});bm.setUpdateCols(updateCols);res = bm.getSummary();From version 0.4.1 the library node supports Table Value Parameters
please refer to tvp.js for examples
assuming a type is created in the database
CREATE TYPE TestTvpType AS TABLE (username nvarchar(30), age int, salary real)
use a connection object to get a Table representing this type.
function (asyncDone) {
theConnection.getUserTypeTable(tableTypeName, function (err, t) {
assert.ifError(err)
table = t
assert(table.columns.length === 3)
asyncDone()
})
}add some rows to the table.
var vec = [
{
username:'santa',
age:1000,
salary:0
},
{
username:'md',
age:28,
salary:100000
}
]
function (asyncDone) {
// see above
table.addRowsFromObjects(vec)
asyncDone()
}create a table parameter from the Table. This method can also accept an mssql Table type.
var tp = sql.TvpFromTable(table)create a table in the datbase representing the type.
create TABLE TestTvp (
username nvarchar(30),
age int,
salary real
)
add a stored procedure to insert to the table from the new type
create PROCEDURE InsertTestTvp
@tvp TestTvpType READONLY
AS
BEGIN
set nocount on
INSERT INTO TestTvp
(
[username],
[age],
[salary]
)
SELECT
[username],
[age],
[salary]
n FROM @tvp tvp
END
invoke the stored procedure to insert records.
function (asyncDone) {
// see above to create a table
var tp = sql.TvpFromTable(table)
table.rows = []
theConnection.query('exec insertTestTvp @tvp = ?;', [tp], function (err) {
assert.ifError(err)
asyncDone()
})
},
function (asyncDone) {
theConnection.query('select * from ' + tableName, function (err, res) {
assert.ifError(err)
assert.deepEqual(vec, res)
asyncDone()
})
}if an existing table exists. i.e. Employee - bind to it using tablem manager
function (asyncDone) {
var tableName = 'Employee'
var tm = theConnection.tableMgr()
tm.bind(tableName, function (bulk) {
bulkMgr = bulk
asyncDone()
})
}then can get a sql representation of the table
function (asyncDone) {
var sql = bulkMgr.asUserType()
theConnection.query(sql, function (err) {
assert.ifError(err)
asyncDone()
})
}which is represented as below
CREATE TYPE EmployeeType AS TABLE (BusinessEntityID int, NationalIDNumber nvarchar(15), LoginID nvarchar(256), OrganizationNode hierarchyid, OrganizationLevel smallint, JobTitle nvarchar(50), BirthDate date, MaritalStatus nchar, Gender nchar, HireDate date, SalariedFlag bit, VacationHours smallint, SickLeaveHours smallint, CurrentFlag bit, rowguid uniqueidentifier, ModifiedDate datetime)
can also get a Table representing the database table
function (asyncDone) {
var parsedJSON = helper.getJSON()
// construct a table type based on a table definition.
var table = bulkMgr.asTableType()
// convert a set of objects to rows representing the table
table.addRowsFromObjects(parsedJSON)
// use a type the native driver can understand, using column based bulk binding.
var tp = sql.TvpFromTable(table)
// can now use the tvp with the driver and bind all data in one go.
theConnection.query('select * from ?;', [tp], function(err, res) {
assert.deepEqual(res,parsedJSON)
asyncDone()
})
}- ensure visual studio 2015 is installed, community edition should be fine. If you do not want the full IDE it is also possible to build with the tools available from Microsoft here
- make sure you have latest version of node installed.
- install node-gyp globally :- npm install -g node-gyp
- download and install the git client
- clone the code base :- git clone git-src msnodesqlv8
- start a shell command and cd into new folder :- copy bindingdotgyp.old binding.gyp
- make sure Python27 is installed
- node-gyp clean configure build --verbose --arch=x64
- if you wish to edit and compile code for Debug, open the generated solution file in VS2015. i.e. build\binding.sln
- copy the node.lib to Debug for example C:\Users<usr>.node-gyp\6.10.0\Debug from x64 folder.
- build from visual studio.
- to run this target change to debug mode from file sqlserver.native.js
can convert the API easily into a promise based API
async function test (request, response) {
response.statusCode = 200
response.setHeader('Content-Type', 'text/plain')
let sqlOpen = toPromise(sql.open)
try {
let connection = await sqlOpen(connectionString)
let connectionQuery = toPromise(connection.queryRaw)
try {
let d = new Date()
let data = await connectionQuery(query)
let elapsed = new Date() - d
response.end(JSON.stringify(data, null, 4))
let close = toPromise(sql.close)
await close()
} catch (err) {
response.end(err.message)
}
} catch (err) {
response.end(err.message)
}
}the msnodesqlv8 library works nicely with the brilliant library asynquence written by Kyle Simpson. Here it is possible to run many different sorts of patterns but using generators to manage an iteration of promises is particularly worth noting.
here the little promise based wrapper has been used to provide promises around the driver. This code and others like it can be found in the wrapper test
now operations can be made to look "serialsed" letting AQS manage the iteration. There are many other patterns avaialble such as gates and waterfalls.
let ASQ = require('asynquence-contrib');
testPrepare: string = `select len(convert(varchar, ?)) as len`;
private prepare(): Promise<any> {
let inst = this;
return new Promise((resolve, reject) => {
ASQ().runner(function *() {
let connection = yield inst.sqlWrapper.open();
let command = connection.getCommand().sql(inst.testPrepare);
command = yield command.prepare();
let res = yield command.params([1000]).execute();
assert.deepEqual(res.asObjects, inst.expectedPrepared, "results didn't match");
yield command.freePrepared();
yield connection.close();
resolve();
}).or((e: any) => {
reject(e);
}
)
})
}By default, the driver will guess the appropriate type based on the type passed in. The api contains methods to force the driver to assign a given type if required.
conn.query("declare @v tinyint = ?; select @v as v", [sql.TinyInt(255)],
(err, res) => {});Bit(v:number): any;
BigInt(v:number): any;
Int(v:number): any;
TinyInt(v:number): any;
SmallInt(v:number): any;
Float(v:number): any;
Numeric(v:number): any;
Money(v:number): any;
SmallMoney(v:number): any;
Decimal(v:number): any;
Double(v:number): any;
Real(v:number): any;
WVarChar(v:String) : any;
Char(v:String) : any;
VarChar(v:String) : any;
NChar(v:String) : any;
NVarChar(v:String) : any;
Text(v:String) : any;
NText(v:String) : any;
Xml(v:String) : any;
WLongVarChar(v:string) : any;
UniqueIdentifier(v:String) : any;
VarBinary(v:any) : any;
LongVarBinary(v:any) : any;
Image(v:any) : any;
Time(v:Date) : any;
Date(v:Date) : any;
DateTime(v:Date) : any;
DateTime2(v:Date) : any;
DateRound(v:Date) : any;
SmallDateTime(v:Date) : any;
DateTimeOffset(v:Date) : any;Ensure the module has been installed first.
A standard connection string can be used to open a connection to the database. Note that in the example below a trusted connection is used and hence authentication is based on the windows login ID used for executing the instance of Node JS. You can also use a connection string where user and password is specified using a SQL Server account.
use the sql.open function :-
var sql = require('msnodesqlv8');
function connect() {
var connStr = "Driver={SQL Server Native Client 11.0};Server=<host>;Database={scratch};Trusted_Connection=Yes;";
sql.open(connStr, function (err, conn) {
assert.ifError(err);
// connection is now ready to use.
});
}A Timeout in seconds can be specified to hand into the driver :-
var sql = require('msnodesqlv8');
function connect_timeout() {
var co = {
conn_str: connStr,
conn_timeout: 2 // specified in seconds.
};
sql.open(co, function (err, conn) {
if (err) {
console.log(err);
return;
}
});
}use the connection procedure manager to execute stored procedures.
var pm = conn.procedureMgr();assuming a stored procedure exists in the database as below, where two input int are passed in, added together by the procedure and returned as an output parameter.
USE [scratch]
CREATE PROCEDURE [dbo].[test_sp_get_int_int](
@num1 INT,
@num2 INT,
@num3 INT OUTPUT
)AS
BEGIN
SET @num3 = @num1 + @num2
RETURN 99;
END
GO
with a connection opened, the stored procedure manager can be used to execute a proc :-
function exec_proc(conn) {
var pm = conn.procedureMgr();
pm.callproc('test_sp_get_int_int', [10, 5], function(err, results, output) {
var expected = [99, 15];
assert.deepEqual(output, expected, "results didn't match");
});
}The return value will always be the first member of the output vector. In this case that value has been assigned 99. Each output parameter in order will then appear, in this case we have the sum of the input parameters On first invocation, the procedure manager will ask the server to return the meta data associated with the procedure. This information is cached and used on subsequent calls. The information is held alongside that connection in the library and hence will be lost each time the connection is closed.
If you wish to see what information is stored for a procedure without actually executing it, the describe function may help. It will callback with the associated meta data showing parameters and sql signature used to invoke the procedure.
function describe() {
sql.open(connStr, function (err, conn) {
assert.ifError(err);
var pm = conn.procedureMgr();
pm.describe('test_sp_get_int_int', function (meta) {
console.log(meta);
});
});
}As an alternative, a procedure object can be fetched by an application where a call method can be called. A helper property 'select' is available on the meta data which alows a query to be submitted to invoke a stored procedure if that is preferred. Else use the method call on the returned proc.
function (asyncDone) {
var pm = theConnection.procedureMgr()
pm.get(spName, function (proc) {
var meta = proc.getMeta()
// use an mssql style select
var s = meta.select
theConnection.query(s, [10, 5], function (err, results) {
assert.ifError(err)
var expected = [{
num3: 15,
___return___: 99
}]
assert.deepEqual(results, expected, 'results didn\'t match')
asyncDone()
})
})
} function (asyncDone) {
var pm = theConnection.procedureMgr()
pm.get(spName, function (proc) {
var count = pm.getCount()
assert.equal(count, 1)
proc.call([10, 5], function (err, results, output) {
var expected = [99, 15]
assert.ifError(err)
assert.deepEqual(output, expected, 'results didn\'t match')
asyncDone()
})
})
}Where no parameters are required, simply omit the array and provide name and callback function only :-
pm.callproc('test_no_param', function(err, results, output) {
});A global timeout can be given in seconds to the procedure manager which will be sent to the driver on every call made through it. Hence an error will be raised should the procedure not complete within the allocated time.
pm.setTimeout(2);If SQL statements are repeatedly executed many times during the execution of a node program, a user may choose to encapsulate some into stored procedures which are compiled server side.
An alternative now available is the use of prepared statements available in this module. Prepared statements fetch meta data describing the query, allocate memory on the client which is bound to the columns representing the query. This is done just once, where each call then only requires potential parameters to be bound. This means efficiency gains over the same SQL string being submitted each time to the database.
Once a connection has been opened, the prepare function can be used to prepare a statement. The provided callback is invoked with an object on which the preparedQuery will allow repeated invocations. If necessary, different parameters can be included :-
function employeePrepare(done) {
var query =
`SELECT [ModifiedDate]
,[BusinessEntityID]
,[OrganizationNode]
,[ModifiedDate]
FROM [scratch].[dbo].[Employee]
WHERE BusinessEntityID = ?`;
sql.open(connStr, (err, conn) => {
assert.ifError(err);
conn.prepare(query, (e, ps) => {
assert.ifError(err);
ps.preparedQuery([1], (err, fetched) => {
console.log(ps.getMeta());
console.log(fetched);
ps.free(() => {
done();
})
});
});
});
}The prepared statement can be used until the free function is invoked. This call is necessary to release the resources associated with the statement. Clearly with poor client side management, leaks are possible. Take care to ensure once the statement is finished with, the free function is called. A collection of the most frequently called statements can be collected by a client into a useful object for use throughout the application.
Some helper functions are avaialble for each prepared statement :-
var meta = ps.getMeta(); // column meta data reserved in driver
var sql = ps.getSignature(); // the sql used to create prepared statement.
var id = ps.getId(); // unique integer reprenting statementThere are some more examples in the test file prepared.js.
for adhoc queries, a connection can opened, sql submitted and results collected all in one call. The driver will close the connection when completed :-
function connectQuery() {
var q = "declare @s NVARCHAR(MAX) = ?; select @s as s";
sql.query(connStr, q, ['node is great'], function (err, res) {
assert.ifError(err);
console.log(res);
});
}If the client will be submitting many queries within close proximity of time, it is probably better to open a connection explicitly. This connection can then be used to make may calls. This will generally be more efficient but will hold a connection to database open for the lifetime of the client. Holding many connections can impact overall performance. The design to use is therefore dependent on requirements.
function openQuery() {
sql.open(connStr, function (err, conn) {
assert.ifError(err);
var q = "declare @s NVARCHAR(MAX) = ?; select @s as s";
conn.query(q, ['node is great'], function (err, res) {
assert.ifError(err);
console.log(res);
});
});
}Where required, a timeout in seconds can be specified for a specific query. In this case, an error will be raised should the query not return within the allocated period.
function timeout() {
sql.open(connStr, function (err, conn) {
var queryObj = {
query_str: "waitfor delay \'00:00:10\';",
query_timeout: 2
};
conn.query(queryObj, function (err, res) {
assert(err != null);
assert(err.message.indexOf('Query timeout expired') > 0);
});
})
}If the query() function is used on the API, the raw row results returned by the database are converted to javascript objects such that an array of objects are returned, where each in the array represents a single row.
however, raw rows can be fetched where an object is returned thus :-
{
meta: [],
rows: []
}this is illustrated below where res.rows would be an array of arrays where each sub array contains each column.
function employee() {
sql.open(connStr, function (err, conn) {
conn.queryRaw("select * from Employee", function (err, res) {
console.log(b);
assert.ifError(err);
console.log(res);
});
});
}From version 0.3.1 onwards the library now supports query cancel. This can only be achieved with queries submitted on polling based statements.
sql.PollingQuery("waitfor delay \'00:00:20\';")examples are provided in the unit test file below.
var q = theConnection.query(sql.PollingQuery("waitfor delay \'00:00:20\';"), function (err) {
assert(err);
assert(err.message.indexOf('Operation canceled') > 0);
test_done();
});
q.cancelQuery(function (err) {
assert(!err);
});in the above example, the driver is instructed to use polling based query submission, thus allowing the cancelQuery function to be called. In polling mode, performance is reduced, this will not be noticed for long running queries but will for large numbers of short running queries. Hence this mode by default is switched off and for any applications not using cancel based queries the driver behaves exactly as before.
prepared queries can also be cancelled as shown in the example below. Note the submitted event is used to indicate when a query has actually been sent to the c++ driver. If a cancel is issued on a statement before submission it will be "soft" removed from the work queue and hence never submitted to the driver. This would be the case on back to back queries sent through the connection where the driver will work to complete one whilst holding back the rest.
var fns = [
function (async_done) {
theConnection.prepare(sql.PollingQuery(s), function (err, pq) {
assert(!err);
prepared = pq;
async_done();
});
},
function (async_done) {
var q = prepared.preparedQuery(['00:00:20'], function (err, res) {
assert(err);
assert(err.message.indexOf('Operation canceled') > 0);
async_done();
});
q.on('submitted', function() {
q.cancelQuery(function(err) {
assert.ifError(err);
});
});
}
];
async.series(fns, function () {
test_done();
})cancel can also be called on a stored proc call, in this case polling needs to be enabled on the proc manager itself.
function (async_done) {
var pm = theConnection.procedureMgr();
pm.setPolling(true); // allow polling
var q = pm.callproc(sp_name, ['0:0:20'], function (err) {
assert(err);
assert(err.message.indexOf('Operation canceled') > 0);
async_done();
});
q.on('submitted', function () {
q.cancelQuery(function (err) {
assert(!err);
});
});an error is expected when trying to cancel a query running on a blocked statement. This will be reported on the callback to the cancel and the execuring query will remain executing to completion.
var q = theConnection.query("waitfor delay \'00:00:3\';", function (err) {
assert(!err);
test_done();
});
theConnection.cancelQuery(q, function (err) {
assert(err);
assert(err.message.indexOf('only supported') > 0);
});When executing a query, certain events will be raised by the driver as it progresses. These events can be useful for debugging or drilling into performance measurements. The examples below show how to subscribe to these events.
Note if no callback is provided and an all event streaming approach is taken, one advantage is data is handed back without being cached in the driver. When the result set becomes large this can be a substantial amount of memory whilst the row set is read to completion.
By processing data via the streaming api a more efficient approach can be taken and reduce large volitity in memory footprint.
It should be noted that a large single column (e.g. 50k binary or text) will result in many smaller transactions between the driver and javascript until the entire column is read. In this case the data is held before being released and freed via the column event.
var s = "select top 1 id, name, type, crdate from sysobjects so where so.type='U'";
console.log(s);
var q = conn.query(s, function (err, res) {
assert.ifError(err);
console.log("res.length = " + res.length);
console.log(res);
});
q.on('meta', function(meta) {
console.log('meta[0].name = ' + meta[0].name);
});
q.on('column', function(colIndex, data) {
console.log('column = ' + colIndex);
});
q.on('rowcount', function(count) {
console.log('rowcount = ' + count);
});
q.on('row', function(row) {
console.log('row = ' + row);
});
q.on('done', function() {
console.log('done');
});
q.on('error', function(err) {
console.log(err.message);
});
q.on('info', function(err) {
console.log(err.message);
});
q.on('submitted', function(q) { // when driver is actually working this query.
console.log(JSON.stringify(q);
});use the info event as illustrated below.
sql.open(conn_str, (err, conn) => {
if (err) {
throw err;
}
let x = 1;
if (err) {
throw err;
}
setInterval(() => {
let q = conn.query(`print 'JS status message ${x}'; SELECT ${x} + ${x} as res;SELECT ${x} * ${x} as res2`,
(err, results, more) => {
if (more && !err && results && results.length === 0) {
return;
}
console.log(`[${x}] more = ${more} err ${err} results ${JSON.stringify(results)}`);
if (more) return;
++x;
});
q.on('info', (e:Error) => {
console.log(`print: ${e.message.substr(e.message.lastIndexOf(']') + 1)}`)
})
}, delay);
});The connection table manager can be useful in interacting with a single table :-
var tm = conn.tableMgr();to interact with a specific table, first invoke bind and provide a callback which will be invoked when the manager is ready :-
tm.bind('Employee', onBind);now insert some rows into the table.
function onBind(bulkMgr) {
// get an array of objects represented by the bound table.
var parsedJSON = helper.getJSON();
bulkMgr.insertRows(parsedJSON, insertDone);
}note that this is achieved with a single transaction to the server as memory is reserved for the entire insert, hence this can save network overhead of individual insert statements.
updates can also be sent against a set of rows in one transaction. For example, update column ModifiedDate based on primary key BusinessEntityID :-
function insertDone(err, res) {
assert.ifError(err);
assert(res.length == 0);
var newDate = new Date("2015-01-01T00:00:00.000Z");
var modifications = [];
parsedJSON.forEach(function(emp) {
emp.ModifiedDate = newDate;
modifications.push( {
BusinessEntityID : emp.BusinessEntityID,
ModifiedDate : newDate
});
});
var updateCols = [];
updateCols.push({
name : 'ModifiedDate'
});
bulkMgr.setUpdateCols(updateCols);
bulkMgr.updateRows(modifications, updateDone);
}Note in the above example, the modified vector from parsedJSON could also be sent to the driver for the update. The method setUpdateCols is required to indicate which columns are being modified.
The summary can be used to re-assign all updateable columns :-
function updateDone(err, res) {
assert.ifError(err);
assert(res.length == 0);
var summary = bulkMgr.getSummary();
bulkMgr.setUpdateCols(summary.assignableColumns);
}A batch size can be specified where the input vector is split and submitted in sets of that batch size.
bulkMgr.setBatchSize(batchSize);Rows can also be fetched based for example on primary key :-
function insertDone(err, res) {
assert.ifError(err);
var keys = extractKey(parsedJSON, 'LoginID');
bulkMgr.selectRows(keys, bulkDone);
}Where non primary key selects are required, use the setWhereCols function :-
function insertDone(err, res) {
assert.ifError(err);
var whereCols = [];
whereCols.push({
name : 'LoginID'
});
var parsedJSON = helper.getJSON();
var keys = extractKey(parsedJSON, 'LoginID');
bulkMgr.setWhereCols(whereCols);
bulkMgr.selectRows(keys, bulkDone);
}Rows can also be deleted based on the where condition :-
function insertDone(err, res) {
assert.ifError(err);
var whereCols = [];
whereCols.push({
name : 'LoginID'
});
var parsedJSON = helper.getJSON();
var keys = extractKey(parsedJSON, 'LoginID');
bulkMgr.setWhereCols(whereCols);
bulkMgr.deleteRows(results, bulkDone);
}Note, that the table mananger can be used along with prepared statements such as below where the table manager is used to fetch column data for a table, and then a prepared statement used against the generated select signature.
// use the select signature to construct a prepared query.
function(async_done) {
var summary = bulkMgr.getSummary();
console.log(summary.select_signature);
console.log("prepare the above statement.");
var select = summary.select_signature;
conn.prepare(select, function(err, ps) {
assert.ifError(err);
ps.preparedQuery([1], function(err, res) {
assert.ifError(err);
assert.check(res.length == 1);
async_done();
});
});
}In summary, the table manager can help when interacting with individual tables by allowing objects to be inserted, updated, deleted and selected.
bulkMgr.insertRows([], cb); // bulk insert objects.
bulkMgr.selectRows([], cb); // select based on where columns.
bulkMgr.deleteRows([], cb); // delete based on where columns.
bulkMgr.updateRows([], cb); // update based on where columns.
bulkMgr.setBatchSize(batchSize); // submit vector in batches to server.
bulkMgr.setWhereCols([]); // set the where condition for select,delete and update.
bulkMgr.setUpdateCols([]); // set which columns will be updated from input vector.
bulkMgr.getMeta(); // obtain interesting information for bound table.
bulkMgr.columns(); // all columns for the table.
bulkMgr.getSummary(); // gives sql signatures for the table.The demo file is a pretty good place to see many of the features being used.
make sure an instance of database is available to connect too. Edit the connection with the appropriate string.
from command line, navigate to where the npm module has been installed :-
admin@DESKTOP MINGW64 ~/dev/js/test/node_modules/node-sqlserver-v8 (master)
$ node mssql-demo.jsThe demo is divided into a set of distinct areas. Open the file, look for the var demos array and edit down to 1 or more tests to focus on a particular area. Hopefully the demo file provides some snippets which can be copied for use in your own programs.
If no SQL server instance is available to connect too, or indeed simply as an excellent test environment sqllocaldb is highly recommended. Select SqlLocalDB.msi and install. Once complete, from a command shell prompt (cmd), use the following commands :-
sqllocaldb create node
sqllocaldb start node
sqllocaldb info nodeThe create command is only required to be run once. Next, if not already installed, download SQLManagementStudio_x64_ENU.exe from same location (or x86 for 32 bit platforms). If you do any work at all with SQL server this is a superb and invaluable full featured IDE to develop and run SQL.
using the connection details provided by info, you should be able to connect to the database instance. Create a database for use with the demo and your own development. This can be done in the IDE by right clicking on Databases and selecting New Database.
Note, some useful commands are shown here which prevents your local SQL server instance from shutting down within a period of inactivity. Very useful during periods of development.
if you wish to run a unit test through an IDE, then the following file :-
provides a simple start point. Simply change the test run to whichever you require such as query.js