如何将sql文件传入node-mssql

Question

如何将外部sql文件传递到node-mssql查询

我试着传入如下代码所示的文件，但它不起作用。我尝试了连接字符串，但仍然不起作用

request.query(query + `IN ('${serverName.join("','")}')`)

const sql = require('mssql')
const config = require('../Util/config')
const fs = require('fs')
const log = require('../Util/log')

module.exports = function (req, res, viewFile, next) {
  log(req)

  const query = fs.readFileSync('./sql/Local-admin-user.sql', 'utf8')
  // console.log(query);

  const serverName = "'" + req.body.server_name.split(/\r?\n/).join("','") + "'"
  console.log(serverName)

  var dbConn = new sql.ConnectionPool(config)
  dbConn.connect().then(function () {
    var request = new sql.Request(dbConn)
    request.query(query)
      .then(output => {
        let dataSet = output.recordset
          console.log(dataSet)
        dbConn.close()
      })
      .catch(err => {
        let dataSet = err
        console.log('error from routes.js', dataSet)
       dbConn.close()
      })
  }).catch(function (err) {
    console.log(err)
  })
}

Local-admin-user.sql

select servername,
username,
groupname from GROUP_MEMBERS_DATA where servername in (@servers)

我收到的错误信息如下

Incorrect syntax near servername

Answer 1

我可以这样解决这个问题

--remove "in (@servers)" from Local-admin-user.sql
--Example
select servername, username, groupname 
from GROUP_MEMBERS_DATA where servername

在javascript中添加以下代码

 const serverName = "'" + req.body.server_name.split(/\r?\n/).join("','") + "'"
 const sqlquery = (query + ` IN (${serverName})`)

因此sqlquery的输出将如下所示

select servername, username, groupname 
from GROUP_MEMBERS_DATA where servername in ('server1','server2')