Board index » delphi » Generate new ID in a multi-user system

Generate new ID in a multi-user system

Hi all,

    I.e.: In system that controls orders I must generate ID's for new items
and orders. When two users at same time save a new order, for example, the
ID generated is the same then an exception is raised because violates the
key. How to generate ID's that always are differents.

Obs.: I ever try use generate and "select max(field)" and don't work well.

Thanks in advance,
Jefferson

 

Re:Generate new ID in a multi-user system


What database do you use?

Use Sequence with Oracle.  Use generator with Interbase.  I do know much
about SQL Server but believe is autoinc field.  For other databases, check
their user manual.

Ping

Quote
Jefferson <harrycr...@uol.com.br> wrote in message

news:39ca45c8$1_1@dnews...
Quote
> Hi all,

>     I.e.: In system that controls orders I must generate ID's for new
items
> and orders. When two users at same time save a new order, for example, the
> ID generated is the same then an exception is raised because violates the
> key. How to generate ID's that always are differents.

> Obs.: I ever try use generate and "select max(field)" and don't work well.

> Thanks in advance,
> Jefferson

Re:Generate new ID in a multi-user system


Well this is an age old issue.  You didn't comment on what database you are
using. paradox has an autoincrement field that handles this, MS SQLServer
has this, Informix has a field like this, and so does Oracle, and Sybase.
Interbase doesn't have a field type for autoincrement (I like the way
Interbase handles it better), but it does have a function you can call to
get the next ID.

The problem with all of the above (except for paradox and interbase) is
sometimes its a hassle to get working.  You have to post the record to the
database and read it back just to find out what the new number is.  This
gets really weird when you want to insert master and detail records at the
same time.  This gets very ugly when your looking at doing this three tier
stuff with inserts with master and detail.

All auto increment fields have an issue once you want to replicate
databases.  ie. you cant!!  A good solution I've found is to use GUID's as
your key field.  Call the CoCreateGUID  and it will return a 38 length
string.  Problem with this route is the key is a 38 length string and will
slow down the server, higher diskspace requirements, etc.

Anyways food for thought.

Blaine Whittle
Apropos Retail

Quote
"Jefferson" <harrycr...@uol.com.br> wrote in message

news:39ca45c8$1_1@dnews...
Quote
> Hi all,

>     I.e.: In system that controls orders I must generate ID's for new
items
> and orders. When two users at same time save a new order, for example, the
> ID generated is the same then an exception is raised because violates the
> key. How to generate ID's that always are differents.

> Obs.: I ever try use generate and "select max(field)" and don't work well.

> Thanks in advance,
> Jefferson

Re:Generate new ID in a multi-user system


Well this is an age old issue.  You didn't comment on what database you are
using. paradox has an autoincrement field that handles this, MS SQLServer
has this, Informix has a field like this, and so does Oracle, and Sybase.
Interbase doesn't have a field type for autoincrement (I like the way
Interbase handles it better), but it does have a function you can call to
get the next ID.

The problem with all of the above (except for paradox and interbase) is
sometimes its a hassle to get working.  You have to post the record to the
database and read it back just to find out what the new number is.  This
gets really weird when you want to insert master and detail records at the
same time.  This gets very ugly when your looking at doing this three tier
stuff with inserts with master and detail.

All auto increment fields have an issue once you want to replicate
databases.  ie. you cant!!  A good solution I've found is to use GUID's as
your key field.  Call the CoCreateGUID  and it will return a 38 length
string.  Problem with this route is the key is a 38 length string and will
slow down the server, higher diskspace requirements, etc.

Anyways food for thought.

Blaine Whittle
Apropos Retail

Quote
"Jefferson" <harrycr...@uol.com.br> wrote in message

news:39ca45c8$1_1@dnews...

Quote
> Hi all,

>     I.e.: In system that controls orders I must generate ID's for new
items
> and orders. When two users at same time save a new order, for example, the
> ID generated is the same then an exception is raised because violates the
> key. How to generate ID's that always are differents.

> Obs.: I ever try use generate and "select max(field)" and don't work well.

> Thanks in advance,
> Jefferson

"Jefferson" <harrycr...@uol.com.br> wrote in message

news:39ca45c8$1_1@dnews...

- Show quoted text -

Quote
> Hi all,

>     I.e.: In system that controls orders I must generate ID's for new
items
> and orders. When two users at same time save a new order, for example, the
> ID generated is the same then an exception is raised because violates the
> key. How to generate ID's that always are differents.

