Dear Neven,
Thank you for your reply.
> OK then for a One Off I'd not even attempt to do it programmatically
I am sorry that I have not made it clear. One off means that user do copy
procedure, not developer, and this application is a product offer to our
customers.
By the way, I tried dts, due to different column type in different database,
actually that DTS failed on lots of tables, i.e. Blob field in Oracle is
called LONG RAW, in SQL Server called image, in interbase called Blob.
And I suspect that self reference table also cause the problem. In DTS, that
error message just so brief, I can not get the real cause for failing.
And I also noticed that DTS use VBScript engine to do data import/export, I
suspect that it would be very slow, row by row, column by column, all are
interpreted. So even DTS works correctly, but compare to native compile
code, it should not be a good candiate.
Best Regards
Leigh
----- Original Message -----
From: "Neven MacEwan" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Friday, November 03, 2000 11:51 AM
Subject: Re: [DUG]: Best way to copy data from one database to another
database using ADO
> Leigh
>
> OK then for a One Off I'd not even attempt to do it programmatically
>
> For MS SQL the process would be as follows
>
> 1/ Create tables in target db with limited constraint (no RI self ref or
> other)
> 2/ Use dts to tx data in
> 3/ script fix ups
> 4/ establish constraints
>
> The only process you could poss program in ADO would be exporting data
from
> existing DB
> (to some low level common format) but you would have examine the bulk load
> method that
> each target server supported to find a common (or simmilar) format (prob
end
> up being text)
>
> HTH
>
> Neven
>
> ----- Original Message -----
> From: Leigh Wanstead <[EMAIL PROTECTED]>
> To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
> Sent: Friday, 3 November 2000 10:15
> Subject: Re: [DUG]: Best way to copy data from one database to another
> database using ADO
>
>
> > Dear Neven,
> >
> > > 1/ Is this a one off or a synch process
> > One off process
> >
> > > 2/ Do you want it to be generic i.e from any of (Interbase, MS SQL
7.0,
> > > Oracle) to any of (Interbase, MS SQL 7.0, Oracle)
> >
> > Currently Source Interbase, Destination any of (Interbase, MS SQL 7.0,
> > Oracle)
> >
> > > 3/ Are you over-writing or appending
> >
> > over-writing
> >
> > Best Regards
> > Leigh
> >
> > ----- Original Message -----
> > From: "Neven MacEwan" <[EMAIL PROTECTED]>
> > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
> > Sent: Friday, November 03, 2000 9:57 AM
> > Subject: Re: [DUG]: Best way to copy data from one database to another
> > database using ADO
> >
> >
> > > Leigh
> > >
> > > Q
> > >
> > > 1/ Is this a one off or a synch process
> > > 2/ Do you want it to be generic i.e from any of (Interbase, MS SQL
7.0,
> > > Oracle) to any of (Interbase, MS SQL 7.0, Oracle)
> > > 3/ Are you over-writing or appending
> > >
> > > ----- Original Message -----
> > > From: Leigh Wanstead <[EMAIL PROTECTED]>
> > > To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
> > > Sent: Friday, 3 November 2000 09:49
> > > Subject: [DUG]: Best way to copy data from one database to another
> > database
> > > using ADO
> > >
> > >
> > > > Hello everyone,
> > > >
> > > > What is the best way to copy data from one database to another
> database
> > > > using ADO?
> > > >
> > > > Database backend could be Interbase, MS SQL 7.0 or Oracle.
> > > >
> > > > I use the following code from book Mastering Delphi 5 P572 from
Marco
> > > Cantu.
> > > >
> > > > procedure TForm1.Button1Click(Sender: TObject);
> > > > var
> > > > I: Integer;
> > > > begin
> > > > ADOConnectionSource.Connected := True;
> > > > ADOConnectionDestination.Connected := True;
> > > >
> > > > ADOTableSource.TableName := 'WhatEver';
> > > > ADOTableDestination.TableName := 'WhatEver';
> > > >
> > > > ADOTableSource.Open;
> > > > ADOTableDestination.Open;
> > > > try
> > > > while not ADOTableSource.Eof do
> > > > begin
> > > > ADOTableDestination.Insert;
> > > > for I := 0 to ADOTableSource.Fields.Count - 1 do
> > > > begin
> > > >
> > > >
> >
ADOTableDestination.FieldByName(ADOTableSource.Fields[I].FieldName).Value
> > > :=
> > > > ADOTableSource.Fields[I].Value;
> > > > end;
> > > > ADOTableSource.Next;
> > > > ADOTableDestination.Post;
> > > > end;
> > > > finally
> > > > ADOTableSource.Close;
> > > > ADOTableDestination.Close;
> > > > ADOConnectionSource.Connected := False;
> > > > ADOConnectionDestination.Connected := False;
> > > > end;
> > > > MessageDlg('finished', mtConfirmation, [mbOK], 0);
> > > > end;
> > > >
> > > > But I was not happy about the code, because in Client/Server
concept,
> I
> > > > should not use Table component. I heard that DTS from SQL 7.0 can
> > actually
> > > > migrate data from one OLE/DB provider to another. But the license
for
> > that
> > > > without SQL 7.0 is uncertainty.
> > > >
> > > > And I am not sure how to handle self reference table.
> > > >
> > > > ADOExpress is an extra layer on top of ADO, which might slow the
> speed.
> > > > By the way, I am talking about around 1G Interbase file to convert
to
> > > other
> > > > data source. So stability and speed are in consideration.
> > > >
> > > > I left BDE solution is because memory hold by SQL links for copy one
> > table
> > > > contain 1 million records which potentialy can grow to 700M virtual
> > memory
> > > > eventually run out of memory.
> > > >
> > > > Any ideas?
> > > > Thanks in advance
> > > >
> > > >
> > > > Best Regards
> > > > Leigh Wanstead
> > > > Software Engineer
> > > > SoftTech New Zealand
> > > >
> > > >
> > >
> >
>
> --------------------------------------------------------------------------
> > > -
> > > > New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> > > > Website: http://www.delphi.org.nz
> > > > To UnSub, send email to: [EMAIL PROTECTED]
> > > > with body of "unsubscribe delphi"
> > > >
> > >
> >
>
> --------------------------------------------------------------------------
> > -
> > > New Zealand Delphi Users group - Delphi List -
[EMAIL PROTECTED]
> > > Website: http://www.delphi.org.nz
> > > To UnSub, send email to: [EMAIL PROTECTED]
> > > with body of "unsubscribe delphi"
> > >
> > >
> >
> >
>
> --------------------------------------------------------------------------
> -
> > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> > Website: http://www.delphi.org.nz
> > To UnSub, send email to: [EMAIL PROTECTED]
> > with body of "unsubscribe delphi"
> >
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
>
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
