Solved

ADO Batch Update in Delphi

Posted on 2003-11-19
11
3,237 Views
Last Modified: 2013-11-23
Hi,

I am currently using the following code to copy data from an ASCII table into my Access Table.  Which works but is very slow, I am trying now to use the ADO batch mode but not having much success.  How would I integrate the batch update mode into this code.  300 points available


procedure upload();
var
  i: integer;
begin

  counter := 0;
  Table1.Open;
  Table1.First;
  while not Table1.Eof do begin
    myquery.Append;
    for i:= 0 to Table1.FieldCount -1 do
      Myquery.Fields[i].Value := Table1.Fields[i].Value ;
    Myquery.Post;
    table1.Next;
    inc(counter);
    //statusbar1.SimpleText := 'Added record number ' + inttostr(counter);
  end;
  DBgrid1.DataSource := mydatasource;
  DBgrid2.DataSource := Datasource1;
end;

I tried adding the following code and changing myquery to ADODataset1 but it didnt work.

with ADODataSet1 do begin
    CursorLocation := clUseServer;
    CursorType := ctKeyset;
    LockType := ltBatchOptimistic;
    CommandType := cmdText;
    CommandText := 'SELECT * FROM products';
    Open;
  end;
  counter := 0;
  Table1.Open;
  Table1.First;
  while not Table1.Eof do begin
    ADODataSet1.Append;
    for i:= 0 to Table1.FieldCount -1 do
      ADODataSet1.Fields[i].Value := Table1.Fields[i].Value ;
    Myquery.Post;
    table1.Next;
    inc(counter);
    //statusbar1.SimpleText := 'Added record number ' + inttostr(counter);
  end;
  DBgrid1.DataSource := mydatasource;
  DBgrid2.DataSource := Datasource1;
  ADODataset1.UpdateBatch(arALL);
0
Comment
Question by:seancaddell
  • 6
  • 5
11 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9781354
just to reask

in your first q you wanted to import via bde into a table
in your second q you wanted this data transfered into an accesstable via ado

is this correct?

would it not be easier to import the text-file directly into access?

meikl ;-)
0
 

Author Comment

by:seancaddell
ID: 9782855
I am trying to export datafiles from a Unix Application into an Access Database, I want to create the tables dynamically to emulate ODBC.  The file formats are proprietry so there is no compatibility with normal ODBC.  That's why I am taking this route.  Are you suggesting that I could do this using the Access OLE object in Delphi?



0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9785214
a sample

unit ADO_Import_TextFile_u;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, ADODB;

type
  TForm1 = class(TForm)
    Button1: TButton;
    ADOConnection1: TADOConnection;  //for the textfiles
    ADOConnection2: TADOConnection;  //for the mdb for check
    ADOQuery1: TADOQuery;            //imports the text, connected to ADOConnection1
    ADOTable1: TADOTable;            //views the table, connected to ADOConnection2
    DataSource1: TDataSource;        //Connected to ADOTable1
    DBGrid1: TDBGrid;
    Button2: TButton;                //View the table content
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

const  //only for docu, strings inserted at designtime
 connstrText = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test\;Extended Properties="Text;Persist";Persist Security Info=False';
 connstrMDB = 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\Test\db1.mdb;Mode=Share Deny None;Extended Properties="";'+
              'Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;'+
              'Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;'+
              'Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;'+
              'Jet OLEDB:Don''t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False';
 sql = 'SELECT * INTO [Test2] IN "D:\test\db1.mdb" FROM test2.txt';

 (* Sample Data in the text-File

900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak2";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak24";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"

*)


procedure TForm1.Button1Click(Sender: TObject);
begin
  //Import, table will be created, may not exist
  //for exististing table use a 'select into ..'-Statement isntead
  //ATTENTION: The Field-Delimitter must be ;
  ADOConnection1.Open;
  ADOQuery1.ExecSQL;
  ADOConnection1.Close;
end;

