No results on my request

Question

I'm working on a small Nodejs/Express website that request a SQL Server database via Tedious.

When I create my connection outside any function, it seems to work but I can't refresh.

When I try to put it in my connx function nothing appends, it doesn't seem to enter in the conn.on('connect'...) order.

I'm quite lost. Hope you could help me.

var http = require('http');

var jade = require('jade');

var moment = require('moment');

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

var express = require('express');
var app = express();

var config = {
    server: 'myserver',
    userName: 'myuser',
    password: 'mypassword',
    options: {
        instanceName: 'myinstance',
        database: 'mydb',
    }
};

app.set('views','./views');
app.set('view engine','jade');

app.use('/public',express.static(__dirname + '/public'));

var results = [];

function requete1(dated, datef) {
    return ligne = "select Batch, Parametres, Etat, Durée, Resultat " +
                            "from LancementsNuit where HeureDebut >= '"+dated+" 18:00:00' " +
                            "and HeureFin <= '"+datef+" 18:00:00'";
}

function connx(x_date1,x_date2) {
    var conn = new Connection(config);
    //flush de result
    result = [];
    conn.on('connect', function () {
        console.log(requete1(x_date1,x_date2));
        var request = new Request(requete1(x_date1,x_date2), function(err, rowCount) {
            if (err)
                console.error(err);
        });

            request.on('row', function(row) {
                results.push({
                    batch: row[0].value,
                    parametres: row[1].value,
                    etat: row[2].value,
                    duree: row[3].value,
                    resultat: row[4].value
                })
            });
            conn.execSql(request);
            conn.close();
        })
}

app.get('/', function (req, res) {
    var datef = moment().format('YYYY-MM-DD');
    console.log(datef);
    var Weekday = moment().isoWeekday();
    if (Weekday == "1") {
        var dated = moment(datef,'YYYY-MM-DD').subtract(3, 'd').format('YYYY-MM-DD');
    } else {
        var dated = moment(datef,'YYYY-MM-DD').subtract(1, 'd').format('YYYY-MM-DD');
    }

    connx(dated, datef);
    console.log(results);
    res.render('index', { data: results,
                                                dated: dated,
                                                datef: datef
                                            });
})

app.get('/:dated', function (req, res) {
        // console.log(results);
        var datef = req.params.dated;
        var Weekday = moment(datef,'YYYY-MM-DD').isoWeekday();
        if (Weekday == "1") {
            var dated = moment(datef,'YYYY-MM-DD').subtract(3, 'd').format('YYYY-MM-DD');
        } else {
            var dated = moment(datef,'YYYY-MM-DD').subtract(1, 'd').format('YYYY-MM-DD');
        }

        console.log("dated = " + dated + "; datef = " + datef);

        connx(dated, datef);
        console.log(results);
        res.render('index', { data: results,
                                                    dated: dated,
                                                    datef: datef
                                                });
})

app.listen(Number(process.argv[2]));

Answer 1

conn.on('connect') is asynchronous, but it's inside a synchronous function - connx. It means res.render is called before it has finished, so you end up with result = []. You need pass a callback to connx and make sure it's called just when conn.on('connect') is done.

Take a look at this link, it will point you in the right direction.