如何使用Node.js和连接池一次与 SQLServer 数据库建立多个连接?

Question

我正在使用池连接构建一个带有 SQLServer 数据库的节点.js应用程序。 它具有异步功能，每个记录完成后都应将一条记录保存到数据库中。 问题是只插入了来自 10 个异步任务的大约 10 条记录，其余的给出此错误"已连接到数据库！在连接到其他数据库之前调用 close。

我尝试编辑池的配置，但在风向标中。

var config = {
host: "localhost",
user: "test",
password: "test",
database: "testDB",
server: '************',
pool: {
max: 20,
maxWaitingClients:1,
fifo:true,
min: 0,
idleTimeoutMillis: 30000
}
};
const pool = new sql.ConnectionPool(config);
pool.on('error', err => {
console.error('SQLServer Pool Error: ', err.toString());
});
async writeToDB(System, url, PortListening, RequestTest, time, TestType, Request, Response, responseTime, pool) {
try {
var query = "INSERT INTO `testTable` " +
"(SystemName,URL,PortListening,RequestTest,TestDate,TestType,Request,Response,ResponseTime) " +
"VALUES " +
"('" + System + "','" + url + "','" + PortListening + "','" + RequestTest + "','" + time + "','" + TestType + "',N'" + Request + "',N'" + Response + "'," + responseTime + ")";
await pool.connect();
let result =await  pool.request().query(query);
return {success: result};
} catch (err) {
console.log(System);
console.error(err);
return {err: err};
} finally {
pool.close();
}
}

它应该插入所有异步任务中的所有行，而不会丢失任何数据。

Answer 1

从池设置中省略 maxWaitingClient。让客户自动处理它。

Answer 2

我找到了答案。我应该在应用程序启动时打开池"pool.connect(("一次，然后在应用程序关闭之前关闭它。所以代码应该是这样的：

var config = {
host: "localhost",
user: "test",
password: "test",
database: "testDB",
server: '************',
pool: {
max: 20,
maxWaitingClients:1,
fifo:true,
min: 0,
idleTimeoutMillis: 30000
}
};
const pool = new sql.ConnectionPool(config);
pool.on('error', err => {
console.error('SQLServer Pool Error: ', err.toString());
});
pool.connect();
async writeToDB(System, url, PortListening, RequestTest, time, TestType, Request, Response, responseTime, pool) {
try {
var query = "INSERT INTO `testTable` " +
"(SystemName,URL,PortListening,RequestTest,TestDate,TestType,Request,Response,ResponseTime) " +
"VALUES " +
"('" + System + "','" + url + "','" + PortListening + "','" + RequestTest + "','" + time + "','" + TestType + "',N'" + Request + "',N'" + Response + "'," + responseTime + ")";

let result =await  pool.request().query(query);
} catch (err) {
return {err: err};
} 
}