Subject Re: [IBO] 'Freeing' stored procedures to allow them to be updated?
Author Helen Borrie
At 10:46 PM 3/12/2009, you wrote:
>Hi all,
>I have a few stored procedures (TIB_StoredProc) that return results that are used in reports.
>As long as no-one is running my application I am able to alter the procedures and save the changes with no problems (as expected).
>If someone has run a report which calls the procedure, I am unable to save any changes (I'm using IBExpert to make the changes) until they close the application completely.

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.

>Are there any commands in IBO that 'free up' the stored procedure once it's been run to allow me to make and save changes on the server?

Disconnect. ;-)
Watch out for connection pools. If you have spare or abandoned connections sitting around in a connection pool, the engine cannot go offline and the cache remains loaded.

>I've tried things like sp.FreeServerResources and sp.UnPrepare but I'm not sure what the likely options are for what I'm trying to do as I don't know the proper name for it!

This is not a client-side issue. Client code can't change the behaviour of the server nor the resources in use by other connections nor by the server itself. Resource calls from the client affect only the resources entailed by the CURRENT_CONNECTION (cursors, output buffers, etc.) The contents of the database cache are entirely out of the reach of the client API.