sails MSSQL DB update error

Question

I could not able to do MSSQL update from sails. I am facing below errors.

User.update({ id: 1 },

        { id: 1, name: 'test' }).exec(function afterwards (err, user) {

            if (err) {
                console.log(err);

            } else {
                console.log("Users updated:", users);
            }
        });

2018-03-21T08:21:52.868Z - error: error: Error (E_UNKNOWN) :: Encountered an unexpected error RequestError: Incorrect syntax near the keyword 'WHERE'. at Connection. (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\lib\tedious.js:658:17) at emitOne (events.js:116:13) at Connection.emit (events.js:211:7) at Parser. (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\node_modules\tedious\lib\connection.js:444:15) at emitOne (events.js:116:13) at Parser.emit (events.js:211:7) at Parser.nextToken (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:91:18) at Parser.addBuffer (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:68:17) at Connection.sendDataToTokenStreamParser (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\node_modules\tedious\lib\connection.js:891:35) at Connection.data (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\node_modules\tedious\lib\connection.js:275:23) at Connection.dispatchEvent (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\node_modules\tedious\lib\connection.js:752:59) at MessageIO. (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\node_modules\tedious\lib\connection.js:680:22) at emitOne (events.js:116:13) at MessageIO.emit (events.js:211:7)
at MessageIO.eventData (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\node_modules\tedious\lib\message-io.js:56:12) at Socket. (D:\Project\WODevelopment\trunk\QMS3.0\POC\WideOrbit-Server\node_modules\sails-sqlserver\node_modules\mssql\node_modules\tedious\lib\message-io.js:3:59) at emitOne (events.js:116:13) at Socket.emit (events.js:211:7) at addChunk (_stream_readable.js:263:12) at readableAddChunk (_stream_readable.js:250:11) at Socket.Readable.push (_stream_readable.js:208:10) at TCP.onread (net.js:594:20) Details: RequestError: Incorrect syntax near the keyword 'WHERE'.

This would have to be a bug in waterline or sails-sqlserver... however you may be able to avoid it if you don't update the id attribute. While this is possible, it seems a bit unusual - do you need to update the id in your actual app or is this just for testing? It could cause trouble with how waterline handles primary keys under the hood, esp if you have model relations or special primary key handling set up in your model. — arbuthnott

Arpit Kumar Pal Arpit Kumar Pal · Accepted Answer · 2018-03-25T10:15:25

Firstly try to use sails-mssql and if does not resolves your problem then go for below

Sails uses do not provide any specific method to debug query generate and executed with waterline Orm

Here are few method listed below to debug the problem more efficiently as the error stack say their is a problem in query near Where clause

2018-03-21T08:21:52.868Z - error: error: Error (E_UNKNOWN) :: Encountered an 
unexpected error RequestError: Incorrect syntax near the keyword 'WHERE'. at 
Connection.

1) Use these method to debug mssql management studio query log with How to see query history in SQL Server Management Studio

2) Try to get to the driver of mssql > core file where it execute query and do console.log

hope it helps you

sails MSSQL DB update error

1 Answers