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

Are you on Azure? I see that you have option encrypt set to true which is only necessary if you're using Azure. — Stan Smith
– Stan Smith, Commented Jan 27, 2016 at 15:12
no i am simply using sql server in localhost, whether i remove the option encrypt , error is there. — Omsairam
– Omsairam, Commented Jan 27, 2016 at 16:39

Sibeesh Venu · Accepted Answer · 2016-11-27 14:08:44Z

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.

DaNeSh · Accepted Answer · 2016-05-11 15:00:50Z

3

You should remove

        options: {
            encrypt: true
        }

from your dbConfig

answered May 11, 2016 at 15:00

DaNeSh

1,0621 gold badge14 silver badges24 bronze badges

2 Comments

Dennis W Over a year ago

This doesn't really address the issue...and if you are using Azure SQL you have to set this flag to true anyway.

Damodar Bashyal Over a year ago

encrypt: false worked for me.

halfer · Accepted Answer · 2023-04-22 08:55:52Z

0

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

    });
}

edited Apr 22, 2023 at 8:55

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Oct 23, 2019 at 11:08

Majedur

3,2523 gold badges34 silver badges46 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related