How Should I be using connection.transaction

[akc42]

The documentation for connection.transactions says that I can just use the callback parameter

So I call called back to my function with 'err' and 'done' as twp parameters. What it says is when I have completed by transaction I should then call done with either an error or null as its only parameter.

Looking at the code (I'll refer to line numbers in the repository - not the same on my working system but I assume that is because of transpiling with Babel or something). in connection.js at line 763 there is a function called txDone which I think is what gets passed to me as my 'done' callback. However the first thing it does is const args = new Array(arguments.length - 2); which then fails because I passed it only one argument (a null).

Is this a bug. What should I be calling 'done' with?

[akc42]

Having worked it through and tested it a bit, I think I now understand what I should call "done" with. It takes (at least) two parameters. The first is null or error dependent on whether you want to commit or rollback the transaction. The second is a callback which is called back with at least one parameter - but could have more if there were more than two in the done call above. The one parameter is an either null or error to indicate any problems with the commit/rollback, the others are the additional parameters that done was called with.

The whole transaction is only complete when the done calls callback has been called.

I guess I would therefore call this a documentation bug.

[arthurschreiber]

@akc42 Any suggestions on how we can improve the documentation to be a bit clearer on how this works?

[akc42]

In the heading connection.transaction(callback, name, isolationLevel) where it says done I would change the text to something like

This is a function that you call when you wish to complete the transaction.  
You should call it with at least two parameters:

 err:     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 you back when it has committed the
             transaction. It will call you with one or more parameters, namely:-
            err:       an indication of any problems with the final commit request,
            other:   the additional parameters you provided to tedious (see items just below).
  other:   other parameters, which tedious will reflect back to you when 

[wangzhiqiang1975]

@akc42
can you write example used connection.transaction(callback, name, isolationLevel)

[akc42]

@wangzhiqiang1975
Unfortunately I don't have something simple that I can show you and it would take a while to generate it. I have wrapped the whole tedious library in function generators so as to make it easier to code up stuff.

Here is an example of using the library . Its a method in another class. db is the main class and the exec method on it requires a function*() call which it iterates over passing a connection (an instance of a wrapper class round the tedious connection). This in turn has methods which wrap the existing tedious calls. So for instance below yield connection.prepare() connection.prepare returns a promise that is fulfilled when the connection emits a 'prepared' event after having called connecton.prepare();.

snapShotCounts() {
  const self = this;
  return this.setUpCounters.then( () => {
    debug('Snapshot counters');
    return self.db.exec(function*(connection) {
      return yield connection.transaction(function*(connection) {
        let request = connection.request(
          'INSERT INTO FunctionCounts(functionName,NoOfCalls) VALUES(@name,@count)');
        request.addParameter('name',self.db.TYPES.NVarChar);
        request.addParameter('count',self.db.TYPES.Int);
        let n = 0;
        yield connection.prepare();
        for(let counter in self.counters) {
          if (self.counters[counter].new) {
            debug('Inserting counter %s value %d',counter,self.counters[counter].value);
            yield connection.execute({name: counter, count: self.counters[counter].value});
            self.counters[counter].new = false;
          }
        }
        yield connection.unprepare();
        request = connection.request(
          'UPDATE FunctionCounts SET noOfCalls = @count WHERE FunctionName = @name');
        request.addParameter('name',self.db.TYPES.NVarChar);
        request.addParameter('count',self.db.TYPES.Int);
        yield connection.prepare();
        for(let counter in self.counters) {
          n++;
          if(!self.counters[counter].new && self.counters[counter].update) {
            debug('Updating counter %s value %d',counter,self.counters[counter].value);
            yield connection.execute({name: counter, count: self.counters[counter].value});
            self.counters[counter].update = false;
          }
        }
        yield connection.unprepare();
        return n;
      });
    }).then((n) => {
      debug('Snapshot Processed %d counters', n);
      return n;
    });
  })
}

Most functions are are called the same as the tedious ones, but are wrappers around then - so for example here is what transaction does as a wrapper round the tedious connection. This is a method on the connection class used above and expects a function*() to be given as its parameter.

transaction(callback) {
  const self = this;
  debug('Transaction Called');
  if (this.transit) {
    return Promise.reject(new Error('Transaction already in progress'));
  }
  this.transit = callback(this); //passes connection in for convenience
  debug('created transaction request interator');
  return new Promise((accept,reject) => {
    const cb = (err,done) => {
      const onResult = lastPromiseResult => {
        debug('In Transaction Promise Iterator');
        const obj = self.transit.next(lastPromiseResult);
        if (!obj.done) {
          debug('Transaction Iterator NOT done yet');
          obj.value.then(onResult, err => {
            self.transit = false;
            done(err,reject);
          });
        } else {
          debug('Transaction Done about to tell connection')
          done(null, err => {
            self.transit = false;
            if (err) {
              reject(err);
            } else {
              accept(obj.value);
            }
          });
        }
      };
      if(err) {
        reject(err);
      }
      onResult();  //starts the generator
    };
    self.connection.transaction(cb);  //call transaction with default params
  });
}

So sorry I couldn't make it more simple, but I think you can probably unwrap it to work out how to use transaction

[wangzhiqiang1975]

@akc42
my project will be build in Docker used Tedious connect SQL SERVER, Transaction must used ,Tedious support Linux and lots people told me it good use in project ,I'll study and try use it
Thanks a lots
have nice day

[wangzhiqiang1975]

@akc42
I try node.js connect SQL SERVER database Transaction successful ,at last used npm mssql package (this package use Tedious driver) and test pass in docker , I'm study nodejs no more than three month ,thanks
you give me help ,I'll study hard thank you

have nice day

[LuisBosquez]

@wangzhiqiang1975 I created a Docker image with tedious as well if you'd like to give it a try: https://hub.docker.com/r/lbosqmsft/mssql-nodejs-tedious/

[IanChokS]

I've updated the docs to match the current parameter connection.transaction takes,

Hopefully I got it right. If not, feel free to re-open this issue!