Re: Unique Fields in Database
Re: Unique Fields in Database
- Subject: Re: Unique Fields in Database
- From: Neil MacLennan <email@hidden>
- Date: Mon, 21 Jan 2008 10:03:59 +0000
Hi Stuart,
Thanks for your reply.
The separate table is a workable solution. And I think that my stored
procedure can take care of all the housekeeping so that all WO has to
concentrate on is inserting the received value into a database:
something it does rather well.
I think that your second idea of checking for the value before insert
is prone to a 0.00001% chance of another instance inserting the same
random number in between you checking the DB and inserting into the
DB. I don't think that WO supports SQL transaction-based mode of
operation (although am happy to be educated on this point if it does).
I'm not even sure if RawRow based operations can be coerced into using
transactions? Using your first idea though, means that I don't need to
go down this route. I'd still be interested, in general, in how to
avoid these 'millisecond' windows of potential disaster between a read
and a write request.
.neilmac
On 21 Jan 2008, at 05:15, Stuart Parker wrote:
Neil
Create a table with as many unique values as you will ever need.
When you need one, grab it from the table and delete it so it can't
be used again.
Instead of looking for an exception when inserting the code, just
check that the value you are about to insert does not exist in the
new table before you call saveChanges.
cheers
Stuart
On 21/01/2008, at 1:06 PM, Neil MacLennan wrote:
Following on from the postings on subject, "Avoiding duplicate
records" can I get my understanding right? (so that I don't waste
any more time trying to fix what can't be fixed):
I'm looking for, say, a six-character unique field in my database
record. I generate this randomly so that each is unpredictable (at
least within 36^6 guesses).
As I understand it, there's no way to:
i) generate the random character string,
ii) check for uniqueness in the database
iii) insert it into the database with the guarantee that no other
instance has co-incidentally inserted the same string in the
milliseconds between ii) and iii)
Currently I have a stored procedure (in MySQL) finding me a free
random string (I figure that this will be quicker than WO trying to
find me one) and then inserting that String into my EO and saving
it. I then look for a EOGeneralAdaptorException on saveChanges()
and parse it for MySQL's error code for failing a UNIQUE constraint
on the DB (error code: 1062). If an exception is thrown, I go back
an look for another free string from the stored procedure. I do
this in a loop repeating 50 times, at which point I give up and
throw the Exception right up to the user interface level as a
"problem".
I'll only be using 0.1% of the available key space for a 6-
character alpha-num string, so I figure that a loop of 50 will be
sufficient to always find me an available string. But, in the
general case, it just seems "undesirable" as a solution.
Is this the best I can hope for?
.neilmac
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden