At 12:05 PM 12/04/2007, you wrote:

>Well, after almost 2 weeks of pulling my hair out on the select stored
>procedure / InsertSQL problem I at least know what will make the problem go
>away (sort of).
>
>I had a varchar field of 1024 chars that when I reduced it to varchar(971)
>or less the problem disappeared. Maybe that size has something to do with
>blobs and will trigger something with Jason.

There is no reason why it would have anything to do with blobs if the
field is a varchar. They are distinctly different data types and are
handled differently in the API. If you have somehow made a link
between your problems and blobs, there is more that you haven't told us.

Some of your reports seem vague or conflicting...wasn't a
characteristic of your problem that the query was passing the
last-fetched row data across to the InsertSQL parameters, instead of
the new data that was inserted into the Fields array? If that
observation is accurate, how could reducing the size of a varchar
field correct it?

If it is somehow "meaningful" that changing the size of a varchar
"somewhere" (??? where ???) removes a source of *something* crashing,
shouldn't you be looking at the definition of the stored procedure?

Suppose, for example, that you have an output field defined in the
RETURNS clause of your SP that is 1024 bytes. Suppose that the table
your InsertSQL is inserting to has the corresponding field defined as
varchar (971). Then, picking up the value from the dataset returned
by the SP and trying to pass it to the input parameter of your
InsertSQL statement *should* cause an exception in Firebird 1.5.4 or
lower or any version of InterBase. (In Fb 2.x, varchar sizes are
evaluated at run-time and will except *only* if the current value
exceeds the defined size.) That is, you should encounter an
exception, not crash.

>The strange part is, with the
>exact same code, the exact same database with the only change begin IBO 4.6
>vs IBO 4.7.16, IBO 4.7.16 always works with 971 or less and IBO 4.6 always
>works with 972 or more. I cut the size down all the way to 16 and IBO 4.6
>would not work. So I am thinking there is some kind of database corruption
>causing the problem.

If the stored data were corrupt, you would not be able restore it
from backup, as you tell us (below) you have done.

At least as far as one can sort the wheat from the chaff here, it has
the hallmarks of memory corruption....I think it would be interesting
to look at those data sizes in the SP definition, as a place to start
looking for sources of memory corruption. So far, it's not possible
so far even to guess whether one is looking at an IBO bug or a
pre-existing metadata anomaly (or application reference) that has now
become significant due to the recent tightening-up in IBO.

- Please double-check all your varchar definitions for the SP and
the table. In fact, if possible, show us the source code of the
entire SP AND the SQL property of the IB_Query.

- Also try to establish at what point the application is
encountering an unreferenced object (which is what you indicated
originally), i.e. does it occur when the app requests a Prepare? or
is it later, when the actual INSERT statement is passed?

- At one point you seemed to say that the AV was occurring as a
result of referring to the IB_Monitor. Do you ever actually make the
Execute call on the Monitor? Where this question is going is - are
you trying to reference a Monitor that is not actually active?

> I have backed up and restored the database many times
>and that does not seem to help at all.
>
>So I guess I rebuild the database from scratch and re-load.

I don't think that is indicated..albeit the evidence is contradictory.

>Any other ideas?

One other thing that comes to me from left-field is the possibility
that you have your server configured with OldParameterOrdering set to
True. This *will* stuff things up with parameterised SPs and more
recent versions of IBO, although that change goes back a fair way,
possibly to the start of the 4.5 series, if not earlier.....

Also, are we certain that the row we are inserting is not going to
violate a Foreign Key relationship somewhere?

It will be at least interesting to see the SP source and the
IB_Query's SQL, if just to eliminate the possibility that your
InsertSQL is inconsistent with the SP output. If that is the case
then the solution would be a simple matter of detaching the InsertSQL
from the dataset altogether and running the operation as a separate function.

It would be soooooo much simpler if you were able to reproduce this
problem in a demo app. Is there any chance you could cook up a demo
database consisting of just the SP and the tables involved in the
problem, with a set of demo data installed in it? If you didn't want
it to be public, you could send it to me directly, or send me a URL
where I could download it...

Helen