NodeJS MSSQL多个查询承诺

Question

我在使用mssql npm库时有很多问题。

这是我的db类：

"use strict"
const mssql = require('mssql');
const moment = require("moment");
let pool_historian = null;
let connexion_historian = null;

function mapBarChart(array) {
    return new Promise((resolve, reject) => {
        let result = {}
        result.old = []
        result.this = []
        let currentYear = null;
        for (let i = 0; i < array.length; i++) {
            if (parseInt(moment().format("YYYY")) !== array[i].Annees) {
                result.old.push(array[i]);
            } else {
                result.this.push(array[i]);
            }
        }
        resolve(result);
    })
};

class Historian {
    constructor(data) {
        pool_historian = new mssql.ConnectionPool({
            server: data.host,
            user: data.username,
            password: data.password,
            database: data.historian_db,
            pool: {
                max: 50,
                min: 1
            }
        });
    }

    getBarChart(sensor, from, to) {
        return pool_historian.connect().then(connector => {
            return connector.query`SELECT Annees=YEAR(DateTime),Mois=MONTH(DateTime), Valeur=ROUND(sum(AnalogHistory.Value),2) FROM AnalogHistory WHERE AnalogHistory.TagName IN (${sensor}) AND Quality = 0 AND wwVersion = 'Latest' AND wwRetrievalMode = 'Full' AND DateTime >= ${from} AND DateTime <= ${to} AND AnalogHistory.Value > 0 GROUP BY YEAR(AnalogHistory.DateTime),MONTH(AnalogHistory.DateTime) ORDER BY Annees, Mois`.then(result => {
                connector.close();
                return mapBarChart(result.recordset).then(result => { return result });
                //return result.recordset;
            }).catch(err => {
                return err;
            })
        })
    }
    getLineChart() {
        return pool_historian.connect().then(connector => {
            let variable = "A_000000000000000000000000000045.PV";
            return connector.query`SELECT Annees=YEAR(DateTime),Mois=MONTH(DateTime),day=DAY(DateTime), Valeur=ROUND(sum(AnalogHistory.Value),2) FROM AnalogHistory WHERE AnalogHistory.TagName IN (${variable}) AND Quality = 0 AND wwVersion = 'Latest' AND wwRetrievalMode = 'Cyclic' AND DateTime >= '20160101 00:00:00.000' AND DateTime <= '20170809 00:00:00.000' AND AnalogHistory.Value > 0 GROUP BY YEAR(AnalogHistory.DateTime),MONTH(AnalogHistory.DateTime), Day(AnalogHistory.DateTime)ORDER BY Annees, Mois`.then(result => {
                connector.close();
                return result.recordset;
            }).catch(err => {
                return err;
            })
        })
    }

    close() {
        pool_historian.close()
    }
}

这个类在这个“商业类”中使用：

const Historian = require(`${__dirname}/historian-query`)
const Fdedb = require(`${__dirname}/fdedb-query`)
const moment = require('moment');

moment.locale("fr-FR");

class Graph_Tasks {
    constructor() {
        this.historian = new Historian({ host: "192.168.1.16", username: "******", password: "w***", historian_db: "R******e" })
        this.fdedb = new Fdedb({ host: "192.168.1.16", username: "*****", password: "*****", fde_db: "S*****" })
    }

    createGraphForBuilding(code) {
        return new Promise((resolve, reject) => {
            this.fdedb.getList(code).then(list => {
                console.log(list)
                let datas = [];

                //Foreach item on the list perform these 2 queryes

                Promise.all([this.historian.getLineChart("A_000000000000000000000000000045.PV", moment().subtract(1, "years").startOf("year").format(), moment().format()), this.historian.getBarChart("A_000000000000000000000000000045.PV", moment().subtract(1, "years").startOf("year").format(), moment().format())]).then(results => {
                    let datas = []
                    datas = { "lineChart": null, "barChart": results[0] };
                    console.log(datas)
                    res.render('index', { title: 'WebGraph', message: 'Yo Yo', datas });
                })

                console.log(datas)
                resolve(datas)

            }).catch(console.log);
        });

    }
}
module.exports = Graph_Tasks;

正如您所看到的，我试图执行的是同时执行数据库请求。正如我在文档中所看到的，连接池必须允许我正确地执行这个操作。因此，当程序到达Promise.all时，我预计这两个请求将同时启动。

但我发现了一个错误：

Une s‘’est produite :错误 承诺拒绝(ConnectionError:已经连接到数据库了！))在连接到不同的数据库之前调用close。) ConnectionError:已经连接到数据库了！在连接到不同的数据库之前调用close。 在(d:\repositories\fde\node_modules\mssql\lib\base.js:1428:7) ConnectionError 在(d:\repositories\fde\node_modules\mssql\lib\base.js:235:23) ConnectionPool._connect 在(d:\repositories\fde\node_modules\mssql\lib\base.js:217:19) EventEmitter.connect.PromiseLibrary 在(d:\repositories\fde\node_modules\mssql\lib\base.js:216:12) ConnectionPool.connect 在(d:\repositories\fde\class\historian-query.js:39:31) Historian.getBarChart 在__dirname.createGraphForBuilding.Promise.fdedb.getList.then.list (d:\repositories\fde\class\graph_tasks.js:21:188) 在process._tickCallback (内部/进程/next_tick.js:109:7)

因此，我的问题是:如何调整代码，让我同时执行多个查询(每个列表项的promise.all )？

Answer 1

问题是您不能打开到同一台服务器的多个连接池(我假设Fdedb正在打开另一个连接，因为您没有为它包括代码)。比方说，如果您从两个不同的服务器中提取数据，那么打开两个连接池是合适的--我以前也遇到过这个用例。但是看起来您的两个数据库位于同一个服务器(localhost)上，所以最好只打开一个连接并将它传递给您的对象来进行查询。您可以使用普通的旧SQL访问同一主机上的多个数据库，请参阅：How do I query tables located in different database?。