In my case the scenario would be a prepared stored procedure that is executed repeatedly from client-side or webserver code (after starting a transaction) but the logic is identical

> If you handle the exception inside your PSQL code (even if you do
> nothing in the WHEN block, then the transaction won't be rolled back.

This is the clarification I was after.

Thank you Thomas.

--- In firebird-support@yahoogroups.com, Thomas Steinmaurer <ts@...> wrote:
>
> Hi!
>
> > Can someone clarify the following please:
> >
> > If you are about to post a batch of 1000+ inserts and you are pretty sure that 1 or more may fail what is the most efficient way of doing this? i.e. I'm interested in the case where you don't care whether all the inserts succeeds *as long as the ones that can do*.
> >
> > Normally the entire batch must succeed or else the transaction has to be rolled back. But is it true that if the inserts are executed within a stored procedure and the sp catches (and ignores the error) then you will get very fast inserts via one transaction rather than the much slower method of wrapping each insert in its own transaction?
> >
> > If not, is there another way to do this in one transaction?
>
> If you handle the exception inside your PSQL code (even if you do
> nothing in the WHEN block, then the transaction won't be rolled back. An
> example:
>
> SET TERM ^^ ;
> CREATE PROCEDURE P_T1_EXCEPTION_HANDLING
> AS
> declare i Integer;
> begin
> delete from t1;
>
> i = 0;
> while (i < 10) do
> begin
> i = i + 1;
> insert into t1 (id) values (:i);
> if (i = 1) then
> begin
> insert into t1 (id) values (:i);
> end
> when any do
> begin
> end
> end
> end ^^
> SET TERM ; ^^
>
>
> The stored procedure will insert 10 records, although for i = 1 the
> insert into will be executed again, ending in a primary key constraint
> violation. Without the WHEN ANY block, the transaction will be rolled
> back, thus no records will be inserted at all.
>
> If you are using Firebird 2.5, then you could use the new autonomous
> transaction feature in PSQL, which basically allows you to run PSQL code
> in its own transaction context, without affecting the client transaction.
>
> If something fails due to primary key / unique constraints violations,
> for example when inserting duplicate primary key values, you could also
> check out the UPDATE OR INSERT INTO statement or the MERGE statement.
> Available since, AFAIR, Firebird 2.1.
>
>
> --
> Best Regards,
> Thomas Steinmaurer
> LogManager Series - Logging/Auditing Suites supporting
> InterBase, Firebird, Advantage Database, MS SQL Server and
> NexusDB V2
> Upscene Productions
> http://www.upscene.com
> My blog:
> http://blog.upscene.com/thomas/
>