node-mssql

Create connection to the server.

`$3`

- callback(err) - A callback which is called after connection has established, or an error has occurred. Optional. If omited, returns Promise.

__Example__

`javascript var connection = new sql.Connection({ user: '...', password: '...', server: 'localhost', database: '...' });

connection.connect(function(err) { // ... });`

__Errors__ - ELOGIN (ConnectionError) - Login failed. - ETIMEOUT (ConnectionError) - Connection timeout. - EALREADYCONNECTED (ConnectionError) - Database is already connected! - EALREADYCONNECTING (ConnectionError) - Already connecting to database! - EINSTLOOKUP (ConnectionError) - Instance lookup failed. - ESOCKET (ConnectionError) - Socket error.

---------------------------------------

`$3`

Close connection to the server.

`javascript connection.close();`

`Requests`

`javascript var request = new sql.Request(/ [connection] /);`

If you omit connection argument, global connection is used instead.

`$3`

- recordset(columns) - Dispatched when metadata for new recordset are parsed. - row(row) - Dispatched when new row is parsed. - done(returnValue) - Dispatched when request is complete. - error(err) - Dispatched on error.

Call a stored procedure.

`$3`

- procedure - Name of the stored procedure to be executed. - callback(err, recordsets, returnValue) - A callback which is called after execution has completed, or an error has occurred.returnValue is also accessible as property of recordsets. Optional. If omited, returns Promise.

__Example__

`javascript var request = new sql.Request(); request.input('input_parameter', sql.Int, value); request.output('output_parameter', sql.Int); request.execute('procedure_name', function(err, recordsets, returnValue) { // ... error checks console.log(recordsets.length); // count of recordsets returned by the procedure console.log(recordsets[0].length); // count of rows contained in first recordset console.log(returnValue); // procedure return value console.log(recordsets.returnValue); // same as previous line console.log(request.parameters.output_parameter.value); // output value // ... });`

__Errors__ - EREQUEST (RequestError) - Message from SQL Server - ECANCEL (RequestError) - Canceled. - ETIMEOUT (RequestError) - Request timeout. - ENOCONN (RequestError) - No connection is specified for that request. - ENOTOPEN (ConnectionError) - Connection not yet open. - ECONNCLOSED (ConnectionError) - Connection is closed. - ENOTBEGUN (TransactionError) - Transaction has not begun. - EABORT (TransactionError) - Transaction was aborted (by user or because of an error).

---------------------------------------

`$3`

Add an input parameter to the request.

- name - Name of the input parameter without @ char. - type - SQL data type of input parameter. If you omit type, module automaticaly decide which SQL data type should be used based on JS data type. - value - Input parameter value.undefined ans NaN values are automatically converted to null values.

`javascript request.input('input_parameter', value); request.input('input_parameter', sql.Int, value);`

__JS Data Type To SQL Data Type Map__

