javascript - 使用 Node.js 全局连接 Sql 查询

Question

我正在构建一个连接到 SQL Server 进行查询的 Node.js 应用程序!该应用程序分为 Router-Controller-Model 和一个名为 DatabaseConfig.js 的文件，该文件继续数据库的配置参数。

当我在模型中运行查询时，出现以下错误:

Global connection already exists. Call sql.close() first.
at Object.connect (/Users/myuser/Desktop/myapp/src/ServerJS/ServerJS/node_modules/mssql/lib/base.js:1591:31)*

如何解决此类错误？

型号:

async function CaricaRisorseCantiere(IdCantiere) {

    var data=[];
    const query = "select  IdRisorseUmane,IdUtenteInserimento,u1.Nome+' '+u1.Cognome as InseritoDA,ExtraPreventivo,u2.Nome+' '+u2.Cognome as Risorsa,RisorseUmane.IdUtente,IdCantiere,CONVERT(VARCHAR(10), Data, 105) as Data,Descrizione,convert(varchar(5), OreInizio, 108) as OreInizio,convert(varchar(5), OreFine, 108) as OreFine,REPLACE(Pausa, '.', ':') as Pausa,convert(varchar(5), Cast(convert(varchar(5), (OreFine - OreInizio), 108) as datetime) - CAST(REPLACE(Pausa, '.', ':') as datetime), 108)  as TotaleOre   from RisorseUmane  inner join Utente as u1  on u1.IdUtente = RisorseUmane.IdUtenteInserimento   inner  join Utente as u2 on u2.IdUtente = RisorseUmane.IdUtente where IdCantiere= @IdCantiere  order by convert(datetime, Data, 103) desc";
    const pool = new sql.ConnectionPool(DbConfigJSON);

    pool.connect().then(() => {
        const request = new sql.Request(pool);
        request.input('IdCantiere', sql.Int, IdCantiere);

        request.query(query).then((result) => {

            for(var i=0; i<result.recordset.length; i++){     
                data.push({
                    IdRisorseUmane: result.recordset[i].IdRisorseUmane,
                    IdUtenteInserimento: result.recordset[i].IdUtenteInserimento,
                    InseritoDA: result.recordset[i].InseritoDA,
                    ExtraPreventivo: result.recordset[i].ExtraPreventivo,
                    Risorsa: result.recordset[i].Risorsa,
                    Data: result.recordset[i].Data,
                    Descrizione: result.recordset[i].Descrizione,
                    TotaleOre: result.recordset[i].TotaleOre
              });
            }
            return data;
        });
    }).catch(err =>{
         console.log("Errore: ",err);
    });

}

DatabaseConfig.js

exports.config = {
    user: 'remote',
    password: 'xx',
    server: 'xx',
    database: 'xx',
    debug: false,
    port: 1433,
    options: {
        encrypt: false
    },
    pool: {
        max: 30,
        min: 0,
        idleTimeoutMillis: 30000
    }

};

Answer 1

我将在此处添加一个连接池使用的简单示例:

const DbConfig = require('./DatabaseConfig.json');
const sql = require('mssql');

const query = 'select * from people';
const pool = new sql.ConnectionPool(DbConfig);

pool.connect().then(() => {
    const request = new sql.Request(pool);
    request.query(query).then ((result) => {
        console.log('Query result: ', JSON.stringify(result));

    });
}).catch ((error) => {
   console.error('An error occurred connecting to the db: ', error);  
});

这假设有一个名为“people”的表，您可以根据需要重命名。

DatabaseConfig.json 文件如下所示:

{
   "user": "some_user",
   "password": "pw",
   "server": "db_Server",
   "database": "test_db",
   "port": 1433
}

这是另一个示例，从函数返回结果(根据请求):

const DbConfig = require('./DatabaseConfig.json');
const sql = require('mssql');

const query = 'select * from people';
const pool = new sql.ConnectionPool(DbConfig);

async function testQuery() {
    await pool.connect();
    const request = new sql.Request(pool);
    const result = await request.query(query);
    return result;
};

testQuery().then ((result) => {
    console.log('Result: ', JSON.stringify(result));
});