Subject Re: [IBO] Data Pump: More efficient way?
Author Helen Borrie
At 07:32 AM 5/09/2004 +0000, you wrote:

>In moving data from MS Access to Firebird I am using ADO to read the
>Access tables, and an IBQuery to write the data to Firebird. Sort of
>like this:
>While Not ADOQuery1.eof do begin
> IBQuery.Append;
> < Code setting IBQuery fields := ADOQuery fields >
> < Some fields must be translated in the process >
> IBQuery.Post;
> ADOQuery1.Next;
>end; // While...
>Field names are not a one-to-one match, and data types are also not
>a perfect match either (example boolean to Char(1)).
>Since I am new to Firebird, and IBObjects, is there a more efficient

TIBQuery isn't an IBObjects component.

Don't try to mix the IBX components with IBO - it won't work.

Don't use a query component for DML operations like inserts. Use the
TIB_DSQL component for the insert; and parameterise all of the columns for
the destination database.
SQL property is:

INSERT INTO BLAH (Col1, Col2, Col3, ...)
VALUES (:Col1, :Col2, :Col3, ....)

This way, you can do this in [BeforeExecute]
with MyDSQL do
if not Prepared then Prepare;
ParamByName('Col1').AsSomeType := AdoQuery1.Acolumn.AsSomeType;
ParamByName('Col2').AsSomeOtherType := AdoQuery1.Acolumn.AsSomeOtherType;


In most cases, you will be able to use the actual As... casting methods to
transform your mismatched data.

The Execute call will post your inserts. Break the operation up into
batches and commit about every 8 - 10,000 inserts.