Solved

Emulating TBatchMove with ADO components

Posted on 2000-05-03
11
1,002 Views
Last Modified: 2013-11-23
Hello,

I'm looking for a way to emulate BDE's TBatchMove ability to copy records between two different databases (eg: Oracle to Access) using ADO components.  The only method I've seen so far is:

1.  Use "Create table" SQL to manually create the destination table structure
    (TBatchMove does this automatically)

2.  Iterate thru a TADOQuery recordset and [Append..Edit..Post] each record to a TADOTable
    (v-e-r-y slow)

Would anyone have an example of something like "Select ... into DestTblName" that can operate on an entire set of result records into an implied table structure?  I can do
this for source and destination tables in the same database, but not between databases.

Thanks for any guidance!
0
Comment
Question by:jwill01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 

Author Comment

by:jwill01
ID: 2780389
Adjusted points from 100 to 300
0
 
LVL 4

Expert Comment

by:Radler
ID: 2782999
Hi jwill01,

Without D5 is a bit hard help, but is TBatchMove.Destination( A TTable class ) a ancestor of the ADO tables ?

T++, Radler.
0
 

Author Comment

by:jwill01
ID: 2785705
Hello, Radler --

Thanks for the reply!  TADOTable and TTable both derive from TDataSet as follows:

TADOTable...TCustomADODataset...TDataSet

TTable...TDBDataset...TBDEDataset...TDataSet

TTable uses the BDE where TADOTable uses ADO instead.  That's why I could really use TBatchMove equivalent that has a Destination of class TADOTable (not requiring the BDE).

Jim
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:Radler
ID: 2789475
Hi  jwill01,

Using this way really is too slow. If exists someyhing like TADOQuery try it. Tomorrow I'll see the next version to more help.

T++, Radler.
0
 

Author Comment

by:jwill01
ID: 2800854
Adjusted points from 300 to 500
0
 

Author Comment

by:jwill01
ID: 2810588
Radler --

Here's code that uses TADOQuery and TADOTable, works, but is very slow.  I'm trying to find a method like TBatchMove must be using to operate on a set of records as one chunk, without having to iterate thru each record.  Thanks!

JWill01

---------------------------------


type
  TADOMod = class(TDataModule)
    ADOCon: TADOConnection;
    DataSrc: TDataSource;
    ADOTbl: TADOTable;
    ADOQry: TADOQuery;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  ADOMod: TADOMod;


function AppendTableQuery (SQLText, DestUDLFileName, DestTbl: string): longint;

{ Run an append query for a pre-existing destination table. }

var
  In1        : System.Text;
  ConnectStr : string;
  Fld        : integer;
  RecArray   : array of variant;

begin
  Result := 0;
  try
    with ADOMod do begin
      ADOQry.Close;
      ADOQry.SQL.Clear;
      ADOQry.SQL.Add (SQLText);
      ADOQry.Open;
      ADOQry.First;
      if not ADOQry.Eof then begin
        System.AssignFile (In1, DestUDLFileName);
        System.Reset (In1);
        Readln (In1, ConnectStr);
        System.CloseFile (In1);
        ADOTbl.ConnectionString := ConnectStr;
        ADOTbl.TableName := DestTbl;
        ADOTbl.Open;
        SetLength (RecArray, ADOQry.FieldCount);
        while not ADOQry.Eof do begin
          ADOTbl.Append;
          for Fld := 0 to ADOQry.FieldCount-1 do begin
            RecArray[Fld] := ADOQry.Fields[Fld].AsVariant;
            ADOTbl.Edit;
            ADOTbl.Fields.Fields[Fld].AsVariant := RecArray[Fld];
          end;
          ADOTbl.Post;
          ADOQry.Next;
        end;  {while}
        ADOTbl.Close;
      end;  {if}
      ADOQry.Close;
    end;  {with}
  except
    Result := 1;
  end;  {try}
  AppendTableQuery := Result;
  if Result > 0 then
    MessageDlg ('AppendTableQuery failed! (' + DestTbl + ')',
                mtError, [mbOK], 0);
end;  
0
 
LVL 4

Accepted Solution

by:
Radler earned 500 total points
ID: 2821647
I think that the solution can be gained together.

I done some modifications test it please.

function AppendTableQuery (SQLText, DestUDLFileName, DestTbl: string): longint;
{ Run an append query for a pre-existing destination table. }
var
  In1        : System.Text;
  ConnectStr : string;
  Fld        : integer;
  //RecArray   : array of variant; //Passed directly
  FCount : integer;
begin
      Result := 0;
      try
            with ADOMod do begin
                  ADOQry.Close;
                  ADOQry.SQL.Clear;
                  ADOQry.SQL.Add (SQLText);
                  ADOQry.Open;
                  ADOQry.First;
                  if not ADOQry.Eof then begin
                        FCount:=ADOQry.FieldCount; //Pre-calcs to optmize
                        System.AssignFile (In1, DestUDLFileName);
                        System.Reset (In1);
                        Readln (In1, ConnectStr);
                        System.CloseFile (In1);
                        ADOTbl.ConnectionString := ConnectStr;
                        ADOTbl.TableName := DestTbl;
                        ADOTbl.Open;
                        //SetLength (RecArray, FieldCount);
                        while not ADOQry.Eof do begin
                              ADOTbl.Append;
                              for Fld := 0 to FCount-1 do begin
                                    {An array ??? why ? }
                                    //RecArray[Fld] := ADOQry.Fields[Fld].AsVariant;
                                    //ADOTbl.Edit; Append already at Edit mode
                                    //ADOTbl.Fields.Fields[Fld].AsVariant := RecArray[Fld];
                                    ADOTbl.Fields.Fields[Fld].AsVariant := ADOQry.Fields[Fld].AsVariant
                              end;
                              ADOTbl.Post;
                              ADOQry.Next;
                        end;  {while}
                        ADOTbl.Close;
                  end;  {if}
                  ADOQry.Close;
            end;  {with}
      except
            Result := 1;
      end;  {try}
      AppendTableQuery := Result;
      if Result > 0 then begin
            MessageDlg ('AppendTableQuery failed! (' + DestTbl + ')', mtError, [mbOK], 0);
      end;
end;

See the identation mode, it's avoid markups like "{ while }", that I done at the past too.

T++, Radler
0
 
LVL 4

Expert Comment

by:Radler
ID: 2821656
with my IDE using smart tabs and optimal fill the results is terrible !!!!
0
 

Author Comment

by:jwill01
ID: 2823655
Radler --

Thanks for the ideas.  I will use this method for now, and keep my eye out for other "batch" methods.

I'll award 500 points for your help.

Thanks!
Jim Williams
jwill01
0
 

Author Comment

by:jwill01
ID: 2823659
Thanks for your time!
0
 
LVL 4

Expert Comment

by:Radler
ID: 2823753
OK ! jwill01,

I hope that the another hint about my code identation mode, help you too.

T++, Radler.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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