Tim,

At 08:36 AM 30/06/2003 +0200, you wrote:

>Hi all,
>
>I have several static queries, several dynamic queries (where the SQL text
>of the TIB_Query object is determined when the user makes the query) and
>several stored procedures in my application.
>
>I have (for years) used something like the following to execute these kinds
>of SQL :
>
>with MyQuery do
>begin
> if not TIB_Transaction.InTransaction then
> TIB_Transaction.Commit;
>
> if active then close;
> if not TIB_Transaction.InTransaction then
> TIB_Transaction.StartTransaction;
> ParamByName('Param_1').AsWhatever := Var_1
> if not prepared then
> Prepare;
> Open; (or execSQL or whatever)
> First;
>end;

Did you realise this is a completely unnecessary consumption of
resources? If the only thing in your query that changes is the value of
the parameter(s) then you only have to call Refresh. The query stays both
active and prepared.

If you change the whole statement, i.e. apply a new statement (different
columns, different where clause, different order by clause) then IBO takes
care of the unpreparing and preparing that's required. If it's just the
where clause or the order by clause changing (but the output spec is the
same), then use the SQLWhereItems and SQLOrder properties in conjunction
with the OnPrepareSQL event to radically cut down the overhead that you'd
otherwise get by starting a fresh SQL statement by "conventional" (read
BDE/IBX/DbExpress) rules.

>I also have a generic procedure that takes a dataset as a parameter and
>commits the transaction, closes the dataset, and unprepares it.
>
>This procedure is called often - every time I am finished with the dataset.
>
>Questions :
>
>When should I unprepare a query or stored procedure?

Only when you know it's not going to be needed any more. OTOH, if it's
prepared and there's a likelihood it will be used again in the session,
then close it but leave it prepared.

>Is this the best way of doing transaction control? There are exceptions -
>when, for example, I need to run two or three procedures / queries and use
>transaction control, they will all form part of the same transaction context.

One interpretation of "Transaction control" is that you (or, rather your
code) is IN CONTROL of transactions. It's absolutely RIGHT to place a
group of queries together inside a single transaction context. The control
part comes in your code making sure that the task 1) gets finished in short
order 2) takes care of things if it fails to commit and 3) is ready to "go
again" next time the same context is wanted.

But, going back to your procedure above, this will certainly make sure that
you're not getting one task impinging on another; but all that stuff with
choking off the dataset doesn't have anything to do with "transaction
control". Think of the transaction as the conversation between the
application and the database. The database doesn't know or care what
happens in the datasets. Once the transaction is committed, it's done,
gone, doesn't exist any more. The exception would be if you were running
autocommit transactions - then, even though all outstanding work is
committed, you've still got the original transaction context and so garbage
collection in the DB will remain stalled...

hth
Helen