Connecting SQL Database from NodeJs - ConnectionError: Connection not yet open

Question

I am trying to connect SQL database from NodeJs, but getting this error: Error while querying database :- ConnectionError: Connection not yet open.

 var executeQuery = function(res, query) { 
   var conn = new sql.ConnectionPool(dbConfig);

   conn.connect().then(function () {
     // create Request object
     var request = new sql.Request(conn);
     // query to the database
     request.query(query, function (err, queryResult) {
       if (err) {
         console.log("Error while querying database :- " + err);
         res.send(err);
       } else {
         res.send(queryResult);
       }
     });

     conn.close();
   })
}

Please help.

Thank You James

Can you include your require statement sql = require("...?!?");? — Ivan Drinchev
– Ivan Drinchev, Commented Aug 11, 2017 at 13:26
@kumbhanibhavesh the error i get is ConnectionError: Connection not yet open. — Amal
– Amal, Commented Aug 11, 2017 at 14:33

Ivan Drinchev · Accepted Answer · 2017-08-11 18:42:21Z

1

The connection pool class will return a promise, which resolves with the pool that was initiated by your connection :

 var executeQuery = function(res, query) { 

     var conn = new sql.ConnectionPool(dbConfig);

     conn.connect()
         .then(function (pool) {
             //    ^ the pool that is created and should be used

             // create Request object
             var request = new sql.Request(pool);
             //                            ^ the pool from the promise

             // query to the database
             request.query(query, function (err, queryResult) {
                 if (err) {
                     console.log("Error while querying database :- " + err);
                     res.send(err);
                 } else {
                     res.send(queryResult);
                 }
             });
             conn.close();
        });           
}

In your case conn variable is always the promise and not the connection itself.

A basic example of a connection pool from the documentation is as follows :

new sql.ConnectionPool(config).connect().then(pool => {
    return pool.query`select * from mytable where id = ${value}`
}).then(result => {
    console.dir(result)
}).catch(err => {
    // ... error checks
})

edited Aug 11, 2017 at 18:42

answered Aug 11, 2017 at 13:56

Ivan Drinchev

19.6k4 gold badges69 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Amal Over a year ago

@drichev Hi Can you please elaborate? My require statement is : sql = require("mssql"); You mean , I should be creating a DB pool? Could you please show that for me?

Ivan Drinchev Over a year ago

Well as far as what you pasted you want to create a connection pool and use it to make the query and the request. There are two ways to make it. 1 with a Promise and 2 with a callback. When you use it with a Promise ( no callback function provided ) you actually need to take what the promise resolved result is as your pool variable.

Amal Over a year ago

If possible could you please edit the same in my code to show the same? I am new to this, so not able tp grasp much.

Ivan Drinchev Over a year ago

Edited my answer.

Amal Over a year ago

thanks @drichev... but I am still getting the error like : ConnectionError: Connection not yet open. Mycurrent code is like exatly like your answer above. Kindly do help

Collectives™ on Stack Overflow

Connecting SQL Database from NodeJs - ConnectionError: Connection not yet open

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related