Board index » delphi » How can I have a multiuser database access with sequential primary field ID value without skips?

How can I have a multiuser database access with sequential primary field ID value without skips?


2004-01-14 02:11:07 AM
delphi235
Hi,
I am using delphi 6.0.2 with IBX6.08 and IB7.1 Server.
I am developing a multiuser application with ClientDataset + Provider +
IBQuery components.
Some tables of the database has a primary key whose value is generated by a
generator that is incremented by a trigger of these tables before any
insertion.
The records of these specific tables cannot be deleted only inserted.
The users can cancel any insertion that has not been posted.
I need to guarantee that the generated primary key values of these tables
will be sequential (1,2,3,4,5,etc.) without any skips, even after the
insertion cancelling on a multiuser access of the same table of the
database.
Is there any solution for this problem ?
Thanks for any help.
Marcelo.
 
 

Re:How can I have a multiuser database access with sequential primary field ID value without skips?

"Marcelo Colonna Rosman" <XXXX@XXXXX.COM>writes
Quote
Hi,

I am using delphi 6.0.2 with IBX6.08 and IB7.1 Server.

I am developing a multiuser application with ClientDataset + Provider +
IBQuery components.

Some tables of the database has a primary key whose value is generated by
a
generator that is incremented by a trigger of these tables before any
insertion.

The records of these specific tables cannot be deleted only inserted.

The users can cancel any insertion that has not been posted.

I need to guarantee that the generated primary key values of these tables
will be sequential (1,2,3,4,5,etc.) without any skips, even after the
insertion cancelling on a multiuser access of the same table of the
database.

Is there any solution for this problem ?

Do a search for "auditable series of numbers" on google and you should come
up with a link or two about how to achieve this. Sorry I don't have the link
stored anywhere to post for you.
HTH
Woody (TMW)
 

Re:How can I have a multiuser database access with sequential primary field ID value without skips?

Thank you.
Marcelo.
"Woody (TMW)" <XXXX@XXXXX.COM>writes
Quote
"Marcelo Colonna Rosman" <XXXX@XXXXX.COM>writes
news:40042650$XXXX@XXXXX.COM...
>Hi,
>
>I am using delphi 6.0.2 with IBX6.08 and IB7.1 Server.
>
>I am developing a multiuser application with ClientDataset + Provider +
>IBQuery components.
>
>Some tables of the database has a primary key whose value is generated
by
a
>generator that is incremented by a trigger of these tables before any
>insertion.
>
>The records of these specific tables cannot be deleted only inserted.
>
>The users can cancel any insertion that has not been posted.
>
>I need to guarantee that the generated primary key values of these
tables
>will be sequential (1,2,3,4,5,etc.) without any skips, even after the
>insertion cancelling on a multiuser access of the same table of the
>database.
>
>Is there any solution for this problem ?
>

Do a search for "auditable series of numbers" on google and you should
come
up with a link or two about how to achieve this. Sorry I don't have the
link
stored anywhere to post for you.

HTH

Woody (TMW)


 

Re:How can I have a multiuser database access with sequential primary field ID value without skips?

You can do a function protected in a block controled by a transaction.
You need a table with a row with a value, ex.
MyTablePK, with a field MYIDPK
Your begin bloq of transaction
You start transaction (with exclusive use of record)
Call a function here, it will do
'SELECT MYIDPK FROM MYTABLEPK
'UPDATE MYTABLEPK SET MYIDPK=' YourIDPK +1
if table is some transaction you will wait your time.
Do the Appies
Rollbak or Commit
if you rollback the old value of MYIDPK is restored
because the transaction
Your end of Transaction
This work well for a good number of connected users.
Roberto Novakosky
São Paulo - Brasil
 

Re:How can I have a multiuser database access with sequential primary field ID value without skips?

Thank you for your answer.
Marcelo.
"Roberto Novakosky" <XXXX@XXXXX.COM>writes
Quote
You can do a function protected in a block controled by a transaction.
You need a table with a row with a value, ex.
MyTablePK, with a field MYIDPK

Your begin bloq of transaction
You start transaction (with exclusive use of record)
Call a function here, it will do
'SELECT MYIDPK FROM MYTABLEPK
'UPDATE MYTABLEPK SET MYIDPK=' YourIDPK +1
if table is some transaction you will wait your time.
Do the Appies
Rollbak or Commit
if you rollback the old value of MYIDPK is restored
because the transaction
Your end of Transaction

This work well for a good number of connected users.


Roberto Novakosky
São Paulo - Brasil

 

Re:How can I have a multiuser database access with sequential primary field ID value without skips?

"Roberto Novakosky" <XXXX@XXXXX.COM>writes
Quote
You can do a function protected in a block controled by a transaction.
You need a table with a row with a value, ex.
MyTablePK, with a field MYIDPK

Your begin bloq of transaction
You start transaction (with exclusive use of record)
Call a function here, it will do
'SELECT MYIDPK FROM MYTABLEPK
'UPDATE MYTABLEPK SET MYIDPK=' YourIDPK +1
if table is some transaction you will wait your time.
Do the Appies
Rollbak or Commit
if you rollback the old value of MYIDPK is restored
because the transaction
Your end of Transaction

This work well for a good number of connected users.
What happens when someone starts a new record and then goes to lunch for an
hour or so? Other users are then locked out from creating new records. IMO,
it's better to implement an auditable series of numbers that let you
continue regardless of what each user is doing. I admit that I use your
technique when I just need an incremented number but not when I need
definate sequential numbers.
Woody (TMW)
 

Re:How can I have a multiuser database access with sequential primary field ID value without skips?

->What happens when someone starts a new record and then goes to lunch
for an
hour or so?
: You must start a new transaction only when user apply your changes, at
this moment you show a new number doc.
Note: I think that PrimaryKeys aren't good to sequencial number of docs.
the numbers are unique but aren't PK. So You have a field to Your
Primary Key (the user don't know) and a field to your number doc (that
the user see).
Thanks too.
Roberto Novakosky