> Obs.: I ever try use generate and "select max(field)" and don't work well.

> Thanks in advance,
> Jefferson

Re:Generate new ID in a multi-user system


  For Paradox tables, we use a separate table containing the last ID
generated.  We put the record in Edit mode (using a TTable), increment the
value in code, and Post it back to the database.  We wrap this code in a
try...except that tries x number of times just in case someone else is doing
it at the same time.  Then we use the value we generated as the key for our
Insert statement into the actual data table.
  For SQL, we use autoincrement fields, and our stored proc's return the
@@IDENTITY value so we know what the value generated was locally.
-Howard

Re:Generate new ID in a multi-user system


Quote
Blaine Whittle <blai...@aproposretail.com> wrote in message

news:39ca4be0$1_2@dnews...
Quote
> Well this is an age old issue.  You didn't comment on what database you
are
> using. paradox has an autoincrement field that handles this, MS SQLServer
> has this, Informix has a field like this, and so does Oracle, and Sybase.
> Interbase doesn't have a field type for autoincrement (I like the way
> Interbase handles it better), but it does have a function you can call to
> get the next ID.

Oracle does not use autoincrement field.  It uses sequences which works
similar to Interbase's generator.

You can select the next value from a sequence like select
sequencename.nextval from dual so you can get it either from your
application, from a stored procedure or in a trigger.  Or you can embedded
it into your insert statement like insert into tableA (field1, field2)
select sequencename.nextval, value2 from dual.  So IMO, it is more flexible
than the Interbase's generator.

Ping

Re:Generate new ID in a multi-user system


We don't use autoinc or sequences because we have to work with
different db.
So we give each user one different 3 digit number (user_id).
Then we create the ID as the union between one number and the user_id.
Example:
suppose you have a table with those ids:

id
1001
2001
1002
2002
3002
1003.

The first Client which connects to the db will receive the 001
user_id, the second 002 etc.
Now if you have to insert a new record you can select the max id which
has your user_id in the last 3 digit. (if you have 002 then the max is
3002).
Then you have to consider only first part of this number (3) and add 1
to this. (you'll have then 4).
Now the new id is 4002 (4 + user_id).
HTH, bye
Rocco Barbaresco:

Herrare Humanum Est,
Perseverare Ovest ....
(By Fichi D'India)

Re:Generate new ID in a multi-user system


Quote
Ping Kam wrote:
> ...
>           Or you can embedded
> it into your insert statement like insert into tableA (field1, field2)
> select sequencename.nextval, value2 from dual.  So IMO, it is more flexible
> than the Interbase's generator.

With IB you would use something like:
   insert into tableA (field1, field2)
   values (gen_id(generatorname,1), value2)
- No big difference, really

Aage J.

Re:Generate new ID in a multi-user system


I don't use db-related autoinc fields because I must work with different
dbs. I've a table that stores one record for every table in the database.
Each of this records contains the last generated OID. When I need a brand
new OID, I simply switch the isolation level to repeatable reads, read the
field of the table, inc the field, get the OID, commit and return to the
original isolation level.
Using MAX is bad, because (depending on the isolation level) you risk to
lock all the records..

Bye,
-=SIMO=-

Re:Generate new ID in a multi-user system


Quote
Aage Johansen <aage.johan...@kreftreg.no> wrote in message

news:39CB3E54.A92F260C@kreftreg.no...
Quote

> Ping Kam wrote:
> > ...
> >           Or you can embedded
> > it into your insert statement like insert into tableA (field1, field2)
> > select sequencename.nextval, value2 from dual.  So IMO, it is more
flexible
> > than the Interbase's generator.

> With IB you would use something like:
>    insert into tableA (field1, field2)
>    values (gen_id(generatorname,1), value2)
> - No big difference, really

With Oracle sequence, the last value from a sequence in each session is
cached in that session. So the sequencexxx.currval returns you the last
sequence number that you request in your session.  The currval will not
change even other users have request some unique number from the sequences.
AFAIK, IB does not do that you so have to store it in your application.
With Oracle, you can cache it again in your application or do it like:

Master: insert into tableA (field1, field2) select sequence1.nextval, value1
from dual;
Detail: insert into tableB(field1, field2, field3) select sequence2.nextval,
sequence.currval, value2 from dual.

And you know that your detail record is referencing your master record, not
some other user's master record.

Yes, this is no real big difference but I still think Oracle sequence is a
bit more flexible than the IB generator.

Ping

Other Threads