CODEWITHSUNDEEP
expressnode-mssql
Omsairam
Omsairam

Reputation: 370

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

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);
            });
        };
}

Upvotes: 8

Views: 23040

Answers (3)

Majedur
Majedur

Reputation: 3242

I just use promise to handle concurrent request:

const executeQuery = function (res, query, dbName) {

    dbConfig = {
        user: "********",
        password: "********",
        server: "*******",
        database: dbName
    }

    sql.connect(dbConfig).then(pool => {

        return pool.request().query(query)
        
    }).then(result => {

        res.send(result);
        
        }).catch(err => {

            res.send(err);
         

    });
}

Upvotes: 0

Sibeesh Venu
Sibeesh Venu

Reputation: 21779

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.

Upvotes: 4

DaNeSh
DaNeSh

Reputation: 1062

You should remove

        options: {
            encrypt: true
        }

from your dbConfig

Upvotes: 3

Related Questions