Helen, I considered placing the commit outside of the loop, but because of
my limited understanding of what the server does in the background, I
thought that the transaction would consume plenty resources if this process
was run over a couple of hundred thousand records.
The way I understand it, is that the server will log all changes at
transaction start, and release these resources when the transaction is
committed or rolled back. It made sense to see the member and all his
dependents as a unit of work needed to be handled together, not all members
collectively.

Thanks,
Dion.


-----Original Message-----
From: Helen Borrie (TeamIBO) [mailto:helebor@...]
Sent: Tuesday, February 05, 2002 3:34 AM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Another Simple question


At 11:00 AM 05-02-02 -0800, you wrote:

>Hi,
>
>Is there conceptually anything wrong with my layout. I want the loop to
>continue processing for the duration of the table length.

It won't if you roll or commit the transaction back partway through the
loop - that closes the cursors. A dataset won't get revived until you open
it again and then it will just revert to the beginning again.

I take it that what you want is to log any error records and then pass on
to the next record in the set?

>Errors must be
>trapped in the exception part and must be written to a text file.

Don't write them to a text file until after the processing is finished.
Write them to a TStringlist and then write that out to a text file if it
contains anything.

>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).

...
var
Badlist: TStringlist;
ErrFile: TFileName;
...
counter := 0; //<--- doesn't seem to do anything
ErrFile := 'ErrorFile.txt';

> if IB_Transaction1.InTransaction then
> IB_Transaction1.Rollback;
> IB_Transaction1.StartTransaction;

Badlist := TStringList.Create;
TRY

> qrySourceMembers.First; // assuming this is a tib_cursor
> while not qrySourceMembers.EOF do
> begin
> { add member to target db }
> with qryTargetMembers do
> begin

if State <> dssInsert then

> 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') )

;

> try
> Post;
> except
> on E:Exception do

Badlist.Add('the string you want to log')
end;
end;

> qrySourceMembers.Next;
> end;
> if IB_Transaction1.InTransaction then

try

> IB_Transaction1.Commit;

except
on E:EIB_ISCError do
begin
IB_Transaction1.RollBack;
BadList.Add(E:Message); // saves the ISC error message
MessageDlg('Could not commit dataset: operation cancelled',
mtError,[mbOK],0);
end;
end;
if BadList.Count > 0 then
BadList.SaveToFile(errfile);
FINALLY
BadList.Free;
END;

Sorry, not tested, but you get the idea...

Still, I suspect this is not the end of the story, since you are writing
rows to another dataset. Presumably, therefore, you want the user to do
something with the new rows. Depending on the dataset, you might not see
those rows (wherever you expect them to be) when you refresh datasets after
the commit.

If the new rows are not for immediate use in the GUI, then don't write them
this way. Further, if the source rows are being brought into the ib_cursor
purely for the purpose of providing data for insertion into another table,
don't do the source part this way either. Have the database do this work
and make your GUI a parameter selector for an ib_dsql that has SQL like

insert into target_table (<list of target columns>)
(select <matching list of source columns>
from source_table where aCol = :aCol and.... )

regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com



___________________________________________________________________________
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