CODEWITHSUNDEEP
node.jssql-server-2008sequelize.jstedious
Jeremy
Jeremy

Reputation: 3548

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;

Upvotes: 8

Views: 4179

Answers (6)

Daniel Araujo
Daniel Araujo

Reputation: 31

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,
            },
        },
    },

Upvotes: 3

Prayag
Prayag

Reputation: 371

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`;

Upvotes: 1

Michael Cole
Michael Cole

Reputation: 16217

I'm using select @@version ->

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
    Feb 20 2014 20:04:26 
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17134: ) (Hypervisor)

and got this same error. sequelize.authenticate() didn't fix it.

See also: https://github.com/tediousjs/tedious/issues/872

For me, the root cause. Basically, you need an ORDER BY for the OFFSET to make sense. @prayag answer above adds that ORDER BY.

Upvotes: 0

Christiano Marques
Christiano Marques

Reputation: 41

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.

Upvotes: 2

Diego Meza
Diego Meza

Reputation: 304

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

Upvotes: 1

treythomas123
treythomas123

Reputation: 1373

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
});

Upvotes: 6

Related Questions