Trouble with asynchronous requests pulling data from SQL database in Microsoft Bot Framework

Question

I have a bot in the Microsoft bot Framework that I want to be able to pull data from an azure SQL database in order to answer questions asked to the bot. I have set up the database and it has some excel files in it.

Here is my code right now:

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

var connection = new Connection(dataconfig);  
connection.on('connect', function(err) {  
    console.log("Connected");  
    executeStatement();
});  

var Request = require('tedious').Request;  
var TYPES = require('tedious').TYPES;  

function executeStatement() {  
    request = new Request("select \"Product Name\" from SPA_Data_Feeds where \"Strategic Priority\" = 'Accelerate to Value (LD)'", 
        function(err, rowCount, rows) 
        {
            console.log(rowCount + ' row(s) returned');
        }
    );


    var result = "";
    var count = 0



    request.on('row', function(columns) {
        columns.forEach(function(column) {
            console.log("%s\t", column.value);
            result+= column.value + "\t\n";
            count++;
            if ( count == rowCount ) {
                ATVData(result);
            } ;
        }); 
    });

    connection.execSql(request);
} 

function ATVData(result) { //Puts "result" inside of an adaptive card }

I cant seem to figure out how to get the if statement right. rowCount does not work because it does not wait for it to be defined by the function before first, and I have tried using things like column(s).length, result(s).length but none work.

Is there something else I could use that would complete the if statement? Or do I need to reformat somehow with callbacks/promises to get it to wait for rowCount to be defined? If so could I get some advice on that?

Answer 1

I think to expand on Grace's answer, for each row, you can also do this for some utility:

 request.on('row', function(columns) {
    var singleResult = {};
    columns.forEach(function(column) {
        console.log("%s\t%s", column.metadata.colName, column.value);

        // Add a property to the singleResult object.
        singleResult[column.metadata.colName] = column.value;

        // Push the singleResult object to the array.
        result.push(singleResult);
     });
 });

Then you can, in your bot's code, call each object by the property name in dot notation, for example: result[x].colName where colName is the name of the column (or object property in this case).

Example (assuming at least one result item from the database, with a "link" column that has data):

var adaptiveCardExample = {
    'contentType': 'application/vnd.microsoft.card.adaptive',
    'content': {
        '$schema': 'http://adaptivecards.io/schemas/adaptive-card.json',
        'type': 'AdaptiveCard',
        'version': '1.0',
        'body': [ 
            {
                "type": "TextBlock", 
                "text": "Code Example"
            },
            {
                "type": "TextBlock", 
                "text": "We're going to " + result[0].link,
                "wrap": true
            }],
            'actions': [
                {
                    'type': 'Action.OpenUrl',
                    'title': 'Go to the example link',
                    'url': result[0].link
                }
            ]
        }
    };

var adaptiveCardMsg = new builder.Message(session).addAttachment(adaptiveCardExample);
session.send(adaptiveCardMsg);

You may want to add a check for null or undefined for the property in the case it is a nullable field in the database, as a precaution.

Answer 2

Is there something else I could use that would complete the if statement? Or do I need to reformat somehow with callbacks/promises to get it to wait for rowCount to be defined? If so could I get some advice on that?

We can use Q.js which is one of the JavaScript Promise implementation to solve this issue. For example:

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

var q = require('q');

// Create connection to database
var config = 
   {
     userName: '', // update me
     password: '', // update me
     server: '', // update me
     options: 
        {
           database: '' //update me
           , encrypt: true
        }
   }
var connection = new Connection(config);

// Attempt to connect and execute queries if connection goes through
connection.on('connect', function(err) 
   {
     if (err) 
       {
          console.log(err)
       }
    else
       {
           queryDatabase().then(function(result){
            ATVData(result);  
           }, function(err){
              console.log(err);
           });
       }
   }
 );

function queryDatabase()
{ 
    console.log('Reading rows from the Table...');
     //create a promise
     var deferred = q.defer();
    // Read all rows from table
    var result = [];
    var request = new Request(
          "SELECT * From ForumMessages",
             function(err, rowCount) 
                {
                    deferred.resolve(result);
                });

     request.on('row', function(columns) {
        columns.forEach(function(column) {
            console.log("%s\t%s", column.metadata.colName, column.value);
            result.push(columns);
         });
     });
     connection.execSql(request);
     //return the promise
     return deferred.promise;
}

function ATVData(result){
    //your bot code goes here
}