Heh. 10046. That must of been my subconcious there :)

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

You are correct. When I ran my tests on the WHEN ANY DO, any exceptions
raised within it would be visible to the caller and would have to be handled
there, hence IB Workbench would pop up the dialog box asking me what to do
with it. Also the DROP_FILE table has no PK or constraints on it. So an
exceptions inside the WHEN ANY DO are transliteration errors or values that
are too big. I have largely solved this problem and the transliteration
errors are few, but once again visible.

I have tested this extensively before to show that it is a good method to
accomplish our goals. For 2 reasons. The WHEN ANY DO causes SERVER side
exception handling and DROP_FILE creation. If it is commented out, or
alternate procedures created without it, then all the exceptions raised
within the SP deliberately or otherwise are handled APP side. We use SERVER
side mostly since we have had the need to query the DROP_FILE on more than
one occasion.

>>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?

No. No distinct pattern here. Randomly distributed throughout the script
files.

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

I am pretty sure MS Access supports DISTINCT. I have made sure that the
script lines are unique though.

However, duplicates will not cause me any problems. In fact I am counting
on them. The stored procedure itself looks for many different errors and
throws the exceptions itself. The WHEN ANY DO catches the exception and
then performs an INSERT on the DROP_FILE. The record itself will have a
descriptor "DUPLICATE".

This particular case I made sure they were unique. I don't always. This
was to save time since it was so large a batch. Usually I allow duplicate
lines in the script since they represent duplicates being presented to us.
The SP will catch the fact it is a duplicate and then throw the proper
exception which gets represented in the DROP_FILE. I actually check what
kind of duplicate it is. I can tell if it a duplicate of data in database
already, duplicates of data being imported in this particular batch, or
duplicates of data with the same source.

That's the wierd thing here. All the exceptions are accounted for and
visible. Exceptions outside the WHEN ANY DO get a global set and then
processed, Exceptions inside the WHEN ANY DO cause a dialog box to pop up
and pause the script. If it is an exception, then it silent for some
reason. How can an exception be silent?






-----Original Message-----
From: Adam [mailto:s3057043@...]
Sent: Tuesday, February 15, 2005 3:43 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Absolute Mystery




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

Yahoo! Groups Links