At 10:27 PM 06-11-02 -0800, you wrote:

>I've read in numerous posts that when you're inserting a lot of records
>into a table, it's best to:
>1) use IB_DSQL
>2) use a prepared parameterized insert
>3) commit about every 15,000 records
>
>First, I'm not really sure how to do a parameterized insert. I can't find
>any documentation that tells me exactly what I need to do to make it
>happen.

It is just an SQL statement:


MyDSQL.SQL.Add('Insert into ATable (');
MyDSQL.SQL.Add('FieldA, FieldB, FieldC, FieldD)');
MyDSQL.SQL.Add(Values(');
MyDSQL.SQL.Add(':FieldA, :FieldB, :FieldC, :FieldD)');

>I see properties for Params, ParamChar, ParamCheck, ParamCount,
>ParamValues, etc., but I don't have a good idea of how they all work
>together, or what methods I need to call. Can someone lay it out for me,
>or point me to some documentation on it? I have the IBO Help, and Getting
>Started Guide, but can't find this info.

Well, that's because most people wouldn't use the Delphi interface to do
bulk inserts. There are simply better ways than pushing them through a
client application.

>Second, I'm attaching the IB_DSQL object to a TIBODatabase. It has
>AutoCommit set to True. If I leave it at True, will it automatically
>commit after each insert? Do I need to set it to False to manually commit
>at 15,000 records?

If you are going to loop through some structure on the client side then
just do the following:

Drop a TIBOTransaction onto your form and set its ib_connection property to
your ibodatabase. Set the Autocommit property of this transaction to False.

Set the SQL property of your ib_dsql as above and make your TIBOTransaction
its ib_transaction.

Set up your source query and all the stuff you want to do with it. Set its
ib_transaction prop to be your ibotransaction.

Then

Start your ibotransaction.
Then

Open your source query and start to loop through it, after this style:

begin
....
if not mydsql.prepared then mydsql.prepare;

with myquery do
begin
First;
while not eof do
begin
mydsql.params[0].AsWhatever := FieldByName('whatever').AsWhatever;
mydsql.params[1].AsSomething := FieldByName('sthg').AsSomething;
// and so on until all the dsql params have their values
mydsql.execute;
Next;
end;
end;
ibotransaction1.commit;
..

Of course, you will have some exception handling in there as well.

But really, unless you have to process the input data in the client, you
should do a bulk insert on the server side via a stored procedure, and just
have the ib_dsql execute that - no looping, no client buffers full of
insert fodder, just a single call to execute the proc.

Helen