Return data from asynchronous function in mssql module

Question

I am trying to get data from sql server database (I'm using mssql module to do that : https://www.npmjs.com/package/mssql) and send it to Redis. So I have created three files :

• sql_server.js contains all the functions related to sql_server (like connection to the database and queries)
• redis.js has functions like connection and set key/value to redis database (the key is an ID and the value is the result of sql_server query)
• server.js is the main file that call functions of the sql_server and redis files and act like a controller

I would like to stream the sql_server database and send the result to server.js for each row returned . Then the server.js file will pass the result to redis function that will create the couple key/value.

But I am not very familiar with node.js and I don't know how to return the query result to server.js for each row :

Here is sql_server.js function to stream database

      execRequests: function(requestEntry, flux){
    try{            
        const request = new sql.Request(pool)
        request.stream = true // You can set streaming differently for each request 
        request.query(requestEntry)// or request.execute(procedure) 

        request.on('recordset', columns => {
            //console.log(columns); 
        })

        request.on('row', row => {
            // THAT I AM DOING FOR NOW
            //redisMethods.setValue(JSON.stringify(row), flux);
            // THAT I WOULD LIKE TO TO
            //return row;
        })

        request.on('error', err => {
            // May be emitted multiple times 
        })

        request.on('done', result => {
            console.log("done for flux :" + flux);
        })



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

Here is server.js

    sqlMethods.connection().then(function(pool){
    try{
        if(pool._connected){
            console.log("Connected to SQL Server database : " + pool.config.database);
            // connection to redis database
            redisMethods.connection();
            // get the requests to execute
            sqlMethods.getRequests().then(function(requestArray){
                // loop over the array containing requests
                for (var i in requestArray){
                    let request = requestArray[i].requete; //extract the request
                    let flux = requestArray[i].flux; //extract the flux name
                    sqlMethods.execRequests(request, flux).then(function(result){
                       // redisMethods.setValue(JSON.stringify(result), flux);
                    });
                }
            });
        }
    } catch (err){
        console.log(err);
    }

Hope my explanation is understandable, I need your help to find a way to return the row from sql_server.js to server.js. Thank you !

Answer 1

I can advise you to use a callback function to pass your errors or data.

   execRequests: function(requestEntry, flux, callback){
    const request = new sql.Request(pool);
    request.stream = true; // You can set streaming differently for each request 
    request.query(requestEntry); // or request.execute(procedure) 

    var errors = [];
    var rows = [];

    request.on('recordset', columns => {
        //console.log(columns); 
    });

    request.on('row', row => {
        // THAT I AM DOING FOR NOW
        //redisMethods.setValue(JSON.stringify(row), flux);
        // THAT I WOULD LIKE TO TO
        //return row;
        rows.push(row);
    });

    request.on('error', err => {
        // May be emitted multiple times 
        errors.push(err);
    });

    request.on('done', result => {
        console.log("done for flux :" + flux);
        callback(errors, rows);
    });
}

And then in your server.js

sqlMethods.connection().then(function(pool) {
    try {
        if (pool._connected) {
            console.log("Connected to SQL Server database : " + pool.config.database);
            // connection to redis database
            redisMethods.connection();
            // get the requests to execute
            sqlMethods.getRequests().then(function (requestArray) {
                // loop over the array containing requests
                for (var i in requestArray) {
                    let request = requestArray[i].requete; //extract the request
                    let flux = requestArray[i].flux; //extract the flux name
                    sqlMethods.execRequests(request, flux, function (errors, rows) {
                        if (errors && errors.length) {
                            // There is errors
                        }
                        else if (rows && rows.length) {
                            // There is no errors but rows
                        }
                    });
                }
            });
        }
    } catch (err) {
        console.log(err);
    }
});

Hope it helps.