Hello Rik,

RB> I originally had "WHEN ANY DO", but when that wasnt working, I removed the
RB> error handler completely and logged the exceptions. It was either -803
RB> (Can't add duplicate rows) or that -901 error shown above. I'm fairly
RB> convinced they're related errors, but when your core knowledge is the size
RB> of an egg cup, you tread extra carefully.

I use WHEN...DO quite rarely myself, so I can't point you. But it
seems strange that WHEN ANY DO doesn't work.

>>Here, I don't get your point again: if you know that the name is in
>>the table already, why do you try to insert the same name one more
>>time (knowing that there is a unique constraint for that)? Maybe
>>another thread deletes names from the table time to time...

RB> I don't. I try and select. If the initial insert fails then I know it's
RB> in the table uncommitted. I need the ID, so I sleep for a random period to
RB> allow the other thread to commit and then perform a Select.

I see now. Then you may get it all just with the SP:
-----
CREATE PROCEDURE ADD_ARTIST(
NAME CHAR(100) CHARACTER SET NONE)
RETURNS(
ARTISTID BIGINT,
ADDED SMALLINT)
AS
BEGIN
Added=1;
ArtistID = gen_id(ARTISTS_ID_GEN, 1);
BEGIN
INSERT INTO Artists (ID, Name) VALUES (:ArtistID, :NAME);
WHEN ANY DO
BEGIN
ArtistID = -1;
select ID from Artists where Name = :Name into :ArtistID;
/* if there is no such name, ArtistID remains the same */
Added=0;
END
END
SUSPEND;
END;
-----
In this case, you get AtristID\1 (if inserted), ArtistID\0 (if
exists, i.e. committed and "visible"), -1\0 (if not committed and not
"visible" yet). If latter, commit your transaction, sleep, and try to
select an existing ArtistID (remember that inserting transaction may
be rolled back, so you may want to use your SP for retries - then it
will return you an existing entry or insert a new one for you). So,
you have stuff like (pseudocode):
-----
AID := -1;
repeat
StartTransaction;
ExecSQL(select ArtistID, Added from Add_Artist('Bob')); // actually, you don't need Added _here_
AID := ParamByName('ArtistID');
CommitTransaction;
until AID <> -1;
-----


--
Best regards,
Pavel Menshchikov
http://www.ls-software.com