Subject Re: [firebird-support] Firebird Stored Procedures to select to update from delphi application forms
Author Helen Borrie
At 09:29 AM 17/11/2009, you wrote:
>There seems to be some idea, that it is possible to use firebird
>stored procedures to populate user interfaces and then take the
>modifications and use them to update a database.
>This method seems unreasonable and impossible to me.

Why? If your purpose is to get a "starting point" for a complicated set of records that you want to use to set up a DML operation, it's a very reasonable option, and doable. Of course, you need to design your selectable SP so that it provides the interface (at least beneath the surface the accessible client-side structure) with the keys that will target the records for update precisely and uniquely.

>Has anyone else done things this way?

Sure, when I've needed to. Since you are (or have been) an IBO user, use a TIB_Query for the client-side "pick set" and make it "updatable" using a TIB_DSQL for your custom update statement (which could be a parameterised ESP, if it's appropriate).

There are ways to do and not do this, of course. The output set is stable only within the context of the current transaction: once the transaction commits or rolls back it's all over and the SSP will have to be "selected" (executed and drawn off, row by row) again to become valid for the next thing you be faithful to the principle of "smallest possible set".

Don't be tempted to write a selectable stored procedure that itself performs updates on the journey to the output buffer.

If you don't need the complexity of a SSP to obtain the set you want then there can be more straightforward ways to do this, as others have suggested - updatable views, global temp tables...