Subject Re: [IBO] Transactional design with IBO question
Author Helen Borrie
At 02:40 AM 7/04/2003 +0000, you wrote:
>Thank you Helen. Eliminating the script idea then the criteria is to
>embody as much as possible in stored procedures. Thus, a first store
>procedure would embody the set of known updates/insert (e.g, catalog
>update, log update, etc. - which are certain), plus a 1 or more
>calls for the "variable" update/inserts (min 1, but max = number of
>elements to insert). Is is reasonable to visualize the "algorithm"
>as described below?
>
> try
> TIB_Script.IB_Connection := TIB_Connection;
> TIB_Script.IB_Transaction :=TIB_Transaction;
> if not TIB_Transaction.TransactionIsActive
> then TIB_Transaction.StartTransaction;
>
>/* 1 stored procedure for all operations which are "fixed" */
> IB_DSQL.SQL.Clear;
> IB_DSQL.SQL := 'Execute SP_Fixed_Ops(Arg1, Arg2, ...)'
> IB_DSQL.ExecSQL;
>
>/* 1 call for each item, since it is a variable number of
>items (e.g., N items)*/
> IB_DSQL.SQL.Clear;
> IB_DSQL.SQL := 'Execute SP_Item(Item1, Arg1, Arg2, ...)'
> IB_DSQL.ExecSQL;
>
> IB_DSQL.SQL.Clear;
> IB_DSQL.SQL := 'Execute SP_Item(Item2, Arg1, Arg2, ...)'
> IB_DSQL.ExecSQL;
> ...
> IB_DSQL.SQL.Clear;
> IB_DSQL.SQL := 'Execute SP_Item(ItemN, Arg1, Arg2, ...)'
> IB_DSQL.ExecSQL;
>
> TIB_Transaction.Commit;
> except
> TIB_Transaction.RollBack;
> end;
>
>Sorry to make it sound like a recipee, but I am trying to have the
>right procedural model. Thank again.

Horacio,
First, I see NO REASON for there to be script component in this.

Second, it would not be a "procedural model" to call a multiplicity of
stored procedures to achieve one task. The SP would normally perform all
of the processes, either in itself entirely, or by calling other stored
procedures from within itself...meaning it is common to use a kind of
"root" SP that simply accepts input parameters and calls a series of
embedded SPs to do the separate pieces of the task.

You would not (could not) design a SP that took a variable number of
arguments. SPs are compiled, so that they "know what to expect" by way of
input arguments. It is valid, however, to pass nulls or dummy values to
arguments, as long as your SP knows what to do with these.

On the client side, the more typical "procedural model" might be to have
one or more scrollable datasets, from which the user picks the rows which
will supply the values for the stored procedure's input argument. There is
one DSQL, the stored procedure call, prepared once only, to which are
passed the values from the user's selection at each execution.

Helen