Solved

Emulating TBatchMove with ADO components

Posted on 2000-05-03
11
940 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

24 Experts available now in Live!

Get 1:1 Help Now