Solved

Emulating TBatchMove with ADO components

Posted on 2000-05-03
11
927 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
Comment Utility
Adjusted points from 100 to 300
0
 
LVL 4

Expert Comment

by:Radler
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Adjusted points from 300 to 500
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:jwill01
Comment Utility
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
Comment Utility
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
Comment Utility
with my IDE using smart tabs and optimal fill the results is terrible !!!!
0
 

Author Comment

by:jwill01
Comment Utility
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
Comment Utility
Thanks for your time!
0
 
LVL 4

Expert Comment

by:Radler
Comment Utility
OK ! jwill01,

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

T++, Radler.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

15 Experts available now in Live!

Get 1:1 Help Now