Subject Re: [IBO] Volume Data Adding
Author Geoff Worboys
Last things first...
> This compiles ok but when I run it I get an error saying
> "invalid transaction handle (expecting an explicit
> transaction start)

Probably due to incomplete or mismatched property settings. Ensure
that...

Transaction.IB_Connection = Connection
Dsql.IB_Connection = Connection
Dsql.IB_Transaction = Transaction

Note that the connection used by the DSQL (or any other ib statement)
must match a connection available to the transaction. You should not
normally have to call StartTransaction, IBO usually takes care of that
automatically.


Now the code. I dont know how much was simply due to your use of
shorthand when posting but...

ALWAYS ALWAYS ALWAYS put try/finally blocks around your use of
beginbusy/endbusy. Without this an exception can make IBO lose
synchronisation of its internal flags.

ParamByName is ok for single instances or short-run loops. For long
running loops try to use column references or array indexes. So your
code should be along the lines of...

ib_dsql.BeginBusy(true);
try
ib_dsql.prepare;
tmpCola := ib_dsql.ParamByName('a');
tmpColb := ib_dsql.ParamByName('b');
tmpColc := ib_dsql.ParamByName('c');
for lots of times do
begin
tmpCola.astype := x;
tmpColb.astype := y;
tmpColc.astype := z;
ib_dsql.ExecSQL;
end
finally
ib_dsql.EndBusy;
end;

OR If you dont wont to declare lots of local variables for temporary
column references, you can use indexes into the parameter column
array. However if you do this I strongly suggest that you define the
SQL in code next to your loop so that the correspondance is obvious
(otherwise it is too easy to change one and forget the other)...

ib_dsql.BeginBusy(true);
try
ib_dsql.SQL.Text := 'insert into tablename(a,b,c)
values(:a,:b,:c)'
ib_dsql.prepare;
for lots of times do
begin
ib_dsql.Params[0].astype := x;
ib_dsql.Params[1].astype := y;
ib_dsql.Params[2].astype := z;
ib_dsql.ExecSQL;
end
finally
ib_dsql.EndBusy;
end;

I have ignored transaction commit and cleanup issues here, and also
processmessages calls to prevent the application from appearing to be
locked up. See the earlier posting for those aspects.

The use of column references in loops is something that we may appear
to push pretty hard. Thats because it really is worth it in tight
loop situations, you will see significant improvement in performance
on long running loops.


HTH

Geoff Worboys
Telesis Computing