Hi Grant,

There is no such thing as the correct time to commit under all
circumstances. You just need to understand the impact of committing
or not committing.

If you commit during your FOR loop, and you receive an exception,
your transaction will rollback, however if you are committing each
procedure call, only the last call will be rolled back. If one of the
records has a PerArray[T].FPrint that can not be cast as an integer,
do you really want ALL of the inserts to be rolled back?

Your call to prepare should NOT happen inside the FOR loop.
Secondly, consider using ParamByName for your own sanity in 6 months
time :)
Also, unless I am going nuts, you probably want to stop at High
(PerArray)-1

What is correct is actually up to your business requirements.

Adam


--- In firebird-support@yahoogroups.com, Grant Brown <grant@s...>
wrote:

> Good morning to all,
>
> With the following execute procedure SQL statement when should I

call

> the commit transaction.
>
> During each loop or after the "for" statement has completed ?
>
> I am assuming the correct layout is as below but could some one

confirm

> it for me.
>
> try
> InsertPermissions.Transaction.StartTransaction;
> for T := 0 to High(PerArray) do
> begin
> InsertPermissions.Prepare;
> InsertPermissions.Params[0].asInteger := ID;
> InsertPermissions.Params[1].asInteger := Integer(PerArray

[T].FAccess);

> InsertPermissions.Params[2].asInteger := Integer(PerArray

[T].FNew);

> InsertPermissions.Params[3].asInteger := Integer(PerArray

[T].FDelete);

> InsertPermissions.Params[4].asInteger := Integer(PerArray

[T].FPrint);

> InsertPermissions.Params[5].asInteger := Integer(PerArray

[T].FEdit);

> InsertPermissions.Params[6].asInteger := T;
> InsertPermissions.ExecProc;
> end;
> InsertPermissions.Transaction.Commit;
> except
> InsertPermissions.Transaction.Rollback;
> end;
>
> --
> Regards,
> Grant Brown
>
> Product Development Manager
> Phone : 02 4229 1185
> Mobile : 0412 926 995
> Email : grant@s...
> Web : www.sitedoc.com.au
>
> SiteDoc - Easy to Use - Powerful Results