Loading data to an ADOTable

[BuilderSpec]

Hi

I have a file with fixed format fields. I am writing a read loop to format and insert the records by:

ADOTable1->Insert();
// Format the record

ADOTable1->Post();

However it seems to take a long time. I have switched to batch updates by:

ADOTable1->LockType = ltBatchOptimistic;
ADOTable1->Insert();
// Format the record

// every 5000 records
ADOTable1->UpdateBatch(arAll);
ADOTable1->Post();

But again the updates seem to be faster but the UpdateBatch method seems to takes ages and in the end is only marginally quicker..


So question is this..

What is the best method / component to add lots of records from a file at once ? ( 70,000 records in my file ) . The above methods just seem too long...



Hope this helps!

Regards

BuilderSpec

 

[2ffat]

Sorry, I've been busy. Did you find a solution? Somewhere I've read about this or a similar problem.


James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[BuilderSpec]

No I didn;t find a satisfactory solution..I am stuck with doing the batch updates and it takes about 10 minutes for 70k records...

You know of a better way ?

Hope this helps!

Regards

BuilderSpec

 

[2ffat]

I'll look around and see if I can find something in my notes.


James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[BuilderSpec]

Something I came across which I thought would do it but didn't..

The ADOTable has a LoadFromFile and SaveToFile method whereby you can save a recordset to an XML file and indeed load an XML file into the ADOTable. This is excellent as I can create the XML using TStringList then use that XML to load the table and the load works quickly.

But..

I could never find a method etc to actually write the loaded recordset to the actual table in the database. is this possible ?



Hope this helps!

Regards

BuilderSpec

 

[BuilderSpec]

Hi

I have used this..

ADOTable1->Close();
ADOTable1->Connection = 0;
ADOTable1->LoadFromFile ( "test.xml" );
ADOTable1->Connection =ADOConnection1;
ADOConnection1->Open();
StatusBar1->SimpleText = "Applying updates..";
Application->ProcessMessages();
ADOTable1->UpdateBatch();
StatusBar1->SimpleText = "Ready";

on the basis of the information from

http://www.asiplease.net/computing/delphi/databases4.htm

however although it runs perfectly the underlying table is still unaffected by any changes I make.




Hope this helps!

Regards

BuilderSpec

 

[2ffat]

In my Borland directory, there are examples of code. The one in the ADO directory called "Briefcase" loads from the file. I haven't compiled it. Have you?


James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[BuilderSpec]

The briefcase model works fine..

The briefcase model uses the principle that you can take the data offline by NULL'ing the Connection and saving the data to an XML and using that as base data. More importantly you are using data aware components to do it so it knows when you have inserted or edited etc.

What I am trying to do is build a brand new file in XML outside the application , then use the application to load the XML and then write that file to the database.

In short my app is :

Delete table contents
Load new data

I wanted to :

Delete table contents
Write an XML file manually containing data.
Load XML file to dataset component
Have the component write all the data to the underlying table



Hope this helps!

Regards

BuilderSpec