Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Tedious or Sequelize uses the wrong syntax for `findOne()`

I am using Sequelize with Tedious to access SQL Server 2008.

When I do a sequelizeModel.findOne() I get this exception -

Unhandled rejection SequelizeDatabaseError: Invalid usage of the option NEXT in the FETCH statement.

I know SQL Server 2008 doesn't support OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY and that is why the exception is thrown.

But I have also explicitly set the tdsVersion in the tedious options to 7_3_B.

As described here -
http://pekim.github.io/tedious/api-connection.html

I've tried all the tds versions and the query syntax that is generated always contains the FETCH/NEXT syntax.

Am I missing something?

Shouldn't the syntax be specific to the tds version?

I've also verified that the tdsVersion option is being passed successfully to the tedious connection library from sequelize.

Example of query syntax generated -

SELECT 
    [id], [FIRST_NAME], [LAST_NAME]  
FROM  
    [USERs] AS [USERS] 
ORDER BY 
    [id]  
    OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
like image 758
Jeremy Avatar asked Jun 03 '15 23:06

Jeremy


5 Answers

This is an issue in Sequelize -- it uses the OFFSET FETCH syntax, which is only supported in SQL Server 2012 and newer.

I submitted this as an issue on GitHub: https://github.com/sequelize/sequelize/issues/4404

The issue also affects the findById method. A workaround for that method is to use findAll with a where to specify the ID, and just only use the first element from the returned array:

Thing.findAll({
  where: {id: id}
}).then( function(things) {
  if (things.length == 0) {
    // handle error
  }
  doSomething(things[0])
}).catch( function(err) {
  // handle error
});
like image 99
treythomas123 Avatar answered Nov 19 '22 01:11

treythomas123


Checking code node_modules/sequelize/lib/dialects/mssql/query-generator.js saw that part

const dbVersion = this.sequelize.options.databaseVersion;
const isSQLServer2008 = semver.valid(dbVersion) && semver.lt(dbVersion, '11.0.0');

So I just added my connection configuration:

production: {
        dialect: 'mssql',
        databaseVersion: '10.50.6000',
        host: process.env.DB_HOST,
        username: process.env.DB_USER,
        password: process.env.DB_PASS,
        database: process.env.DB_NAME,
        dialectOptions: {
            options: {
                useUTC: false,
                dateFirst: 1,
                enableArithAbort: true,
                encrypt: false,
            },
        },
    },

like image 32
Daniel Araujo Avatar answered Nov 19 '22 01:11

Daniel Araujo


I have the same problem using sequelize v4.42.0 and SQL Server 2008 R2 (SP1). Inspecting the source code of SequelizeJS, I found in file lib/dialects/mssql/query-generator.js you have selectFromTableFragment function in line 821 of that version. In 826 line exists an if statement that verify the version number of SQL Server via the databaseVersion option, from the Sequelize options object.

// Handle SQL Server 2008 with TOP instead of LIMIT
if (semver.valid(this.sequelize.options.databaseVersion) && semver.lt(this.sequelize.options.databaseVersion, '11.0.0')) {

This option is not present in the docs from http://docs.sequelizejs.com, I searched for this option and did not found. In this option I setup my SQL Server version number ('10.50.2500' - Equivalent to 2008 R2 SP1) and this did works. The query now is created using SELECT TOP ... and not OFFSET AND FETCH NEXT.

I expect that helps other people that are having this issue like me.

like image 27
Christiano Marques Avatar answered Nov 19 '22 01:11

Christiano Marques


Excecuting the "sequeIize.authenticate" method inmediatly after initialization resolves the problem for me. I dont know what is happening here but, I was stock on the same error.

SequelizeDatabaseError: Invalid usage of the option NEXT in the FETCH statement.
    at Query.formatError (C:\xampp\htdocs\Benoit\node_modules\sequelize\lib\dialects\mssql\query.js:315:12)
    at Request.connection.lib.Request [as userCallback] (C:\xampp\htdocs\Benoit\node_modules\sequelize\lib\dialects\mssql\query.js:107:25)
    at Request._this.callback (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\request.js:60:27)
    at Connection.endOfMessageMarkerReceived (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\connection.js:1922:20)
    at Connection.dispatchEvent (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\connection.js:1004:38)
    at Parser.<anonymous> (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\connection.js:805:18)
    at emitOne (events.js:116:13)
    at Parser.emit (events.js:211:7)
    at Parser.<anonymous> (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\token\token-stream-parser.js:54:15)
    at emitOne (events.js:116:13)
    at Parser.emit (events.js:211:7)
    at addChunk (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_readable.js:291:12)
    at readableAddChunk (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_readable.js:278:11)
    at Parser.Readable.push (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_readable.js:245:10)
    at Parser.Transform.push (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_transform.js:148:32)
    at Parser.afterTransform (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_transform.js:91:10)
    at Parser._transform (C:\xampp\htdocs\Benoit\node_modules\tedious\lib\token\stream-parser.js:69:9)
    at Parser.Transform._read (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_transform.js:184:10)
    at Parser.Transform._write (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_transform.js:172:83)
    at doWrite (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_writable.js:428:64)
    at writeOrBuffer (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_writable.js:417:5)
    at Parser.Writable.write (C:\xampp\htdocs\Benoit\node_modules\readable-stream\lib\_stream_writable.js:334:11)

I got the error after a brief restructure of my code on a project, I realize that, just because the history of my sublime stored it, I was just deleting all the excess code in the project and the authenticated method was commented.

I dont know what is the relation between the initial authentication method and the grammar and syntax of the queries, I don't even think that must have a relation, but is what it is

like image 28
Diego Meza Avatar answered Nov 18 '22 23:11

Diego Meza


If you can change the sequelize library in node modules. Please follow the steps: Goto Node_modules -> sequelize ->lib -> mssql -> query-generator.js

You will find this line :

    fragment += ` OFFSET ${this.escape(offset)} ROWS`;

Add a line above it :

    fragment += ` ORDER BY ${this.quoteTable(options.tableAs || model.name)}.${this.quoteIdentifier(model.primaryKeyField)}`;

This should look like :

    fragment += ` ORDER BY ${this.quoteTable(options.tableAs || model.name)}.${this.quoteIdentifier(model.primaryKeyField)}`;

    fragment += ` OFFSET ${this.escape(offset)} ROWS`;
like image 44
Prayag Avatar answered Nov 19 '22 00:11

Prayag