Node js: mssql [ConnectionError: Connection is closed.] name: 'ConnectionError', message: 'Connection is closed.', code: 'ECONNCLOSED'

Question

i am getting error in npm mssql 3.0.0 with sqlserver 2012

i am creating single page application where i used restful using express . there are 4 method which executing the query and returning the data to response. for each method i am opening the connection and closing the connection.

but when savedquery is calling then connection close error occurs.

each method code is similar to savedquery method (copy pasted code only queries are changed) but they are executing savedquery is not executing

{ [ConnectionError: Connection is closed.] name: 'ConnectionError', message: 'Connection is closed.', code: 'ECONNCLOSED' }

var savedquery=function(req,res){
       dbConfig= {
                user: 'XXX',
                password: 'XXXXXXXXXX',
                server: 'localhost', // You can use 'localhost\\instance' to connect to named instance 
                database: 'DEMO_ODS',       
                options: {
                    encrypt: true
                }
            };

        sql.connect(dbConfig).then(function (err) {
                var sqlrequest = new sql.Request();
                sqlrequest.query("SELECT * from SavedQuery").then(function (recordset) {
                    sql.close(function (value) {
                      console.log("connection6 closed");
                    });
                    return res.status(200).send(recordset);

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

Answer 1

I know it is an old questionm but this answer is for the others who are facing the same isue. I had the same problem, What I did is, used promises as below.

    function getData() {
    try {
        sqlInstance.connect(setUp)
            .then(function () {
                // Function to retrieve all the data - Start
                new sqlInstance.Request()
                    .query("select * from Course")
                    .then(function (dbData) {
                        if (dbData == null || dbData.length === 0)
                            return;
                        console.dir('All the courses');
                        console.dir(dbData);
                    })
                    .catch(function (error) {
                        console.dir(error);
                    });

                // Function to retrieve all the data - End

                // To retrieve specicfic data - Start
                var value = 1;
                new sqlInstance.Request()
                    .input("param", sqlInstance.Int, value)
                    .query("select * from Course where CourseID = @param")
                    .then(function (dbData) {
                        if (dbData == null || dbData.length === 0)
                            return;
                        console.dir('Course with ID = 1');
                        console.dir(dbData);
                    })
                    .catch(function (error) {
                        console.dir(error);
                    });
                // To retrieve specicfic data - End

            }).catch(function (error) {
                console.dir(error);
            });
    } catch (error) {
        console.dir(error);
    }
}

This solved my issue. You can find the fix here.

Answer 2

You should remove

        options: {
            encrypt: true
        }

from your dbConfig