>> It can't be a "silent exception". You have this covered in your logic.

That is why I am stumped

>>What is the source of what you refer to as "script lines"? It it an
>>external table?

I receive rather large files of delimited data on a regular basis. I use MS
Access most of the time to load it up and basically create the SQL procedure
statments. Afterwards, i run the script through IB Workbench or the SQL
utility. It parses and runs every SQL statement one by one.

EXECUTE PROCEDURE PROC_A('214125','24525','23452345','2435245');

I refer to the above SQL statment as a "script line". MS Access creates it
as a fixed width file with CRLF's after 100 characters. I know MS Access
has its limitations, but it has never screwed up on me in creating the
files. However, the passed data could be causing it as well. I have run
into exactly the situation you describe in the past and have corrected for
it.

The SQL statement is formed by "EXECUTE PROCEDURE PROC_A('" &
CleanUP(Variable_1) & "','" & CleanUp(Variable_2) & "');". The CleanUP()
function will perform a UCASE() and then strip out all characters that are
not between 0-9 and A-Z. I have been using such a method for a long time
with great success. I am very confident that the scripts are being
constructed correctly with no malformed delmiters, placeholders, or CLRF's.
Confidence aside though, I have checked the script files themselves and
there is no wierd illegally formed SQL statements that would cause multiple
records to be recorded as one. Even if there were such statments, I have
observed that IB Workbench will stop and say that it does not understand
some token. Basically it cannot parse the script any longer and find valid
SQL statments to execute.


The other problem with that possible cause, is the observation about how a
single instance can be missing inbetween 2 successfully executed statements

SQL Statment A;
SQL Statment B;
SQL Statment C;

"A" and "C" executed properly and its record was created correctly with no
malformed data. "B" is just missing. If it was caused by a malformed
script line, then the "A" would be in DROP_FILE and the data would be
clearly malformed. FIELD_1 would look something like "','2842923498');
EXECUTE PROCEDURE PROC_A('13241234". I cannot find any instances of this in
the database.

There is also extensive error checking on the data being passed into the
fields. It has to fall within a tightly defined range of data types, like
phone number, zip code, first name, extended zip code, etc. So malformed
records would automatically be in the DROP_FILE table since exceptions would
almost certainly be thrown.

More Mystery ........

I have been able to export all succesfully processed SQL statements and then
compared them against the scripts. I now have a file of 550K SQL statements
that did not execute for some reason. Now here is by far the wierdest part
of this trip. There is no malformed data and the same process I use to
create the SQL statements shows perfectly formed statements in the entire
file. I have picked several statements at random and they execute against
the database with no problem at all.

I just don't know. Unfortunately, that statement is not satisfying my
supervisor right now.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Tuesday, February 15, 2005 6:28 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Absolute Mystery



At 04:02 PM 15/02/2005 -0800, you wrote:

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

It can't be a "silent exception". You have this covered in your logic.

What is the source of what you refer to as "script lines"? It it an
external table?

If so, I would be considering that the discrepancy is due to some lines not
having record delimiters. This error would be trapped by your WHEN ANY
block as "one very long, illegal record" and would be written away as one
log entry - even though, according to your perception of the source data,
it involved "several lines".

Even if you are not dependent on e.g. CRLF as a record delimiter, but on
placeholders, a missing or invalid placeholder on one record could throw
hundreds out of synch, with no prediction about how they would be read,
i.e. one trapped exception could span scores or even hundreds of "source
lines".

./heLen





Yahoo! Groups Links