CODEWITHSUNDEEP
javascriptsql-servernode.js
lukehawk
lukehawk

Reputation: 1493

Close MSSQL connection using mssql in node.js

I am trying to write a script in node.js to query a MSSQL database. I am new to javascript, new to node.js, new to VSCode, but I know a few things about SQL. I have working code, but the connection never seems to close, and I cannot get the values OUT of the function.

SO, I have this chunk of code, which I got from the example from npm:

const sql = require('mssql');
var dbConfig = {
    server:'theServer',
    database:'theDB',
    user:'un',
    password:'pw',
    port:1433
};

sql.connect(dbConfig).then(pool => {
    // Query     
    return pool.request()
    .query('select top 10 * from THE_TABLE')
}).then(result => {
    console.log(result);
}).catch(err => {
    // ... error checks
})

This works, and I can see the 10 results get logged in the console. However, the code never stops running. How do I get the connection to close up and stop?

I really want the results to be saved into a variable, so I changed the code to this:

const sql = require('mssql');
var dbConfig = {
    server:'theServer',
    database:'theDB',
    user:'un',
    password:'pw',
    port:1433
};

let theList;
sql.connect(dbConfig).then(pool => {
    // Query     
    return pool.request()
    .query('select top 10 * from THE_TABLE')
}).then(result => {
    theList= result;
}).catch(err => {
    // ... error checks
})

console.log(theList);

This returns 'undefined' to the console for theList, and again the connection never seems to cose, and the script never shuts down.

How do I just grab the results of the query and move on down the road??

Upvotes: 8

Views: 25333

Answers (4)

KyleMit
KyleMit

Reputation: 29977

Using Async / Await

Here's how to open a connection, query, and close using async await

const sql = require('mssql/msnodesqlv8')

let config = {
    driver: "msnodesqlv8",
    server: serverName,
    database: databaseName,
    options: { trustedConnection: true }
};

// connect to db
let cnn = await sql.connect(config)

// query
let result = await sql.query(query)

// close connection
await cnn.close()

Upvotes: 2

ccordon
ccordon

Reputation: 1102

This happens because promises are asynchronous, which happens then when console.log(theList); is executed the query to the database is still in process therefore theList prints undefined because no value has been assigned to it.

Following your code and if your version of Node.js is 7.6.0 or higher you can do this: 

const sql = require('mssql');
var dbConfig = {
    server: 'theServer',
    database: 'theDB',
    user: 'un',
    password: 'pw',
    port: 1433
};

let theList;

function executeQuery() {
    return sql.connect(dbConfig).then(pool => {
        // Query     
        return pool.request()
            .query('select top 10 * from THE_TABLE')
    }).then(result => {
        theList = result;
    }).catch(err => {
        // ... error checks
    })
}

async function getData() {
    await executeQuery();
    console.log(theList);
}

getData();

Upvotes: 0

Vikas Gautam
Vikas Gautam

Reputation: 1913

This worked for me. There is a .close() method. 

const DB = require("mssql")
const config = require("./config.json")
DB.connect(config)
   .then((conn) => 
      conn.query("SELECT * FROM table1")
         .then((v) => console.log(v))
         .then(() => conn.close())
   )

Upvotes: 14

marvel308
marvel308

Reputation: 10458

you should call

process.exit()

in the end. Source Node Docs

Moreover 

sql.connect(dbConfig).then(pool => {
    // Query     
    return pool.request()
    .query('select top 10 * from THE_TABLE')
}).then(result => {
    theList= result;
}).catch(err => {
    // ... error checks
})

is an async function 

console.log(theList);

would not wait for it to update the value.

I think you want to do

sql.connect(dbConfig).then(pool => {
    // Query     
    return pool.request()
    .query('select top 10 * from THE_TABLE')
}).then(result => {
    theList= result;
}).catch(err => {
    // ... error checks
}).then(function(){
  console.log(theList);
  process.exit(1);
})

Upvotes: -4

Related Questions