NodeJS+Express+SQL Server backend application Error : Incorrect Syntax near '83' , Incorrect Syntax near '68' etc

Viewed 37

In my application I get the following errors when I'm trying to reach GET, PUT, DELETE routes (tried POSTMAN and VSCode REST Server):

Error 1:

GET by ID : `Incorrect Syntax near '83'.`

Error 2:

GET ALL : `Incorrect Syntax near '83'.`

Error 3:

PUT : `Incorrect syntax near '85'.`

Error 4:

DELETE : `Incorrect syntax near '68'.`

I have googled the error but most of them are related to spacing errors in parameters in the SQL query but I have checked mine and the queries I have are all correct compared to those.

I suspect if this related to any SQL Server related encoding but not sure.

Below are some of my coding as a start to investigate:

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
}

UPDATE : Added data/events

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

Request for GET ALL via POSTMAN GET ALL POSTMAN

Request for GET by ID via POSTMAN GET by ID POSTMAN

Request for GET ALL via REST Server GET ALL via REST SERVER

RAW Body of GET ALL request RAW Body of GET ALL

RAW Body of GET by ID request RAW Body of GET by ID

0 Answers
Related