 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Crazy Horse's crazier lit
Guest
|
 Posted: Thu Mar 17, 2005 8:56 pm Post subject: How to implement sequential ID numbers with no missing vals? |
 |
|
We need sequential "admission numbers" in our app to be auto-generated for
the user (they need to see them, but getting the next sequential value
automatically rather than relying on the data entry people to always
accurately know what the next one should be).
A generator won't work, I don't think, because we don't want any missing
values. IOW, if the user starts a record, and gets number 123445 and then
for some reason doesn't complete the record, that number will be unused and
there will be a hole in the sequencing.
If I could wait until just before the post to grab the number, that would
probably solve it, but again, the user wants to see this number all through
the data-entry process.
Any ideas on the best way to implement something like this? The best thing I've
been able to think of so far is to select max(admission_number) from the
table and use the next number through the data-entry process, but query for
that value again just before posting and, if it has changed, increment the
number again and inform the user of the numbering change. There must be a
better way, though.
--
Blackbird Crow Raven, NSGW
|
|
| Back to top |
|
 |
Kevin Frevert
Guest
|
| Posted: Thu Mar 17, 2005 9:28 pm Post subject: Re: How to implement sequential ID numbers with no missing v |
|
|
Just curious, is this an end-user specification or are you having to conform
to some federal regulation?
What happens when records are deleted? Can the sequence numbers be re-used?
If so, can sequence numbers be out of "date/time sequence"? Example.
Sally creates 12345 at 1:30PM
Bill creates 12346 at 1:31PM
Sally decides "I need a break" cancels what she is doing and 12345 becomes
available.
John creates 12345 at 2:00PM
Now the data looks like
12345 2:00PM
12346 1:31PM
krf
"Crazy Horse's crazier little brother" <cshannon (AT) d4sw (DOT) com> wrote
| Quote: | We need sequential "admission numbers" in our app to be auto-generated for
the user (they need to see them, but getting the next sequential value
automatically rather than relying on the data entry people to always
accurately know what the next one should be).
|
|
|
| Back to top |
|
|
Crazy Horse's crazier lit
Guest
|
| Posted: Thu Mar 17, 2005 10:04 pm Post subject: Re: How to implement sequential ID numbers with no missing v |
|
|
"Kevin Frevert" <drinkingdietcoke (AT) work (DOT) com> wrote
<
conform
to some federal regulation?>>
Company policy, not legally required.
I'm leaning towards providing a provisional ID, but only getting the "real"
ID (which in most cases will be the same, but not always) when they post the
data.
--
Blackbird Crow Raven, NSGW
|
|
| Back to top |
|
|
Bill Todd
Guest
|
| Posted: Thu Mar 17, 2005 10:24 pm Post subject: Re: How to implement sequential ID numbers with no missing v |
|
|
Crazy Horse's crazier little brother wrote:
| Quote: | If I could wait until just before the post to grab the number, that would
probably solve it, but again, the user wants to see this number all
through
the data-entry process.
|
No, that will not solve it. What happens if the number is assigned, the
record is posted then something causes the transaction to roll back? All
of the rows that are part of that transaction will vanish, however, since
the generator spans transactions it is not reset.
| Quote: |
Any ideas on the best way to implement something like this? The best
thing I've
been able to think of so far is to select max(admission_number) from the
table and use the next number through the data-entry process, but query
for
that value again just before posting and, if it has changed, increment
the
number again and inform the user of the numbering change. There must be a
better way, though.
|
Doing a SELECT MAX is not safe in a multi-user environment. Two users can
easily get the same number. One solution is to put a long series of
numbers in a table. Start a transaction, get the first number in the
table, delete the number from the table then commit the transaction. Use
the number in your new record. If the user cancels or rolls back insert
the number back into the number table so it will be reused. Create an
after delete trigger on the number table that counts the number of rows.
If the number is less than 100 insert another 100 rows.
--
Bill Todd (TeamB)
TeamB cannot answer questions received via email
|
|
| Back to top |
|
|
Eric Hill
Guest
|
| Posted: Fri Mar 18, 2005 5:29 am Post subject: Re: How to implement sequential ID numbers with no missing v |
|
|
Assuming your database doesn't support this feature natively, then you can
do something like this:
// Assumes a single-row table named keytable
update keytable set key = key + 1;
select key from keytable;
commit;
Since most databases support transactions, the update statement locks the
row/table forcing any other transactions to wait on you to get a new key.
When you commit, the next transaction trying to do an update unblocks and
repeats the process.
Eric
|
|
| Back to top |
|
|
Tomislav Kardaš
Guest
|
| Posted: Sun Mar 20, 2005 6:40 pm Post subject: Re: How to implement sequential ID numbers with no missing v |
|
|
Hi!
On Thu, 17 Mar 2005 14:56:27 -0600, "Crazy Horse's crazier little
brother" <cshannon (AT) d4sw (DOT) com> wrote:
| Quote: | We need sequential "admission numbers" in our app to be auto-generated for
the user (they need to see them, but getting the next sequential value
automatically rather than relying on the data entry people to always
accurately know what the next one should be).
A generator won't work, I don't think, because we don't want any missing
values. IOW, if the user starts a record, and gets number 123445 and then
for some reason doesn't complete the record, that number will be unused and
there will be a hole in the sequencing.
If I could wait until just before the post to grab the number, that would
probably solve it, but again, the user wants to see this number all through
the data-entry process.
Any ideas on the best way to implement something like this? The best thing I've
been able to think of so far is to select max(admission_number) from the
table and use the next number through the data-entry process, but query for
that value again just before posting and, if it has changed, increment the
number again and inform the user of the numbering change. There must be a
better way, though.
|
First of all, exact number can not be obtained when the record entry
is started, only a guess which will have to be verified prior to
saving to database and changed if another user got and saved the same
ID in the meanwhile. So, you got that part right.
Second, if entries can be deleted after saved then you will have holes
in the numbering. So using max() will not be good if you want to catch
up the missing ID's. Here is an sql that migth help with this, how to
select next id:
select min(T1.id)+1
from table T1
left outer join table T2 on
T2.id = T1.id + 1
where T2.id is null
tomi.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
