At 07:31 PM 07-08-02 +0200, you wrote:

>Hi
>When I do the following i get a invalid tranaction handle error.
>What am I doing wrong. I have tried various different combinations of the
>code but no success
>
> SPDiary: TIBOStoredProc;
>
> SPDiary.Prepare;
> SPDiary.ParamByName('IN_DATE').AsDate:=CurrentDiaryDate;
>
>SPDiary.ParamByName('IN_USER').AsString:=DBR.IB_Connection1.LoginUsername;
> SPDiary.Active:=True;
>
>I have also tried Params[0].AsDate but it also does not work.
>I have moved prepare around but it does not have a effect.
>
>I have also deleted the params properties values and created a new component
>to
>see if that would have a effect.
>
>It is an ISC error. It could mean that I am giving IBO the wrong data
>causing it to
>send the wrong data to the server causing the server to raise this problem.

Is "Invalid transaction handle" the only error? If so, don't look at the
data assignments, they are not causing this error.

A transaction handle is passed back to the client when the client library
successfully starts a transaction on the server on behalf of the
application. It gets a unique new transaction handle each time your
application's transaction object starts a new transaction (CommitRetaining
aside; which effectively "restarts" the same transaction).

If your transaction object (which will be SPDiary.IB_Transaction) committed
or rolled back, then the transaction handle becomes
invalid. Alternatively, if no transaction was started, then there is no
transaction handle and the client library might attempt to pass Null as a
transaction handle - that's not valid either.

So, you'll need to test for the presence of a *physical* transaction
handle. The property for this is InTransaction:

with SPDiary do
begin
if not IB_Transaction.InTransaction then
IB_Transaction.StartTransaction;
if not Prepared then
Prepare;
ParamByName('IN_DATE').AsDate:=CurrentDiaryDate;
(* ParamByName('IN_USER').AsString:=DBR.IB_Connection1.LoginUsername; *)
// get this through the object's own property
ParamByName('IN_USER').AsString:=IB_Connection.LoginUsername;
TRY
(* SPDiary.Active:=True; *)
// if this is a "selectable" stored procedure, call Open:
Open; (***** or *****)
// if it's an executable SP, call Execute:
Execute;
// if you want to commit now ----
IB_Transaction.Commit;
EXCEPT
// handle database exception conditions
END;
end;

Without so little info on what you are doing, this is at best a guess in
the absence of missing symptoms...such as the transaction handle DOES exist
but has unhandled exceptions somewhere...in which case, the suggested code
above will continue to throw errors.

Failed posts (or calls to execute SPs) don't have posted work waiting to be
committed. TransactionIsActive is a useful property to test whether the
transaction has posted work that is waiting to be committed (or, in the
case of datasets, has edits, inserts or deletes that can be
posted). TransactionIsActive will be false whenever the app has no changes
pending. It's not a substitute for proper exception handling,
though. Every instance of a task should be "atomic", i.e. it should handle
its own exceptions and leave the transaction object "clear" for any ensuing
tasks to proceed.

cheers,
Helen