时间戳

首页 > 解决方案 > NodeJS：在循环中查询 MS SQL 会冻结响应

javascript - NodeJS：在循环中查询 MS SQL 会冻结响应

问题描述

我正在使用 fetch 从 NodeJS 中的 API 构建中读取数据。我有以下方法：

exports.read_detail = async function(req, res) {
    try {

        let pool = await new sql.ConnectionPool(config).connect();

        pool.on('error', err => {
            console.log("Pool Error");
            console.log(err);
        })

        let resultDcn = await pool.request()
            .input('DcnId', sql.Int, parseInt(req.body.DcnId))
            .query('SELECT * FROM Dcn WHERE DcnId = @DcnId');

        if(resultDcn.rowsAffected[0]) {

            var DCNObj = resultDcn.recordset[0];

            let resultDcnDay = await pool.request()
                .input('DcnId', sql.Int, parseInt(DCNObj.DcnId))
                .query('SELECT DcnDayId, DcnDate FROM DcnDay WHERE DcnId = @DcnId');

            let timeClocks = [];
            let myActivities = [];
            for (var i = 0; i < resultDcnDay.recordset.length; i++) {

                let dcnDayId = resultDcnDay.recordset[i].DcnDayId;
                console.log("Day: " + dcnDayId);

                /*Get Time Clocks per day*/
                let resultDcnTimeClocks = await pool.request()
                    .input('DcnDayId', sql.Int, parseInt(dcnDayId))
                    .query('SELECT * FROM DcnDayTimeClock WHERE DcnDayId = @DcnDayId');
                console.log(resultDcnTimeClocks.recordset);
                timeClocks.push(resultDcnTimeClocks.recordset);

                /*Get Activities per day*/
                let resultDcnDayActivity = await pool.request()
                    .input('DcnDayId', sql.Int, parseInt(dcnDayId))
                    .query('SELECT DcnDayActivity.*, DcnDay.DcnDate FROM DcnDayActivity INNER JOIN DcnDay ON DcnDayActivity.DcnDayId = DcnDay.DcnDayId WHERE DcnDayActivity.DcnDayId = @DcnDayId');
                console.log(resultDcnDayActivity.recordset);
                myActivities.push(resultDcnDayActivity.recordset);
            }

            const DcnDto = {
                "Dcn": DCNObj,
                "timeClocks": timeClocks,
                "dayActivities": myActivities
            };

            res.status(200).send({data: DcnDto});

        } else {
            res.status(200).send({msg: 'DCN does not exist'}); 
        }  

    } catch(err){
        console.log("Error: ", err);
        res.status(400).send({msg: 'error ocurred: ' + err});
    }
}

我猜错误与返回的对象有关，因为如果我删除像 dayActivities 或 Dcn 这样的节点，则返回响应正常。Dcn 有 12 个属性。

返回的对象是否有限制？或我应该知道的任何其他限制或格式？

标签： javascriptnode.jsjsonsql-serverfetch

解决方案

我认为问题在于，myActivities只存在于if(resultDcn.rowsAffected[0]).

此时程序崩溃。

const DcnDto = {
  "Dcn": DCNObj,
  "timeClocks": timeClocks,
  "dayActivities": myActivities
};

建议：当你使用 async/await 时，我建议使用 try catch。

推荐阅读