re-executing statement from a loop

[noregrets]

First of all, great work on this module.

I have a handful of items I need to loop through and query the db for information about each item. My code works fine if I only query the db once, but as soon as I query in a loop, I get an error stating that the client must be in the initial state before connecting. I assume I need to end the statement or connection after each query, but I'm not sure how to do that correctly. I tried numerous approaches for closing the connection with the same end result.

Here is my function that queries the db:

 function queryMSSQL(callback) {
   var length = ''; 
   conn.connect(function(error) {
     if (error != null) {
       console.error('Received error while connecting to local db', error);
     } else {  
       var stmt = conn.createStatement("SELECT cast(length as char) from mytable where filename = '123456'");   
       stmt.on('row', function(row) {
         //console.log('Received row: ', row.getValue(0));
         length = row.getValue(0);
       });
       stmt.on('done', function(done) {
         conn.end();   
       });           
       stmt.execute();
     }
   })
   .on('end', function() {
     console.log('connection ended');
     callback(length);
   });   
 }

Here is how I call the function inside a loop. I will pass it more params when I'm done testing - right now I just execute the same static query for testing purposes:

 queryMSSQL(function(returnValue) {
      console.log('length: ' + returnValue);
 });

I would greatly appreciate any advice to point me in the right direction.

[cretz]

Don't close the connection each time your statement is done, just reuse the connection. If you do close the connection each time, you have to re-connect.

Also, if you are reusing the statement lots, just create the statement with parameters and you can call execute(params) on it as much as you want. So something like this would work to execute a statement 100 times:

var conn = new Connection(myconfig);
var stmt = null;
conn.on('connect', function() {
  stmt = conn.createStatement('SELECT @somenum AS Val', { somenum: { type: 'Int' } });
  var index = 0;
  stmt.on('row', function(row) {
    console.log('Value: ', row.getValue('Val'));
  });
  stmt.on('done', function() {
    if (++index < 100) {
      stmt.execute({somenum: index });
    } else {
      conn.end();
    }
  });
  stmt.execute({somenum: 0})
});

(note, I just scratched this down, I didn't really test it, but the idea is the same). Does that help?

[noregrets]

Thank you for your quick and thorough response. I like your parameters suggestion and will implement that once I get it functional in this state.

Your example makes sense and works perfectly if I re-execute the statement as you show above. However, I actually need to re-execute the statement from a different function. What I'm trying to do looks something like this:

I moved the connect outside of the query function and placed it at the top of my file so it is not reconnected each time my query function is called. I also create my statement there:

var stmtMeas = '';
var conn = new tds.Connection({
  host: 'dbserver',
  port: 1433,
  userName: 'user',
  password: 'pw'
});
conn.connect(function(error) {
  if (error != null) {
    console.error('Received error while connecting to local db', error);
  } else {
    console.log('Database connection ready');

    stmtMeas = conn.createStatement("SELECT cast(length as char) from table_name where order = '123456'");
  }
});  

I have a query function that I need to call repeatedly and will return the query result via the callback function that is passed to it:

function queryMSSQL(callback) {

  var length = ''; 
  stmtMeas.on('row', function(row) {
    //console.log('Received row: ', row.getValue(0));
    length = row.getValue(0);
  });
  stmtMeas.on('done', function(done) {
    callback(length);   
  });           
  stmtMeas.execute();
}

Elsewhere in the file, I have another function that collects data about a particular item or list of items from various sources, once of which is an mssql db. It needs to call the aforementioned query function to get a piece of data. I am calling the query function something like this:

function another_function() {
     ...

     for(var i = 0; i < number_of_items; i++) {    
           //get measurements from MSSQL db
           queryMSSQL(function(returnValue) {
                console.log('orthotic length: ' + returnValue);
           });
     }

     ...
}

This approach also works fine for a single query, but when I try to execute it multiple times in the for loop, I get an error telling me that "another statement is already executing." I didn't expect to get this since I am executing the callback function in the statement's done event. Can you see what I'm doing wrong?

[cretz]

In that last snippet, you're not going to be able to do a for loop here as it is the antithesis of async processing. Instead, you have to maintain a counter and call queryMSSQL inside the callback of the first call (incrementing the counter each time). If the counter hits number_of_items, then you can consider yourself done. So, you're last snippet would look like:

function another_function(all_done_callback) {
  count = 0;
  cb = function(returnValue) {
    console.log('orthotic length: ' + returnValue);
    if (++count < number_of_items) {
      queryMSSQL(cb);
    } else {
      all_done_callback();
    }
  };
  queryMSSQL(cb);
}

I have seen drivers like jTDS allow multiple concurrent statements per connection, but this driver currently doesn't support that. So, since only one statement can be running at a time, you have to make sure you aren't calling execute before the previous one's done event. Also, if the async stuff is annoying, you may want to peek at streamline.js. I hope this helps.

[noregrets]

Thank you for your help and advice - I think I am back on track now. This is my first foray into node, so I'm still getting used to the async approach.

[cretz]

Good deal.