Transactions

Transactions

v1.5.0 brings support for executing transactions. This is useful if you need to execute multiple insert/update commands and the entire process depends on all queries running successfully, otherwise changes will be rolled back.

How?

Here is a simple example of how to run an insert, an update query and a select query in a single transaction.

let commands = [
    {
        query: 'insert into account (name) values (?)',
        params: [ 'Bob' ]
    },
    {
        query: 'update account set name = ? where name = ?',
        params: [ 'Jim', 'Bob' ]
    },
    {
        query: 'select * from account where name = ?',
        type: oledb.COMMAND_TYPES.QUERY,
        params: [ 'Jim' ]
    }
];

db.transaction(commands)
.then(results => {
    console.log(results); //An array of each query results.
},
err => {
    console.log(err);
});

The first command executes an insert query, inserting a new account record with the name 'Bob', the second query then updates this record to 'Jim' and finally a select query is executed returning the record.

If any of these queries fail, then the changes will be rolled back.

$prev Parameter

Additionally, there is a special parameter called '$prev', this allows you to use the result of the previous query as a parameter to the next query. Here's an example of it in action:

let commands = [
    //First query, executes a stored procedure and returns an account id.
    {
        query: 'insert_account (@name)',
        params: [ 
            {
                name: '@name',
                value: 'Bob'
            },
            {
                name: '@accountId',
                direction: oledb.PARAMETER_DIRECTIONS.RETURN_VALUE
            }
        ],
        type: oledb.COMMAND_TYPES.PROCEDURE
    },
    //Second query, executes a select query with the returned value from the previous query.
    {
        query: 'select * from account where id = @accountId',
        type: oledb.COMMAND_TYPES.QUERY,
        params: [
            {
                name: '@accountId',
                value: '$prev'      //Note: This value must be a string.
            }
        ]
    }
];

db.transaction(commands)
.then(results => {
    console.log(results[0]); //Insert stored procedure result. Returns the ID of the account.
    console.log(results[1]); //Select query result. Returns the account 'Bob' record.
},
err => {
    console.log(err);
});

Be careful when using output parameters, the $prev parameter only supports single values returned by queries.

---

Any other changes?

Yes, there are some breaking changes to query results. Have a look at the following example:

db.query('select * from account;')
.then(result => {
    console.log(result);
})
.catch(err => {
    console.error(err);
});

For versions <1.5.0, the result variable would look like this:

[
    //First result set.
    [
        { id: 1, name: 'Bob' },
        { id: 2, name: 'Jim' }
    ]
]

This has now been changed, the above query results will be contained within a field called result in an object:

{
    query: 'select * from account;',
    type: 'query',
    params: [ ],
    'result': [
        [
            { id: 1, name: 'Bob' },
            { id: 2, name: 'Jim' }
        ]
    ]
}

After executing a command, the results will contain a detailed object with all parameters and their values (they may have changed if they are output or return values) as well as the query that was executed and the query result either as an Array or a single value.

Improved initialization

New Initialisation Functions

Instead of providing the connection type, the module now exposes three functions:

const db = require('oledb');

let oledb = db.oledbConnection(connectionString);
let odbc = db.odbcConnection(connectionString);
let sql = db.sqlConnection(connectionString);

Then each connection exposes the same functions as before, execute, scalar, and query.

Updating Your Code

This is a breaking change from v1.3.0. All previous initialisation of the module must be changed. Here is an example:

OLD

const db = oledb(connectionString, 'sql');

NEW

const db = oledb.sqlConnection(connectionString);

Support for ODBC and SQL

Added support for ODBC and SQL database connections. The module assumes OLE DB by default however can be overridden in the initializer.

Here is an example:

const connectionString = 'Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;';
const connectionType = 'sql';

const oledb = require('oledb');
const db = oledb(connectionString, connectionType);

...

Support for query parameters

Parameters are now supported and uses positional parameters that are marked with a question mark (?) instead of named parameters. Here is an example:

let command = `
    select * from account 
    where
        firstname = ?
        and id = ?
`;

let parameters = [ 'Bob', 123 ];

db.query(command, parameters)
.then(function(results) {
    console.log(results[0]);
},
function(error) {
    console.error(error);
});

Multiple instance of OLE DB support

It's now possible to have more than one instance of an oledb, for example:

const oledb = require('oledb');

const db1 = oledb('my connection string');
const db2 = oledb('my other connection string');

This enables connections to multiple databases within the application.

Multiple result sets

Added support for multiple result sets returned by the .query promise. Please note that this is a breaking change from v1.0.x.

For example it is now possible to execute multiple queries in one call:

let command = `
    select * from account;
    select * from address;
`;

db.query(command)
.then((results) => {
    console.log(results[0]);
    console.log(results[1]);
},
(err) => {
    console.log(err);
});

The result will be an array of results instead of one array being the first result set.

Note: Multiple queries are not supported if you are using Visual FoxPro OLE DB Provider.

Scalar Promise

Introduced a new scalar promise to the module.