Creating AutoNumber field

[yoachan]

If SQL-Server has an 'Auto Number' datatype, how can I make an autonumber on Progress?
I'm having a hard time with this, for often I had concurent events that cause duplication on my autonumber which caused error as it is a unique field.
Please help....

 

[TomBascom]

One method is to use a sequence and a trigger. The sports database uses this technique:

/* crcust.p */

TRIGGER PROCEDURE FOR Create OF Customer.

/* Automatically Increment Customer Number using Next-Cust-Num Sequence */

ASSIGN Customer.Cust-Num = NEXT-VALUE( Next-Cust-Num ).

(Use the data dictionary to see the definition of the trigger and the sequence.)

 

[yoachan]

Thanks for your reply.
I tried the sequence, and it it works. I thank you for that too

But I have another problem. Some of my friend shared the table with me and it's not possible for everyone of them to change their code shortly. So I use NEXT-VALUE on my own code, not on Database-Trigger.
The problem is when my friend manually add the autonumber using find-last and +1, my sequence will crash on this manually generated auto-number...

e.g.
me using NEXT-VALUE (sequence) get number 100.
friend useing FIND LAST and +1 will get 101.
me using NEXT-VALUE (sequence) get number 101 again......

now what should i do....

PS: CMIIW, each sequence has it's own counter, so if I use the same sequence for several tables it won't keep each table's last counter but will only have one sequence counter.

 

[TomBascom]

You have to pick a method and everyone needs to abide by it.

 

[sdjensen]

Agree but the quick and very dirty way of doing it could be

DEF VAR I AS INT.
I = NEXT-VALUE( Next-Cust-Num ).
FIND CUSTOMER WHERE CUST-NUM = I NO-LOCK NO-ERROR.
 
DO WHILE AVAIL CUSTOMER:
  I = NEXT-VALUE( Next-Cust-Num ).
  FIND CUSTOMER WHERE CUST-NUM = I NO-LOCK NO-ERROR. 
END.
CREATE CUSTOMER.
ASSIGN Customer.Cust-Num = I.

:awink:

 

[yoachan]

@Tom:
I think you were right.....
Thanks for your replies

@sdjensen:
this is like my prev method, and sadly it's not working when concurency occurs.
Thanks for your reply too

 

[sdjensen]

@sdjensen:
this is like my prev method, and sadly it's not working when concurency occurs.
Thanks for your reply too

As you can see in my code it tries to find a record with the new number and if a record is found it continues to increase the sequence until a record is not found.

 

[4GLNewbie]

In my idea Tom said the better thing to do. Make your decision and then align the code to this method. Try not to go into further troubles having different solutions ...

If you want, write some source code to use for create the new record and make all the others use that code ( inside it you will use sequence or whatever ). For maintenance purposes it is easier, i think.

If you dont think to use the sequence, it may be a good idea to remove it from the db so that no one will be able to use it anymore..

Bye!