A Connection instance represents a single connection to a database server.
Only one request at a time may be executed on a connection. Once a Request has been initiated (with callProcedure, execSql, or execSqlBatch), another should not be initiated until the Request's completion callback is called.
var Connection = require('tedious').Connection;
var config = {
"authentication": {
...,
"options": {...}
},
"options": {
...
}
};
var connection = new Connection(config);
// Setup event handler when the connection is established.
connection.on('connect', function(err) {
if(err) {
console.log('Error: ', err)
}
// If no error, then good to go...
executeStatement();
});
// Initialize the connection.
connection.connect();config
Note: be aware of the different options field:
server
authentication.type
default, ntlm, azure-active-directory-default, azure-active-directory-password, azure-active-directory-access-token, azure-active-directory-msi-vm, azure-active-directory-msi-app-service, or azure-active-directory-service-principal-secret
azure-active-directory-defaultThis uses DefaultAzureCredential to try multiple methods of authentication. You can find more information on what values to provide and how to provide them from this Microsoft document.
"authentication": {
"type":'azure-active-directory-default',
...,
"options": {
"clientId": value (Optional)
}
} azure-active-directory-password"authentication": {
"type":'azure-active-directory-password',
...,
"options": {
"userName": value,
"password": value,
"clientId": value,
"tenantId": value (Optional)
}
} common). If your server does not support the /common endpoint, please provide a tenantId. Note: this used to be called domain. domain will proceed to work but will be removed in the near future.azure-active-directory-access-token"authentication": {
"type":'azure-active-directory-access-token',
...,
"options": {
"token": value
}
} azure-active-directory-password. This is the recommended method because Tedious will handle the process of retrieving the Azure token internally instead of requiring the user to manually retrieve the token as shown below. How to get the token There are different approaches if a user actually needs to retrieve a token manually. The details can be found in Microsoft documentation. Here are some examples:
azure-active-directory-msi-vm"authentication": {
"type":'azure-active-directory-msi-vm',
...,
"options": {
"clientId": value,
"msiEndpoint": value
}
} azure-active-directory-msi-app-service"authentication": {
"type":'azure-active-directory-msi-app-service',
...,
"options": {
"clientId": value,
"msiEndpoint": value,
"msiSecret": value
}
} azure-active-directory-service-principal-secret"authentication": {
"type":'azure-active-directory-service-principal-secret',
...,
"options": {
"clientId": value,
"clientSecret": value,
"tenantId": (Optional)
}
} NTLM--openssl-legacy-provider command line flag must be enabled, else an error will be thrown. Check the FAQ for more information.
authentication.options.userName
authentication.options.password
authentication.options.domain
authentication.options.token
authentication.options.clientId
authentication.options.clientSecret
options.abortTransactionOnError
SET XACT_ABORT during the initial SQL phase of a connection (documentation).
options.appName
Tedious)
options.camelCaseColumns
true) or not. This value is ignored if you provide a columnNameReplacer. (default: false).
options.cancelTimeout
5000).
options.columnNameReplacer
(columnName, index, columnMetaData) and returning a string. If provided, this will be called once per column per result-set. The returned value will be used instead of the SQL-provided column name on row and meta data objects. This allows you to dynamically convert between naming conventions. (default: null).
options.connectionRetryInterval
500)
options.connectTimeout
15000).
options.connectionIsolationLevel
require('tedious').ISOLATION_LEVEL .
READ_UNCOMMITTEDREAD_COMMITTEDREPEATABLE_READSERIALIZABLESNAPSHOTREAD_COMMITED).
options.cryptoCredentialsDetails
secureContext field when calling new tls.TLSSocket. The available options are listed under tls.createSecureContext. (default: {}).
options.database
options.datefirst
options.dateFormat
mdy)
options.debug.packet
debug events will be emitted with text describing packet details (default: false).
options.debug.data
debug events will be emitted with text describing packet data details (default: false).
options.debug.payload
debug events will be emitted with text describing packet payload details (default: false).
options.debug.token
debug events will be emitted with text describing token stream tokens (default: false).
options.enableAnsiNull
true)
options.enableAnsiNullDefault
SET ANSI_NULL_DFLT_ON ON will be set in the initial sql. This means new columns will be nullable by default. See the T-SQL documentation for more details. (Default: true).
options.enableAnsiPadding
true)
options.enableAnsiWarnings
true)
options.enableArithAbort
true)
options.enableConcatNullYieldsNull
true)
options.enableCursorCloseOnCommit
null)
options.enableImplicitTransactions
false)
options.enableNumericRoundabort
false)
options.enableQuotedIdentifier
true)
options.encrypt
strict enables the TDS 8.0 protocol. Otherwise, encrypt can be set to a boolean value which determines whether or not the connection will be encrypted under the TDS 7.x protocol. (default: true)
options.fallbackToDefaultDb
options.database cannot be accessed, the connection will fail with an error. However, if options.fallbackToDefaultDb is set to true, then the user's default database will be used instead (Default: false).
options.instanceName
options.port.
options.isolationLevel
require('tedious').ISOLATION_LEVEL .
READ_UNCOMMITTEDREAD_COMMITTEDREPEATABLE_READSERIALIZABLESNAPSHOTREAD_COMMITED).
options.language
us_english).
options.localAddress
options.maxRetriesOnTransientErrors
3).
options.multiSubnetFailover
false).
options.packetSize
4096).
options.port
1433).
options.instanceName.
options.readOnlyIntent
false).
options.requestTimeout
0 for no timeout (default: 15000).
options.rowCollectionOnDone
done* events. See done, doneInProc and doneProc. (default: false)
Caution: If many row are received, enabling this option could result in excessive memory usage.
options.rowCollectionOnRequestCompletion
false)
Caution: If many row are received, enabling this option could result in excessive memory usage.
options.tdsVersion
require('tedious').TDS_VERSION.
7_1 (use for SQL Server 2000)7_27_3_A7_3_B7_47_4).
options.textsize
2147483647) (Textsize is set by a numeric value.)
options.trustServerCertificate
true).
options.useColumnNames
false).
options.useUTC
true).
The attempt to connect and validate has completed.
err
If successfully connected, will be falsey.
If there was a problem (with either connecting or validation), will be an error object.
Event: 'end'function () { }The connection has ended. This may be as a result of the client calling close(), the server closing the connection, or a network error.
Internal error occurs.
Event: 'debug'function (messageText) { }A debug message is available. It may be logged or ignored.
messageText
The debug message.
Event: 'infoMessage'function (info) { }The server has issued an information message.
info
An object with these properties:
number
state
class
message
procName
lineNumber
The server has issued an error message.
error
An object with the same properties as.listed for the infoMessage event.
The server has reported that the active database has changed. This may be as a result of a successful login, or a use statement.
databaseName
The name of the new active database
Event: 'languageChange'function (languageName) { }The server has reported that the language has changed.
languageName
The newly active language.
Event: 'charsetChange'function (charset) { }The server has reported that the charset has changed.
Event: 'secure'function (cleartext) { }A secure connection has been established.
connection.connect(callback)Initialize a connection.
callback
function (err) { }The callback is called when the connection is either successfully created (on a connect event) or has an error (on a error event). If an error occurs, then err will describe the error.
This parameter is optional, if a user choose to not include this parameter, then they have to call the
connection.on('connect', (err) => {});
to check the connection status.
err
If an error occurs, either a connection error or an aggregate error object will be returned.
console.log('Error: ', err)err.errors.map(error=>console.log('Error: ', err))Start a transaction.
callback
function (err) { }The callback is called when the request to start the transaction has completed, either successfully or with an error. If an error occurred then err will describe the error.
As only one request at a time may be executed on a connection, another request should not be initiated until this callback is called.
err
If an error occurred, a string with details of the error.
name
A string representing a name to associate with the transaction.
Optional, and defaults to an empty string. Required when isolationLevel is present.
isolationLevel
The isolation level that the transaction is to be run with. The isolation levels are available from require('tedious').ISOLATION_LEVEL .
READ_UNCOMMITTEDREAD_COMMITTEDREPEATABLE_READSERIALIZABLESNAPSHOTOptional, and defaults to the Connection's isolation level.
connection.callProcedure(request)Call a stored procedure represented by request.
request
A Request object representing the request.
Cancel currently executed request.
connection.close()Closes the connection to the database.
The end will be emitted once the connection has been closed.
Execute the SQL represented by request.
As sp_executesql is used to execute the SQL, if the same SQL is executed multiples times using this function, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution. This may also result in SQL server treating the request like a stored procedure which can result in the doneInProc or doneProc events being emitted instead of the done event you might expect. Using execSqlBatch will prevent this from occurring but may have a negative performance impact.
Beware of the way that scoping rules apply, and how they may affect local temp tables. If you're running in to scoping issues, then execSqlBatch may be a better choice. See also issue #24.
request
A Request object representing the request.
Commit a transaction.
There should be an active transaction. That is, beginTransaction should have been previously called.
callback
function (err) { }The callback is called when the request to commit the transaction has completed, either successfully or with an error. If an error occurred then err will describe the error.
As only one request at a time may be executed on a connection, another request should not be initiated until this callback is called.
err
If an error occurred, a string with details of the error.
connection.execSqlBatch(request)Execute the SQL batch represented by request. There is no param support, and unlike execSql, it is not likely that SQL Server will reuse the execution plan it generates for the SQL.
In almost all cases, execSql will be a better choice.
request
A Request object representing the request.
Executes a BulkLoad.
rows
The row data. Accepts objects that implements the Iterable or AsyncIterable interface (e.g. a Readable stream or an AsyncGenerator). Each row can either be an array or object.
const rows = [{col1name: col1data, col2name: col2data, col3name: col3data, ... },
[col1data, col2data, col3data, ...],
{col2name: col2data}]const rows = Readable.from([{col1name: col1data, col2name: col2data, col3name: col3data, ... },
[col1data, col2data, col3data, ...],
{col2name: col2data}])Execute previously prepared SQL, using the supplied parameters.
request
A previously prepared Request.
parameters
An object whose names correspond to the names of parameters that were added to the request before it was prepared. The object's values are passed as the parameters' values when the request is executed.
Prepare the SQL represented by the request. The request can then be used in subsequent calls to execute and unprepare
request
A Request object representing the request.
Parameters only require a name and type. Parameter values are ignored.
connection.reset(callback)Reset the connection to its initial state. Can be useful for connection pool implementations.
callback
function (err) { }The callback is called when the connection reset has completed, either successfully or with an error. If an error occurred then err will describe the error.
As only one request at a time may be executed on a connection, another request should not be initiated until this callback is called.
err
If an error occurred, a string with details of the error.
connection.rollbackTransaction(callback)Rollback a transaction.
There should be an active transaction. That is, beginTransaction should have been previously called.
callback
function (err) { }The callback is called when the request to rollback the transaction has completed, either successfully or with an error. If an error occurred then err will describe the error.
As only one request at a time may be executed on a connection, another request should not be initiated until this callback is called.
err
If an error occurred, a string with details of the error.
connection.saveTransaction(callback)Set a savepoint within a transaction.
There should be an active transaction. That is, beginTransaction should have been previously called.
callback
function (err) { }The callback is called when the request to set a savepoint within the transaction has completed, either successfully or with an error. If an error occurred then err will describe the error.
As only one request at a time may be executed on a connection, another request should not be initiated until this callback is called.
err
If an error occurred, a string with details of the error.
connection.transaction(callback, name, isolationLevel)Run the given callback after starting a transaction, and commit or rollback the transaction afterwards.
This is a helper that employs beginTransaction, commitTransaction, rollbackTransaction and saveTransaction to greatly simplify the use of database transactions and automatically handle transaction nesting.
callback
function (err, done) { }The callback is called when the request to start a transaction (or create a savepoint, in the case of a nested transaction) has completed, either successfully or with an error. If an error occurred, then err will describe the error. If no error occurred, the callback should perform its work and eventually call done with an error or null (to trigger a transaction rollback or a transaction commit) and an additional completion callback that will be called when the request to rollback or commit the current transaction has completed, either successfully or with an error. Additional arguments given to done will be passed through to this callback.
As only one request at a time may be executed on a connection, another request should not be initiated until the completion callback is called.
err
Pass null to commit the transaction or not null (an error) when the transaction is to be rolled back.
done
This is another callback you provide that tedious calls when it has committed the transaction or a rollback transaction. It will call done with one or more parameters, namely:
err
an indication of any problems with the final commit request
done
Yet another call back with error as first parameter, followed by variable number of arguments as array.
other
the additional parameters you provided to tedious, which tedious will reflect back to you
name
A string representing a name to associate with the transaction.
Optional, and defaults to an empty string. In case of a nested transaction, naming the transaction name has no effect.
isolationLevel
The isolation level that the transaction is to be run with. The isolation levels are available from require('tedious').ISOLATON_LEVEL .
READ_UNCOMMITTEDREAD_COMMITTEDREPEATABLE_READSERIALIZABLESNAPSHOTOptional, and defaults to the Connection's isolation level.
Here's the actual parameter input of transaction in TypeScript notation:
transaction <T extends (...args: any[]) => void>(cb: (err: Error | null | undefined, txDone?: (err: Error | null | undefined, done: T, ...args: Parameters<T>) => void) => void, isolationLevel?: typeof ISOLATION_LEVEL[keyof typeof ISOLATION_LEVEL]) connection.unprepare(request)
Release the SQL Server resources associated with a previously prepared request.
request
The request to be unprepared.
connection.newBulkLoad(tableName, options, callback)Creates a new BulkLoad instance.
tableName
The name of the table to bulk-insert into.
options(optional)
checkConstraints
Honors constraints during bulk load, using T-SQL CHECK_CONSTRAINTS. (default: false)
fireTriggers
false)
keepNulls
false)
tableLock
false)
callback
function (err, rowCount) { }A function which will be called after the BulkLoad finishes executing. rowCount will equal the number of rows inserted.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4