Subject Re: [firebird-support] Bad design causing problems
Author Woody
From: "Rik Barker" <rik.barker@...>

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.


You may be able to fashion a critical section procedure in Delphi that
your threads share where they make the call to the procedure. This will make
sure that only one thread accesses the procedure at a time which would
eliminate your problem. The critical section procedure could take the
threads database connection and artist name as a parameter and test for the
existence of the name before adding it if needed. It could return the artist
ID in either a variable parameter or as a result if you make it a function
instead of a procedure call.

However, that being said, I'm sure you will be berated (or at least
roundly lectured) on the dangers and pitfalls of using a select procedure
which modified the data. This is just not something you should do for
various reasons, not the least of which is introducing a "side-affect" in an
otherwise ordinary select routine.

Woody (TMW)