Mark Wongwrote:
>
> On Mon, 2002-02-25 at 10:22, Mark Wong wrote:
> > On Mon, 2002-02-25 at 10:05, Mark Wong wrote:
> > > On Thu, 2002-02-21 at 01:29, Zabach, Elke wrote:
> > >
> > > > (BTW: all output-parameter are initialized with NULL
> internally.)
> > >
> > > Maybe I don't quite understand what that means exactly.
> If those some
> > > of those output paremeters are not set, meaning that less
> than 100 items
> > > have been returned, I get the following error:
> > >
> > > SQLSTATE S1000
> > > [SAP AG][LIBSQLOD SO][SAP DB]General error;-9806 Invalid
> output value.
> > >
> > > Thanks,
> > > Mark
> > >
> > > _______________________________________________
> > > sapdb.general mailing list
> > > [EMAIL PROTECTED]
> > > http://listserv.sap.com/mailman/listinfo/sapdb.general
> >
> > I did not relay what is happening correctly, my bad. I
> will try again.
> >
> > The error message I reported early is correct. The hypothetical
> > situation is a bit different. If I'm expecting up to 100
> items, and 99
> > are returned, the if-statement still execute as true:
> >
> > IF $rc = 0 THEN
> > BEGIN
> > FETCH INTO :i_id100, :i_title100, :i_publisher100, :i_cost100,
> > :ol_qty100,
> > :ol_discount100, :ol_comments100;
> > SET items = items + 1;
> > END;
> >
> > I believe I have verified this by setting items = 0 originally, and
> > seeing items is set to 100 after the stored procedure is
> called, instead
> > of seeing items set to 99.
> >
> > I'll note that I said hypthetical earlier because I
> actually tested this
> > with a different stored procedure. I can give that data out if this
> > information doesn't reveal anything obvious.
> >
> > Thanks,
> > Mark
> >
> > _______________________________________________
> > sapdb.general mailing list
> > [EMAIL PROTECTED]
> > http://listserv.sap.com/mailman/listinfo/sapdb.general
>
> I discovered something else about this. If the number of
> items returned
> is 0, then none of the if-statements execute. If the number of items
> returned (x) is less than 100, then x+1 if-statements executes. I'm
> trying to figure out what the valued returned from FETCH ($RC) is for
> the x+1 case, but if anyone can offer a hint for how to see what the
> value of $RC is exactly or what I should be checking against,
> that would
> be great.
>
> Thanks,
> Mark
There were two problems:
1. -9806 or something else telling that one output parameter is incorrect.
Shame on me, I did not tell the truth.
I told you, that every output-parameter is initialized with NULL
internally.
That is NOT true.
Local variables are initialized with NULL, output-parameter are not.
(we are thinking of changes, but that will not help now)
Therefore:
YOU have to initialize every output-parameter (hard work in your case).
And if -9806 arrives, we have to asssume, that at least one was not set
to 0 / ''.
2. if the SELECT does not find any result, error 100 is returned.
In your procedure, items is set to 0, $rc checked and no if-clause done.
Ok
If x items were found, items is set to x.
The x+1st check of $rc is ok, the FETCH is done, it returns (as with all
SQL-database systems) the error 100 row not found.
items is set to x+1 no matter what the fetch's $rc was.
The x+2nd check of $rc and all the following are not ok, items will be
to high.
I would change like this
IF $rc = 0 THEN
BEGIN
/* no item-setting for the FIRST
fetch */
FETCH INTO :i_id1, :i_title1, :i_publisher1, :i_cost1,
:ol_qty1, :ol_discount1, :ol_comments1;
END;
/* for FETCH no 2 to 100 this clause
*/
/* this means: if the fetch before
found a result ($rc=0) then items have to be increased */
IF $rc = 0 THEN
BEGIN
SET items = items + 1;
FETCH INTO :i_id1, :i_title1, :i_publisher1, :i_cost1,
:ol_qty1, :ol_discount1, :ol_comments1;
END;
IF $rc = 0 THEN
BEGIN
/* if even the 100th FETCH returned
something, set items to 100. It should be 99 here */
SET items = items + 1;
END;
error 100 is always the error saying: nothing can be found any more
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
