Subject Re: [IBO] TID_DSQL and keylinks
Author Helen Borrie
At 08:46 PM 11/01/2004 +0100, you wrote:

> Okay, let me try to explain what I am trying to do.
> I am inserting a record into table e_project, using TIB_DSQL. The SQL
> for that TIB_DSQL is EXECUTE PROCEDURE <my proc(params1..paramsn). That
> works fine.
> Now when I retrieve a dataset with can contain multiple records, also
> using TIB_DSQL I am not seeing the synchornised data.

You can't use tib_dsql to retrieve multiple records.

> If output fields in a selectable SP are cooked at execution time, is
> any synchronisation necessary?

Yes: after any change, the selectable SP has to be re-executed.

> My preference is to use a selectable SP, what are my options?

Now that we have the two ends of the puzzle, let's just take this back to
the drawing board. :-)

Use an ib_query or iboquery for the selectable SP, let's call it
sp_select. Set KeyLinks for it so that IBO can identify each row uniquely
when it is the current row in the buffer.

Go to the InsertSQL property of sp_select. Into that, place the EXECUTE
PROCEDURE my_exec_proc(:param1, ...:paramN) statement. Name the parameters
the same as the corresponding output fields in sp_select - the InsertSQL
property wraps a tib_dsql internally.

OK, now, when you call Insert on the dataset that has sp_select, IBO will
recognise it as "updatable" (for inserting) and open a new row in the
dataset. Fill out the row and call Post. Now IBO will pick up the
parameter values from the new row (provided the names match, of course) and
pass them ByName to my_exec_proc and execute it.

At this point the new row will be placed into a new recversion on the
server but it's not committed yet - so call Commit on your transaction.

Now, call Refresh on the iboquery or ib_query. This will close the dataset
and re-execute sp_select.

If you want the output set from sp_select to be fully updatable, go ahead
and write executable SPs for the Update and Delete operations and deal with
them similarly using the EditSQL and DeleteSQL properties of your