Hi Edwin,

It looks like your methodology is pretty good. The only thing I might
clarify though is that the when any do .... code does not catch
exceptions that occur within it.

Simplifying the example just a bit further, you have something like
this:

begin
insert into tablea (id, name) values (5, 'Adam');

when any do
begin
insert into problema (id, name) values (5, 'Adam');
end
end
^

Now if the first insert excepts, it will be handled by the when any
do clause. However if the second insert excepts, this will not be
handled.

One experiment you could do is to create a temporary generator, which
might look something like this.

declare variable tmp integer;
begin
insert into tablea (id, name) values (5, 'Adam');

when any do
begin
tmp = gen_id (tablea_errors,1);
insert into problema (id, name) values (5, 'Adam');
end
end
^

Now when you run the procedure, you would expect the same number of
records in problema as is the current value of tablea_errors. If
there is a discrepancy, then the second insert must have failed
somewhere.

I would also pay careful attention to line 10046 (wow that looks like
a winsock error message :p) Is there something about this line. Is it
the same line every attempt?

Possibly also do a count of the distinct rows from access (if that
can support distinct count(*) syntax yet), as you know duplicates
will cause primary key violations in firebird.

Adam




--- In firebird-support@yahoogroups.com, "Edwin A. Epstein, III"
<eepstein@c...> wrote:

> Good Questions :)
>
> >>The only gotcha I can think of at the moment, is maybe one (or

many)

> >>of the transactions is still uncommitted, or your transaction you

are

> >>running select count(*) from was started before all of the insert
> >>transactions were committed.
>
> All of the lines are run inside a single transaction. A single SP

will by

> itself perform many count(*)'s and then perform an insert on either

TABLE_A

> or DROP_FILE. It is all performed serial, so there is no problem

here.

> Also, I don't see how it would make a record not processed at all.

This

> would just create more drops than necessary, etc.
>
> >>What happens when this code raises an exception (duplicate key
> >>violation or similar)? Does your application raise this, or does

the

> >>script stop on these errors?
>
> For this situation, there is no "app" side. I am using IB

Workbench to

> process the script. I could just as easily use the SQL utility as

well.

> The WHEN ANY DO code in the SP catches all server side exceptions

and then

> performs an INSERT into the DROP_FILE table. This does not stop

the SP or

> any subsequent calls to the SP. You would need to raise an

additional

> exception within the WHEN ANY DO code to do that. So all

exceptions for any

> reason are caught and do not stop processing.
>
> >>How have you calculated the original script query count? Are you
> >>possibly including blank lines in this count?
>
> Yep. Used MS Access to load up the script file and got myself a

record

> count where the field was not null. Any blank lines would of been

accounted

> for.
>
> >>Is it possible for the input to have too many characters to fit

into

> >>the input parameter of the stored procedure?
>
> Now this has occasionally happened to me. In this case however the

script

> stops processing and I receive a dialog box asking if I want to

ignore or

> abort. If I abort, the whole script is aborted and the transaction

is rolled

> back, if I ignore it then processing continues as normal and that

particular

> execution of the SP never occured as far as the database is

concerned. I

> have since stopped this from happening completely by creating the

script

> with functions that select only the first X amount of characters

from the

> input string. The only situation unresolved is transliteration

errors which

> is completely my fault since I left the field with NONE for the

character

> set. In any case, these errors are few, highly visible, and

accounted for.

>
> >>Perhaps you could try and locate the last record you attempted to
> >>insert from each script, and see whether there is any script that

did

> >>not totally complete? When it is inserting hundreds of records per
> >>second, you have to be watching closely to see a problem.
>
> Heh. I performed this very same check. Now here is the mystery....
>
> SCRIPT LINE 10045 'EXECUTED'
> SCRIPT LINE 10046 'MISSED'
> SCRIPT LINE 10047 'EXECUTED'
> SCRIPT LINE 10048 'EXECUTED'
>
> The missed lines are distributed randomly throughout the script

files. No

> clear pattern or indication of why. Line 10045 executed with no

errors, no

> malformed parameters, no transliteration errors, nothing. Why Line

10046

> just did not execute is beyond me. Line 10047 executed just fine,

so

> clearly 10046 did not stop the whole process, and COMMIT has

nothing to do

> with it, since if it did ALL 4 lines would not be present in either

TABLE_A

> or DROP_FILE. 10045,10047, and 10048 are.
>
> Once again, I am stumped :)
>