how to get a function to return after running the sql query with nodejs

Question

I got the following nodejs code snippet working. It hangs in the end and does n ot return. In the end I want to get, insert and delete users based on the status. If the function does not return I cannot do so. I just gives this output.

[ { UserName: 'user4',
    LoggedStatus: 'avail',
    UserPass: 'demo',
    UserType: 'gen' },
  { UserName: 'user3',
    LoggedStatus: 'avail',
    UserPass: 'demo',
    UserType: 'gen' },
  { UserName: 'user2',
    LoggedStatus: 'avail',
    UserPass: 'demo',
    UserType: 'gen' },
  { UserName: 'user1',
    LoggedStatus: 'used',
    UserPass: 'demo',
    UserType: 'gen' } ]

......(hangs here)....

A) How to get the function getAllRecords() to return and the program to end?? B) Return particular rows or particular values from the DB.

var fs = require("fs");
var sql = require("mssql");

var config = {  
    "server": "localhost",
    "user": "xxxx",
    "password": "xxxx",
    "database": "dbTA",
     "port": 1433 
};


function getAllRecords(config) {
    sql.connect(config).then(function() {
        // Query
        new sql.Request().query('select * from USERSTATUS').then(function(recordset) {
            console.log(recordset);
        }).catch(function(err) {
            console.log("Error1", err);
        });
    }).catch(function(err) {
        console.log("Error2", err);
    });
}

getAllRecords(config);

Adding sql.close(); after the console.log(recordset) works !!! I will try to get answer of remaining part. :-) — win
– win, Commented Mar 16, 2017 at 18:26

kevrom · Accepted Answer · 2017-03-16 18:39:58Z

1

Promises are unwrapped using .then(), and anything returned in .then() will be wrapped in a Promise, allowing you to chain them together.

Let's add some returns and get the value out of your query.

var config = {  
  "server": "localhost",
  "user": "xxxx",
  "password": "xxxx",
  "database": "dbTA",
  "port": 1433
};

function getAllRecords(configuration) {
  // return the entire chain of promises
  return sql.connect(configuration)
    .then(function() {
      // return this query so we can have access to its value 
      return new sql.Request().query('select * from USERSTATUS');
    .catch(function(err) {
        console.log("Error1", err);
    });
  }).catch(function(err) {
    console.log("Error2", err);
  });
}

// since we're returning a promise we have to consume it using a `.then()`
getAllRecords(config).then(function(value) {
  console.log(value);
});

answered Mar 16, 2017 at 18:39

kevrom

1566 bronze badges

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

4 Comments

win Over a year ago

Yes, thats an alternative with Promises. However I was looking for sql.close(); in the end to end the program I added it in the end and it solved my problem. Thanks for your reply though. I will mark it as an answer though :-)

Sachin Over a year ago

He is already consuming it in .then. Not sure if question was updated afterwards.

kevrom Over a year ago

Ah I see. I misunderstood what the actual problem was.

win Over a year ago

By the way if I use use following again (which is quite the natural requirement) then I get error: getAllRecords(config).then(function(value) { console.log(value); }); and the error is Error1a { ConnectionError: Connection is closed. at C:\qpr\trunk\Source\EnticeUI\src\main\frontend\node_modules\mssql\lib\main.js:1569:17 at _combinedTickCallback (internal/process/next_tick.js:67:7) at process._tickCallback (internal/process/next_tick.js:98:9) name: 'ConnectionError', message: 'Connection is closed.', code: 'ECONNCLOSED' }

Sachin · Accepted Answer · 2017-03-16 19:26:12Z

1

Since you are not closing your DB connection, that active connection is preventing node from ending.

You should close your DB connection in your then and catch blocks using sql.close()

answered Mar 16, 2017 at 19:26

Sachin

3,5704 gold badges21 silver badges29 bronze badges

4 Comments

win Over a year ago

By the way if I use use following again (which is quite the natural requirement) then I get error: getAllRecords(config).then(function(value) { console.log(value); }); and the error is Error1a { ConnectionError: Connection is closed. at C:\qpr\trunk\Source\EnticeUI\src\main\frontend\node_modules\‌mssql\lib\main.js:15‌69:17 at _combinedTickCallback (internal/process/next_tick.js:67:7) at process._tickCallback (internal/process/next_tick.js:98:9) name: 'ConnectionError', message: 'Connection is closed.', code: 'ECONNCLOSED' }

Sachin Over a year ago

@win Yes, you will have to open connection again, because you closed it in previous function. That is upto your requirements and scenario, wether to open/close connection everytime or after a set of operations.

win Over a year ago

Thanks for your comment Sachin, but the function getAllRecords() does a sql.connect() everytime it is called. The next time the function is called even if I pass it a new this following sql = require("mssql"); object it complains that connection has been closed and does not connect again. How can I get past this problem?

Sachin Over a year ago

create a new connection like this var connection = new sql.Connection({ /* config */ }) and then connect using connection.connect npmjs.com/package/mssql#connect-callback

Collectives™ on Stack Overflow

how to get a function to return after running the sql query with nodejs

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related