NodeJS+Express+SQL Server后端语法错误排查求助

我在NodeJS+Express+SQL Server后端应用中，调用GET、PUT、DELETE路由时（已用POSTMAN和VSCode REST Server测试）遇到以下语法错误：

• 错误1：GET by ID：Incorrect Syntax near '83'.
• 错误2：GET ALL：Incorrect Syntax near '83'.
• 错误3：PUT：Incorrect syntax near '85'.
• 错误4：DELETE：Incorrect syntax near '68'.

我查过资料，这类错误大多和SQL查询的参数空格有关，但核对后确认我的查询语句没问题。怀疑可能和SQL Server编码有关，但不确定。

相关代码 #

eventController.js #

'use strict';

const eventData = require('../data/events');

const getEvents = async (req, res, next) => {
    try {
        const events = await eventData.getEvents();
        res.send(events);
    }catch (error) {
        res.status(400).send(error.message);
    }
}

const getEvent = async (req, res, next) => {
    try {
        const eventId = req.params.id;
        const oneEvent = await eventData.getById(eventId);
        res.send(oneEvent)
    } catch (error) {
        res.status(400).send(error.message)
    }
}

const updateEvent = async (req, res, next) => {
    try {
        const eventId = req.params.id;
        const data = req.body;
        const updated = await eventData.updateEvent(eventId, data);
        res.send(updated);
    } catch (error) {
        res.status(400).send(error.message)
    }
}

const deleteEvent = async (req, res, next) => {
    try {
        const eventId = req.params.id;
        const deletedevent = await eventData.deleteEvent(eventId);
        res.send(deletedevent);
    } catch (error) {
        res.status(400).send(error.message)
    }
}

module.exports = {
    getEvents,
    getEvent,
    updateEvent,
    deleteEvent
}

eventslist.sql #

SELECT [ord_type],
[ord_no],
[line_seq_no],
[lvl_no],
[cmt_type],
[cmt_seq_no],
[cmt],
[CMT_DOC_TYPE],
[EXTRA_1],
[extra_2],
[extra_3],
[extra_4],
[extra_5],
[extra_6],
[extra_7],
[extra_8],
[extra_9],
[extra_10],
[extra_11],
[extra_12],
[extra_13],
[extra_14],
[extra_15],
[FILLER_0001],
[ID],
[is_ext],
[RowVersion] 
FROM [100].[dbo].[OELINCMT_SQL]

utils.js #

'use strict';

const fs = require('fs-extra');
const {join} = require('path');

const loadSqlQueries = async (folderName) => {
    const filePath = join(process.cwd(), 'data', folderName);
    const files = await fs.readdir(filePath);
    const sqlFiles = await files.filter(f => f.endsWith('.sql'));
    const queries = {};

    for (const sqlFile of sqlFiles) {
        const query = await fs.readFileSync(join(filePath, sqlFile));
        queries[sqlFile.replace(".sql", "")] = query
    }
    return queries;
}

module.exports = {
    loadSqlQueries
}

更新补充代码 #

index.js #

'use strict';

const utils = require('../utils');
const config = require('../../config');
const sql = require('mssql');

const getEvents = async () => {
    try {
        let pool = await sql.connect(config.sql);
        const sqlQueries = await utils.loadSqlQueries('events');
        const list = await pool.request().query(sqlQueries.eventslist);
        return list.recordset;
    } catch (error) {
        return error.message;
    }
}

const getById = async (eventId) => {
    try {
        let pool = await sql.connect(config.sql);
        const sqlQueries = await utils.loadSqlQueries('events');
        const oneEvent = await pool.request()
                        .input('eventId', sql.Char(8), eventId)
                        .query(sqlQueries.eventbyId);
        return oneEvent.recordset;
    } catch (error) {
        return error.message;
    }
}

const updateEvent = async (eventId, eventData) => {
    try {
        let pool = await sql.connect(config.sql);
        const sqlQueries = await utils.loadSqlQueries('events');
        const update = await pool.request()
                        .input('eventId', sql.Char(8), eventId)
                        .input('cmt', sql.NVarChar(4000), eventData.cmt)
                        .query(sqlQueries.updateEvent);
        return update.recordset;
    } catch (error) {
        return error.message
    }
}

const deleteEvent = async (eventId) => {
    try {
        let pool = await sql.connect(config.sql);
        const sqlQueries = await utils.loadSqlQueries('events');
        const deleted = await pool.request()
                        .input('eventId', sql.Char(8), eventId)
                        .query(sqlQueries.deleteEvent);
        return deleted.recordset;
    } catch (error) {
        return error.message
    }
}

module.exports = {
    getEvents,
    getById,
    updateEvent,
    deleteEvent
}

eventbyId.sql #

SELECT [ord_type]
        ,[ord_no]
        ,[line_seq_no]
        ,[lvl_no]
        ,[cmt_type]
        ,[cmt_seq_no]
        ,[cmt]
        ,[CMT_DOC_TYPE]
        ,[EXTRA_1]
        ,[extra_2]
        ,[extra_3]
        ,[extra_4]
        ,[extra_5]
        ,[extra_6]
        ,[extra_7]
        ,[extra_8]
        ,[extra_9]
        ,[extra_10]
        ,[extra_11]
        ,[extra_12]
        ,[extra_13]
        ,[extra_14]
        ,[extra_15]
        ,[FILLER_0001]
        ,[ID]
        ,[is_ext]
        ,[RowVersion]
    FROM [100].[dbo].[OELINCMT_SQL]
    WHERE [ord_no] = @eventId

请求测试截图说明 #

• POSTMAN中GET ALL请求截图：显示GET请求返回400错误，错误信息为Incorrect Syntax near '83'.
• POSTMAN中GET by ID请求截图：显示带ID参数的GET请求返回400错误，错误信息为Incorrect Syntax near '83'.
• VSCode REST Server中GET ALL请求截图：显示GET请求返回400错误，错误信息与上述一致
• GET ALL请求原始Body截图：响应Body为错误信息Incorrect Syntax near '83'.
• GET by ID请求原始Body截图：响应Body为错误信息Incorrect Syntax near '83'.

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