Subject Re: [IBO] Integer Overflow Exception - TIB_StoredProc
Author Helen Borrie
At 10:31 AM 7/09/2005 +0200, you wrote:
>it is indeed an integer overflow and not a GDSCode.
>The overflow happens in the IB_storedproc.execute method.

TIB_Dataset.SysClose (in my version 4.5Ai of IBO)
> try
> Inc( FCursorGen ); <<<<-- here
> except
> FCursorGen := 0;
> end;
>FcursorGen is defined as word and overflowing at a count of 65535
>(which happens approx. after calling TIB_Storedproc.execute 32768

Still, I think it will be signed, so WORD (being a 4-byte integer) will
overflow at 32768.

>Is this a limitation within FB or do I have to do some cleanup work
>after a call to TIB_storedproc.execute to keep the value of the
>FCursorGen low?

I don't know the answer but I would suppose that Jason limits it
deliberately for a specific reason, e.g. to lower the bar for excessive
concurrent executions, or something of that ilk.

Hopefully Jason will step in and explain his exact rationale.

>To give it a test I redefined the FcursorGen from Word to LongWord
>and can call TIB_storedproc now without any limit.
>But this doesn't sound like a correct solution to me.
>What has to be done to keep the FCursorGen counter at low

In real life, you wouldn't have a SP like your example. Why wouldn't you
just write a client function to calculate something that always produced
the same result (or an algorithmic result)? SPs are meant to act on data,
within the context of a transaction.

It's very uncool, for example, to iterate through 30,000 records on the
client side and feed through 30,000 SP calls - presumably parameterised to
be at all useful - without batching them into realistic chunks. If a
developer absolutely insists on designing an application this way, then
8000 record changes is the recommended maximum per transaction.

If (as your sample SP suggests) your aim is to return a set from the SP,
then you would not have multiple SP calls in a transaction: only one.

A further tip: stay clear of TIB_StoredProc. It's only there for
compatibility. IBO has much better ways to use SPs - ib_query or ib_cursor
for selectable procedures, ib_dsql for executable ones.