At 12:26 AM 10/05/2003 -0700, you wrote:

> >IMHO, none of them is efficient. I don't touch TIB_StoredProc, ever.
>
>Why is that? Why does TIB_StoredProc exist at all if you can get the same
>functionality with the objects you mention below?

"Consumer demand" - there was a time in IBO's history when IBO was too hard
for people unless it mimicked the VCL behaviour. Fortunately, we are way
past that now. If people want to do BDE-style apps, they can use the TIBO
components.

> >For executable SP's I use TIB_DSQL. For selectables, I use TIB_Query when
> >I want a scrolling dataset, TIB_Cursor when I don't.
>
>Ok. These are SP's that both update data and return values (just one row
>with output values, not a scrolling dataset). So, in that case, would I
>use TIB_Cursor?

Yes. You will get a one-row buffer.

>What would I set the SQL to?
>Would it be "execute procedure MyProc ('12345", 234, 'Hello')"
>Or, would it be "select retval1,retval2,retval3 from MyProc where MyParam1
>= '12345' and MyParam2 = 234 and MyParam3 = 'Hello'"
>or something else?

If the purpose of the procedure is to return a dataset (even a one-row one)
for work on the client, or as a provider of parameters, then you need the
SELECT syntax. If the only purpose is to execute some DML on the server,
then use the EXECUTE syntax. The reason is that, if you are going to have
the user do updating, you want to keep the transaction open. Although you
can, *don't* mix and match, or you will be exposing users to performing
manipulation on "dirty" (uncommitted) data.

>In either of the above SQL statements, do I use paramaters (:MyParam1,
>:MyParam2, etc.) instead of the literal strings, just like in a
>parameterized select?

Sure can. IBO does the "clever thing" with all params that match the names
of either input columns (arguments) or output columns.

>Then, do I call Open() or Execute()? Which is correct in this case?

Call Open on an IB_Query. Call First on an IB_Cursor. Call Execute on an
executable (that has the EXECUTE PROCEDURE(...) syntax).

>Then, do I just use the TIB_Cursor's FieldByName() method to get the return
>values?

Correct. Or Fields[] if you need to refer to them by position rather than
name. Fields[] and Params[] are just implementations of TIB_Row (an array
of TIB_Column), so you can also refer to them using the Row.ByName() method
of TIB_Row if it's appropriate.

> >If the same SP is going to be called again and again with different
> >parameters, at random, it makes sense to create it at create-time, prepare
> >it once the first time it's called and then select or execute it as
> >required. I don't want to waste resources on continually creating,
> >preparing and destroying instances of the same object if the only variable
> >factor is the input parameters.
>
>The call to my SP is going to be done in a loop, with different parameters
>each time.
>
>So, in my case above, I would set the SQL parameter to the correct
>statement, prepare, and execute/select.

Prepare once, then execute/select iteratively. Just test "if not Prepared
then Prepare;" Take note that the Params[] array (a property) is not
available in an unprepared dataset. ParamByName() and Row.ByName() are
methods, which include this test, so you won't need it if you use either of
them.

>Then, what do I do for each iteration of the loop? Do I use ParamByName to
>set the params for each iteration?

Answered above? Set the parameter values by one of the three methods after
the Prepared test and before calling Open, First or Execute (as the case
may be). It's a good precaution to call Clear on each parameter before
assigning the next value; or you can call Params.ClearBuffers(rsNone) on
the whole Row object if you want to kill all the birds with one stone and
avoid an extra looping level. :-))

If you have gazillions of iterations, put a counter in the client and
commit the transaction in bunches of about 10,000 max. This avoids getting
into an ever-decreasing spiral of dirty-row overhead on the server and
getting rotten fruit thrown at you by your users.

cheers,
Helen