Subject Re: [IBO] Stored Procedure problem
Author sgharp
--- In, Lucas Franzen <luc@r...> wrote:
> Steve,
> > the stored procedure and is populated afterward. There is
> > one field that is set to zero instead of the correct value when
> > from my application. When run from IBExpert, everything is
> > populated correctly. I'm sure I'm doing something wrong, but I
> > don't have a clue what.
> Yes, now I'm almost clueless, too.
> Either the stored proc also works from delphi (then you've got the
> result as you've got in IBExpert; but this is not the case) or it
> doesn't work at all (then you should have got 0 in all fields, not
> in one; which isn't the case either).
> But a stored proc that is working half way is - from my point of
view -
> impossible.
> It can't be a transaction problem; if it was you'd see either see
> original values or the new ones (but then ALL fields populated).
> It can't be a problem in the procedure either.
> So the only thing that comes to my mind is that you're working on
> different databases where the data in the tables or the procedure
> differ.
> Have you checked this and if so, have you checked the databases

I checked to verify that the database being called by my application
is the same as being called by IBExpert. They are the same.

I've noticed something interesting about the stored procedure (SP)
in question. The fields that are being populated correctly are
populated by a different SP that is called from the SP in question.
For instance,

create procedure spInQuestion
execute procedure spOtherFieldsProcedure

for select this, that
from xtable
into :this, :that
execute procedure spDoSomeStuff(:this, :that)
returning_values :mynum;

update xtargettable
set FieldThatsZero = :mynum
where this = :this;

This is an over simplification of a rather complex set of SPs but it
should give you the general idea. The point is, that in IBExpert,
all works fine but from my application, the only part that works is
the stuff being populated by the spOtherFieldsProcedure. Doesn't it
seem strange that a call to a sub-procedure like that works but a
direct update from with the SP is failing? Could this be