Node.js + msnodesqlv8查询返回两次结果触发ERR_HTTP_HEADERS_SENT错误

问题场景 #

使用Node.js结合msnodesqlv8操作SQL Server时，调用自定义exeQuery函数执行存储过程[dbo].[GetMobileLog]，该函数两次触发回调：首次返回空数组[]，Express已向客户端返回200响应；随后返回有效数据结果集，最终触发ERR_HTTP_HEADERS_SENT错误。

相关代码 #

查询执行函数代码 #

const sql = require('msnodesqlv8');
const connectionString = "******************************";

function exeQuery(query, params, callback) {
    console.log('Executing query:', query);
    console.log('Query parameters:', params);

    sql.query(connectionString, query, params, (err, result) => {
        if (err) {
            console.error('Error executing query:', err);
            callback(err, null);
        } else {
            console.log('Query executed successfully. Result:', result);
            callback(null, result);
        }
    });
}

module.exports = exeQuery;

Express接口路由代码 #

// Route to view mobile logs
router.get('/viewMobileLogs/:mobileNumber', (req, res) => {
    const mobileNumber = req.params.mobileNumber;
    const query = `EXEC [dbo].[GetMobileLog] @MobileNum = ?`;

    exeQuery(query, [mobileNumber], (err, result) => {
        if (err) {
            console.error('Error fetching mobile logs:', err);
            return res.status(500).json({ error: 'Internal Server Error' });
        } else {
            return res.json(result);
        }
    });
});

运行日志 #

[nodemon] starting `node app.js`
Server is running on port 3000
Redis Connected Successfully
Executing query: EXEC [dbo].[GetMobileLog] @MobileNum = ?
Query parameters: [ '01639521598' ]
Query executed successfully. Result: []
GET /EmployeeMobiles/viewMobileLogs/01639521598 200 1084.553 ms - 2
Query executed successfully. Result: [
  {
    LDate: 2023-05-05T16:20:00.000Z { nanosecondsDelta: 0 },
    UserName: 'John Doe',
    ActionText: 'New bill added (New Bill)'
  }
]
node:_http_outgoing:652
    throw new ERR_HTTP_HEADERS_SENT('set');
    ^

Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client
    at ServerResponse.setHeader (node:_http_outgoing:652:11)
    at ServerResponse.header (C:\Users\mohamed.eltanany\Desktop\employees_bills_back\node_modules\express\lib\response.js:795:10)
    at ServerResponse.send (C:\Users\mohamed.eltanany\Desktop\employees_bills_back\node_modules\express\lib\response.js:175:12)
    at ServerResponse.json (C:\Users\mohamed.eltanany\Desktop\employees_bills_back\node_modules\express\lib\response.js:279:15)
    at C:\Users\mohamed.eltanany\Desktop\employees_bills_back\routes\employeeMobilesRoutes.js:67:24
    at ChunkyArgs.callback (C:\Users\mohamed.eltanany\Desktop\employees_bills_back\config\anotherAsyncConnection.js:14:13)
    at C:\Users\mohamed.eltanany\Desktop\employees_bills_back\node_modules\msnodesqlv8\lib\sql-client.js:82:18
    at Immediate.<anonymous> (C:\Users\mohamed.eltanany\Desktop\employees_bills_back\node_modules\msnodesqlv8\lib\connection.js:218:9)
    at process.processImmediate (node:internal/timers:478:21) {
  code: 'ERR_HTTP_HEADERS_SENT'
}

Node.js v20.11.0
[nodemon] app crashed - waiting for file changes before starting...

异常成因 #

1. msnodesqlv8的回调触发逻辑：
   msnodesqlv8执行存储过程时，会针对存储过程返回的每个结果集触发一次回调。如果存储过程返回多个结果集（比如未开启SET NOCOUNT ON时，SQL Server会返回受影响行数的结果集，或者存储过程内部包含多个SELECT语句），就会多次触发回调。首次返回的空数组[]就是额外的结果集，第二次才是业务数据结果集。

2. Express重复发送响应：
   第一次回调触发时，Express已经通过res.json(result)向客户端发送了200响应；第二次回调触发时，再次调用res.json(result)尝试发送响应，此时HTTP响应头已经发送完毕，Node.js不允许在响应已发送后再修改或发送新内容，因此抛出ERR_HTTP_HEADERS_SENT错误。

3. 存储过程的潜在问题：
   检查[dbo].[GetMobileLog]存储过程，是否未开启SET NOCOUNT ON，或者包含多个SELECT语句。SET NOCOUNT ON会抑制SQL Server返回受影响行数的结果集，避免额外的空结果集触发回调。

内容的提问来源于stack exchange，提问作者Mohammed Gamal