Ann W. Harrison a écrit :

>At 04:26 AM 2/20/2004, Jonathan Neve wrote:
>
>
>
>>Yes, of course. However, there are ways to get around this problem. For
>>example, what I usually do, is first get the generator value and display
>>it, using "gen_id(gen_name, 0) + 1", and then, if and when the user
>>confirms, I actually increment the generator. This avoids all
>>concurrency issues in a simple way.
>>
>>
>
>Oh no no no... Don't do that. Oh dear no, don't do that! Oh my.... Sorry,
>I'll get a grip on myself now. That technique works as long as the user
>doesn't care what is displayed on the screen. In which case, why
>bother? gen-id(gen_name, 0) doesn't reserve the value for you. If two
>users do it at the same, time, they'll both get the same value. Which is
>fine, as long as that value is not used in secondary tables or stored in
>the primary table. But why bother calling gen_id if you're going to ignore
>the result?
>
>

As I said elseware, this value is indeed ingored (or rather, it is used,
but since I have an update cascade foreign key on all refering tables, I
can update that value if necessary), but it is almost always correct,
which is why I show it. In other words, in most cases, everything works
as though I had reserved a generator value from the start, but in a few
rare cases, when a concurrent insert occurs, the value will change as
soon as the user confirms.

Anyway, I can't help but think that this is a minor point. I agree that
showing it is not necessary, and in some situations I don't show it. But
since this value is usually correct, I sometimes do. But either way I
don't rely upon it as though it were truly unique.

>As for the rest, what's wrong with concatenating a properly acquired
>generator value with whatever other useful stuff the user wants to see?
>
>

Well, that's fine in many cases, but it depends what your user wants. If
the user wants (as in the example I gave) his invoices to be numbered
with a distinct counter for every month (and year), this isn't possible.
So the codes would go : 04010001, 04010002, ..., 0401000N, 04020001,
04020002, ..., 0402000M, etc. If there's no relationship between the
counter for the different months, as in this example, then it's no way
to use the same generator for both.

Currently, my only option in such situations is to not make this column
my PK at all. But still, this doesn't really solve the problem, since I
then give it a UNIQUE constraint. If the value of this column has to be
unique, it would still be nice to have the sort of generator system I
described, so that a counter could be maintained in a transaction
independant way, based on certain other criteria. So what I do now, is
query the table to find what seems to be the latest value, and then I
increment it, and try to insert my record. Then, if there's a PK
violation, I continue incrementing the value and trying to insert until
I manage to get through. This works ok, but it would be nice to have a
simpler way of doing this.

Thanks for any and all input regarding this idea!

Jonathan Neve.

>Regards
>
>
>Ann
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>

[Non-text portions of this message have been removed]