Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (10)

Resolved Sequence syntax from Oracle to SqlServer

  1. How-to
0
Votes
Undo
  1. billy hugon
    billy hugon
  2. PowerBuilder
  3. Monday, 26 February 2024 20:24 PM UTC

I'm converting my DB from Oracle to SqlServer.  Everything is working but I'm needing to convert Oracle Sequences to SqlServer.  I have the sequence created in the database and it works when I issue this command. 

SELECT NEXT VALUE  FOR trans_id_seq ;

But when I try and compile it, I'm getting a syntax error (top part is Oracle code commented out).  Any clues?

 

 

How to copy data from subreport on a nested dw
Migrating PB-2017 to PB-2022
Accepted Answer
Mark Goldsmith
Mark Goldsmith Accepted Answer Pending Moderation
  1. Wednesday, 28 February 2024 13:05 PM UTC
  2. PowerBuilder
  3. # Permalink
@John...thanks John, nice idea. @Mike...I'm using PB 2022 R3 Build 3289 and SQL Server version 16.0.1110.1 and it's working just fine, unless it's something else you're referring to that isn't working. The code I'm using in embedded SQL is as follows: Integer li_sequence Select Next Value For Test_sequence Into :li_sequence from Temp1; MessageBox("Next value:", String(li_sequence)) I ran this alternating between PB and executing this code "Select next value for Test_sequence;" in SSMS. PB and SSMS each incrementally returned the next value correctly.
Comment
There are no comments made yet.
Responses (10)
Mark Goldsmith
Mark Goldsmith Accepted Answer Pending Moderation
  1. Wednesday, 28 February 2024 13:05 PM UTC
  2. PowerBuilder
  3. # 1
@John...thanks John, nice idea. @Mike...I'm using PB 2022 R3 Build 3289 and SQL Server version 16.0.1110.1 and it's working just fine, unless it's something else you're referring to that isn't working. The code I'm using in embedded SQL is as follows: Integer li_sequence Select Next Value For Test_sequence Into :li_sequence from Temp1; MessageBox("Next value:", String(li_sequence)) I ran this alternating between PB and executing this code "Select next value for Test_sequence;" in SSMS. PB and SSMS each incrementally returned the next value correctly.
Comment
mike S
  1. mike S
  2. Wednesday, 28 February 2024 15:29 PM UTC
i tried it yesterday and it didn't work - today it worked. I must have typed it in wrong!
  1. Helpful
mike S
  1. mike S
  2. Wednesday, 28 February 2024 15:30 PM UTC
i removed my comment where i said it doesn't work for when someone looks at this in the future.
  1. Helpful
Mark Goldsmith
  1. Mark Goldsmith
  2. Wednesday, 28 February 2024 16:05 PM UTC
No worries Mike, thanks for trying again and for the update!
  1. Helpful
There are no comments made yet.
Mark Goldsmith
Mark Goldsmith Accepted Answer Pending Moderation
  1. Tuesday, 27 February 2024 16:02 PM UTC
  2. PowerBuilder
  3. # 2

Hi Billy,

I would agree with Chris and Mike that a "nextnumber" table is not the way to go. 

Coming back to your original sequence syntax error, I suspect the issue here is that for embedded SQL PB adheres to the ISO standard, meaning in this case it's failing due to the absence of a "From" clause (even though SQL Server doesn't require it and similar SQL...excluding the "into" and variable name...in the ISQL Session tab of the Database Painter will execute just fine). Like your Oracle example, you're using "From Dual" which allows it to compile.

Once you add a "From" clause with a legitimate table name (that has at least one record) I'm thinking this should work fine. You may wish to set up a Dummy table with one column and one record for this kind of usage.

HTH...regards,

Mark

Comment
John Fauss
  1. John Fauss
  2. Tuesday, 27 February 2024 16:33 PM UTC
Instead of defining a "dummy" table, what I do is use a throwaway SELECT statement as the FROM clause, for example: SELECT GetDate() INTO :ldt_temp FROM (SELECT a=1) AS t USING SQLCA; PB's rules/conditions are satisfied and SQL Server handles it without difficulty.
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Tuesday, 27 February 2024 02:23 AM UTC
  2. PowerBuilder
  3. # 3

Look up how to call a stored procedure in PB. 

Then use the stored procedure i gave you.  It is the code we use in our application to get a value from the sequence in sql server. 

Chris is right that using a nextnumber table is slow and can various problems.  It is only really good if you must not have gaps in the sequence numbers.

 

Comment
There are no comments made yet.
billy hugon
billy hugon Accepted Answer Pending Moderation
  1. Tuesday, 27 February 2024 00:48 AM UTC
  2. PowerBuilder
  3. # 4

No I really dont know what is going on.  I created a simple function in SqlServer to return my next number.  I hate dealing with SqlServer because of the quirks that Oracle does not have but this seems like it is on the PB side.

 

If I call from SqlServer, it returns fine

 

 

 

If I try to compile in PB, I get a Syntax Error.  No description.  

 

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 26 February 2024 20:59 PM UTC
  2. PowerBuilder
  3. # 5

Hi Billy;

  You don't need any of that, just SS's "Auto Increment" feature to have SS do all the work. https://www.w3schools.com/sql/sql_autoincrement.asp#:~:text=The%20MS%20SQL%20Server%20uses,perform%20an%20auto%2Dincrement%20feature

HTH   ;-)

Regards ... Chris 

Comment
Load more comments
billy hugon
  1. billy hugon
  2. Tuesday, 27 February 2024 12:22 PM UTC
Yes, very low volume, but I think I will probably go with what Mike suggested. It would be great if they would fix that bug though.
  1. Helpful
Berka Frenfert
  1. Berka Frenfert
  2. Tuesday, 27 February 2024 12:40 PM UTC
for me generating keys manually is a nightmare in multiuser environment. if i were you i would have refuse to do any help.
  1. Helpful
Berka Frenfert
  1. Berka Frenfert
  2. Tuesday, 27 February 2024 12:44 PM UTC
ah, numeric keys. of course and char keys are easy and important
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.