>> > INSERT INTO Artists (Name) VALUES (:NAME);
>> > SELECT gen_id(ARTISTS_ID_GEN,0) FROM RDB$DATABASE INTO :FoundID;
>>
>>This isn't multi-user safe.
>
>It's been the one part of my design that's had me concerned. It's in a
>stored procedure for 2 reasons. The first is because I wasn't sure which
>database engine I was going to use and I wanted to code the classes without
>getting caught up in SQL engine specifics. Secondly, because I was trying
>to make that as thread / user safe as possible.
>
>Obviously, there's a before insert trigger populating the ID field that I
>didn't post in my last mail that increments the generator. I thought the
>combination of trigger and stored procedure was going to be the best

design.

Use the solution I gave to the original poster.

Get the new value from the generator INSIDE your Stored Procedure,
then use that value to insert the new record.

Keep the trigger, but modify it slightly so that it will only assign a new
value if there's no value for the ID column. Why? Well, if, for some
reason, any application starts inserting new rows into that table, at
least the new records will have IDs. If you don't want this, remove
the trigger.

>>If someone else uses this procedure/code, an INSERT could occur between
>>your INSERT and the next SELECT. You will get the wrong ID here, because
>>the generated has been incremented.
>
>Should I be locking? Or is the design just rubbish?

See above. Don't lock, there's no point in using generators if you're
doing locking :-)

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com