At 11:06 AM 24/10/2003 +1000, you wrote:

>Thanks for your help Jason.
>
>I think I need to illustrate the scope of the problem in our situation. We
>have hundreds of storedprocs in our database which translates to hundreds
>of TIBOStoredProc components scattered through out our 30 or so
>applications. This is a lot of maintenance for us to add Unprepare and
>Prepare statements before referencing each TIBOStoredProc. And while we
>may be able to do a lengthy search for ".ExecProc", if we miss just one
>stored-proc the repercussions could be disastrous.
>
>Jason, in all our findings on this error, we still have not come across an
>explaination as to what the SQLDA error actually is. Can someone please
>enlighten us???

Ben,
This isn't a question of something becoming a problem just because you are
converting to IBO. It's a bug in the API that has been there all along, at
least up to and including IB 6.0.2. (After that, it disappeared behind the
doors at Scotts Valley, so nobody knows). The bug has already been
affecting your app for as long as you have been using those SPs with IB 5.6.

The XSQLDA is the structure that the API uses to pass the SP input
parameters across to the server, and to pass any output parameters
back. The bug comes in the *re*-assignment of input values to the
parameters on calls subsequent to the first. Under some conditions,
instead of clearing the old parameters and slotting in the new ones that
are passed to it, it silently ignores the new parameters and, when called
thereafter, continues to use the original parameter values. Other
observations include it replacing some but not all values. So it's not
something you can predict and work around.

>If we can atleast understand the error and what exactly causes it to occur
>we may be able to find a better workaround for us.
>
> >If you Unprepare and Prepare each time then you won't have the problem at
> >all.
>Can you tell us why this works?
>Why, without the calls to unprepare and prepare does it work the first
>time but not the second, third time with different params?

Unpreparing and re-preparing the statement causes the whole structure to be
cleared and reinstated as if it were a different statement. That gets the
statement back to its initial state. So, unless you Unprepare + Prepare +
Assign Param values on each call, you can't be certain that the parameters
passed across the API are the ones passed by your application. It's a
hideous bug, because you're not likely to detect it until something bad
happens as a result of the wrong data being updated, or the right data
being updated with the wrong values.

> >It was also difficult to trigger the bug since it has to do with a
> >combination of usage patters that tripped it up.
>Can you be more specific please?

As Jason stated, it's pretty well impossible to predict which SP calls it
will affect without putting it through a lot of high-volume testing and
post-execution validation, with every possible configuration.

>Also, did I mention we are on Interbase? Moving to Firebird 1.5 is not an
>option for us. Our company has a strong commitment to Interbase (our Boss
>prefers a non-open source solution, and we have already purchased the
>software for our migration from 5.6 to 7.1), and moving to firebird is not
>a fix to the actual problem. :oP

Something your firm might consider is asking Borland to fix it. :-) Of
course, first they have to admit that it's there. That was the hard part
in the past. When IB went open source, it proved to be not hard at
all. It was found and fixed in very short order.

You could even trace back through the Firebird source history, to when the
problem was fixed in Firebird, identify the fix and ask Borland to supply
you with the needed source code to recompile IB yourselves. Of course, who
knows, that may be something Borl already thought of, all by
themselves...check back through the release notes and you might find a fix
was done at some point between IB 6.0.2 and IB 7.1.

Helen