Thanks, Helen. Another question:

Here's what I'm actually doing in my loop:

Grab the key from the source data record.
Do a select to see if the key already exists.
If (key doesn't exist)
Insert it
else
Update it

Now, with the scenario that you outlined below (Using the transaction),
what will happen if the source data has the same key twice? If I'm not
committing after each insert, will the next select accurately know whether
or not the key already exists (if it was previously inserted but not
committed)?

One other thing: If I have a hard-coded value that I'm inserting into EVERY
record, is there any performance benefit to hard coding it into the insert
statement, as opposed to leaving it as a parameter that I set the same
every time?

Thanks,
Joe

At 08:10 PM 11/7/2002 +1100, you wrote:

>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
>
>
>
>___________________________________________________________________________
>IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
>___________________________________________________________________________
>http://www.ibobjects.com - your IBO community resource for Tech Info papers,
>keyword-searchable FAQ, community code contributions and more !
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/