passing Tedious connection as parameter

Question

I am trying to use a simple suite of functions built utilizing the Tedious library to access a Microsoft SQL Server. Here is my "tools" file:

'use strict';
const tedious = require('tedious');
const q = require('q');

var Connection = tedious.Connection;
var Request = tedious.Request;

module.exports = {

    connectSQL : function(config) {

        var connection = new Connection(config);

        connection.on('connect', function(err) {
            if (err) {
                console.log('FAIL ON CONNECT');
                console.log(err);
            } else {
                try {
                    /* ----- */
                    return connection;
                } catch (err) {
                    console.log(err);
                    return;
                }
            }
        });

        connection.on('error', function(err) {

           if (err) {
                console.log('FAIL ON ERROR');
                console.log(err);
            } else {
                console.log("Error called with no err object.");
            }
        });

    },

    executeSQL: function(connection, requestString) {

        var results = [];

        var request = new Request( requestString , function(err, data) {
            if (err) {
                console.log(err);
            } else {
                console.log( data );
            }

        });

        request.on('row', function(row) {
            //console.log(row);
            results.push( row );

        });

        request.on('requestCompleted', function(){
            console.log('Finished');
            return results;
        });

        connection.execSql(request);
    }
}

I call these functions as follows in my server file.

const sqlTools = require('./sqlTools.js');

var connection = sqlTools.connectSQL(config);

sqlTools.executeSQL(connection, "select * from dbo.test");

However, I get the error "TypeError: Cannot read property 'execSql' of undefined", even if I make the program sleep for 10 seconds before calling my function sqlTools.executeSQL (obviously not ideal).

I was able to get this to work by calling the request within the sqlTools.connectSQL function (at the "/* ----- */"), but I want to re-use the Tedious connection to make multiple calls. Any suggestions? Thanks!

~~~~~~~EDIT~~~~~~~~~~

With help from akinjide I was able to implement callbacks that allow me to make a single call to my SQL database. However, I am struggling to implement promises to make subsequent calls. I changed my "tools" file as such:

'use strict';
const tedious = require('tedious');
const q = require('q');

var Connection = tedious.Connection;
var Request = tedious.Request;

module.exports = {

    connectSQL: function(config) {

        var deferred = q.defer();
        var connection = new Connection(config);

        connection.on('connect', function(err) {
            if (err) {
                deferred.reject( err );
            } else { 
                deferred.resolve( connection );
            }
        });

        connection.on('error', function(err) {
            deferred.reject(err);
        });

        return deferred.promise;
    },


    executeSQL: function(connection, requestString, callback) {

        var results = [];

        const request = new Request(requestString, function(err) {

            callback(err);

        });

        request.on('row', function(row) {

            results.push(row);

        });

        request.on('requestCompleted', function() {

            console.log('request completed!');
            callback(null, results);

        });

        connection.execSql(request);
    }
}

and I call this code like this...

var promise = sqlTools.connectSQL(config);

promise.then(function (connection) {
    sqlTools.executeSQL(connection, "select * from dbo.test", function(err, results) {

      if (err) {
        console.log(err);
      }

      console.log(results);

    });

}).catch(function (err) {
    console.log(err);  
}).then(function (connection) {

    sqlTools.executeSQL(connection, "select * from dbo.test2", function(err, results) {

      if (err) {
        console.log(err);
      }

      console.log(results);

    });

}).catch(function(err) {
    console.log(err);
});

This returns the first call's results correctly, but unfortunately returns this error "TypeError: Cannot read property 'execSql' of undefined" for the second call as it is not recognizing the connection the second time around. Any suggestions?

You `connectSQL` function does not return anything... See https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call — jcaron, Sep 10 '18 at 22:13
Possible duplicate of [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — jcaron, Sep 10 '18 at 22:14

Akinjide · Accepted Answer · 2018-09-10T22:18:23.637

A better approach would be to pass a node.js callback style function as an argument to connectSQL.

return keyword won't work within an asynchronous program.

'use strict';

const tedious = require('tedious');
const Connection = tedious.Connection;
const Request = tedious.Request;

module.exports = {
    connectSQL: function(config, callback) {
        const connection = new Connection(config);

        connection.on('connect', function(err) {
            if (err) {
                callback(err);
            } else {
                callback(null, connection);
            }
        });

        connection.on('error', function (err) {
           callback(err);
        });
    },
    executeSQL: function(connection, requestString, callback) {
        let results = [];

        const request = new Request(requestString, function(err) {
            callback(err);
        });

        request.on('row', function(row) {
            results.push(row);
        });

        request.on('requestCompleted', function(){
            console.log('Finished');
            callback(null, results);
        });

        connection.execSql(request);
    }
}

Then you can require, use sqlTools.connectSQL passing two parameters config and function(err, connection) {}

const sqlTools = require('./sqlTools');

sqlTools.connectSQL(config, function(err, connection) {
   if (err) {
     console.log('FAIL ON CONNECT');
     console.log(err);
   }

   sqlTools.executeSQL(connection, "select * from dbo.test", function (err, results) {
      if (err) {
         console.log(err);
      } 

      console.log(results);
   });
});

Thanks for the response! This helps a lot, but I want to know how I can re-use the connection (perhaps if I was building a RESTful app). I see this example on the web [example](https://vmokshagroup.com/blog/building-restful-apis-using-node-js-express-js-and-ms-sql-server/) but it re-initiates the connection every time, which I can't imagine is ideal. — ziv, Sep 11 '18 at 15:22
You can return a promise from `var promise = sqlTools.connectSQL(config);`, then reuse the promise as `promise.then(function (connection) { console.log(connection) })`. But I know you can close connection and open when needed. — Akinjide, Sep 11 '18 at 15:25
I added some additional code in the original question where I tried implementing promises with only partial success. Let me know if you have any ideas, thanks! — ziv, Sep 11 '18 at 17:34

passing Tedious connection as parameter

1 Answers1