Node mssql Error: "Connection is closed" when I run several queries at the same time

Question

Node mssql Error: "Connection is closed" when I run several queries at the same time

Navigation

#1 by (0 votes)

0

It turns out that I'm using Node and SQLServer and angular, when I make a query, I do not get any error, the detail is when for example I enter a form and I want to load data as types of ID, user types, ect . In which I call a query for each one to load them in their respective select. As I read, the queries are truncated between them, so I made a delay to give time to complete them, however if I go into production the number of users working will leave me those errors and I want to fix it once and for all.

var express = require('express');
var bodyParser=require('body-parser');
var Connection = require('tedious').Connection;
const  sql = require("mssql");
var jwt=require('jsonwebtoken');
var expressJwt=require('express-jwt');
var app = express();

var dbConfig = {
	"user": "sa",
	"password": "*****",
	"database": "****",
	"host": "localhost",
	"dialect": "mssql",
	"port": 1433
};

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

app.get("/api/station", function(req , res){
	var query = "select * from Estacion";
	executeQuery (res , query);
});

app.get("/api/educationlevel", function(req , res){
	var query = "select * from nivelEducativo";
	executeQuery (res , query);
});


app.get("/api/relationship", function(req , res){
	var query = "select * from parentesco";
	executeQuery (res , query);
});

If I run one at a time it works for me, but when I load them all at once I get the error "Connection is closed."

I appreciate all your cooperation.

sql-server nodejs

asked by Andres Godoy 16.03.2018 в 21:22

source

1 answer

How do I store what I have selected in canvas and fabric Firebase + Fragments + Recycleview

score 0 · Answer 1

I already found the solution. Apparently in the request the connection is lost when making several requests at the same time. Therefore in the Request constructor you must place the connection. If you do not place it, it will try to guess the open connection, at that moment it may be that when making several requests it chooses a connection that is closed.

solution link

Now the code remains like this:

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

Change this:

sql.connect(dbConfig, function (err) {

for

var connection =  new  sql.Connection(dbConfig);
connection.connect(function(err) {

And in the request add the connection:

Before:

var request = new sql.Request();

After:

var request = new sql.Request(connection);