cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

calling sql sequence in Thingworx

CS_9939070
3-Visitor

calling sql sequence in Thingworx

I have a stored procedure that contains an insert statement using "NEXT VALUE FOR" to use a sequence. The stored procedure works fine in SSMS, but fails to execute when executed from a DB Thing. My DB Service is a SQL Query and uses "EXECUTE". If I remove "NEXT VALUE FOR" from my stored procedure and just hard code a value it all works.

 

Is there some trick to get my stored procedure to be able to use the "NEXT VALUE FOR"?

 

Re:  post

1 ACCEPTED SOLUTION

Accepted Solutions
Tudor
12-Amethyst
(To:CS_9939070)

Hello, thank you for the question.  Our JDBC statements are prepared and sent to the DB using a syntax that prevents 'next value for' from being utilized due to restrictions on where this function can appear -- we unfortunately cannot adjust or bypass this mechanism.

 

One possible workaround is to SELECT the current sequence value (and increment appropriately):

SELECT current_value FROM sys.sequences WHERE name = '<sequence name>';

 

There are other approaches as well such as writing the value in a table to be read later, or relying on auto sequence increments to determine the next row in the sequence.

 

Please let us know if the information above gives you a path forward.

View solution in original post

1 REPLY 1
Tudor
12-Amethyst
(To:CS_9939070)

Hello, thank you for the question.  Our JDBC statements are prepared and sent to the DB using a syntax that prevents 'next value for' from being utilized due to restrictions on where this function can appear -- we unfortunately cannot adjust or bypass this mechanism.

 

One possible workaround is to SELECT the current sequence value (and increment appropriately):

SELECT current_value FROM sys.sequences WHERE name = '<sequence name>';

 

There are other approaches as well such as writing the value in a table to be read later, or relying on auto sequence increments to determine the next row in the sequence.

 

Please let us know if the information above gives you a path forward.

Announcements