nodejs - 使用mssql运行多个查询

I have an express API with a POST request containing the SQL query using mssql. This is working and returns the result as JSON well. The issue I have, is when I want to call this more than once while the other query is still running..

我有一个带有POST请求的快速API，其中包含使用mssql的SQL查询。这是有效的，并将结果作为JSON返回。我遇到的问题是，当我想在另一个查询仍在运行时多次调用它时...

Here is my code:

这是我的代码：

app.post('/select', (req, res) => {
  config.database = req.body.db;

  var sqlQuery = `SELECT ${req.body.select} FROM ${req.body.from}`;
  if (req.body.where !== '' && req.body.where !== undefined) sqlQuery += ` WHERE ${req.body.where}`;
  if (req.body.order !== '' && req.body.order !== undefined) sqlQuery += ` ORDER BY ${req.body.order}`;
  console.log(`Query: ${sqlQuery}`);

  sql.connect(config).then(pool => {
    return pool.request().query(sqlQuery)
  }).then(result => {
    sql.close();
    sqlDone = true;
    console.dir(result);
    res.header('Content-Type', 'Application/JSON');
    res.json(result.recordset);
  }).catch(err => {
    sql.close();
    sqlDone = true;
    console.log('Caught Error:');
    console.log(err);
  });
  sql.on('error', err => {
    sql.close();
    console.log('SQL Request Error:');
    console.log(err);
  });
});

The error I get when I call this from my react webapp with superagent is:

我从反应webapp和superagent调用这个错误时得到的错误是：

Error: Global connection already exists. Call sql.close() first.

This is irritating as I have to call my API synchronously and wait for the first query to return, before i can call the next one..

这很烦人，因为我必须同步调用我的API并等待第一个查询返回，然后才能调用下一个查询。

Does anyone know how to run multiple queries at once? This would have to be a separate connection as I am running a query on one db then another. Same server though.

有谁知道如何一次运行多个查询？这必须是一个单独的连接，因为我在一个数据库上运行查询，然后是另一个数据库。但是服务器相同。

1 个解决方案

#1

OK I have managed to get past this by using the wait-until library to wait until a boolean is true as suggested by @SPlatten

好的我已经设法通过使用wait-until库来等待，直到布尔值为真，如@SPlatten所建议的那样

app.post('/select', (req, res) => {
  waitUntil()
    .interval(500)
    .times(60)
    .condition(() => {
      return (sqlDone ? true : false);
    })
    .done((result) => {
      sqlDone = false;
      sql.connect(config).then(pool => {
        console.log();
        console.log('Connected!');

        config.database = req.body.db;

        var sqlQuery = `SELECT ${req.body.select} FROM ${req.body.from}`;
        if (req.body.where !== '' && req.body.where !== undefined) sqlQuery += ` WHERE ${req.body.where}`;
        if (req.body.order !== '' && req.body.order !== undefined) sqlQuery += ` ORDER BY ${req.body.order}`;

        console.log(`Query: ${sqlQuery}`);
        return pool.request().query(sqlQuery);
      }).then(result => {
        sql.close();
        sqlDone = true;
        console.log();
        console.log('Result:');
        console.dir(result.recordset);
        res.header('Content-Type', 'Application/JSON');
        res.json(result.recordset);
      }).catch(err => {
        sql.close();
        sqlDone = true;
        console.log();
        console.error('Caught Error:');
        console.error(err);
        res.status(500).send({ error: err });
      });
      sql.on('error', err => {
        sql.close();
        sqlDone = true;
        console.log();
        console.error('SQL Error:');
        console.error(err);
        res.status(500).send({ error: err });
      });
    });

});

Bit of a shame you can't run multiple queries at once but I guess this is a limitation of SQL, not the library.

有点遗憾，你不能一次运行多个查询，但我想这是SQL的限制，而不是库。

#1