//show the data
procedure TForm1.Button2Click(Sender: TObject);
begin
//you cannot open the table directly after
//the import, atleast wait 5 seconds before try
  ADOConnection2.Open;
  ADOTable1.Open;
end;

end.

meikl ;-)
0
 

Author Comment

by:seancaddell
ID: 9786741
A little more explanation is required.

I have copied the code and it creates a table in db1.mbd but the fields are not separated, the data is imported as a single field. Do I need to define the field names and add them first?  If so how?

You mention using a 'select into' sql statement for a table that already exists, can you give me more detail on the command structure for this as it is more likely that I will be using this method.  I am increasing the points on this one as you have been so helpful.

You've been an enormous help assisting the learning process, Thanks

Sean...

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9786907
>but the fields are not separated, the data is imported as a single field.

the fields must be separated with semicolon (;) not with (,)

>Do I need to define the field names and add them first?  If so how?

no, the content of the first line are interpreted as fieldnames,
-> means you can include a fieldnames-line at first
-> see data-sample below

>You mention using a 'select into' sql statement for a table that already >exists, can you give me more detail on the command structure

well, usual it was a typo, i meant 'Insert into ...' for existing tables ;-)

if you use 'Insert into ...', the targettable must exist
and must have the needed structure, a fieldname-line can be also there
-> will be discarded
a sample

procedure TForm1.Button3Click(Sender: TObject);
const
   isql = 'INSERT INTO [Test2] IN "D:\test\db1.mdb" SELECT * FROM test2.txt';
begin
  ADOConnection1.Open;
  ADOQuery1.SQL.Text := isql;
  ADOQuery1.ExecSQL;
  ADOConnection1.Close;
end;

-------
sample data:

f1;f2;f3;f4;f5;f6;f7;f8
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak2";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak24";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
900;3820;24;16;1356.75;3999.95;"Dive kayak";"SMALL DINGY"
-----------

this line
f1;f2;f3;f4;f5;f6;f7;f8
is taken as fieldname-line by the 'Select Into ...'-Statement
and will be discarded by the 'Insert into ...'-Statement

don't hestitate tp ask, if somewhat unclear
and
there is no need to raise points

meikl ;-)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:seancaddell
ID: 9787495


I get the following error message when I use the sample data you have listed.  EOleException with message 'The INSERT INTO statement contains the following unknown field name: 'f1;f2;f3;f4;f5;f6;f7;f8'. Make sure you have typed the name correctly and try again.

If I change the separator to a space the fieldname listed in the error is reduced to 'f1'

It seems like it doesnt like the semicolon(;) as a separator.  Do I need to modify any settings on the components?

Thanks yet again

Sean :¬)





0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9787630
>the following unknown field name

of course the fieldname-line should match
the real-fieldnames you have in the table

in my testtable the fields are named f1....

>It seems like it doesnt like the semicolon(;) as a separator.  
>Do I need to modify any settings on the components?

no, just replace the fieldname-line with your real-fieldnames,
or delete this line from the sample-data, and try again

tell me the result then

meikl ;-)
0
 

Author Comment

by:seancaddell
ID: 9787789
I removed the f1,f2 etc and got the same error with the first line of data.  I then replaced all the semi-colons with commas, replaced the f1,f2 etc and the data was imported correctly.  I am interested to find out why this happened as I dont want to come across it later on in development.

Regards

Sean

:~)
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 500 total points
ID: 9787879
hmm,
thats really mysterious,
guessing its a regional setting problem,
why we differs about ; and ,

where do you come from?

meikl ;-)
0
 

Author Comment

by:seancaddell
ID: 9788057
I am in the UK....

Once again thanks for your help, I wish I'd posted a question here when I started this project, it would have saved me lots of valuable time.  At least I learned a bit about SQL on the way.

Best Regards

Sean
;¬)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9788132
well,
glad to helped you :-)

atleast UK isn't far away from germany,
where i come from, in this case,
there should be not much differences,
well, just wondering

meikl ;-)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now