BIGINT = function () {
return {
type: TYPES.BigInt
};
}n/a
BINARY = function (length) {
return {
type: TYPES.Binary,
length: length
};
}n/a
BIT = function () {
return {
type: TYPES.Bit
};
}n/a
BigInt = function () {
return {
type: TYPES.BigInt
};
}n/a
Binary = function (length) {
return {
type: TYPES.Binary,
length: length
};
}n/a
Bit = function () {
return {
type: TYPES.Bit
};
}n/a
CHAR = function (length) {
return {
type: TYPES.Char,
length: length
};
}n/a
Char = function (length) {
return {
type: TYPES.Char,
length: length
};
}n/a
function Connection(config1, callback) {
var base, base1, base2, base3, base4, err, error, ex, ref1;
this.config = config1;
if ('string' === typeof this.config) {
try {
this.config = ConnectionString.resolve(this.config);
} catch (error) {
ex = error;
if (callback) {
return callback(ex);
} else {
throw ex;
}
}
}
if ((base = this.config).driver == null) {
base.driver = 'tedious';
}
if ((base1 = this.config).port == null) {
base1.port = 1433;
}
if ((base2 = this.config).options == null) {
base2.options = {};
}
if ((base3 = this.config).stream == null) {
base3.stream = false;
}
if ((base4 = this.config).parseJSON == null) {
base4.parseJSON = false;
}
if (/^(.*)\\(.*)$/.exec(this.config.server)) {
this.config.server = RegExp.$1;
this.config.options.instanceName = RegExp.$2;
}
if (ref1 = this.config.driver, indexOf.call(DRIVERS, ref1) >= 0) {
this.driver = this.initializeDriver(require("./" + this.config.driver));
if (module.exports.fix) {
this.driver.fix();
}
} else {
err = new ConnectionError("Unknown driver " + this.config.driver + "!", 'EDRIVER');
if (callback) {
return callback(err);
} else {
throw err;
}
}
if (callback) {
this.connect(callback);
}
}...
database: '...',
options: {
encrypt: true // Use this if you're on Windows Azure
}
}
var connection1 = new sql.Connection(config, function(err) {
// ... error checks
// Query
var request = new sql.Request(connection1); // or: var request = connection1.request();
request.query('select 1 as number', function(err, recordset) {
// ... error checks
...function ConnectionError(message, code) {
var err;
if (!(this instanceof ConnectionError)) {
if (message instanceof Error) {
err = new ConnectionError(message.message, message.code);
Object.defineProperty(err, 'originalError', {
value: message
});
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new ConnectionError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
if (code != null) {
this.code = code;
}
ConnectionError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
DATE = function () {
return {
type: TYPES.Date
};
}n/a
DATETIME = function () {
return {
type: TYPES.DateTime
};
}n/a
DATETIME2 = function (scale) {
return {
type: TYPES.DateTime2,
scale: scale
};
}n/a
DATETIMEOFFSET = function (scale) {
return {
type: TYPES.DateTimeOffset,
scale: scale
};
}n/a
DECIMAL = function (precision, scale) {
return {
type: TYPES.Decimal,
precision: precision,
scale: scale
};
}n/a
Date = function () {
return {
type: TYPES.Date
};
}n/a
DateTime = function () {
return {
type: TYPES.DateTime
};
}n/a
DateTime2 = function (scale) {
return {
type: TYPES.DateTime2,
scale: scale
};
}...
request.output("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.Decimal, 155.33); // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33); // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33); // decimal(10, 2)
request.input("name", sql.DateTime2, new Date()); // datetime2(7)
request.input("name", sql.DateTime2(5), new Date()); // datetime2(5)
```
List of supported data types:
```
sql.Bit
sql.BigInt
...DateTimeOffset = function (scale) {
return {
type: TYPES.DateTimeOffset,
scale: scale
};
}n/a
Decimal = function (precision, scale) {
return {
type: TYPES.Decimal,
precision: precision,
scale: scale
};
}...
request.input("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.VarChar(50), "abc"); // varchar(50)
request.input("name", sql.VarChar(sql.MAX), "abc"); // varchar(MAX)
request.output("name", sql.VarChar); // varchar(8000)
request.output("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.Decimal, 155.33); // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33); // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33); // decimal(10, 2)
request.input("name", sql.DateTime2, new Date()); // datetime2(7)
request.input("name", sql.DateTime2(5), new Date()); // datetime2(5)
```
List of supported data types:
...FLOAT = function () {
return {
type: TYPES.Float
};
}n/a
Float = function () {
return {
type: TYPES.Float
};
}n/a
GEOGRAPHY = function () {
return {
type: TYPES.Geography
};
}n/a
GEOMETRY = function () {
return {
type: TYPES.Geometry
};
}n/a
Geography = function () {
return {
type: TYPES.Geography
};
}n/a
Geometry = function () {
return {
type: TYPES.Geometry
};
}n/a
IMAGE = function () {
return {
type: TYPES.Image
};
}n/a
INT = function () {
return {
type: TYPES.Int
};
}n/a
Image = function () {
return {
type: TYPES.Image
};
}n/a
Int = function () {
return {
type: TYPES.Int
};
}n/a
MONEY = function () {
return {
type: TYPES.Money
};
}n/a
Money = function () {
return {
type: TYPES.Money
};
}n/a
NCHAR = function (length) {
return {
type: TYPES.NChar,
length: length
};
}n/a
NChar = function (length) {
return {
type: TYPES.NChar,
length: length
};
}n/a
NTEXT = function () {
return {
type: TYPES.NText
};
}n/a
NText = function () {
return {
type: TYPES.NText
};
}n/a
NUMERIC = function (precision, scale) {
return {
type: TYPES.Numeric,
precision: precision,
scale: scale
};
}n/a
NVARCHAR = function (length) {
return {
type: TYPES.NVarChar,
length: length
};
}n/a
NVarChar = function (length) {
return {
type: TYPES.NVarChar,
length: length
};
}n/a
Numeric = function (precision, scale) {
return {
type: TYPES.Numeric,
precision: precision,
scale: scale
};
}n/a
function PreparedStatement(connection) {
if (connection instanceof Transaction) {
this.transaction = connection;
this.connection = connection.connection;
} else if (connection instanceof Connection) {
this.connection = connection;
} else {
this.connection = global_connection;
}
this._queue = [];
this.parameters = {};
}...
- EREQINPROG (`TransactionError`) - Can't rollback transaction. There is a request in progress.
## Prepared Statement
**IMPORTANT:** always use `PreparedStatement` class to create prepared statements - it ensures that all your executions of prepared
statement are executed on one connection. Once you call `prepare`, a single connection is acquired from the connection pool and
all subsequent executions are executed exclusively on this connection. Prepared Statement also contains a queue to make sure your
executions are executed in series. After you call `unprepare`, the connection is then released back to the connection pool.
```javascript
var ps = new sql.PreparedStatement(/* [connection] */);
```
If you omit the connection argument, the global connection is used instead.
__Example__
```javascript
...function PreparedStatementError(message, code) {
var err;
if (!(this instanceof PreparedStatementError)) {
if (message instanceof Error) {
err = new PreparedStatementError(message.message, message.code);
err.originalError = message;
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new PreparedStatementError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
this.code = code;
PreparedStatementError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
function Promise() { [native code] }n/a
REAL = function () {
return {
type: TYPES.Real
};
}n/a
Real = function () {
return {
type: TYPES.Real
};
}n/a
function Request(connection) {
if (connection instanceof Transaction) {
this.transaction = connection;
this.connection = connection.connection;
} else if (connection instanceof PreparedStatement) {
this.pstatement = connection;
this.connection = connection.connection;
} else if (connection instanceof Connection) {
this.connection = connection;
} else {
this.connection = global_connection;
}
this.parameters = {};
}...
```javascript
var sql = require('mssql');
sql.connect("mssql://username:password@localhost/database").then(function() {
// Query
new sql.Request().query('select * from mytable').then(function(recordset) {
console.dir(recordset);
}).catch(function(err) {
// ... query error checks
});
// Stored Procedure
...function RequestError(message, code) {
var err, ref1, ref10, ref11, ref2, ref3, ref4, ref5, ref6, ref7, ref8, ref9;
if (!(this instanceof RequestError)) {
if (message instanceof Error) {
err = new RequestError(message.message, (ref1 = message.code) != null ? ref1 : code);
err.number = (ref2 = (ref3 = message.info) != null ? ref3.number : void 0) != null ? ref2 : message.code;
err.lineNumber = (ref4 = message.info) != null ? ref4.lineNumber : void 0;
err.state = (ref5 = (ref6 = message.info) != null ? ref6.state : void 0) != null ? ref5 : message.sqlstate;
err["class"] = (ref7 = (ref8 = message.info) != null ? ref8["class"] : void 0) != null ? ref7 : (ref9 = message.info) != null
? ref9.severity : void 0;
err.serverName = (ref10 = message.info) != null ? ref10.serverName : void 0;
err.procName = (ref11 = message.info) != null ? ref11.procName : void 0;
Object.defineProperty(err, 'originalError', {
value: message
});
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new RequestError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
if (code != null) {
this.code = code;
}
RequestError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
SMALLDATETIME = function () {
return {
type: TYPES.SmallDateTime
};
}n/a
SMALLINT = function () {
return {
type: TYPES.SmallInt
};
}n/a
SMALLMONEY = function () {
return {
type: TYPES.SmallMoney
};
}n/a
SmallDateTime = function () {
return {
type: TYPES.SmallDateTime
};
}n/a
SmallInt = function () {
return {
type: TYPES.SmallInt
};
}n/a
SmallMoney = function () {
return {
type: TYPES.SmallMoney
};
}n/a
TEXT = function () {
return {
type: TYPES.Text
};
}n/a
TIME = function (scale) {
return {
type: TYPES.Time,
scale: scale
};
}n/a
TINYINT = function () {
return {
type: TYPES.TinyInt
};
}n/a
TVP = function (tvpType) {
return {
type: TYPES.TVP,
tvpType: tvpType
};
}n/a
function Table(name) {
var ref1;
if (name) {
ref1 = Table.parseName(name), this.name = ref1.name, this.schema = ref1.schema, this.database = ref1.database;
this.path = "" + (this.database ? "[" + this.database + "]." : "") + (this.schema ? "[" + this.schema + "]." : "") + "[" + this
.name + "]";
this.temporary = this.name.charAt(0) === '#';
}
this.columns = [];
this.rows = [];
Object.defineProperty(this.columns, "add", {
value: function(name, column, options) {
if (options == null) {
options = {};
}
if (column == null) {
throw new Error("Column data type is not defined.");
}
if (column instanceof Function) {
column = column();
}
column.name = name;
column.nullable = options.nullable;
column.primary = options.primary;
return this.push(column);
}
});
Object.defineProperty(this.rows, "add", {
value: function() {
var values;
values = 1 <= arguments.length ? slice.call(arguments, 0) : [];
return this.push(values);
}
});
}...
- **table** - `sql.Table` instance.
- **callback(err, rowCount)** - A callback which is called after bulk insert has completed, or an error has occurred. Optional.
If omitted, returns [Promise](#promises).
__Example__
```javascript
var table = new sql.Table('table_name'); // or temporary table, e.g. #temptable
table.create = true;
table.columns.add('a', sql.Int, {nullable: true, primary: true});
table.columns.add('b', sql.VarChar(50), {nullable: false});
table.rows.add(777, 'test');
var request = new sql.Request();
request.bulk(table, function(err, rowCount) {
...Text = function () {
return {
type: TYPES.Text
};
}n/a
Time = function (scale) {
return {
type: TYPES.Time,
scale: scale
};
}n/a
TinyInt = function () {
return {
type: TYPES.TinyInt
};
}n/a
function Transaction(connection) {
this._abort = bind(this._abort, this);
this.connection = connection != null ? connection : global_connection;
this._queue = [];
}...
```
## Transaction
**IMPORTANT:** always use `Transaction` class to create transactions - it ensures that all your requests are executed on one connection
. Once you call `begin`, a single connection is acquired from the connection pool and all subsequent requests (initialized with
the `Transaction` object) are executed exclusively on this connection. Transaction also contains a queue to make sure your requests
are executed in series. After you call `commit` or `rollback`, connection is then released back to the connection pool.
```javascript
var transaction = new sql.Transaction(/* [connection] */);
```
If you omit connection argument, global connection is used instead.
__Example__
```javascript
...function TransactionError(message, code) {
var err;
if (!(this instanceof TransactionError)) {
if (message instanceof Error) {
err = new TransactionError(message.message, message.code);
Object.defineProperty(err, 'originalError', {
value: message
});
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new TransactionError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
if (code != null) {
this.code = code;
}
TransactionError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
UDT = function () {
return {
type: TYPES.UDT
};
}n/a
UNIQUEIDENTIFIER = function () {
return {
type: TYPES.UniqueIdentifier
};
}n/a
UniqueIdentifier = function () {
return {
type: TYPES.UniqueIdentifier
};
}n/a
VARBINARY = function (length) {
return {
type: TYPES.VarBinary,
length: length
};
}n/a
VARCHAR = function (length) {
return {
type: TYPES.VarChar,
length: length
};
}n/a
VARIANT = function () {
return {
type: TYPES.Variant
};
}n/a
VarBinary = function (length) {
return {
type: TYPES.VarBinary,
length: length
};
}n/a
VarChar = function (length) {
return {
type: TYPES.VarChar,
length: length
};
}...
// ... query error checks
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name').then(function(recordsets) {
console.dir(recordsets);
}).catch(function(err) {
// ... execute error checks
});
// ES6 Tagged template literals (experimental)
...Variant = function () {
return {
type: TYPES.Variant
};
}n/a
XML = function () {
return {
type: TYPES.Xml
};
}n/a
Xml = function () {
return {
type: TYPES.Xml
};
}n/a
batch = function () {
var strings, values;
strings = arguments[0], values = 2 <= arguments.length ? slice.call(arguments, 1) : [];
return new Request()._template('batch', strings, values);
}...
- **batch** - T-SQL command to be executed.
- **callback(err, recordset)** - A callback which is called after execution has completed, or an error has occurred. Optional. If
omitted, returns [Promise](#promises).
__Example__
```javascript
var request = new sql.Request();
request.batch('create procedure #temporary as select * from table', function
(err, recordset) {
// ... error checks
});
```
__Errors__
- ETIMEOUT (`RequestError`) - Request timeout.
- EREQUEST (`RequestError`) - *Message from SQL Server*
...close = function (callback) {
return global_connection != null ? global_connection.close(callback) : void 0;
}...
### close()
Close all active connections in the pool.
__Example__
```javascript
connection.close();
```
## Request
```javascript
var request = new sql.Request(/* [connection] */);
```
...connect = function (config, callback) {
global_connection = new Connection(config);
return global_connection.connect(callback);
}...
npm install mssql
## Quick Example
```javascript
var sql = require('mssql');
sql.connect("mssql://username:password@localhost/database").then(function() {
// Query
new sql.Request().query('select * from mytable').then(function(recordset) {
console.dir(recordset);
}).catch(function(err) {
// ... query error checks
});
...init = function () {
return module.exports.connect({
user: module.exports.connection.userName,
password: module.exports.connection.password,
server: module.exports.connection.server,
options: module.exports.connection.options,
driver: 'tedious',
pool: module.exports.pool
});
}n/a
on = function (event, handler) {
return global_connection != null ? global_connection.on(event, handler) : void 0;
}...
.execute('procedure_name', function(err, recordsets, returnValue) {
// ... error checks
console.dir(recordsets);
});
});
sql.on('error', function(err) {
// ... error handler
});
```
### Streaming
If you plan to work with large amount of rows, you should always use streaming. Once you enable this, you must listen for events
to receive data.
...query = function () {
var strings, values;
strings = arguments[0], values = 2 <= arguments.length ? slice.call(arguments, 1) : [];
return new Request()._template('query', strings, values);
}...
```javascript
var sql = require('mssql');
sql.connect("mssql://username:password@localhost/database").then(function() {
// Query
new sql.Request().query('select * from mytable').then(function(recordset) {
console.dir(recordset);
}).catch(function(err) {
// ... query error checks
});
// Stored Procedure
...BigInt = function () {
return {
type: TYPES.BigInt
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Binary = function (length) {
return {
type: TYPES.Binary,
length: length
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Bit = function () {
return {
type: TYPES.Bit
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Char = function (length) {
return {
type: TYPES.Char,
length: length
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...function Connection(config1, callback) {
var base, base1, base2, base3, base4, err, error, ex, ref1;
this.config = config1;
if ('string' === typeof this.config) {
try {
this.config = ConnectionString.resolve(this.config);
} catch (error) {
ex = error;
if (callback) {
return callback(ex);
} else {
throw ex;
}
}
}
if ((base = this.config).driver == null) {
base.driver = 'tedious';
}
if ((base1 = this.config).port == null) {
base1.port = 1433;
}
if ((base2 = this.config).options == null) {
base2.options = {};
}
if ((base3 = this.config).stream == null) {
base3.stream = false;
}
if ((base4 = this.config).parseJSON == null) {
base4.parseJSON = false;
}
if (/^(.*)\\(.*)$/.exec(this.config.server)) {
this.config.server = RegExp.$1;
this.config.options.instanceName = RegExp.$2;
}
if (ref1 = this.config.driver, indexOf.call(DRIVERS, ref1) >= 0) {
this.driver = this.initializeDriver(require("./" + this.config.driver));
if (module.exports.fix) {
this.driver.fix();
}
} else {
err = new ConnectionError("Unknown driver " + this.config.driver + "!", 'EDRIVER');
if (callback) {
return callback(err);
} else {
throw err;
}
}
if (callback) {
this.connect(callback);
}
}...
database: '...',
options: {
encrypt: true // Use this if you're on Windows Azure
}
}
var connection1 = new sql.Connection(config, function(err) {
// ... error checks
// Query
var request = new sql.Request(connection1); // or: var request = connection1.request();
request.query('select 1 as number', function(err, recordset) {
// ... error checks
...function EventEmitter() {
EventEmitter.init.call(this);
}n/a
init = function () {
this.domain = null;
if (EventEmitter.usingDomains) {
// if there is an active domain, then attach to it.
domain = domain || require('domain');
if (domain.active && !(this instanceof domain.Domain)) {
this.domain = domain.active;
}
}
if (!this._events || this._events === Object.getPrototypeOf(this)._events) {
this._events = new EventHandlers();
this._eventsCount = 0;
}
this._maxListeners = this._maxListeners || undefined;
}n/a
listenerCount = function (emitter, type) {
if (typeof emitter.listenerCount === 'function') {
return emitter.listenerCount(type);
} else {
return listenerCount.call(emitter, type);
}
}n/a
_close = function (callback) {
if (this._debugStream) {
this._debugStream.removeAllListeners();
this._debugStream.end();
this._debugStream = null;
}
if (this.connecting) {
this.connecting = false;
this.driver.Connection.prototype.close.call(this, (function(_this) {
return function(err) {
return callback(err);
};
})(this));
this.driver = null;
} else if (this.connected) {
this.connected = false;
this.driver.Connection.prototype.close.call(this, (function(_this) {
return function(err) {
if (!err) {
_this.connected = false;
_this.emit('close');
}
return callback(err);
};
})(this));
this.driver = null;
}
return this;
}n/a
_connect = function (callback) {
var go;
if (!this.driver) {
return callback(new ConnectionError("Connection was closed. Create a new instance."));
}
if (this.connected) {
return callback(new ConnectionError("Database is already connected! Call close before connecting to different database.", 'EALREADYCONNECTED
'));
}
if (this.connecting) {
return callback(new ConnectionError("Already connecting to database! Call close before connecting to different database.", '
EALREADYCONNECTING'));
}
go = (function(_this) {
return function() {
_this.connecting = true;
return _this.driver.Connection.prototype.connect.call(_this, _this.config, function(err) {
if (!_this.connecting) {
return;
}
_this.connecting = false;
if (err) {
if (_this._debugStream) {
_this._debugStream.removeAllListeners();
_this._debugStream.end();
_this._debugStream = null;
}
} else {
_this.connected = true;
_this.emit('connect');
}
return callback(err);
});
};
})(this);
if (this.config.debug) {
this._debugStream = fs.createWriteStream("./mssql_debug_" + (Date.now()) + ".log");
this._debugStream.once('open', go);
this._debugStream.on('error', function(err) {
if (this.connecting || this.connected) {
return console.error(err.stack);
} else {
this._debugStream.removeListener('open', go);
return callback(new ConnectionError("Failed to open debug stream. " + err.message, 'EDEBUG'));
}
});
} else {
go();
}
return this;
}n/a
_debug = function (msg) {
var ref1;
return (ref1 = this._debugStream) != null ? ref1.write((String(msg).replace(/\x1B\[[0-9;]*m/g, '')) + "\n") : void 0;
}...
c.hasError = true;
return;
}
return _this.emit('error', err);
});
if (config.debug) {
return c.on('debug', function(msg) {
return _this._debug(msg);
});
}
};
})(this),
validate: function(c) {
return (c != null) && !c.closed && !c.hasError;
},
...batch = function () {
var strings, values;
strings = arguments[0], values = 2 <= arguments.length ? slice.call(arguments, 1) : [];
return new Request(this)._template('batch', strings, values);
}...
- **batch** - T-SQL command to be executed.
- **callback(err, recordset)** - A callback which is called after execution has completed, or an error has occurred. Optional. If
omitted, returns [Promise](#promises).
__Example__
```javascript
var request = new sql.Request();
request.batch('create procedure #temporary as select * from table', function
(err, recordset) {
// ... error checks
});
```
__Errors__
- ETIMEOUT (`RequestError`) - Request timeout.
- EREQUEST (`RequestError`) - *Message from SQL Server*
...close = function (callback) {
if (callback != null) {
return this._close(callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._close(function(err) {
if (err) {
return reject(err);
}
return resolve();
});
};
})(this));
}...
### close()
Close all active connections in the pool.
__Example__
```javascript
connection.close();
```
## Request
```javascript
var request = new sql.Request(/* [connection] */);
```
...connect = function (callback) {
if (callback != null) {
return this._connect(callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._connect(function(err) {
if (err) {
return reject(err);
}
return resolve(_this);
});
};
})(this));
}...
npm install mssql
## Quick Example
```javascript
var sql = require('mssql');
sql.connect("mssql://username:password@localhost/database").then(function() {
// Query
new sql.Request().query('select * from mytable').then(function(recordset) {
console.dir(recordset);
}).catch(function(err) {
// ... query error checks
});
...function Connection(config1, callback) {
var base, base1, base2, base3, base4, err, error, ex, ref1;
this.config = config1;
if ('string' === typeof this.config) {
try {
this.config = ConnectionString.resolve(this.config);
} catch (error) {
ex = error;
if (callback) {
return callback(ex);
} else {
throw ex;
}
}
}
if ((base = this.config).driver == null) {
base.driver = 'tedious';
}
if ((base1 = this.config).port == null) {
base1.port = 1433;
}
if ((base2 = this.config).options == null) {
base2.options = {};
}
if ((base3 = this.config).stream == null) {
base3.stream = false;
}
if ((base4 = this.config).parseJSON == null) {
base4.parseJSON = false;
}
if (/^(.*)\\(.*)$/.exec(this.config.server)) {
this.config.server = RegExp.$1;
this.config.options.instanceName = RegExp.$2;
}
if (ref1 = this.config.driver, indexOf.call(DRIVERS, ref1) >= 0) {
this.driver = this.initializeDriver(require("./" + this.config.driver));
if (module.exports.fix) {
this.driver.fix();
}
} else {
err = new ConnectionError("Unknown driver " + this.config.driver + "!", 'EDRIVER');
if (callback) {
return callback(err);
} else {
throw err;
}
}
if (callback) {
this.connect(callback);
}
}n/a
initializeDriver = function (driver) {
return driver(Connection, Transaction, Request, ConnectionError, TransactionError, RequestError);
}n/a
query = function () {
var strings, values;
strings = arguments[0], values = 2 <= arguments.length ? slice.call(arguments, 1) : [];
return new Request(this)._template('query', strings, values);
}...
```javascript
var sql = require('mssql');
sql.connect("mssql://username:password@localhost/database").then(function() {
// Query
new sql.Request().query('select * from mytable').then(function(recordset) {
console.dir(recordset);
}).catch(function(err) {
// ... query error checks
});
// Stored Procedure
...request = function () {
return new Request(this);
}...
}
var connection1 = new sql.Connection(config, function(err) {
// ... error checks
// Query
var request = new sql.Request(connection1); // or: var request = connection1.request();
request.query('select 1 as number', function(err, recordset) {
// ... error checks
console.dir(recordset);
});
});
...transaction = function () {
return new Transaction(this);
}...
console.log("Transaction committed.");
});
});
});
```
Transaction can also be created by `var transaction = connection.transaction();`. Requests
can also be created by `var request = transaction.request();`.
__Aborted transactions__
This example shows how you should correctly handle transaction errors when `abortTransactionOnError` (`XACT_ABORT`) is enabled.
Added in 2.0.
```javascript
var transaction = new sql.Transaction(/* [connection] */);
...function ConnectionError(message, code) {
var err;
if (!(this instanceof ConnectionError)) {
if (message instanceof Error) {
err = new ConnectionError(message.message, message.code);
Object.defineProperty(err, 'originalError', {
value: message
});
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new ConnectionError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
if (code != null) {
this.code = code;
}
ConnectionError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
function captureStackTrace() { [native code] }n/a
function ConnectionError(message, code) {
var err;
if (!(this instanceof ConnectionError)) {
if (message instanceof Error) {
err = new ConnectionError(message.message, message.code);
Object.defineProperty(err, 'originalError', {
value: message
});
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new ConnectionError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
if (code != null) {
this.code = code;
}
ConnectionError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
Date = function () {
return {
type: TYPES.Date
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...DateTime = function () {
return {
type: TYPES.DateTime
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...DateTime2 = function (scale) {
return {
type: TYPES.DateTime2,
scale: scale
};
}...
request.output("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.Decimal, 155.33); // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33); // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33); // decimal(10, 2)
request.input("name", sql.DateTime2, new Date()); // datetime2(7)
request.input("name", sql.DateTime2(5), new Date()); // datetime2(5)
```
List of supported data types:
```
sql.Bit
sql.BigInt
...inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...DateTimeOffset = function (scale) {
return {
type: TYPES.DateTimeOffset,
scale: scale
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Decimal = function (precision, scale) {
return {
type: TYPES.Decimal,
precision: precision,
scale: scale
};
}...
request.input("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.VarChar(50), "abc"); // varchar(50)
request.input("name", sql.VarChar(sql.MAX), "abc"); // varchar(MAX)
request.output("name", sql.VarChar); // varchar(8000)
request.output("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.Decimal, 155.33); // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33); // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33); // decimal(10, 2)
request.input("name", sql.DateTime2, new Date()); // datetime2(7)
request.input("name", sql.DateTime2(5), new Date()); // datetime2(5)
```
List of supported data types:
...inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Float = function () {
return {
type: TYPES.Float
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Geography = function () {
return {
type: TYPES.Geography
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Geometry = function () {
return {
type: TYPES.Geometry
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Image = function () {
return {
type: TYPES.Image
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Int = function () {
return {
type: TYPES.Int
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Money = function () {
return {
type: TYPES.Money
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...NChar = function (length) {
return {
type: TYPES.NChar,
length: length
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...NText = function () {
return {
type: TYPES.NText
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...NVarChar = function (length) {
return {
type: TYPES.NVarChar,
length: length
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Numeric = function (precision, scale) {
return {
type: TYPES.Numeric,
precision: precision,
scale: scale
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...function PreparedStatement(connection) {
if (connection instanceof Transaction) {
this.transaction = connection;
this.connection = connection.connection;
} else if (connection instanceof Connection) {
this.connection = connection;
} else {
this.connection = global_connection;
}
this._queue = [];
this.parameters = {};
}...
- EREQINPROG (`TransactionError`) - Can't rollback transaction. There is a request in progress.
## Prepared Statement
**IMPORTANT:** always use `PreparedStatement` class to create prepared statements - it ensures that all your executions of prepared
statement are executed on one connection. Once you call `prepare`, a single connection is acquired from the connection pool and
all subsequent executions are executed exclusively on this connection. Prepared Statement also contains a queue to make sure your
executions are executed in series. After you call `unprepare`, the connection is then released back to the connection pool.
```javascript
var ps = new sql.PreparedStatement(/* [connection] */);
```
If you omit the connection argument, the global connection is used instead.
__Example__
```javascript
...function EventEmitter() {
EventEmitter.init.call(this);
}n/a
init = function () {
this.domain = null;
if (EventEmitter.usingDomains) {
// if there is an active domain, then attach to it.
domain = domain || require('domain');
if (domain.active && !(this instanceof domain.Domain)) {
this.domain = domain.active;
}
}
if (!this._events || this._events === Object.getPrototypeOf(this)._events) {
this._events = new EventHandlers();
this._eventsCount = 0;
}
this._maxListeners = this._maxListeners || undefined;
}n/a
listenerCount = function (emitter, type) {
if (typeof emitter.listenerCount === 'function') {
return emitter.listenerCount(type);
} else {
return listenerCount.call(emitter, type);
}
}n/a
_execute = function (values, callback) {
var name, param, ref1, req;
req = this.lastRequest = new Request(this);
if (this.stream != null) {
req.stream = this.stream;
}
req.input('handle', TYPES.Int, this._handle);
ref1 = this.parameters;
for (name in ref1) {
param = ref1[name];
req.parameters[name] = {
name: name,
type: param.type,
io: param.io,
value: values[name],
length: param.length,
scale: param.scale,
precision: param.precision
};
}
req.execute('sp_execute', (function(_this) {
return function(err, recordsets, returnValue) {
if (err) {
return callback(err);
}
return callback(null, (_this.multiple ? recordsets : recordsets[0]), req.rowsAffected);
};
})(this));
return req;
}n/a
_prepare = function (statement, callback) {
var done;
if (this._pooledConnection) {
callback(new PreparedStatementError("Statement is already prepared.", 'EALREADYPREPARED'));
return this;
}
if (typeof statement === 'function') {
callback = statement;
statement = void 0;
}
if (statement != null) {
this.statement = statement;
}
done = (function(_this) {
return function(err, connection) {
var name, param, req;
if (err) {
return callback(err);
}
_this._pooledConnection = connection;
req = new Request(_this);
req.stream = false;
req.output('handle', TYPES.Int);
req.input('params', TYPES.NVarChar, ((function() {
var ref1, results;
ref1 = this.parameters;
results = [];
for (name in ref1) {
param = ref1[name];
results.push("@" + name + " " + (declare(param.type, param)) + (param.io === 2 ? " output" : ""));
}
return results;
}).call(_this)).join(','));
req.input('stmt', TYPES.NVarChar, _this.statement);
return req.execute('sp_prepare', function(err) {
if (err) {
if (_this.transaction) {
_this.transaction.next();
} else {
_this.connection.pool.release(_this._pooledConnection);
_this._pooledConnection = null;
}
return callback(err);
}
_this._handle = req.parameters.handle.value;
return callback(null);
});
};
})(this);
if (this.transaction) {
if (!this.transaction._pooledConnection) {
callback(new TransactionError("Transaction has not begun. Call begin() first.", 'ENOTBEGUN'));
return this;
}
this.transaction.queue(done);
} else {
this.connection.pool.acquire(done);
}
return this;
}n/a
_unprepare = function (callback) {
var done, req;
if (!this._pooledConnection) {
callback(new PreparedStatementError("Statement is not prepared. Call prepare() first.", 'ENOTPREPARED'));
return this;
}
done = (function(_this) {
return function(err) {
if (err) {
return callback(err);
}
if (_this.transaction) {
_this.transaction.next();
} else {
_this.connection.pool.release(_this._pooledConnection);
_this._pooledConnection = null;
}
_this._handle = 0;
return callback(null);
};
})(this);
req = new Request(this);
req.stream = false;
req.input('handle', TYPES.Int, this._handle);
req.execute('sp_unprepare', done);
return this;
}n/a
function PreparedStatement(connection) {
if (connection instanceof Transaction) {
this.transaction = connection;
this.connection = connection.connection;
} else if (connection instanceof Connection) {
this.connection = connection;
} else {
this.connection = global_connection;
}
this._queue = [];
this.parameters = {};
}n/a
execute = function (values, callback) {
if (callback != null) {
return this._execute(values, callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._execute(values, function(err, recordset) {
if (err) {
return reject(err);
}
return resolve(recordset);
});
};
})(this));
}...
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name').then(function(recordsets) {
console.dir(recordsets);
}).catch(function(err) {
// ... execute error checks
});
// ES6 Tagged template literals (experimental)
...input = function (name, type) {
if (/(--| |\/\*|\*\/|')/.test(name)) {
throw new PreparedStatementError("SQL injection warning for param '" + name + "'", 'EINJECT');
}
if (arguments.length < 2) {
throw new PreparedStatementError("Invalid number of arguments. 2 arguments expected.", 'EARGS');
}
if (type instanceof Function) {
type = type();
}
this.parameters[name] = {
name: name,
type: type.type,
io: 1,
length: type.length,
scale: type.scale,
precision: type.precision,
tvpType: type.tvpType
};
return this;
}...
}).catch(function(err) {
// ... query error checks
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name').then(function(recordsets) {
console.dir(recordsets);
}).catch(function(err) {
// ... execute error checks
});
...next = function () {
if (this._queue.length) {
process.nextTick((function(_this) {
return function() {
return _this._queue.shift()(null, _this._pooledConnection);
};
})(this));
} else {
this._working = false;
}
return this;
}n/a
output = function (name, type) {
if (/(--| |\/\*|\*\/|')/.test(name)) {
throw new PreparedStatementError("SQL injection warning for param '" + name + "'", 'EINJECT');
}
if (arguments.length < 2) {
throw new PreparedStatementError("Invalid number of arguments. 2 arguments expected.", 'EARGS');
}
if (type instanceof Function) {
type = type();
}
this.parameters[name] = {
name: name,
type: type.type,
io: 2,
length: type.length,
scale: type.scale,
precision: type.precision
};
return this;
}...
// ... query error checks
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name').then(function(recordsets) {
console.dir(recordsets);
}).catch(function(err) {
// ... execute error checks
});
// ES6 Tagged template literals (experimental)
...prepare = function (statement, callback) {
if (callback != null) {
return this._prepare(statement, callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._prepare(statement, function(err) {
if (err) {
return reject(err);
}
return resolve(_this);
});
};
})(this));
}...
If you omit the connection argument, the global connection is used instead.
__Example__
```javascript
var ps = new sql.PreparedStatement(/* [connection] */);
ps.input('param', sql.Int);
ps.prepare('select @param as value', function(err) {
// ... error checks
ps.execute({param: 12345}, function(err, recordset) {
// ... error checks
ps.unprepare(function(err) {
// ... error checks
...queue = function (callback) {
if (!this._pooledConnection) {
callback(new PreparedStatementError("Statement is not prepared. Call prepare() first.", 'ENOTPREPARED'));
return this;
}
if (this._working) {
this._queue.push(callback);
} else {
this._working = true;
callback(null, this._pooledConnection);
}
return this;
}n/a
unprepare = function (callback) {
if (callback != null) {
return this._unprepare(callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._unprepare(function(err) {
if (err) {
return reject(err);
}
return resolve();
});
};
})(this));
}...
ps.input('param', sql.Int);
ps.prepare('select @param as value', function(err) {
// ... error checks
ps.execute({param: 12345}, function(err, recordset) {
// ... error checks
ps.unprepare(function(err) {
// ... error checks
});
});
});
```
...function PreparedStatementError(message, code) {
var err;
if (!(this instanceof PreparedStatementError)) {
if (message instanceof Error) {
err = new PreparedStatementError(message.message, message.code);
err.originalError = message;
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new PreparedStatementError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
this.code = code;
PreparedStatementError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
function captureStackTrace() { [native code] }n/a
function PreparedStatementError(message, code) {
var err;
if (!(this instanceof PreparedStatementError)) {
if (message instanceof Error) {
err = new PreparedStatementError(message.message, message.code);
err.originalError = message;
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new PreparedStatementError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
this.code = code;
PreparedStatementError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
Real = function () {
return {
type: TYPES.Real
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...function Request(connection) {
if (connection instanceof Transaction) {
this.transaction = connection;
this.connection = connection.connection;
} else if (connection instanceof PreparedStatement) {
this.pstatement = connection;
this.connection = connection.connection;
} else if (connection instanceof Connection) {
this.connection = connection;
} else {
this.connection = global_connection;
}
this.parameters = {};
}...
```javascript
var sql = require('mssql');
sql.connect("mssql://username:password@localhost/database").then(function() {
// Query
new sql.Request().query('select * from mytable').then(function(recordset) {
console.dir(recordset);
}).catch(function(err) {
// ... query error checks
});
// Stored Procedure
...function EventEmitter() {
EventEmitter.init.call(this);
}n/a
init = function () {
this.domain = null;
if (EventEmitter.usingDomains) {
// if there is an active domain, then attach to it.
domain = domain || require('domain');
if (domain.active && !(this instanceof domain.Domain)) {
this.domain = domain.active;
}
}
if (!this._events || this._events === Object.getPrototypeOf(this)._events) {
this._events = new EventHandlers();
this._eventsCount = 0;
}
this._maxListeners = this._maxListeners || undefined;
}n/a
listenerCount = function (emitter, type) {
if (typeof emitter.listenerCount === 'function') {
return emitter.listenerCount(type);
} else {
return listenerCount.call(emitter, type);
}
}n/a
_acquire = function (callback) {
if (this.transaction) {
return this.transaction.queue(callback);
} else if (this.pstatement) {
return this.pstatement.queue(callback);
} else {
if (!this.connection.pool) {
return callback(new ConnectionError("Connection not yet open.", 'ENOTOPEN'));
}
return this.connection.pool.acquire(callback);
}
}...
"class": msg["class"],
lineNumber: msg.lineNumber,
serverName: msg.serverName,
procName: msg.procName
});
};
})(this);
return this._acquire((function(_this) {
return function(err, connection) {
var bulk, col, done, i, j, len, len1, objectid, ref1, ref2, req, row;
if (!err) {
if (_this.verbose) {
_this._log("-------- sql bulk load --------\n table: " + table.name);
}
if (_this.canceled) {
..._batch = function (batch, callback) {
if (!this.connection) {
return process.nextTick((function(_this) {
return function() {
var e;
e = new RequestError("No connection is specified for that request.", 'ENOCONN');
if (_this.stream) {
_this.emit('error', e);
return _this.emit('done');
} else {
return callback(e);
}
};
})(this));
}
if (!this.connection.connected) {
return process.nextTick((function(_this) {
return function() {
var e;
e = new ConnectionError("Connection is closed.", 'ECONNCLOSED');
if (_this.stream) {
_this.emit('error', e);
return _this.emit('done');
} else {
return callback(e);
}
};
})(this));
}
this.canceled = false;
this.connection.driver.Request.prototype.batch.call(this, batch, (function(_this) {
return function(err, recordsets) {
if (_this.stream) {
if (err) {
_this.emit('error', err);
}
return _this.emit('done', _this.rowsAffected);
} else {
return callback(err, recordsets, _this.rowsAffected);
}
};
})(this));
return this;
}n/a
_bulk = function (table, callback) {
if (!this.connection) {
return process.nextTick((function(_this) {
return function() {
var e;
e = new RequestError("No connection is specified for that request.", 'ENOCONN');
if (_this.stream) {
_this.emit('error', e);
return _this.emit('done');
} else {
return callback(e);
}
};
})(this));
}
if (!this.connection.connected) {
return process.nextTick((function(_this) {
return function() {
var e;
e = new ConnectionError("Connection is closed.", 'ECONNCLOSED');
if (_this.stream) {
_this.emit('error', e);
return _this.emit('done');
} else {
return callback(e);
}
};
})(this));
}
this.canceled = false;
this.connection.driver.Request.prototype.bulk.call(this, table, (function(_this) {
return function(err, rowCount) {
if (_this.stream) {
if (err) {
_this.emit('error', err);
}
return _this.emit('done');
} else {
return callback(err, rowCount);
}
};
})(this));
return this;
}n/a
_dedicated = function (connection) {
this._acquire = function(callback) {
return callback(null, connection);
};
this._release = function() {};
return this;
}n/a
_execute = function (procedure, callback) {
if (!this.connection) {
return process.nextTick(function() {
var e;
e = new RequestError("No connection is specified for that request.", 'ENOCONN');
if (this.stream) {
this.emit('error', e);
return this.emit('done');
} else {
return callback(e);
}
});
}
if (!this.connection.connected) {
return process.nextTick((function(_this) {
return function() {
var e;
e = new ConnectionError("Connection is closed.", 'ECONNCLOSED');
if (_this.stream) {
_this.emit('error', e);
return _this.emit('done');
} else {
return callback(e);
}
};
})(this));
}
this.canceled = false;
this.connection.driver.Request.prototype.execute.call(this, procedure, (function(_this) {
return function(err, recordsets, returnValue) {
if (_this.stream) {
if (err) {
_this.emit('error', err);
}
return _this.emit('done', returnValue, _this.rowsAffected);
} else {
return callback(err, recordsets, returnValue, _this.rowsAffected);
}
};
})(this));
return this;
}n/a
_log = function (out) {
if (typeof this.logger === "function") {
return this.logger(out);
} else {
return console.log(out);
}
}...
};
})(this);
return this._acquire((function(_this) {
return function(err, connection) {
var bulk, col, done, i, j, len, len1, objectid, ref1, ref2, req, row;
if (!err) {
if (_this.verbose) {
_this._log("-------- sql bulk load --------\n table: " + table.name
);
}
if (_this.canceled) {
if (_this.verbose) {
_this._log("---------- canceling ----------");
}
_this._release(connection);
return typeof callback === "function" ? callback(new RequestError("Canceled.", 'ECANCEL')) :
void 0;
..._query = function (command, callback) {
if (!this.connection) {
return process.nextTick((function(_this) {
return function() {
var e;
e = new RequestError("No connection is specified for that request.", 'ENOCONN');
if (_this.stream) {
_this.emit('error', e);
return _this.emit('done');
} else {
return callback(e);
}
};
})(this));
}
if (!this.connection.connected) {
return process.nextTick((function(_this) {
return function() {
var e;
e = new ConnectionError("Connection is closed.", 'ECONNCLOSED');
if (_this.stream) {
_this.emit('error', e);
return _this.emit('done');
} else {
return callback(e);
}
};
})(this));
}
this.canceled = false;
this.connection.driver.Request.prototype.query.call(this, command, (function(_this) {
return function(err, recordsets) {
if (_this.stream) {
if (err) {
_this.emit('error', err);
}
return _this.emit('done', _this.rowsAffected);
} else {
return callback(err, recordsets, _this.rowsAffected);
}
};
})(this));
return this;
}n/a
_release = function (connection) {
if (this.transaction) {
return this.transaction.next();
} else if (this.pstatement) {
return this.pstatement.next();
} else {
return this.connection.pool.release(connection);
}
}...
} else {
if (doReturn && !hasReturned) {
if (connection != null) {
for (event in errorHandlers) {
handler = errorHandlers[event];
connection.removeListener(event, handler);
}
_this._release(connection);
}
hasReturned = true;
if (typeof callback === "function") {
callback(e);
}
}
}
..._template = function (method, strings, values) {
var command, i, index, len, value;
command = [strings[0]];
for (index = i = 0, len = values.length; i < len; index = ++i) {
value = values[index];
this.input("param" + (index + 1), value);
command.push("@param" + (index + 1), strings[index + 1]);
}
return this[method](command.join(''));
}n/a
batch = function (batch, callback) {
var ref1;
if (this.stream == null) {
this.stream = (ref1 = this.connection) != null ? ref1.config.stream : void 0;
}
this.rowsAffected = 0;
if (this.stream || (callback != null)) {
return this._batch(batch, callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._batch(batch, function(err, recordset) {
if (err) {
return reject(err);
}
return resolve(recordset);
});
};
})(this));
}...
- **batch** - T-SQL command to be executed.
- **callback(err, recordset)** - A callback which is called after execution has completed, or an error has occurred. Optional. If
omitted, returns [Promise](#promises).
__Example__
```javascript
var request = new sql.Request();
request.batch('create procedure #temporary as select * from table', function
(err, recordset) {
// ... error checks
});
```
__Errors__
- ETIMEOUT (`RequestError`) - Request timeout.
- EREQUEST (`RequestError`) - *Message from SQL Server*
...bulk = function (table, callback) {
var ref1;
if (this.stream == null) {
this.stream = (ref1 = this.connection) != null ? ref1.config.stream : void 0;
}
if (this.stream || (callback != null)) {
return this._bulk(table, callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._bulk(table, function(err, rowCount) {
if (err) {
return reject(err);
}
return resolve(rowCount);
});
};
})(this));
}...
var table = new sql.Table('table_name'); // or temporary table, e.g. #temptable
table.create = true;
table.columns.add('a', sql.Int, {nullable: true, primary: true});
table.columns.add('b', sql.VarChar(50), {nullable: false});
table.rows.add(777, 'test');
var request = new sql.Request();
request.bulk(table, function(err, rowCount) {
// ... error checks
});
```
**IMPORTANT**: Always indicate whether the column is nullable or not!
**TIP**: If you set `table.create` to `true`, module will check if the table exists before it start sending data. If it doesn'
;t, it will automatically create it. You can specify primary key columns by setting `primary: true` to column's options. Primary
key constraint on multiple columns is supported.
...cancel = function () {
this.canceled = true;
this.connection.driver.Request.prototype.cancel.call(this);
return this;
}...
console.log(err instanceof sql.RequestError); // true
console.log(err.message); // Cancelled.
console.log(err.code); // ECANCEL
// ...
});
request.cancel();
```
## Transaction
**IMPORTANT:** always use `Transaction` class to create transactions - it ensures that all your requests are executed on one connection
. Once you call `begin`, a single connection is acquired from the connection pool and all subsequent requests (initialized with
the `Transaction` object) are executed exclusively on this connection. Transaction also contains a queue to make sure your requests
are executed in series. After you call `commit` or `rollback`, connection is then released back to the connection pool.
```javascript
...function Request(connection) {
if (connection instanceof Transaction) {
this.transaction = connection;
this.connection = connection.connection;
} else if (connection instanceof PreparedStatement) {
this.pstatement = connection;
this.connection = connection.connection;
} else if (connection instanceof Connection) {
this.connection = connection;
} else {
this.connection = global_connection;
}
this.parameters = {};
}n/a
execute = function (command, callback) {
var ref1;
if (this.stream == null) {
this.stream = (ref1 = this.connection) != null ? ref1.config.stream : void 0;
}
this.rowsAffected = 0;
if (this.stream || (callback != null)) {
return this._execute(command, callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._execute(command, function(err, recordset) {
if (err) {
return reject(err);
}
return resolve(recordset);
});
};
})(this));
}...
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name').then(function(recordsets) {
console.dir(recordsets);
}).catch(function(err) {
// ... execute error checks
});
// ES6 Tagged template literals (experimental)
...input = function (name, type, value) {
if (/(--| |\/\*|\*\/|')/.test(name)) {
throw new RequestError("SQL injection warning for param '" + name + "'", 'EINJECT');
}
if (arguments.length === 1) {
throw new RequestError("Invalid number of arguments. At least 2 arguments expected.", 'EARGS');
} else if (arguments.length === 2) {
value = type;
type = getTypeByValue(value);
}
if ((value != null ? value.valueOf : void 0) && !(value instanceof Date)) {
value = value.valueOf();
}
if (value === void 0) {
value = null;
}
if (value !== value) {
value = null;
}
if (type instanceof Function) {
type = type();
}
this.parameters[name] = {
name: name,
type: type.type,
io: 1,
value: value,
length: type.length,
scale: type.scale,
precision: type.precision,
tvpType: type.tvpType
};
return this;
}...
}).catch(function(err) {
// ... query error checks
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name').then(function(recordsets) {
console.dir(recordsets);
}).catch(function(err) {
// ... execute error checks
});
...output = function (name, type, value) {
if (!type) {
type = TYPES.NVarChar;
}
if (/(--| |\/\*|\*\/|')/.test(name)) {
throw new RequestError("SQL injection warning for param '" + name + "'", 'EINJECT');
}
if (type === TYPES.Text || type === TYPES.NText || type === TYPES.Image) {
throw new RequestError("Deprecated types (Text, NText, Image) are not supported as OUTPUT parameters.", 'EDEPRECATED');
}
if ((value != null ? value.valueOf : void 0) && !(value instanceof Date)) {
value = value.valueOf();
}
if (value === void 0) {
value = null;
}
if (value !== value) {
value = null;
}
if (type instanceof Function) {
type = type();
}
this.parameters[name] = {
name: name,
type: type.type,
io: 2,
value: value,
length: type.length,
scale: type.scale,
precision: type.precision
};
return this;
}...
// ... query error checks
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name').then(function(recordsets) {
console.dir(recordsets);
}).catch(function(err) {
// ... execute error checks
});
// ES6 Tagged template literals (experimental)
...pipe = function (stream) {
this.stream = true;
this.on('row', stream.write.bind(stream));
this.on('error', stream.emit.bind(stream, 'error'));
this.on('done', function() {
return setImmediate(function() {
return stream.end();
});
});
stream.emit('pipe', this);
return stream;
}...
- **stream** - Writable stream in object mode.
__Example__
```javascript
var request = new sql.Request();
request.pipe(stream);
request.query('select * from mytable');
stream.on('error', function(err) {
// ...
});
stream.on('finish', function() {
// ...
});
...query = function (command, callback) {
var ref1;
if (this.stream == null) {
this.stream = (ref1 = this.connection) != null ? ref1.config.stream : void 0;
}
this.rowsAffected = 0;
if (this.stream || (callback != null)) {
return this._query(command, callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._query(command, function(err, recordsets) {
if (err) {
return reject(err);
}
return resolve(recordsets);
});
};
})(this));
}...
```javascript
var sql = require('mssql');
sql.connect("mssql://username:password@localhost/database").then(function() {
// Query
new sql.Request().query('select * from mytable').then(function(recordset) {
console.dir(recordset);
}).catch(function(err) {
// ... query error checks
});
// Stored Procedure
...function RequestError(message, code) {
var err, ref1, ref10, ref11, ref2, ref3, ref4, ref5, ref6, ref7, ref8, ref9;
if (!(this instanceof RequestError)) {
if (message instanceof Error) {
err = new RequestError(message.message, (ref1 = message.code) != null ? ref1 : code);
err.number = (ref2 = (ref3 = message.info) != null ? ref3.number : void 0) != null ? ref2 : message.code;
err.lineNumber = (ref4 = message.info) != null ? ref4.lineNumber : void 0;
err.state = (ref5 = (ref6 = message.info) != null ? ref6.state : void 0) != null ? ref5 : message.sqlstate;
err["class"] = (ref7 = (ref8 = message.info) != null ? ref8["class"] : void 0) != null ? ref7 : (ref9 = message.info) != null
? ref9.severity : void 0;
err.serverName = (ref10 = message.info) != null ? ref10.serverName : void 0;
err.procName = (ref11 = message.info) != null ? ref11.procName : void 0;
Object.defineProperty(err, 'originalError', {
value: message
});
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new RequestError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
if (code != null) {
this.code = code;
}
RequestError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
function captureStackTrace() { [native code] }n/a
function RequestError(message, code) {
var err, ref1, ref10, ref11, ref2, ref3, ref4, ref5, ref6, ref7, ref8, ref9;
if (!(this instanceof RequestError)) {
if (message instanceof Error) {
err = new RequestError(message.message, (ref1 = message.code) != null ? ref1 : code);
err.number = (ref2 = (ref3 = message.info) != null ? ref3.number : void 0) != null ? ref2 : message.code;
err.lineNumber = (ref4 = message.info) != null ? ref4.lineNumber : void 0;
err.state = (ref5 = (ref6 = message.info) != null ? ref6.state : void 0) != null ? ref5 : message.sqlstate;
err["class"] = (ref7 = (ref8 = message.info) != null ? ref8["class"] : void 0) != null ? ref7 : (ref9 = message.info) != null
? ref9.severity : void 0;
err.serverName = (ref10 = message.info) != null ? ref10.serverName : void 0;
err.procName = (ref11 = message.info) != null ? ref11.procName : void 0;
Object.defineProperty(err, 'originalError', {
value: message
});
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new RequestError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
if (code != null) {
this.code = code;
}
RequestError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
SmallDateTime = function () {
return {
type: TYPES.SmallDateTime
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...SmallInt = function () {
return {
type: TYPES.SmallInt
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...SmallMoney = function () {
return {
type: TYPES.SmallMoney
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...TVP = function (tvpType) {
return {
type: TYPES.TVP,
tvpType: tvpType
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...BigInt = function () {
return {
type: TYPES.BigInt
};
}n/a
Binary = function (length) {
return {
type: TYPES.Binary,
length: length
};
}n/a
Bit = function () {
return {
type: TYPES.Bit
};
}n/a
Char = function (length) {
return {
type: TYPES.Char,
length: length
};
}n/a
Date = function () {
return {
type: TYPES.Date
};
}n/a
DateTime = function () {
return {
type: TYPES.DateTime
};
}n/a
DateTime2 = function (scale) {
return {
type: TYPES.DateTime2,
scale: scale
};
}...
request.output("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.Decimal, 155.33); // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33); // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33); // decimal(10, 2)
request.input("name", sql.DateTime2, new Date()); // datetime2(7)
request.input("name", sql.DateTime2(5), new Date()); // datetime2(5)
```
List of supported data types:
```
sql.Bit
sql.BigInt
...DateTimeOffset = function (scale) {
return {
type: TYPES.DateTimeOffset,
scale: scale
};
}n/a
Decimal = function (precision, scale) {
return {
type: TYPES.Decimal,
precision: precision,
scale: scale
};
}...
request.input("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.VarChar(50), "abc"); // varchar(50)
request.input("name", sql.VarChar(sql.MAX), "abc"); // varchar(MAX)
request.output("name", sql.VarChar); // varchar(8000)
request.output("name", sql.VarChar, "abc"); // varchar(3)
request.input("name", sql.Decimal, 155.33); // decimal(18, 0)
request.input("name", sql.Decimal(10), 155.33); // decimal(10, 0)
request.input("name", sql.Decimal(10, 2), 155.33); // decimal(10, 2)
request.input("name", sql.DateTime2, new Date()); // datetime2(7)
request.input("name", sql.DateTime2(5), new Date()); // datetime2(5)
```
List of supported data types:
...Float = function () {
return {
type: TYPES.Float
};
}n/a
Geography = function () {
return {
type: TYPES.Geography
};
}n/a
Geometry = function () {
return {
type: TYPES.Geometry
};
}n/a
Image = function () {
return {
type: TYPES.Image
};
}n/a
Int = function () {
return {
type: TYPES.Int
};
}n/a
Money = function () {
return {
type: TYPES.Money
};
}n/a
NChar = function (length) {
return {
type: TYPES.NChar,
length: length
};
}n/a
NText = function () {
return {
type: TYPES.NText
};
}n/a
NVarChar = function (length) {
return {
type: TYPES.NVarChar,
length: length
};
}n/a
Numeric = function (precision, scale) {
return {
type: TYPES.Numeric,
precision: precision,
scale: scale
};
}n/a
Real = function () {
return {
type: TYPES.Real
};
}n/a
SmallDateTime = function () {
return {
type: TYPES.SmallDateTime
};
}n/a
SmallInt = function () {
return {
type: TYPES.SmallInt
};
}n/a
SmallMoney = function () {
return {
type: TYPES.SmallMoney
};
}n/a
TVP = function (tvpType) {
return {
type: TYPES.TVP,
tvpType: tvpType
};
}n/a
Text = function () {
return {
type: TYPES.Text
};
}n/a
Time = function (scale) {
return {
type: TYPES.Time,
scale: scale
};
}n/a
TinyInt = function () {
return {
type: TYPES.TinyInt
};
}n/a
UDT = function () {
return {
type: TYPES.UDT
};
}n/a
UniqueIdentifier = function () {
return {
type: TYPES.UniqueIdentifier
};
}n/a
VarBinary = function (length) {
return {
type: TYPES.VarBinary,
length: length
};
}n/a
VarChar = function (length) {
return {
type: TYPES.VarChar,
length: length
};
}...
// ... query error checks
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name').then(function(recordsets) {
console.dir(recordsets);
}).catch(function(err) {
// ... execute error checks
});
// ES6 Tagged template literals (experimental)
...Variant = function () {
return {
type: TYPES.Variant
};
}n/a
Xml = function () {
return {
type: TYPES.Xml
};
}n/a
function Table(name) {
var ref1;
if (name) {
ref1 = Table.parseName(name), this.name = ref1.name, this.schema = ref1.schema, this.database = ref1.database;
this.path = "" + (this.database ? "[" + this.database + "]." : "") + (this.schema ? "[" + this.schema + "]." : "") + "[" + this
.name + "]";
this.temporary = this.name.charAt(0) === '#';
}
this.columns = [];
this.rows = [];
Object.defineProperty(this.columns, "add", {
value: function(name, column, options) {
if (options == null) {
options = {};
}
if (column == null) {
throw new Error("Column data type is not defined.");
}
if (column instanceof Function) {
column = column();
}
column.name = name;
column.nullable = options.nullable;
column.primary = options.primary;
return this.push(column);
}
});
Object.defineProperty(this.rows, "add", {
value: function() {
var values;
values = 1 <= arguments.length ? slice.call(arguments, 0) : [];
return this.push(values);
}
});
}...
- **table** - `sql.Table` instance.
- **callback(err, rowCount)** - A callback which is called after bulk insert has completed, or an error has occurred. Optional.
If omitted, returns [Promise](#promises).
__Example__
```javascript
var table = new sql.Table('table_name'); // or temporary table, e.g. #temptable
table.create = true;
table.columns.add('a', sql.Int, {nullable: true, primary: true});
table.columns.add('b', sql.VarChar(50), {nullable: false});
table.rows.add(777, 'test');
var request = new sql.Request();
request.bulk(table, function(err, rowCount) {
...fromRecordset = function (recordset, name) {
var col, i, j, len, len1, ref1, ref2, row, t;
t = new this(name);
ref1 = recordset.columns;
for (name in ref1) {
col = ref1[name];
t.columns.add(name, {
type: col.type,
length: col.length,
scale: col.scale,
precision: col.precision
}, {
nullable: col.nullable
});
}
if (t.columns.length === 1 && t.columns[0].name === JSON_COLUMN_ID) {
for (i = 0, len = recordset.length; i < len; i++) {
row = recordset[i];
t.rows.add(JSON.stringify(row));
}
} else {
for (j = 0, len1 = recordset.length; j < len1; j++) {
row = recordset[j];
(ref2 = t.rows).add.apply(ref2, (function() {
var k, len2, ref2, results;
ref2 = t.columns;
results = [];
for (k = 0, len2 = ref2.length; k < len2; k++) {
col = ref2[k];
results.push(row[col.name]);
}
return results;
})());
}
}
return t;
}...
Object.defineProperty(recordset, 'columns', {
enumerable: false,
value: columns
});
Object.defineProperty(recordset, 'toTable', {
enumerable: false,
value: function() {
return Table.fromRecordset(this);
}
});
recordsets.push(recordset);
}
recordset = [];
return columns = {};
};
...parseName = function (name) {
var buffer, char, cursor, escaped, length, path;
length = name.length;
cursor = -1;
buffer = '';
escaped = false;
path = [];
while (++cursor < length) {
char = name.charAt(cursor);
if (char === '[') {
if (escaped) {
buffer += char;
} else {
escaped = true;
}
} else if (char === ']') {
if (escaped) {
escaped = false;
} else {
throw new Error("Invalid table name.");
}
} else if (char === '.') {
if (escaped) {
buffer += char;
} else {
path.push(buffer);
buffer = '';
}
} else {
buffer += char;
}
}
if (buffer) {
path.push(buffer);
}
switch (path.length) {
case 1:
return {
name: path[0],
schema: null,
database: null
};
case 2:
return {
name: path[1],
schema: path[0],
database: null
};
case 3:
return {
name: path[2],
schema: path[1],
database: path[0]
};
default:
throw new Error("Invalid table name.");
}
}n/a
_makeBulk = function () {
var col, i, len, ref1;
ref1 = this.columns;
for (i = 0, len = ref1.length; i < len; i++) {
col = ref1[i];
switch (col.type) {
case TYPES.Xml:
col.type = TYPES.NVarChar(MAX).type;
break;
case TYPES.UDT:
case TYPES.Geography:
case TYPES.Geometry:
col.type = TYPES.VarBinary(MAX).type;
}
}
return this;
}...
/*
Bulk load.
*/
TediousRequest.prototype.bulk = function(table, callback) {
var errorHandlers, errors, handleError, handleInfo, hasReturned, started;
table._makeBulk();
if (!table.name) {
process.nextTick(function() {
return callback(RequestError("Table name must be specified for bulk insert.", "ENAME"));
});
}
if (table.name.charAt(0) === '@') {
process.nextTick(function() {
...declare = function () {
var cols, pkey;
pkey = this.columns.filter(function(col) {
return col.primary === true;
}).map(function(col) {
return col.name;
});
cols = this.columns.map(function(col) {
var def;
def = ["[" + col.name + "] " + (declare(col.type, col))];
if (col.nullable === true) {
def.push("null");
} else if (col.nullable === false) {
def.push("not null");
}
if (col.primary === true && pkey.length === 1) {
def.push("primary key");
}
return def.join(' ');
});
return "create table " + this.path + " (" + (cols.join(', ')) + (pkey.length > 1 ? ", constraint PK_" + (this.temporary ? this
.name.substr(1) : this.name) + " primary key (" + (pkey.join(', ')) + ")" : "") + ")";
}...
}
if (table.create) {
if (table.temporary) {
objectid = "tempdb..[" + table.name + "]";
} else {
objectid = table.path;
}
req = new tds.Request("if object_id('" + (objectid.replace(/'/g, '\'\'')) + "'
;) is null " + (table.declare()), function(err) {
if (err) {
return done(err);
}
return connection.execBulkLoad(bulk);
});
return connection.execSqlBatch(req);
} else {
...Text = function () {
return {
type: TYPES.Text
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Time = function (scale) {
return {
type: TYPES.Time,
scale: scale
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...TinyInt = function () {
return {
type: TYPES.TinyInt
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...function Transaction(connection) {
this._abort = bind(this._abort, this);
this.connection = connection != null ? connection : global_connection;
this._queue = [];
}...
```
## Transaction
**IMPORTANT:** always use `Transaction` class to create transactions - it ensures that all your requests are executed on one connection
. Once you call `begin`, a single connection is acquired from the connection pool and all subsequent requests (initialized with
the `Transaction` object) are executed exclusively on this connection. Transaction also contains a queue to make sure your requests
are executed in series. After you call `commit` or `rollback`, connection is then released back to the connection pool.
```javascript
var transaction = new sql.Transaction(/* [connection] */);
```
If you omit connection argument, global connection is used instead.
__Example__
```javascript
...function EventEmitter() {
EventEmitter.init.call(this);
}n/a
init = function () {
this.domain = null;
if (EventEmitter.usingDomains) {
// if there is an active domain, then attach to it.
domain = domain || require('domain');
if (domain.active && !(this instanceof domain.Domain)) {
this.domain = domain.active;
}
}
if (!this._events || this._events === Object.getPrototypeOf(this)._events) {
this._events = new EventHandlers();
this._eventsCount = 0;
}
this._maxListeners = this._maxListeners || undefined;
}n/a
listenerCount = function (emitter, type) {
if (typeof emitter.listenerCount === 'function') {
return emitter.listenerCount(type);
} else {
return listenerCount.call(emitter, type);
}
}n/a
_abort = function () {
return this.connection.driver.Transaction.prototype._abort.call(this);
}n/a
_begin = function (isolationLevel, callback) {
if (isolationLevel != null) {
this.isolationLevel = isolationLevel;
}
if (this._pooledConnection) {
callback(new TransactionError("Transaction has already begun.", 'EALREADYBEGUN'));
return this;
}
this.connection.driver.Transaction.prototype.begin.call(this, (function(_this) {
return function(err) {
if (!err) {
_this.emit('begin');
}
return callback(err);
};
})(this));
return this;
}n/a
_commit = function (callback) {
if (!this._pooledConnection) {
callback(new TransactionError("Transaction has not begun. Call begin() first.", 'ENOTBEGUN'));
return this;
}
if (this._working) {
callback(new TransactionError("Can't commit transaction. There is a request in progress.", 'EREQINPROG'));
return this;
}
if (this._queue.length) {
callback(new TransactionError("Can't commit transaction. There are request in queue.", 'EREQINPROG'));
return this;
}
this.connection.driver.Transaction.prototype.commit.call(this, (function(_this) {
return function(err) {
if (!err) {
_this.emit('commit');
}
return callback(err);
};
})(this));
return this;
}n/a
_rollback = function (callback) {
if (this._aborted) {
callback(new TransactionError("Transaction has been aborted.", 'EABORT'));
return this;
}
if (!this._pooledConnection) {
callback(new TransactionError("Transaction has not begun. Call begin() first.", 'ENOTBEGUN'));
return this;
}
if (this._working) {
callback(new TransactionError("Can't rollback transaction. There is a request in progress.", 'EREQINPROG'));
return this;
}
if (this._queue.length) {
this._aborted = true;
}
this.connection.driver.Transaction.prototype.rollback.call(this, (function(_this) {
return function(err) {
if (!err) {
_this.emit('rollback', _this._aborted);
}
return callback(err);
};
})(this));
return this;
}n/a
begin = function (isolationLevel, callback) {
if (isolationLevel instanceof Function) {
callback = isolationLevel;
isolationLevel = void 0;
}
if (callback != null) {
return this._begin(isolationLevel, callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._begin(isolationLevel, function(err) {
if (err) {
return reject(err);
}
return resolve(_this);
});
};
})(this));
}...
If you omit connection argument, global connection is used instead.
__Example__
```javascript
var transaction = new sql.Transaction(/* [connection] */);
transaction.begin(function(err) {
// ... error checks
var request = new sql.Request(transaction);
request.query('insert into mytable (mycolumn) values (12345)', function(err, recordset) {
// ... error checks
transaction.commit(function(err, recordset) {
...commit = function (callback) {
if (callback != null) {
return this._commit(callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._commit(function(err) {
if (err) {
return reject(err);
}
return resolve();
});
};
})(this));
}...
transaction.begin(function(err) {
// ... error checks
var request = new sql.Request(transaction);
request.query('insert into mytable (mycolumn) values (12345)', function(err, recordset) {
// ... error checks
transaction.commit(function(err, recordset) {
// ... error checks
console.log("Transaction committed.");
});
});
});
```
...function Transaction(connection) {
this._abort = bind(this._abort, this);
this.connection = connection != null ? connection : global_connection;
this._queue = [];
}n/a
next = function () {
var toAbort;
if (this._aborted) {
toAbort = this._queue;
this._queue = [];
process.nextTick((function(_this) {
return function() {
var results;
results = [];
while (toAbort.length) {
results.push(toAbort.shift()(new TransactionError("Transaction aborted.", "EABORT")));
}
return results;
};
})(this));
}
this._working = false;
if (this._queue.length) {
process.nextTick((function(_this) {
return function() {
if (_this._aborted) {
return _this.next();
}
_this._working = true;
return _this._queue.shift()(null, _this._pooledConnection);
};
})(this));
}
return this;
}n/a
queue = function (callback) {
if (!this._pooledConnection) {
callback(new TransactionError("Transaction has not begun. Call begin() first.", 'ENOTBEGUN'));
return this;
}
if (this._working || this._queue.length) {
this._queue.push(callback);
} else {
this._working = true;
callback(null, this._pooledConnection);
}
return this;
}n/a
request = function () {
return new Request(this);
}...
}
var connection1 = new sql.Connection(config, function(err) {
// ... error checks
// Query
var request = new sql.Request(connection1); // or: var request = connection1.request();
request.query('select 1 as number', function(err, recordset) {
// ... error checks
console.dir(recordset);
});
});
...rollback = function (callback) {
if (callback != null) {
return this._rollback(callback);
}
return new module.exports.Promise((function(_this) {
return function(resolve, reject) {
return _this._rollback(function(err) {
if (err) {
return reject(err);
}
return resolve();
});
};
})(this));
}...
var request = new sql.Request(transaction);
request.query('insert into mytable (bitcolumn) values (2)', function(err, recordset) {
// insert should fail because of invalid value
if (err) {
if (!rolledBack) {
transaction.rollback(function(err) {
// ... error checks
});
}
} else {
transaction.commit(function(err) {
// ... error checks
});
...function TransactionError(message, code) {
var err;
if (!(this instanceof TransactionError)) {
if (message instanceof Error) {
err = new TransactionError(message.message, message.code);
Object.defineProperty(err, 'originalError', {
value: message
});
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new TransactionError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
if (code != null) {
this.code = code;
}
TransactionError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
function captureStackTrace() { [native code] }n/a
function TransactionError(message, code) {
var err;
if (!(this instanceof TransactionError)) {
if (message instanceof Error) {
err = new TransactionError(message.message, message.code);
Object.defineProperty(err, 'originalError', {
value: message
});
Error.captureStackTrace(err, arguments.callee);
return err;
} else {
err = new TransactionError(message);
Error.captureStackTrace(err, arguments.callee);
return err;
}
}
this.name = this.constructor.name;
this.message = message;
if (code != null) {
this.code = code;
}
TransactionError.__super__.constructor.call(this);
Error.captureStackTrace(this, this.constructor);
}n/a
UDT = function () {
return {
type: TYPES.UDT
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...UniqueIdentifier = function () {
return {
type: TYPES.UniqueIdentifier
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...VarBinary = function (length) {
return {
type: TYPES.VarBinary,
length: length
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...VarChar = function (length) {
return {
type: TYPES.VarChar,
length: length
};
}...
// ... query error checks
});
// Stored Procedure
new sql.Request()
.input('input_parameter', sql.Int, value)
.output('output_parameter', sql.VarChar(50))
.execute('procedure_name').then(function(recordsets) {
console.dir(recordsets);
}).catch(function(err) {
// ... execute error checks
});
// ES6 Tagged template literals (experimental)
...inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Variant = function () {
return {
type: TYPES.Variant
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...Xml = function () {
return {
type: TYPES.Xml
};
}n/a
inspect = function () {
return "[sql." + key + "]";
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...parse = function (string) {
var buffer, char, cursor, original, param, parsed, parsing, quotes;
cursor = 0;
parsing = 'name';
param = null;
buffer = '';
quotes = null;
parsed = {};
original = {};
Object.defineProperty(parsed, '__original__', {
value: original
});
Object.defineProperty(parsed, 'toString', {
value: function() {
var key, value;
return ((function() {
var ref, ref1, ref2, ref3, results;
results = [];
for (key in this) {
value = this[key];
if (indexOf.call(IGNORE_KEYS, key) < 0) {
results.push(original[key].name + "=" + ((ref = (ref1 = original[key].escape) != null ? ref1[0] : void 0) != null ?
ref : '') + value + ((ref2 = (ref3 = original[key].escape) != null ? ref3[1] : void 0) != null ? ref2 : ''));
}
}
return results;
}).call(this)).join(';');
}
});
while (cursor < string.length) {
char = string.charAt(cursor);
switch (char) {
case '=':
if (parsing === 'name') {
buffer = buffer.trim();
param = buffer.toLowerCase();
original[param] = {
name: buffer
};
parsing = 'value';
buffer = '';
} else {
buffer += char;
}
break;
case '\'':
case '"':
if (parsing === 'value') {
if (!buffer.trim().length) {
original[param].escape = [char, char];
quotes = char;
buffer = '';
} else {
if (quotes) {
if (char === quotes) {
if (char === string.charAt(cursor + 1)) {
buffer += char;
cursor++;
} else {
parsed[param] = buffer;
param = null;
parsing = null;
buffer = '';
quotes = null;
}
} else {
buffer += char;
}
} else {
buffer += char;
}
}
} else {
throw new Error("Invalid connection string.");
}
break;
case '{':
if (parsing === 'value') {
if (!buffer.trim().length) {
original[param].escape = ['{', '}'];
quotes = '{}';
buffer = '';
} else {
buffer += char;
}
} else {
throw new Error("Invalid connection string.");
}
break;
case '}':
if (parsing === 'value') {
if (quotes === '{}') {
parsed[param] = buffer;
param = null;
parsing = null;
buffer = '';
quotes = null;
} else {
buffer += char;
}
} else {
throw new Error("Invalid connection string.");
}
break;
case ';':
if (parsing === 'value') {
if (quotes) {
buffer += char;
} else {
parsed[param] = buffer;
param = null;
parsing = 'name';
buffer = '';
}
} else {
buffer = '';
parsing = 'name';
}
break;
default:
buffer += char;
}
cursor++;
}
if (parsing === 'value') {
parsed[param] = buffer;
}
return parsed;
}...
} catch (error) {
ex = error;
console.error("Failed to load config file. " + ex.message);
process.exit(1);
}
try {
config = JSON.parse(config);
} catch (error1) {
ex = error1;
console.error("Failed to parse config file. " + ex.message);
process.exit(1);
}
buffer = [];
...resolve = function (string) {
var config, parsed, ref, ref1, ref10, ref11, ref12, ref13, ref14, ref15, ref16, ref17, ref2, ref3, ref4, ref5, ref6, ref7, ref8
, ref9, server, user;
if (/^(mssql|tedious|msnodesql|tds)\:\/\//i.test(string)) {
parsed = parseConnectionURI(string);
} else {
parsed = parseConnectionString(string);
}
if (parsed.driver === 'msnodesql') {
parsed.driver = 'SQL Server Native Client 11.0';
if ((ref = parsed.__original__) != null) {
ref.driver = {
name: 'Driver',
escape: ['{', '}']
};
}
return {
driver: 'msnodesql',
connectionString: parsed.toString()
};
}
user = (ref1 = parsed.uid) != null ? ref1 : parsed['user id'];
server = (ref2 = (ref3 = (ref4 = (ref5 = parsed.server) != null ? ref5 : parsed.address) != null ? ref4 : parsed.addr) != null
? ref3 : parsed['data source']) != null ? ref2 : parsed['network address'];
config = {
driver: parsed.driver,
password: (ref6 = parsed.pwd) != null ? ref6 : parsed.password,
database: (ref7 = parsed.database) != null ? ref7 : parsed['initial catalog'],
connectionTimeout: (ref8 = (ref9 = (ref10 = parsed.connectionTimeout) != null ? ref10 : parsed.timeout) != null ? ref9 : parsed
['connect timeout']) != null ? ref8 : parsed['connection timeout'],
requestTimeout: (ref11 = parsed.requestTimeout) != null ? ref11 : parsed['request timeout'],
stream: (ref12 = (ref13 = parsed.stream) != null ? ref13.toLowerCase() : void 0) === 'true' || ref12 === 'yes' || ref12 === '
1',
options: {
encrypt: (ref14 = (ref15 = parsed.encrypt) != null ? ref15.toLowerCase() : void 0) === 'true' || ref14 === 'yes' || ref14 === '
1'
}
};
if (parsed.useUTC != null) {
config.options.useUTC = (ref16 = parsed.useUTC.toLowerCase()) === 'true' || ref16 === 'yes' || ref16 === '1';
}
if (config.connectionTimeout != null) {
config.connectionTimeout = parseInt(config.connectionTimeout);
}
if (config.requestTimeout != null) {
config.requestTimeout = parseInt(config.requestTimeout);
}
if (/^(.*)\\(.*)$/.exec(user)) {
config.domain = RegExp.$1;
user = RegExp.$2;
}
if (server) {
server = server.trim();
if (/^np\:/i.test(server)) {
throw new Error("Connection via Named Pipes is not supported.");
}
if (/^tcp\:/i.test(server)) {
server = server.substr(4);
}
if (/^(.*)\\(.*)$/.exec(server)) {
server = RegExp.$1;
config.options.instanceName = RegExp.$2;
}
if (/^(.*),(.*)$/.exec(server)) {
server = RegExp.$1.trim();
config.port = parseInt(RegExp.$2.trim());
}
if ((ref17 = server.toLowerCase()) === '.' || ref17 === '(.)' || ref17 === '(localdb)' || ref17 === '(local)') {
server = 'localhost';
}
}
config.user = user;
config.server = server;
return config;
}...
cfgPath = process.argv[2];
if (!cfgPath) {
cfgPath = process.cwd();
}
cfgPath = path.resolve(cfgPath);
if (fs.lstatSync(cfgPath).isDirectory()) {
cfgPath = path.resolve(cfgPath, './.mssql.json');
}
if (!fs.existsSync(cfgPath)) {
console.error("Config file not found.");
...cast = function (value, type, options) {
var ns, ref, scale;
if (value == null) {
return null;
}
switch (typeof value) {
case 'string':
return "N'" + (value.replace(/'/g, '\'\'')) + "'";
case 'number':
return value;
case 'boolean':
if (value) {
return 1;
} else {
return 0;
}
case 'object':
if (value instanceof Date) {
ns = value.getUTCMilliseconds() / 1000;
if (value.nanosecondDelta != null) {
ns += value.nanosecondDelta;
}
scale = (ref = options.scale) != null ? ref : 7;
if (scale > 0) {
ns = String(ns).substr(1, scale + 1);
} else {
ns = "";
}
return "N'" + (value.getUTCFullYear()) + "-" + (zero(value.getUTCMonth() + 1)) + "-" + (zero(value.getUTCDate())) + " " + (
zero(value.getUTCHours())) + ":" + (zero(value.getUTCMinutes())) + ":" + (zero(value.getUTCSeconds())) + ns + "'";
} else if (Buffer.isBuffer(value)) {
return "0x" + (value.toString('hex'));
} else {
return null;
}
break;
default:
return null;
}
}n/a
declare = function (type, options) {
var ref, ref1, ref2, ref3, ref4, ref5;
switch (type) {
case TYPES.VarChar:
case TYPES.NVarChar:
case TYPES.VarBinary:
return type.declaration + " (" + (options.length > 8000 ? 'MAX' : (ref = options.length) != null ? ref : 'MAX') + ")";
case TYPES.NVarChar:
return type.declaration + " (" + (options.length > 4000 ? 'MAX' : (ref1 = options.length) != null ? ref1 : 'MAX') + ")";
case TYPES.Char:
case TYPES.NChar:
case TYPES.Binary:
return type.declaration + " (" + ((ref2 = options.length) != null ? ref2 : 1) + ")";
case TYPES.Decimal:
case TYPES.Numeric:
return type.declaration + " (" + ((ref3 = options.precision) != null ? ref3 : 18) + ", " + ((ref4 = options.scale) != null
? ref4 : 0) + ")";
case TYPES.Time:
case TYPES.DateTime2:
case TYPES.DateTimeOffset:
return type.declaration + " (" + ((ref5 = options.scale) != null ? ref5 : 7) + ")";
case TYPES.TVP:
return options.tvpType + " readonly";
default:
return type.declaration;
}
}...
}
if (table.create) {
if (table.temporary) {
objectid = "tempdb..[" + table.name + "]";
} else {
objectid = table.path;
}
req = new tds.Request("if object_id('" + (objectid.replace(/'/g, '\'\'')) + "'
;) is null " + (table.declare()), function(err) {
if (err) {
return done(err);
}
return connection.execBulkLoad(bulk);
});
return connection.execSqlBatch(req);
} else {
...register = function (jstype, sqltype) {
var i, index, item, len;
for (index = i = 0, len = this.length; i < len; index = ++i) {
item = this[index];
if (!(item.js === jstype)) {
continue;
}
this.splice(index, 1);
break;
}
this.push({
js: jstype,
sql: sqltype
});
return null;
}...
- `sql.Table` -> `sql.TVP`
Default data type for unknown object is `sql.NVarChar`.
You can define your own type map.
```javascript
sql.map.register(MyClass, sql.Text);
```
You can also overwrite the default type map.
```javascript
sql.map.register(Number, sql.BigInt);
```
...function String() { [native code] }n/a
sql = function (length) {
return {
type: TYPES.NVarChar,
length: length
};
}n/a
entityify = function (){return this.replace(/&/g,"&").replace(/</g,"<").replace(/>/g,">"
)}n/a
isAlpha = function ( ){return this>="a"&&this<="z\uffff"||this>="A"&&this<="Z\uffff"}n/a
isDigit = function (){return this>="0"&&
this<="9"}n/a
supplant = function (e){return this.replace(/\{([^{}]*)\}/g,function(t,n){var r=e[n];return typeof
r=="string"||typeof r=="number"?r:t})}n/a
function Number() { [native code] }n/a
sql = function () {
return {
type: TYPES.Int
};
}n/a
function Boolean() { [native code] }n/a
sql = function () {
return {
type: TYPES.Bit
};
}n/a
function Date() { [native code] }n/a
sql = function () {
return {
type: TYPES.DateTime
};
}n/a
function Buffer(arg, encodingOrOffset, length) {
// Common case.
if (typeof arg === 'number') {
if (typeof encodingOrOffset === 'string') {
throw new Error(
'If encoding is specified then the first argument must be a string'
);
}
return Buffer.allocUnsafe(arg);
}
return Buffer.from(arg, encodingOrOffset, length);
}n/a
sql = function (length) {
return {
type: TYPES.VarBinary,
length: length
};
}n/a
function asciiSlice() { [native code] }n/a
function asciiWrite() { [native code] }n/a
function base64Slice() { [native code] }n/a
function base64Write() { [native code] }n/a
function compare(target, start, end, thisStart, thisEnd) {
if (!(target instanceof Buffer))
throw new TypeError('Argument must be a Buffer');
if (arguments.length === 1)
return compare_(this, target);
if (start === undefined)
start = 0;
else if (start < 0)
throw new RangeError('out of range index');
else
start >>>= 0;
if (end === undefined)
end = target.length;
else if (end > target.length)
throw new RangeError('out of range index');
else
end >>>= 0;
if (thisStart === undefined)
thisStart = 0;
else if (thisStart < 0)
throw new RangeError('out of range index');
else
thisStart >>>= 0;
if (thisEnd === undefined)
thisEnd = this.length;
else if (thisEnd > this.length)
throw new RangeError('out of range index');
else
thisEnd >>>= 0;
if (thisStart >= thisEnd)
return (start >= end ? 0 : -1);
else if (start >= end)
return 1;
return compareOffset(this, target, start, thisStart, end, thisEnd);
}n/a
function copy() { [native code] }n/a
equals = function (other) {
if (this.length !== other.length) {
return false;
}
for (var i = 0, len = this.length; i < len; i++) {
if (this[i] !== other[i]) {
return false;
}
}
return true;
}n/a
function fill(val, start, end, encoding) {
// Handle string cases:
if (typeof val === 'string') {
if (typeof start === 'string') {
encoding = start;
start = 0;
end = this.length;
} else if (typeof end === 'string') {
encoding = end;
end = this.length;
}
if (encoding !== undefined && typeof encoding !== 'string') {
throw new TypeError('encoding must be a string');
}
var normalizedEncoding = internalUtil.normalizeEncoding(encoding);
if (normalizedEncoding === undefined) {
throw new TypeError('Unknown encoding: ' + encoding);
}
if (val.length === 0) {
// Previously, if val === '', the Buffer would not fill,
// which is rather surprising.
val = 0;
} else if (val.length === 1) {
var code = val.charCodeAt(0);
if ((normalizedEncoding === 'utf8' && code < 128) ||
normalizedEncoding === 'latin1') {
// Fast path: If `val` fits into a single byte, use that numeric value.
val = code;
}
}
} else if (typeof val === 'number') {
val = val & 255;
}
// Invalid ranges are not set to a default, so can range check early.
if (start < 0 || end > this.length)
throw new RangeError('Out of range index');
if (end <= start)
return this;
start = start >>> 0;
end = end === undefined ? this.length : end >>> 0;
binding.fill(this, val, start, end, encoding);
return this;
}n/a
function hexSlice() { [native code] }n/a
function hexWrite() { [native code] }n/a
function includes(val, byteOffset, encoding) {
return this.indexOf(val, byteOffset, encoding) !== -1;
}n/a
function indexOf(val, byteOffset, encoding) {
return bidirectionalIndexOf(this, val, byteOffset, encoding, true);
}n/a
function inspect() {
var str = '';
var max = exports.INSPECT_MAX_BYTES;
if (this.length > 0) {
str = this.toString('hex', 0, max).match(/.{2}/g).join(' ');
if (this.length > max)
str += ' ... ';
}
return '<' + this.constructor.name + ' ' + str + '>';
}...
}
} else {
row = {};
row[Object.keys(columns)[0]] = chunksBuffer.join('');
}
chunksBuffer = null;
if (_this.verbose) {
_this._log(util.inspect(row));
_this._log("---------- --------------------");
}
if (_this.stream) {
_this.emit('row', row);
} else {
recordset.push(row);
}
...function lastIndexOf(val, byteOffset, encoding) {
return bidirectionalIndexOf(this, val, byteOffset, encoding, false);
}n/a
function latin1Slice() { [native code] }n/a
function latin1Write() { [native code] }n/a
function readDoubleBE(offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 8, this.length);
return binding.readDoubleBE(this, offset);
}n/a
function readDoubleLE(offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 8, this.length);
return binding.readDoubleLE(this, offset);
}...
var i, j, point, points, ref;
points = [];
if (count < 1) {
return points;
}
for (i = j = 1, ref = count; 1 <= ref ? j <= ref : j >= ref; i = 1 <= ref ? ++j : --j) {
points.push((point = new Point));
point.x = buffer.readDoubleLE(buffer.position);
point.y = buffer.readDoubleLE(buffer.position + 8);
buffer.position += 16;
}
return points;
};
parseZ = function(buffer, points) {
...function readFloatBE(offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 4, this.length);
return binding.readFloatBE(this, offset);
}n/a
function readFloatLE(offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 4, this.length);
return binding.readFloatLE(this, offset);
}n/a
readInt16BE = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 2, this.length);
var val = this[offset + 1] | (this[offset] << 8);
return (val & 0x8000) ? val | 0xFFFF0000 : val;
}n/a
readInt16LE = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 2, this.length);
var val = this[offset] | (this[offset + 1] << 8);
return (val & 0x8000) ? val | 0xFFFF0000 : val;
}n/a
readInt32BE = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 4, this.length);
return (this[offset] << 24) |
(this[offset + 1] << 16) |
(this[offset + 2] << 8) |
(this[offset + 3]);
}n/a
readInt32LE = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 4, this.length);
return (this[offset]) |
(this[offset + 1] << 8) |
(this[offset + 2] << 16) |
(this[offset + 3] << 24);
}...
})();
parseGeography = function(buffer, geometry) {
var flags, numberOfFigures, numberOfPoints, numberOfSegments, numberOfShapes, properties, srid, value;
if (geometry == null) {
geometry = false;
}
srid = buffer.readInt32LE(0);
if (srid === -1) {
return null;
}
value = {
srid: srid,
version: buffer.readUInt8(4)
};
...readInt8 = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 1, this.length);
var val = this[offset];
return !(val & 0x80) ? val : (0xff - val + 1) * -1;
}n/a
readIntBE = function (offset, byteLength, noAssert) {
offset = offset >>> 0;
byteLength = byteLength >>> 0;
if (!noAssert)
checkOffset(offset, byteLength, this.length);
var i = byteLength;
var mul = 1;
var val = this[offset + --i];
while (i > 0 && (mul *= 0x100))
val += this[offset + --i] * mul;
mul *= 0x80;
if (val >= mul)
val -= Math.pow(2, 8 * byteLength);
return val;
}n/a
readIntLE = function (offset, byteLength, noAssert) {
offset = offset >>> 0;
byteLength = byteLength >>> 0;
if (!noAssert)
checkOffset(offset, byteLength, this.length);
var val = this[offset];
var mul = 1;
var i = 0;
while (++i < byteLength && (mul *= 0x100))
val += this[offset + i] * mul;
mul *= 0x80;
if (val >= mul)
val -= Math.pow(2, 8 * byteLength);
return val;
}n/a
readUInt16BE = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 2, this.length);
return (this[offset] << 8) | this[offset + 1];
}n/a
readUInt16LE = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 2, this.length);
return this[offset] | (this[offset + 1] << 8);
}n/a
readUInt32BE = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 4, this.length);
return (this[offset] * 0x1000000) +
((this[offset + 1] << 16) |
(this[offset + 2] << 8) |
this[offset + 3]);
}n/a
readUInt32LE = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 4, this.length);
return ((this[offset]) |
(this[offset + 1] << 8) |
(this[offset + 2] << 16)) +
(this[offset + 3] * 0x1000000);
}...
properties.H = flags & (1 << 3) ? true : false;
}
if (properties.P) {
numberOfPoints = 1;
} else if (properties.L) {
numberOfPoints = 2;
} else {
numberOfPoints = buffer.readUInt32LE(buffer.position);
buffer.position += 4;
}
value.points = parsePoints(buffer, numberOfPoints);
if (properties.Z) {
parseZ(buffer, value.points);
}
if (properties.M) {
...readUInt8 = function (offset, noAssert) {
offset = offset >>> 0;
if (!noAssert)
checkOffset(offset, 1, this.length);
return this[offset];
}...
}
srid = buffer.readInt32LE(0);
if (srid === -1) {
return null;
}
value = {
srid: srid,
version: buffer.readUInt8(4)
};
flags = buffer.readUInt8(5);
buffer.position = 6;
properties = {
Z: flags & (1 << 0) ? true : false,
M: flags & (1 << 1) ? true : false,
V: flags & (1 << 2) ? true : false,
...readUIntBE = function (offset, byteLength, noAssert) {
offset = offset >>> 0;
byteLength = byteLength >>> 0;
if (!noAssert)
checkOffset(offset, byteLength, this.length);
var val = this[offset + --byteLength];
var mul = 1;
while (byteLength > 0 && (mul *= 0x100))
val += this[offset + --byteLength] * mul;
return val;
}n/a
readUIntLE = function (offset, byteLength, noAssert) {
offset = offset >>> 0;
byteLength = byteLength >>> 0;
if (!noAssert)
checkOffset(offset, byteLength, this.length);
var val = this[offset];
var mul = 1;
var i = 0;
while (++i < byteLength && (mul *= 0x100))
val += this[offset + i] * mul;
return val;
}n/a
function slice(start, end) {
const srcLength = this.length;
start = adjustOffset(start, srcLength);
end = end !== undefined ? adjustOffset(end, srcLength) : srcLength;
const newLength = end > start ? end - start : 0;
return new FastBuffer(this.buffer, this.byteOffset + start, newLength);
}n/a
function swap16() {
// For Buffer.length < 128, it's generally faster to
// do the swap in javascript. For larger buffers,
// dropping down to the native code is faster.
const len = this.length;
if (len % 2 !== 0)
throw new RangeError('Buffer size must be a multiple of 16-bits');
if (len < 128) {
for (var i = 0; i < len; i += 2)
swap(this, i, i + 1);
return this;
}
return swap16n(this);
}n/a
function swap32() {
// For Buffer.length < 192, it's generally faster to
// do the swap in javascript. For larger buffers,
// dropping down to the native code is faster.
const len = this.length;
if (len % 4 !== 0)
throw new RangeError('Buffer size must be a multiple of 32-bits');
if (len < 192) {
for (var i = 0; i < len; i += 4) {
swap(this, i, i + 3);
swap(this, i + 1, i + 2);
}
return this;
}
return swap32n(this);
}n/a
function swap64() {
// For Buffer.length < 192, it's generally faster to
// do the swap in javascript. For larger buffers,
// dropping down to the native code is faster.
const len = this.length;
if (len % 8 !== 0)
throw new RangeError('Buffer size must be a multiple of 64-bits');
if (len < 192) {
for (var i = 0; i < len; i += 8) {
swap(this, i, i + 7);
swap(this, i + 1, i + 6);
swap(this, i + 2, i + 5);
swap(this, i + 3, i + 4);
}
return this;
}
return swap64n(this);
}n/a
toByteArray = function () {
return Array.prototype.slice.call(this, 0);
}n/a
toJSON = function () {
return "0x" + (this.toString('hex'));
}n/a
toString = function () {
let result;
if (arguments.length === 0) {
result = this.utf8Slice(0, this.length);
} else {
result = slowToString.apply(this, arguments);
}
if (result === undefined)
throw new Error('"toString()" failed');
return result;
}...
sql = require('./main');
write = function(text) {
return process.stdout.write(text);
};
Buffer.prototype.toJSON = function() {
return "0x" + (this.toString('hex'));
};
cfgPath = process.argv[2];
if (!cfgPath) {
cfgPath = process.cwd();
}
...function ucs2Slice() { [native code] }n/a
function ucs2Write() { [native code] }n/a
function utf8Slice() { [native code] }n/a
function utf8Write() { [native code] }n/a
write = function (string, offset, length, encoding) {
// Buffer#write(string);
if (offset === undefined) {
encoding = 'utf8';
length = this.length;
offset = 0;
// Buffer#write(string, encoding)
} else if (length === undefined && typeof offset === 'string') {
encoding = offset;
length = this.length;
offset = 0;
// Buffer#write(string, offset[, length][, encoding])
} else if (isFinite(offset)) {
offset = offset >>> 0;
if (isFinite(length)) {
length = length >>> 0;
if (encoding === undefined)
encoding = 'utf8';
} else {
encoding = length;
length = undefined;
}
} else {
// if someone is still calling the obsolete form of write(), tell them.
// we don't want eg buf.write("foo", "utf8", 10) to silently turn into
// buf.write("foo", "utf8"), so we can't ignore extra args
throw new Error('Buffer.write(string, encoding, offset[, length]) ' +
'is no longer supported');
}
var remaining = this.length - offset;
if (length === undefined || length > remaining)
length = remaining;
if (string.length > 0 && (length < 0 || offset < 0))
throw new RangeError('Attempt to write outside buffer bounds');
if (!encoding)
encoding = 'utf8';
var loweredCase = false;
for (;;) {
switch (encoding) {
case 'hex':
return this.hexWrite(string, offset, length);
case 'utf8':
case 'utf-8':
return this.utf8Write(string, offset, length);
case 'ascii':
return this.asciiWrite(string, offset, length);
case 'latin1':
case 'binary':
return this.latin1Write(string, offset, length);
case 'base64':
// Warning: maxLength not taken into account in base64Write
return this.base64Write(string, offset, length);
case 'ucs2':
case 'ucs-2':
case 'utf16le':
case 'utf-16le':
return this.ucs2Write(string, offset, length);
default:
if (loweredCase)
throw new TypeError('Unknown encoding: ' + encoding);
encoding = ('' + encoding).toLowerCase();
loweredCase = true;
}
}
}...
fs = require('fs');
path = require('path');
sql = require('./main');
write = function(text) {
return process.stdout.write(text);
};
Buffer.prototype.toJSON = function() {
return "0x" + (this.toString('hex'));
};
cfgPath = process.argv[2];
...function writeDoubleBE(val, offset, noAssert) {
val = +val;
offset = offset >>> 0;
if (!noAssert)
binding.writeDoubleBE(this, val, offset);
else
binding.writeDoubleBE(this, val, offset, true);
return offset + 8;
}n/a
function writeDoubleLE(val, offset, noAssert) {
val = +val;
offset = offset >>> 0;
if (!noAssert)
binding.writeDoubleLE(this, val, offset);
else
binding.writeDoubleLE(this, val, offset, true);
return offset + 8;
}n/a
function writeFloatBE(val, offset, noAssert) {
val = +val;
offset = offset >>> 0;
if (!noAssert)
binding.writeFloatBE(this, val, offset);
else
binding.writeFloatBE(this, val, offset, true);
return offset + 4;
}n/a
function writeFloatLE(val, offset, noAssert) {
val = +val;
offset = offset >>> 0;
if (!noAssert)
binding.writeFloatLE(this, val, offset);
else
binding.writeFloatLE(this, val, offset, true);
return offset + 4;
}n/a
writeInt16BE = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 2, 0x7fff, -0x8000);
this[offset] = (value >>> 8);
this[offset + 1] = value;
return offset + 2;
}n/a
writeInt16LE = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 2, 0x7fff, -0x8000);
this[offset] = value;
this[offset + 1] = (value >>> 8);
return offset + 2;
}n/a
writeInt32BE = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 4, 0x7fffffff, -0x80000000);
this[offset] = (value >>> 24);
this[offset + 1] = (value >>> 16);
this[offset + 2] = (value >>> 8);
this[offset + 3] = value;
return offset + 4;
}n/a
writeInt32LE = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 4, 0x7fffffff, -0x80000000);
this[offset] = value;
this[offset + 1] = (value >>> 8);
this[offset + 2] = (value >>> 16);
this[offset + 3] = (value >>> 24);
return offset + 4;
}n/a
writeInt8 = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 1, 0x7f, -0x80);
this[offset] = value;
return offset + 1;
}n/a
writeIntBE = function (value, offset, byteLength, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert) {
checkInt(this,
value,
offset,
byteLength,
Math.pow(2, 8 * byteLength - 1) - 1,
-Math.pow(2, 8 * byteLength - 1));
}
var i = byteLength - 1;
var mul = 1;
var sub = 0;
this[offset + i] = value;
while (--i >= 0 && (mul *= 0x100)) {
if (value < 0 && sub === 0 && this[offset + i + 1] !== 0)
sub = 1;
this[offset + i] = ((value / mul) >> 0) - sub;
}
return offset + byteLength;
}n/a
writeIntLE = function (value, offset, byteLength, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert) {
checkInt(this,
value,
offset,
byteLength,
Math.pow(2, 8 * byteLength - 1) - 1,
-Math.pow(2, 8 * byteLength - 1));
}
var i = 0;
var mul = 1;
var sub = 0;
this[offset] = value;
while (++i < byteLength && (mul *= 0x100)) {
if (value < 0 && sub === 0 && this[offset + i - 1] !== 0)
sub = 1;
this[offset + i] = ((value / mul) >> 0) - sub;
}
return offset + byteLength;
}n/a
writeUInt16BE = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 2, 0xffff, 0);
this[offset] = (value >>> 8);
this[offset + 1] = value;
return offset + 2;
}n/a
writeUInt16LE = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 2, 0xffff, 0);
this[offset] = value;
this[offset + 1] = (value >>> 8);
return offset + 2;
}n/a
writeUInt32BE = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 4, 0xffffffff, 0);
this[offset] = (value >>> 24);
this[offset + 1] = (value >>> 16);
this[offset + 2] = (value >>> 8);
this[offset + 3] = value;
return offset + 4;
}n/a
writeUInt32LE = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 4, 0xffffffff, 0);
this[offset + 3] = (value >>> 24);
this[offset + 2] = (value >>> 16);
this[offset + 1] = (value >>> 8);
this[offset] = value;
return offset + 4;
}n/a
writeUInt8 = function (value, offset, noAssert) {
value = +value;
offset = offset >>> 0;
if (!noAssert)
checkInt(this, value, offset, 1, 0xff, 0);
this[offset] = value;
return offset + 1;
}n/a
writeUIntBE = function (value, offset, byteLength, noAssert) {
value = +value;
offset = offset >>> 0;
byteLength = byteLength >>> 0;
if (!noAssert) {
const maxBytes = Math.pow(2, 8 * byteLength) - 1;
checkInt(this, value, offset, byteLength, maxBytes, 0);
}
var i = byteLength - 1;
var mul = 1;
this[offset + i] = value;
while (--i >= 0 && (mul *= 0x100))
this[offset + i] = (value / mul) >>> 0;
return offset + byteLength;
}n/a
writeUIntLE = function (value, offset, byteLength, noAssert) {
value = +value;
offset = offset >>> 0;
byteLength = byteLength >>> 0;
if (!noAssert) {
const maxBytes = Math.pow(2, 8 * byteLength) - 1;
checkInt(this, value, offset, byteLength, maxBytes, 0);
}
var mul = 1;
var i = 0;
this[offset] = value;
while (++i < byteLength && (mul *= 0x100))
this[offset + i] = (value / mul) >>> 0;
return offset + byteLength;
}n/a
function Table(name) {
var ref1;
if (name) {
ref1 = Table.parseName(name), this.name = ref1.name, this.schema = ref1.schema, this.database = ref1.database;
this.path = "" + (this.database ? "[" + this.database + "]." : "") + (this.schema ? "[" + this.schema + "]." : "") + "[" + this
.name + "]";
this.temporary = this.name.charAt(0) === '#';
}
this.columns = [];
this.rows = [];
Object.defineProperty(this.columns, "add", {
value: function(name, column, options) {
if (options == null) {
options = {};
}
if (column == null) {
throw new Error("Column data type is not defined.");
}
if (column instanceof Function) {
column = column();
}
column.name = name;
column.nullable = options.nullable;
column.primary = options.primary;
return this.push(column);
}
});
Object.defineProperty(this.rows, "add", {
value: function() {
var values;
values = 1 <= arguments.length ? slice.call(arguments, 0) : [];
return this.push(values);
}
});
}n/a
sql = function (tvpType) {
return {
type: TYPES.TVP,
tvpType: tvpType
};
}n/a