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.