BEGIN and COMMIT, MS-SQL commands

[Robert]

I have the following MS-SQL table that stores my next Household, Member, or Visit ID in it. I need to keep this table in this format since I am converting from old data into a new system and cannot use auto-increment. Yes, that’s a bummer…

Table Name: NN
Columns: HH, MB, VS
KEY1: KEY(NN:ID)

Using Gemini.AI to help me write the following code: I need to know if the code will allow multiple workstations to interact with the table and increment the three different columns flawlessly. This is a very small office that this code is being used in (no more than five workstations) so the table will not be “hit” very often.

Here is a picture of my two procedures that is easier to read:

Here is the code to copy and mess around with:

!========================================================================
! GET NEXT RECORD NUMBER FOR HOUSEHOLD, MEMBER, OR VISIT
!========================================================================
PROCEDURE GetNext(STRING PCOLUMN)
!========================================================================
  CODE
  NN{PROP:SQL} = 'BEGIN TRANSACTION;'
  IF FILEERROR()
    ROLLBACK_AND_ERROR('Begin failed')
  END

  CASE PCOLUMN
  OF 'HH'; NN{PROP:SQL} = 'SELECT HH FROM NN WHERE ID = 1 WITH (UPDLOCK, ROWLOCK);'
  OF 'MB'; NN{PROP:SQL} = 'SELECT MB FROM NN WHERE ID = 1 WITH (UPDLOCK, ROWLOCK);'
  OF 'VS'; NN{PROP:SQL} = 'SELECT VS FROM NN WHERE ID = 1 WITH (UPDLOCK, ROWLOCK);'
  END !CASE
  IF FILEERROR()
    ROLLBACK_AND_ERROR('Select failed')
  END

  CASE PCOLUMN
  OF 'HH'; NN:HH += 1
  OF 'MB'; NN:MB += 1
  OF 'VS'; NN:VS += 1
  END !CASE

  CASE PCOLUMN
  OF 'HH'; NN{PROP:SQL} = 'UPDATE NN SET HH = ' & NN:HH & ' WHERE ID = 1;'
  OF 'MB'; NN{PROP:SQL} = 'UPDATE NN SET MB = ' & NN:MB & ' WHERE ID = 1;'
  OF 'VS'; NN{PROP:SQL} = 'UPDATE NN SET VS = ' & NN:VS & ' WHERE ID = 1;'
  END
  IF FILEERROR() 
    ROLLBACK_AND_ERROR('Update failed')
  END

  NN{PROP:SQL} = 'COMMIT TRANSACTION;'
  IF FILEERROR()
    ROLLBACK_AND_ERROR('Commit failed')
  END

  CASE PCOLUMN
  OF 'HH'; RETURN(NN:HH)
  OF 'MB'; RETURN(NN:MB)
  OF 'VS'; RETURN(NN:VS)
  END !CASE

!========================================================================
PROCEDURE ROLLBACK_AND_ERROR(ErrorMessage STRING)
!========================================================================
  CODE
  NN{PROP:SQL} = 'ROLLBACK TRANSACTION;'
  IF FILEERROR()
    MESSAGE(ErrorMessage,'SQL Error',ICON:EXCLAMATION)
  RETURN(0)
!========================================================================

Thanks for you help!

[Robert]

I re-wrote the procedure “inside” of Clarion and it’s compilable now… I also used standard Clarion commands instead of select statements since these calls should work just fine.

[Jane]

Or you could rewrite the proc as a stored procedure in SQL and call it from Clarion, which would have the least blocking time on the server.

Something like (which returns the current number from the table and then increments it… or could change to return the incremented value).

CREATE PROC dbo.getNextNumber @whichField varchar(10), @id int OUTPUT
AS
BEGIN;
IF @whichField NOT IN ('HH','MB','VS')
   RETURN

DECLARE @h INT, @m INT, @v INT;
BEGIN TRAN;
SELECT @h = ISNULL(hh,0), @m = ISNULL(mb,0), @v = ISNULL(vs,0) FROM nn WHERE id = 1;
IF @whichField = 'HH'
   SELECT @id = @h, @h += 1;
IF @whichField = 'MB'
   SELECT @id = @m, @m += 1;
IF @whichField = 'VS'
   SELECT @id = @v, @v += 1;
UPDATE nn SET hh = @h, mb = @m, vs = @v WHERE id = 1;
COMMIT	

END;

[Robert]

Very interesting/excellent idea, Jane! Then, it stays resident/inside MS-SQL.

I haven’t ever used any store procedures in Clarion, but it would be fun to use one since I understand the concept. I can “Gemini” how to use stored procedures in Clarion. No big “thang”.

That’s great, clean code! Thanks so much!

[Jane]

Using a stored procedure like that with an output parameter with PROP:SQL needs a specific syntax, including binding the return value.
You could change the stored procedure to return the value as a SELECT instead, which might be more like what you’re used to.

For the proc I posted, look in the Clarion help in the “Using Embedded SQL” topic.
Toward the bottom is an example on “how to return an output parameter” which would work with the way I wrote the proc.

Nice to learn about different options.

[Robert]

Thanks so much for your help, Jane! I will check the help text out and will ask any questions if anything doesn’t make sense. Cheers!

Clarion Help:
https://clarion.help/doku.php?id=mssql_accelerator_using_embedded_sql.htm

[JonW]

Hi Robert,

Probably all academic if the chance of two people trying to update at the same time is very small.

1. Your original version AI-generated version uses pessimistic concurrency checking; basically, “I’m about to change this, so everyone else back off”. Your Clarion version will do the traditional optimistic checking: “Hopefully this record still has the same value as when I grabbed it”. If that is not true you will return 0 and it is up to the caller to then try again.
2. Putting logout and commit around a single update to a single table doesn’t really help anything. Just letting the PUT autocommit itself would be fine.
3. Getting a value, adding one to it, and then putting the value does make sense in the ISAM world, but in SQL normally you would just do update sometable set somevalue = somevalue+1
4. I think the critical thing is that each user gets a unique value. I’m not sure what you are doing guarantees that. Say two workstations are trying to get a new MB at the same time. The first workstation grabs 29,716, adds one on the client side, and before they write it back the second workstation also grabs 29,716. They both add one and try to write the record back, and they both succeed. (In hindsight I think I take that back. The second one would do the optimistic check in the where clause (where md = 29716) and would fail and return 0).
   I think the most robust way would be a case statement for each of the three columns, each like:
   update nn set hh = hh+1 output hh into @newval with (UPDLOCK,ROWLOCK);
   @newval is a table variable, but really you are only returning a scalar. You could call that straight from Clarion, so like:
   tabletogetnumber{PROP:SQLRowSet} = 'update nn set hh = hh+1 output hh into @newval with (UPDLOCK,ROWLOCK)'
   I think @newval would be bound to your tabetogetnumber row buffer. If that doesn’t work then you probably have to set up a stored procedure declaring @newval and return it, or the first element from it.