--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 11:33 PM 29/10/2007, you wrote:
> >>
> >> Show us the SQL for the SrcDataset and DstStatement properties

and

> >also how you are handling the DstLinks and any event handling...
> >>
> >> Helen
> >>
> >
> >I have the following components:
> >
> >SrcDatabase: TIB_Database;
> >DestDatabase: TIB_Database;
> >SrcCursor: TIB_Cursor;
> >// DestDSQL: TIB_DSQL; <--- remove
> >DataPump: TIB_DataPump;
>
> Add a TIB_Transaction - you need this operation to happen in a

single cross-database transaction.

>
>
> >SrcCursor.IBConnection is set to SrcDataBase
> >DestDSQL.IBConnection is set to DestDataBase
>
> Need IB_Transaction of both connections to be your explicit

transaction. Make certain that it is in tiConcurrency isolation and
has AutoCommit False.

>
>
> >DataPump.SrcDataSet is set to SrcCursor
> >DataPump.DstStatement is set to DestDSQL
>
> No, place the statement directly into DstStatement. The

IB_Datapump creates the TIB_Statement internally.

>
>
> >SrcCursor SQL added
> >SELECT DATETIME, SMSSOURCE,SMSNAME, SMSADDRESS, SMSID,
> >SMSREP,SMSDEST, CELLNO, CELLMSG FROM OUTMESSAGES WHERE DATETIME
> >>= :FROMDATE AND DATETIME <= :TODATE
>
> OK
>
>
> >DestDSQL SQL added
>
> Place this statement directly into DstStatement.
>
> >INSERT INTO OUTMESSAGES (DATETIME, SMSSOURCE, SMSNAME,

SMSADDRESS,

> >SMSID, SMSREP,SMSDEST, CELLNO, CELLMSG, IPADDRESS) VALUES
> >

(:DATETIME, :SMSSOURCE, :SMSNAME, :SMSADDRESS, :SMSID, :SMSREP, :SMSD
EST, :CELLNO, :CELLMSG, :IPADDRESS)

> >
> >The only event handling is the DataPump.OnError event that I just
> >log to a file.
>
> Show it to us.
>
> >I have nothing in DstLinks
>
> This is a stringlist for mapping the output fields of the

SrcDataset to the input parameters of the DstStatement. You have
almost enough here for the component to do this mapping
automatically, i.e. parameter names matching output field names and
in the same order. But you have an extra parameter in the insert
statement (:IPADDRESS). How do you get that into there?

>
> You don't say anything about the processing you do when the user

hits the "Pump" button. You will need to:

>
> 1. Connect to the two databases.
> 2. Start the explicit transaction.
> 3. Prepare the source statement (if not ... Prepared then Prepare)
> 4. Prepare the destination statement (likewise)
> 5. Call the Execute method of the datapump.
> 6. When it's finished, commit the explicit transaction.
>
> Helen
>

Made some changes now as you suggested:

My components looks like this:

SrcDatabase: TIB_Database;
DestDatabase: TIB_Database;
SrcCursor: TIB_Cursor;
DataPump: TIB_DataPump;
DBTransaction: TIB_Transaction;

SrcDatabase.isolation:= tiConcurrency
DestDatabase.isolation:= tiConcurrency
DBTransaction.IB_Connection:= SrcDatabase
DBTransaction.IB_Connection1:= DestDatabase
DBTransaction.isolation:= tiConcurrency
DBTransaction.autocommit:= false
SrcCursor.IB_Transaction:= DBTransaction
Datapump.SrcDataset:= SrcCursor

There are 4 different tables in the database.
The source sql and destination sql looks more or less the same all 4
times. Something
like this:

SourceSQL:= SELECT DATETIME, SMSID, CELLNO, SMSRETCODE,
SMSRETMESSAGE,SMSSTATUS, SMSCID FROM SECONDSTATUS WHERE DATETIME

>= :FROMDATE AND DATETIME <= :TODATE

DestinationSQL:= INSERT INTO SECONDSTATUS (DATETIME, SMSID, CELLNO,
SMSRETCODE, SMSRETMESSAGE,SMSSTATUS, SMSCID, IPADDRESS) VALUES
(:DATETIME, :SMSID, :CELLNO, :SMSRETCODE, :SMSRETMESSAGE,:SMSSTATUS,
:SMSCID, ''' + '127.0.0.1' + ''')

This code execute 4 times for the 4 different tables in the
databases:
(For some reason the Datapump.DstStatement does not get created as
you said, I had to create it myself?)

with SrcDatabase do
begin
Username := AccountUsernameEdit.Text;
Password := AccountPasswordEdit.Text;
Path := AccountDBPathEdit.Text;
Server := '127.0.0.1';
end;

with DestDatabase do
begin
Username := AccountStatsUsernameEdit.Text;
Password := AccountStatsPasswordEdit.Text;
Path := AccountStatsDBPathEdit.Text;
Server := '127.0.0.1';
end;

SrcCursor.SQL.Clear;
SrcCursor.SQL.Add(SourceSQL);
SrcCursor.ParamByName('FROMDATE').AsDate:=BeginDateTimePicker.Date;
SrcCursor.ParamByName('TODATE').AsDate := EndDateTimePicker.Date;
Datapump.DstStatement:= TIB_Statement.Create(nil);
Datapump.DstStatement.SQL.Add(DestinationSQL);

SrcDatabase.Connect;
DestDatabase.Connect;

try
if not DBTransaction.Started then
DBTransaction.StartTransaction;

if not SrcCursor.Prepared then
SrcCursor.Prepare;

if not Datapump.DstStatement.Prepared then
Datapump.DstStatement.Prepare;

try
DataPump.Execute;
except
on E: Exception do
AddToLog(EVENTLOG_ERROR_TYPE, 'Executing failed: ' +
e.Message);
end;
failed := DataPump.ExecutingFailed;

if (not failed) then
begin
DBTransaction.Commit;
DataPump.Unprepare;
end
else
begin
DBTransaction.Rollback;
end;
finally
try
SrcDatabase.Disconnect;
DestDatabase.Disconnect;
except
end;
end;


My only event:

procedure TAccountDataPumpForm.DataPumpError(Sender: TObject;
const ERRCODE: Integer; ErrorMessage, ErrorCodes: TStringList;
const SQLCODE: Integer; SQLMessage, SQL: TStringList;
var RaiseException: Boolean);
begin
AddToLog(EVENTLOG_ERROR_TYPE, 'Data pump error occured ' + IntToStr
(ERRCODE) + ' - ' + ErrorMessage.CommaText);
end;

My results are still the same. Some pc's run this perfectly in 5
minutes and others looks like hanging most of the time. Also
it appears to be faster after a reboot. Maybe some memory leaking?

Thanks, appreciate all the help!
E