Tedious: Trouble Getting Rows to Return

[gregorylmartin]

I have the following code...

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

var config = {
userName: 'myuser',
password: 'mypass',
server: 'biglongservername',
options: {
rowCollectionOnRequestCompletion: true
}
}

exports.sqlconfig = config;

exports.getdata = function(req,res,config,sqlstring){
var sqlconnection = new Connection(config);
var cb = req.query['callback'];
var retval = '';
console.log('Line 20');
sqlconnection.on('connect', function(err){
console.log('Line 22');
var request;
request = new Request(sqlstring, function(err, rowCount) {
console.log('Line 25');
if (err){
console.log('Error');
connection.close();
} else {
console.log('Success');
connection.close();
res.contentType('application/json');
retval = JSON.stringify(rows);
if (cb){
retval = cb + '(' + retval + ');'
}
res.write(retval);
res.end();
}
});
console.log('Line 41');
});
}

console.log 20,22, and 41 all get written. But 25 never does. The fact that 22 gets written tells me that I have a good "connection". I think. That being said, I can't understand why 25 never happens.

The above is just one section of the code. I know that the sqlstring being passed in is good. I'm stumped. Any help would be most appreciated.

[evanlucas]

Try to use code formatting. It's a lot easier to read the code :]

It looks like you are not actually executing the sql.

Try adding a sqlconnection.execSql(request) after you successfully connect.

[gregorylmartin]

I would love to format the code better, but I see no option for doing that. I read the GitHub Flavored Markdown and searched around a little for how to do it. But couldn't find anything.

As for execSql, I don't think I need it. I have another node.js project and it works beautifully. The line...

request = new Request(sqlstring, function(err, rowCount){

seems to be all it takes to get the code to execute. no execSql at all.

[cortfritz]

for GFM just put three backticks before and after your code.

[gregorylmartin]

"backticks" ?

GFM?

[cortfritz]

backticks: not sure what the name of the char is. it is the character typically in the upper left of your keyboard. on the same key you use to make a tilde but don't hit shift.

GFM: from the discussion above: Github Flavored Markup

also, I agree w/ evan, I don't think setting up a Request executes it IIRC. something like myConnection.callProcedure(myRequest)

[gregorylmartin]

I'm wish I didn't disagree, but this is what I have in another project...

request = new Request("catalog_name.dbo.proc_name", function(err, rowCount) {
    if (err) {
      console.log(err);
      connection.close();
      setTimeout(function(){console.log("Script Ending With Error");},10000);

      var strBody = '{"monitorName": "myLogglyStuff-err"';
      strBody = strBody + ', "timestamp": "' +strDt + '"}';
      postToLoggly(strBody);
    } else {
      console.log(rowCount2 + ' rows');
      connection.close();
      setTimeout(function(){console.log("Script Ending");},10000);

      var strBody = "";

      if (rowCount2 > 0){
        strBody = '{"monitorName": "myLogglyStuff-counter", "count": ' + rowCount2;
        strBody = strBody + ', "timestamp": "' + strDt + '"}';
        postToLoggly(strBody);
      }

      strBody = '{"monitorName": "myLogglyStuff-heartbeat"';
      strBody = strBody + ', "timestamp": "' + strDt + '"}';
      postToLoggly(strBody);
    }
  });

It works like a champ has has been for months. Just slaves along 24x7, no worries. No sqlconnection.execSql in sight.

[evanlucas]

It wouldn't be in the request callback. But, without actually calling one of the execute functions (or callProcedure), how is it actually sending the request to the server?

[gregorylmartin]

Oops! You are so right. When I grabbed my working code as a starting point for this one, I failed to see a line waaaaaaayyyyyyyy at the bottom that was doing the execSql.

Thank you, and my most sincere apologies.

[evanlucas]

No problem. Glad you found the issue

[gregorylmartin]

Ok. Got that to at least "work". Code looks like this...

exports.getdata = function(req,res,config,sqlstring){
    var sqlconnection = new Connection(config);
    var cb = req.query['callback'];
    var retval = '';
    sqlconnection.on('connect', function(err){
        var request;
        request = new Request(sqlstring, function(err, rowCount, rows) {
            if (err){
                console.log('Error');
                sqlconnection.close();
            } else {
                sqlconnection.close();
                res.contentType('application/json');
                retval = JSON.stringify(rows);
                if (cb){
                    retval = cb + '(' + retval + ');'
                }
                res.write(retval);
                res.end();
            }
        });
        sqlconnection.execSql(request);
    });
}

Now, I have a different question. The above returns json that looks somewhat like this...

[
    [
        {
            "value": 146,
            "metadata": {
                "userType": 0,
                "flags": 16,
                "type": {
                    "type": "INT4",
                    "name": "Int",
                    "id": 56
                },
                "colName": "LocationID"
            }
        },
        {
            "value": "Abilene",
            "metadata": {
                "userType": 0,
                "flags": 8,
                "type": {
                    "type": "BIGVARCHR",
                    "name": "VarChar",
                    "hasCollation": true,
                    "dataLengthLength": 2,
                    "maximumLength": 8000,
                    "id": 167
                },
                "colName": "LocationName",
                "collation": {
                    "lcid": 1033,
                    "codepage": "WINDOWS-1252",
                    "flags": 208,
                    "version": 0,
                    "sortId": 52
                },
                "dataLength": 64
            }
        }
    ]
]

Instead of that, I would much prefer to simply get JSON like this...

[
    [
        {
            "LocationID": 146,
            "LocationName": "Abilene"
        }
    ]
]

Any thoughts on the best way to accomplish that? I simply don't need all the metadata about the columns in the resultset. In fact, I could also do without one set of [ ] .

[gregorylmartin]

Figured it out. Takes a few minutes to get the hang of it, but very powerful stuff...

exports.getdata = function(req,res,config,sqlstring){
    var sqlconnection = new Connection(config);
    var cb = req.query['callback'];
    var retval = '';
    sqlconnection.on('connect', function(err){
        var request;
        request = new Request(sqlstring, function(err, rowCount, rows) {
            if (err){
                console.log('Error');
                sqlconnection.close();
            } else {
                var rowarray = [];
                rows.forEach(function(columns){
                    var rowdata = new Object();
                    columns.forEach(function(column) {
                        rowdata[column.metadata.colName] = column.value;
                    });
                    rowarray.push(rowdata);
                })
                sqlconnection.close();
                res.contentType('application/json');
                retval = JSON.stringify(rowarray);
                if (cb){
                    retval = cb + '(' + retval + ');'
                }
                res.write(retval);
                res.end();
            }
        });
        sqlconnection.execSql(request);
    });
}