Subject Re: [IBO] Re: Import dbf data
Author Jason Wharton
This would be slower than the INSERT statement in the TIB_DSQL component
using input parameters.

I suppose I better be a little more explicit in my instructions...

var
Col1: TIB_Column;
Col2: TIB_Column;
...
begin
MyDSQL.SQL.Text := 'INSERT INTO MYTBL ( COL1, COL2, ... ) VALUES ( :COL1,
:COL2, ... )';
MyDSQL.Prepare;
Col1 := MyDSQL.ParamByName( 'COL1' );
Col2 := MyDSQL.ParamByName( 'COL2' );
...
MyDSQL.BeginBusy( false );
try
<start transaction>
try
<parse out a new record from input or quit if done>
Col1.As<type> := <value for column1>;
Col2.As<type> := <value for column2>;
...
MyDSQL.ExecSQL
<loop back up for next input record>
<commit transaction>
except
<rollback transaction>
raise;
end;
finally
MyDSQL.EndBusy;
end;
end;

If you would like a slightly fancier example that allows you to cancel the
operation look at the BlobInserts sample application. It does almost exactly
what is being done here.

There isn't a faster method other than using external tables to load data
into InterBase.

HTH,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com


----- Original Message -----
From: "Clément Doss" <cdoss@...>
To: <IBObjects@egroups.com>
Sent: Saturday, December 02, 2000 10:56 AM
Subject: [IBO] Re: Import dbf data


You could also use the IB_SCRIPT component.
I´ve used it a few times. I have imported 200.000 records from a
table. 80.0000 from another..

Just include a SQL like:
INSERT INTO SomeTable(FIELD1,Field2,Field3...,FieldN) Value ("John
Smith","01-JUN-1992",35.2,....,"Y")

You will have 450.000 lines in your IB_SCRIPT (It´s huge... but it
might work)
Call the Execute and there you go!
This is faster then call insert for each record...
(You could build 1k records/lines at a time)

Good Luck!
Clément


> I can do an export with the dbf components to an textfile so that
will be no
> problem.
>
> - Using the create table to an external file looks like using an
xml file as
> table....
> - removing the index is a good idea
> - So I don't need any IBO component...
>
> I have the idea I was doing this 10 year ago also for importing
data to get
> it into dbf/dbase. Not much changed over the years...
>
>
> Bram van der Voet / A&V Automatisering
> bram@a... <mailto:bram@a...>
> Glasbergenlaan 6
> 2235 BP VALKENBURG ZH
> tel 071 407 6956
> fax 071 407 3939
>
>
> -----Oorspronkelijk bericht-----
> Van: Clément Doss [mailto:cdoss@d...]
> Verzonden: zaterdag 2 december 2000 14:07
> Aan: IBObjects@egroups.com
> Onderwerp: [IBO] Re: Import dbf data
>
>
> Hi Bram,
>
> How fast can you create FixedLength ASCII files with your
components?
>
> You could try to use
> CREATE TABLE TB_ZIPCODE EXTERNAL FILE 'C:\TEMP\ZIPCODE.TXT'
> (That means that you can SELECT * FROM TB_ZIPCODE and IB will read
> the info from a TXT file...
>
> To use external files, I believe they must be on the same machine
> as IB server. Then you can INSERT (with type casting) in a permanent
> table. (Remove your indexes before massing insertions.)
>
> This is the procedure I use to convert dBase or Paradox to IB.
> (this will not work if you have MEMOs and alikes... :-(
>
> Good Luck!
> Clément
>
>
> >
> > Helen, I never used Interbase 5. Dbf is the dbaseIII file format
> also used
> > in Foxpro. I have IBO alike components for fast access to the dbf
> file. I
> > was hoping to combine this with an IBO component to do a fast
> transfer.
> >
> > Bram
> > _______________________________________________________