Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Emulating TBatchMove with ADO components

Posted on 2000-05-03
11
Medium Priority
?
1,066 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1500 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

971 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