Thanks Geoff,

Just got one problemette..

i've used a kind of combination of the ideas below. I have put the
insert statement into the sql strings property of the IB_DSQL component
with a parameter for the actual data value (i.e. insert into tablename
values (:a, :b, :c)) then in my app I have prepared the dsql, set up the
parameter values and excecuted:

ib_dsql.prepare;
ib_dsql.beginbusy(true);
for lots of times do
begin
ib_dsql.parambyname('a').astype := x;
ib_dsql.parambyname('b').astype := y;
ib_dsql.parambyname('c').astype := z;
ib_dsql.execsql;
end;
ib_dsql.endbusy;
ib_dsql.unprepare

This compiles ok but when I run it I get an error saying "invalid
transaction handle (expecting an explicit transaction start)

I've obviously missed something here. I've tried puting a transaction
component on the DM and puting a starttransaction just before the
prepare but this didn't do the trick.

Any idea what I'm doing wrong?

TIA
Paul

Geoff Worboys wrote:

>
> > That sound like it might help me out as well.
> >
> > Is there any chance you could pull that out and send it to me
> directly
> > as yahoogroups is behind the firewall here so I can't see it.
>
> Its a big file - over 3Mb - and I dont want to start sending it around
> by email.
>
> Whoops!!! Just checked the date on that archive file, and it seems it
> does not go through to March anyway. Below are some of the snippets I
> was talking about. I've not acknowledged the authors, I dont think
> they will mind. I just cut-n-paste in chronological order (skipping
> large chunks).
>
> HTH
>
> Geoff Worboys
> Telesis Computing
>
> var
> tmpCol: TIB_Column;
> i: integer;
> begin
> dsql.SQL.Text := 'INSERT INTO SYMBOLS (SYMBOL)' +
> ' VALUES (:SYMBOL)';
> dsql.Prepare;
> tmpCol := dsql.ParamByName( 'SYMBOL' );
> for i := 0 to tsl1.Count - 1 do
> begin
> tmpCol.AsString := tsl2.Strings[i];
> dsql.ExecSQL;
> // make the app responsive during long processing
> if (i mod 1000) = 0 then
> Application.ProcessMessages;
> end;
> end;
>
> Remember to use tmpCol.Clear if you want to insert a NULL value as IBO
> will not clear parameters between calls to ExecSQL.
>
> The basic steps are:
> - Use a TIB_DSQL and write the parameterized SQL once.
> - Prepare it.
> - Get a reference to your parameters in temp vars. Faster than using
> ParamByName. This is only important in a loop like the yours, not in a
> single param assignment.
> - Call BeginBusy or another function that will stop the cursor's image
> from
> being changed according to the component's state (so less CPU cycles
> are
> wasted in the GUI).
> - Start a transaction.
> - Do your loop by reading your input values, assigning them to
> parameters
> and calling Execute.
> - Commit the transaction.
> - Call EndBusy.
> - Unprepare the TIB_DSQL if you aren't going to use it again.
> - Protect the relevant code with try-finally (for EndBusy and Commit)
> or
> include also try-except (just in case you want to Rollback if your
> insertion
> loop raises an exception instead of forcing a Commit in the finally
> part).
>
> you've gotten pretty good advice so far, but noone seems to have
> noticed
> your components. You have to do two changes to maximize speed:
>
> object IB_Transaction1: TIB_Transaction
> IB_Connection = database1
> AutoCommit = False //Don't use true for batch inserts
> Isolation = tiConcurrency
> Left = 504
> Top = 408
> end
> object DSQL: TIB_DSQL
> DatabaseName = '*.*.*.*:f:\ib_db\tradedata\ibqd.gdb'
> IB_Connection = database1
> IB_Transaction = IB_Transaction1 //You must include the
> transaction in
> your DSQL
> Left = 226
> Top = 136
> end
>
> If you then change your code to something like
> var
> i: integer;
> begin
> try
> dsql.BeginBusy(True);
> dsql.SQL.Text := 'INSERT INTO SYMBOLS (SYMBOL)' +
> ' VALUES (:SYMBOL)';
> dsql.Prepare;
> for i := 0 to tsl1.Count - 1 do
> begin
> dsql.Params[0].AsString:=tsl2.Strings[i];
> dsql.ExecSQL;
> if (i mod 1000) = 0 then
> Application.ProcessMessages;
> end;
> IB_Transaction1.Commit;
> finally
> dsql.EndBusy;
> end;
> end;
> (yes, I stole this code from Geoff, just simplified it to suit the
> lazy
> amongst us).
>
> I'd expect you to multiply your performance
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/