Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1085
  • Last Modified:

Emulating TBatchMove with ADO components

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
jwill01
Asked:
jwill01
  • 6
  • 5
1 Solution
 
jwill01Author Commented:
Adjusted points from 100 to 300
0
 
RadlerCommented:
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
 
jwill01Author Commented:
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
Technology Partners: 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!

 
RadlerCommented:
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
 
jwill01Author Commented:
Adjusted points from 300 to 500
0
 
jwill01Author Commented:
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
 
RadlerCommented:
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
 
RadlerCommented:
with my IDE using smart tabs and optimal fill the results is terrible !!!!
0
 
jwill01Author Commented:
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
 
jwill01Author Commented:
Thanks for your time!
0
 
RadlerCommented:
OK ! jwill01,

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

T++, Radler.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now