At 01:07 AM 5/12/2009, bamber12001 wrote:

>Thanks for the information Helen. However, by this...
>
>> That is "as designed" (on the database engine side). The executable code for a SP is held in the database cache to protect it from changes being done whilst the version is in use. The SP recompile will be allowed once the cache is cleared. For SS, that means when the database goes offline (no connections at all, then DB Owner logs in exclusively). For CS and embedded, where each connection has its own individual cache, the cache is unloaded when the connection is detached.
>
>
>...do you mean that if I use Classic Server instead of Super Server, when the user(s) who have accessed the stored procedure disconnect I can make the change rather than everyone having to disconnect? If so, I can live with that!

Ouch, too simplistic! Better to think of this as an "anti-feature" from the client POV, rather than a feature - a point where CS allows a window for BLR to be out-of-synch with executing client code, whereas SS doesn't! The effects for the exception handling in your applications will be unpredictable in the active environment, since there could be entailed locks in the global lock table on tables and dependents, whose state cannot be known. A lock table deadlock is a fatal error that a client can't handle and it affects everyone.

Walk the tightrope if you want to, in the knowledge that one toe wiggled in the wrong moment can have unintended consequences.

SPs and triggers are not ad hoc scripts and should never be thought of as such. They are intended for run-time execution, NEVER as objects that can be arbitrarily modified during run-time. If you have a situation where server-side processing has to vary under different conditions, then write a SP for each set of conditions and let your client code choose accordingly.

If you really do have the need for ad hoc, client-injected PSQL in your run-time environment, and you are using Fb 2.0 or higher, then look at EXECUTE BLOCK.

Helen