- String -> sql.NVarChar-Number -> sql.Int-Boolean -> sql.Bit-Date -> sql.DateTime-Buffer -> sql.VarBinary-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);`

__Errors__ (synchronous) - EARGS (RequestError) - Invalid number of arguments. - EINJECT (RequestError) - SQL injection warning.

Add an output parameter to the request.

`$3`

- name - Name of the output parameter without @ char. - type - SQL data type of output parameter. - value - Output parameter value initial value.undefined and NaN values are automatically converted to null values. Optional.

`javascript request.output('output_parameter', sql.Int); request.output('output_parameter', sql.VarChar(50), 'abc');`

__Errors__ (synchronous) - EARGS (RequestError) - Invalid number of arguments. - EINJECT (RequestError) - SQL injection warning.

Sets request to stream mode and pulls all rows from all recordsets to a given stream.

`$3`

- stream - Writable stream in object mode.

`javascript var request = new sql.Request(); request.pipe(stream); request.query('select * from mytable'); stream.on('error', function(err) { // ... }); stream.on('finish', function() { // ... });`

__Version__

2.0

`$3`

Execute the SQL command. To execute commands like create procedure or if you plan to work with local temporary tables, use batch instead.

__Arguments__

- command - 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 omited, returns Promise.

__Example__

`javascript var request = new sql.Request(); request.query('select 1 as number', function(err, recordset) { // ... error checks console.log(recordset[0].number); // return 1 // ... });`

__Errors__ - ETIMEOUT (RequestError) - Request timeout. - EREQUEST (RequestError) - Message from SQL Server - ECANCEL (RequestError) - Canceled. - ENOCONN (RequestError) - No connection is specified for that request. - ENOTOPEN (ConnectionError) - Connection not yet open. - ECONNCLOSED (ConnectionError) - Connection is closed. - ENOTBEGUN (TransactionError) - Transaction has not begun. - EABORT (TransactionError) - Transaction was aborted (by user or because of an error).

You can enable multiple recordsets in queries with the request.multiple = true command.

`javascript var request = new sql.Request(); request.multiple = true;

request.query('select 1 as number; select 2 as number', function(err, recordsets) { // ... error checks console.log(recordsets[0][0].number); // return 1 console.log(recordsets[1][0].number); // return 2 });`

---------------------------------------

`$3`

Execute the SQL command. Unlike query, it doesn't use sp_executesql, so is not likely that SQL Server will reuse the execution plan it generates for the SQL. Use this only in special cases, for example when you need to execute commands like create procedure which can't be executed with query or if you're executing statements longer than 4000 chars on SQL Server 2000. Also you should use this if you're plan to work with local temporary tables (more information here).

NOTE: Table-Valued Parameter (TVP) is not supported in batch.

__Arguments__

- 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 omited, returns Promise.

__Example__

`javascript var request = new sql.Request(); request.batch('create procedure #temporary as select * from table', function(err, recordset) { // ... error checks });`

You can enable multiple recordsets in queries with the request.multiple = true command.

---------------------------------------

`$3`

Perform a bulk insert.

- table - sql.Tableinstance. - callback(err, rowCount) - A callback which is called after bulk insert has completed, or an error has occurred. Optional. If omited, returnsPromise.

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

TIP: You can also create Table variable from any recordset with recordset.toTable().

__Errors__ - ENAME (RequestError) - Table name must be specified for bulk insert. - ETIMEOUT (RequestError) - Request timeout. - EREQUEST (RequestError) - Message from SQL Server - ECANCEL (RequestError) - Canceled. - ENOCONN (RequestError) - No connection is specified for that request. - ENOTOPEN (ConnectionError) - Connection not yet open. - ECONNCLOSED (ConnectionError) - Connection is closed. - ENOTBEGUN (TransactionError) - Transaction has not begun. - EABORT (TransactionError) - Transaction was aborted (by user or because of an error).

---------------------------------------

`$3`

Cancel currently executing request. Return true if cancellation packet was send successfully.

`javascript var request = new sql.Request(); request.query('waitfor delay \'00:00:05\'; select 1 as number', function(err, recordset) { console.log(err instanceof sql.RequestError); // true console.log(err.message); // Canceled. console.log(err.code); // ECANCEL // ... });

request.cancel();`

`Transactions`

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.

`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) { // ... error checks console.log("Transaction commited."); }); }); });`

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] /); transaction.begin(function(err) { // ... error checks var rolledBack = false; transaction.on('rollback', function(aborted) { // emited with aborted === true rolledBack = true; });

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

`$3`

- begin - Dispatched when transaction begin. - commit - Dispatched on successful commit. - rollback(aborted) - Dispatched on successful rollback with an argument determining if the transaction was aborted (by user or because of an error).

`$3`

Begin a transaction.

- isolationLevel - Controls the locking and row versioning behavior of TSQL statements issued by a connection. Optional. READ_COMMITTED by default. For possible values see sql.ISOLATION_LEVEL. - callback(err) - A callback which is called after transaction has began, or an error has occurred. Optional. If omited, returnsPromise.

__Example__

`javascript var transaction = new sql.Transaction(); transaction.begin(function(err) { // ... error checks });`

__Errors__ - ENOTOPEN (ConnectionError) - Connection not yet open. - EALREADYBEGUN (TransactionError) - Transaction has already begun.

---------------------------------------

`$3`

Commit a transaction.

- callback(err) - A callback which is called after transaction has committed, or an error has occurred. Optional. If omited, returns Promise.

__Example__

`javascript var transaction = new sql.Transaction(); transaction.begin(function(err) { // ... error checks transaction.commit(function(err) { // ... error checks }) });`

__Errors__ - ENOTBEGUN (TransactionError) - Transaction has not begun. - EREQINPROG (TransactionError) - Can't commit transaction. There is a request in progress.

---------------------------------------

`$3`

Rollback a transaction. If the queue isn't empty, all queued requests will be canceled and the transaction will be marked as aborted.

- callback(err) - A callback which is called after transaction has rolled back, or an error has occurred. Optional. If omited, returns Promise.

__Example__

`javascript var transaction = new sql.Transaction(); transaction.begin(function(err) { // ... error checks transaction.rollback(function(err) { // ... error checks }) });`

__Errors__ - ENOTBEGUN (TransactionError) - Transaction has not begun. - EREQINPROG (TransactionError) - Can't rollback transaction. There is a request in progress.

`PreparedStatement`

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 aquired 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.

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

IMPORTANT: Remember that each prepared statement means one reserved connection from the pool. Don't forget to unprepare a prepared statement!

TIP: You can also create prepared statements in transactions (new sql.PreparedStatement(transaction)), but keep in mind you can't execute other requests in the transaction until you call unprepare.

Add an input parameter to the prepared statement.

`$3`

- name - Name of the input parameter without @ char. - type - SQL data type of input parameter.

`javascript ps.input('input_parameter', sql.Int); ps.input('input_parameter', sql.VarChar(50));`

__Errors__ (synchronous) - EARGS (PreparedStatementError) - Invalid number of arguments. - EINJECT (PreparedStatementError) - SQL injection warning.

Add an output parameter to the prepared statement.

`$3`

- name - Name of the output parameter without @ char. - type - SQL data type of output parameter.

`javascript ps.output('output_parameter', sql.Int); ps.output('output_parameter', sql.VarChar(50));`

__Errors__ (synchronous) - EARGS (PreparedStatementError) - Invalid number of arguments. - EINJECT (PreparedStatementError) - SQL injection warning.

`$3`

Prepare a statement.

- statement - T-SQL statement to prepare. - callback(err) - A callback which is called after preparation has completed, or an error has occurred. Optional. If omited, returns Promise.

__Example__

`javascript var ps = new sql.PreparedStatement(); ps.prepare('select @param as value', function(err) { // ... error checks });`

__Errors__ - ENOTOPEN (ConnectionError) - Connection not yet open. - EALREADYPREPARED (PreparedStatementError) - Statement is already prepared. - ENOTBEGUN (TransactionError) - Transaction has not begun.

---------------------------------------

`$3`

Execute a prepared statement.

- values - An object whose names correspond to the names of parameters that were added to the prepared statement before it was prepared. - callback(err) - A callback which is called after execution has completed, or an error has occurred. Optional. If omited, returns Promise.

__Example__

`javascript var ps = new sql.PreparedStatement(); ps.input('param', sql.Int); ps.prepare('select @param as value', function(err) { // ... error checks ps.execute({param: 12345}, function(err, recordset) { // ... error checks console.log(recordset[0].value); // return 12345 }); });`

You can enable multiple recordsets by ps.multiple = true command.

`javascript var ps = new sql.PreparedStatement(); ps.input('param', sql.Int); ps.prepare('select @param as value', function(err) { // ... error checks ps.multiple = true; ps.execute({param: 12345}, function(err, recordsets) { // ... error checks console.log(recordsets[0][0].value); // return 12345 }); });`

You can also stream executed request.

`javascript var ps = new sql.PreparedStatement(); ps.input('param', sql.Int); ps.prepare('select @param as value', function(err) { // ... error checks ps.stream = true; request = ps.execute({param: 12345}); request.on('recordset', function(columns) { // Emitted once for each recordset in a query }); request.on('row', function(row) { // Emitted for each row in a recordset }); request.on('error', function(err) { // May be emitted multiple times }); request.on('done', function(returnValue) { // Always emitted as the last one }); });`

__Errors__ - ENOTPREPARED (PreparedStatementError) - Statement is not prepared. - ETIMEOUT (RequestError) - Request timeout. - EREQUEST (RequestError) - Message from SQL Server - ECANCEL (RequestError) - Canceled.

---------------------------------------

`$3`

Unprepare a prepared statement.