Clash Royale CLAN TAG#URR8PPP

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 -

sequelizeModel.findOne()

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.

OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

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

tdsVersion

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.

FETCH/NEXT

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.

tdsVersion

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;


findAll()

3 Answers
3

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:

findById

findAll

where

Thing.findAll(
where: id: id
).then( function(things)
if (things.length == 0)
// handle error

doSomething(things[0])
).catch( function(err)
// handle error
);


findAll( limit: 10, offset: 10 )

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

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:xampphtdocsBenoitnode_modulessequelizelibdialectsmssqlquery.js:315:12)
at Request.connection.lib.Request [as userCallback] (C:xampphtdocsBenoitnode_modulessequelizelibdialectsmssqlquery.js:107:25)
at Request._this.callback (C:xampphtdocsBenoitnode_modulestediouslibrequest.js:60:27)
at Connection.endOfMessageMarkerReceived (C:xampphtdocsBenoitnode_modulestediouslibconnection.js:1922:20)
at Connection.dispatchEvent (C:xampphtdocsBenoitnode_modulestediouslibconnection.js:1004:38)
at Parser.<anonymous> (C:xampphtdocsBenoitnode_modulestediouslibconnection.js:805:18)
at emitOne (events.js:116:13)
at Parser.emit (events.js:211:7)
at Parser.<anonymous> (C:xampphtdocsBenoitnode_modulestediouslibtokentoken-stream-parser.js:54:15)
at emitOne (events.js:116:13)
at Parser.emit (events.js:211:7)
at addChunk (C:xampphtdocsBenoitnode_modulesreadable-streamlib_stream_readable.js:291:12)
at readableAddChunk (C:xampphtdocsBenoitnode_modulesreadable-streamlib_stream_readable.js:278:11)
at Parser.Readable.push (C:xampphtdocsBenoitnode_modulesreadable-streamlib_stream_readable.js:245:10)
at Parser.Transform.push (C:xampphtdocsBenoitnode_modulesreadable-streamlib_stream_transform.js:148:32)
at Parser.afterTransform (C:xampphtdocsBenoitnode_modulesreadable-streamlib_stream_transform.js:91:10)
at Parser._transform (C:xampphtdocsBenoitnode_modulestediouslibtokenstream-parser.js:69:9)
at Parser.Transform._read (C:xampphtdocsBenoitnode_modulesreadable-streamlib_stream_transform.js:184:10)
at Parser.Transform._write (C:xampphtdocsBenoitnode_modulesreadable-streamlib_stream_transform.js:172:83)
at doWrite (C:xampphtdocsBenoitnode_modulesreadable-streamlib_stream_writable.js:428:64)
at writeOrBuffer (C:xampphtdocsBenoitnode_modulesreadable-streamlib_stream_writable.js:417:5)
at Parser.Writable.write (C:xampphtdocsBenoitnode_modulesreadable-streamlib_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

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.