Hi,

I've got a problem inserting records that is definitely down to bad design
on my part. I'm hopeful someone can slap some sense into me before I make
matters any worse.

Someone hinted this was wrong when helping me with a problem with embedded
server a few days ago, but I stumbled on blindly. *8)

To get to the problem: This is using Firebird 1.5.3 superserver (to
eventually run embedded). It has multiple threads inserting thousands of
records into the database. Each thread maintains its own connection. In
case it's relevant, this is coded in Delphi 7 using ZeosLib to connect.

Several of the tables need to only insert if the record isn't already
there. I always need the PK back returned because I need to insert it into
cross-reference relationship tables (I don't know the correct name, I mean
a table that holds the PK from one table and the PK from another table)

Even though someone advised me against it, the inserts are being done using
a selectable stored procedure.

PROCEDURE FIND_OR_ADD_ARTIST(
NAME CHAR(100) CHARACTER SET NONE)
RETURNS(
ARTISTID BIGINT,
ADDED SMALLINT)
AS
DECLARE VARIABLE FOUNDID BIGINT;
BEGIN
FoundID = -1;
Added=0;
SELECT ID FROM ARTISTS WHERE (Artists.Name = :NAME) INTO :FoundID;

if (:FoundID = -1) THEN
BEGIN
SELECT gen_id(ARTISTS_ID_GEN,1) FROM RDB$DATABASE INTO :FoundID;
INSERT INTO Artists (ID, Name) VALUES (:FoundID, :NAME);
Added=1;
END

ArtistID = :FoundID;
SUSPEND;
END;

I'm calling it with the following query:
SELECT ARTISTID, ADDED FROM FIND_OR_ADD_ARTIST ('Bob')

It seemed to be working perfectly. Then during a mass insert from a bunch
of threads, one of the threads started failing the inserts with: "multiple
rows in singleton select." (Error -811)

So I've got duplicates where no duplicates should be. I figure this is
because each thread is so isolated from the view of what the others are
doing that one can add a record for artist "Bob", get a unique ID back,
then another thread does exactly the same and adds a second Bob. There's
no room for 2 Bobs in my database.

So, I've got a design issue. I can see a potential fix, but I don't want
to code myself into the wrong corner, I'd rather get the advice first.

I could alter the SELECT to only "SELECT FIRST 1...". Now the code won't
fail, but I'll still have too many Bobs. Hardly a good solution/

I could make all the fields in the table unique, but then I'd have to trap
for the error and I haven't found how to do that in a stored procedure
yet. I prefer the 2nd approach, but I don't know how to do it. I'm
guessing given the gaping void that represents my lack of knowledge that
there's probably several other ways to do this too.

I'm trying to avoid asking stupid questions and I'm doing a lot of
background reading, but the majority of resources assume you already have a
semblance of a clue.

Any thoughts appreciated. Sorry, I think I've rambled on a bit.

Cheers,
Rik