I need to start a new transaction every iteration of the loop, as there are
other posts taking place. I want either all or none to be committed.

Thanks,
Dion.


-----Original Message-----
From: Svein Erling Tysvær
[mailto:svein.erling.tysvaer@...]
Sent: Tuesday, February 05, 2002 4:42 AM
To: ibobjects@yahoogroups.com
Subject: Re: [IBO] Another Simple question

>Is there conceptually anything wrong with my layout. I want the loop to
>continue processing for the duration of the table length. Errors must be
>trapped in the exception part and must be written to a text file. There are
>approx 1000 records, but on the first error, I get 'end of dataset' error.

I

>am reading from a Source table and copying to a Target table(this is only
>part of the bigger app).

Dion,
Are qrySourceMembers connected to IB_Transaction1? If so, this would
certainly explain that you get eof after the first execution.

InTransaction only tests for transactions explicitly started by you - in
general I prefer to check TransactionIsActive which also yields true if IBO
itself started a transaction. And committing for each insertion seems a bit
of an overkill, greatly increasing the execution time of your program.
Below you'll find a modified version of your program written the way I
normally do things (well, I've never used the Assign method - normally
stick to AsInteger, AsString etc. and in loops use Fields[<fieldnr>] rather
than FieldByName).

HTH,
Set

AssignFile(ErrorFile, 'ErrorFile.txt');
ReWrite(ErrorFile);
try
try //Yes, a double try
counter := 0;
qrySourceMembers.First;
while not qrySourceMembers.EOF do
begin
//No need to explicitly start a transaction and certainly not within each
//iteration of the loop
// if not IB_Transaction1.InTransaction then
// IB_Transaction1.StartTransaction;

{ add member to target db }
with qryTargetMembers do
begin
Append;


FieldByName('MEMBERID').Assign(qrySourceMembers.FieldByName('MEMBERID') )
;

FieldByName('TITLEID').Assign(qrySourceMembers.FieldByName('TITLEID') )
;

FieldByName('INITIALS').Assign(qrySourceMembers.FieldByName('INITIALS') )
;

FieldByName('FIRSTNAME').Assign(qrySourceMembers.FieldByName('FIRSTNAME')
) ;

FieldByName('SURNAME').Assign(qrySourceMembers.FieldByName('SURNAME')
) ;
FieldByName('DOB').Assign(qrySourceMembers.FieldByName('DOB')
) ;

qryTargetMembers.Post;



qrysourcemembers.next;
end;
if IB_Transaction1.TransactionIsActive then
IB_Transaction1.Commit;
except on E:Exception do
if IB_Transaction1.TransactionIsActive then
IB_Transaction1.RollBack;

end;
finally
CloseFile(ErrorFile);
end;




___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.314 / Virus Database: 175 - Release Date: 01/11/2002

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.314 / Virus Database: 175 - Release Date: 01/11/2002