"select NEXT VALUE FOR" query against sequence in SQL Server gives error

[Pat Wheaton]

I created a method: 

@SqlQuery("select next value for sync_sequence as syncId")
public abstract long getNewSyncIdentifier();

Whenever I try to get a sequence value using this method against SQL Server 2012, I get the following error: 

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.microsoft.sqlserver.jdbc.SQLServerException: NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.  [statement:"select next value for sync_sequence as syncId", located:"select next value for sync_sequence as syncId", rewritten:"select next value for sync_sequence as syncId", arguments:{ positional:{}, named:{}, finder:[]}]
        at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1334)
        at org.skife.jdbi.v2.Query.fold(Query.java:173)

This works as expected (e.g., without error) against an H2 database. 

Any ideas on workarounds? The same SQL (that is, "select next value from sync_sequence as syncId") works fine running in SQL Server Mgmt. Studio. 

TIA
p

[Matthew Hall]

Do you have any other annotations on that method, or on the class? Maybe a @MaxRows annotation?

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Pat Wheaton]

I have tried it with GetGeneratedKeys and also tried it as a call, but no luck. The call ran, but didn't get the value back, unsurprisingly, and GetGeneratedKeys gave same error, iirc.  I have not done anything with maxrows annotation and it does not currently have one. 

Sent from my iPad

You received this message because you are subscribed to a topic in the Google Groups "jDBI" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jdbi/bn-BdZy3M3E/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jdbi+uns...@googlegroups.com.

[Matthew Hall]

This appears to be specific to SQL Server, which I'm not very familiar with.

From what I'm reading here: https://msdn.microsoft.com/en-us/library/ff878370.aspx, it looks like the statement `select next value for sync_sequence as syncId` is actually setting a `syncId` variable in the database session, not returning it as a query result set. I'm not sure how to get the sequence value in a standalone statement through JDBC

Have you tried using `next value for` in an insert expression, and then returning generated keys from that?

@SqlUpdate("insert into sync(id, ...) values (next value for sync_sequence, ...)")

@GetGeneratedKeys

long insertSync(@BindBean Sync sync)

-Matthew

To unsubscribe from this group and all its topics, send an email to jdbi+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.