At 01:00 PM 12/01/2006 +0000, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
>wrote:
> >
> > At 01:05 PM 11/01/2006 +0000, you wrote:
> > >Hi,
> > >I have some code like EXECUTE STATEMENT 'EXECUTE PROCEDURE blabla'
> > >
> > >While running this my pagefile grows until available disk space is
> > >exhausted.
> > >
>
> >
> > You're running what's sometimes known as "a runaway query" - in this
>case,
> > the procedure logic is probably executing an infinite loop. Show
>the
> > actual code of the two procedures so that it's possible to comment
>on what
> > the procedure is *actually* doing, rather than what you think it
>ought to do...
> >
>
>I have nailed down the cause for this, it is the following select
>(performed in a procedure)
> SELECT COUNT(*)
> FROM Bunke
> WHERE Aar = :Aar AND Bilagsserie = :Bilagsserie AND :Bilag =
>Bilag
> AND Motpartkategori IS NOT NULL
> AND ( Konto = :Konto AND Dato = :Dato AND Linje < :
>Bunkelinje )
> AND EXISTS( SELECT *
> FROM HentDimSaldoBalanse( 'B', Bunke.Id
> )
> WHERE Dimensjoner = :Dimensjoner )
> INTO Reskontrolinje;
>
>It is the EXISTS statement that "breaks the server", it seems like it
>consumes a lot of memory for each invocation.
>HentDimSaldoBalanse is simplified to just return the string '#'.
>
>When inserting an Exception statement just after this select I got the
>message "No current record for fetch operation" before my exception
>info was output (Using IBObjects).
>When this statement is run from IBOConsole no errors are reported,
>when rewriting it to
>SELECT COUNT(*)
> FROM Bunke
> JOIN HentDimSaldoBalanse( 'B', Bunke.Id )
> ON Dimensjoner = :Dimensjoner
> WHERE Aar = :Aar AND Bilagsserie = :Bilagsserie AND Bilag = :
>Bilag
> AND Linje < :Bunkelinje AND Dato = :Dato AND Konto = :
>Konto
> AND Motpartkategori IS NOT NULL
>
>IBOConsole gives the same error, but this works in the procedure (no
>memory "trashing").
>Since I can't try this from IBOConsole I'm not sure if the rewrite is
>correct.
>
>Is there anything obvious wrong with what I am doing?

Yes. Even if this approach made any sense (which it doesn't!), there's
quite a lot of illegal or impossible SQL in both these fragments of code
that you have divulged here. Because it's late and I'm tired and I still
have stuff to do, let's ignore the second fragment with its weird, invalid
join.

The second argument in the select procedure call in the subquery is
illegal. The value for a procedure argument has to be a constant, or else
a colon-prefixed variable or a parenthesised expression resolving to a
constant of the right type. Bunke.Id is none of these things. It's a
reference to a table that only *you* know about. The procedure doesn't
know anything about any row or column in Bunke.

Next, if it was reasonable to attempt to subquery the output of a
selectable stored procedure (which it's not), the syntax is missing some
important elements, notably identifiers or aliases that would enable
correlation.

Assuming that you actually need that downstream SP to get the existence
information, let's cut to the chase and do something that is more reasonable.
Write a sensible EXECUTABLE SP that that returns a single true or false
value; invoke it using EXECUTE PROCEDURE and use RETURNING_VALUES(
:avariable). Make sure you declare an argument or variable for the
returning value in the calling procedure AND initialise it before you
invoke the downstream procedure.

So - let's suppose the header and logic of the EXECUTABLE procedure looks
something like this:

create procedure HentDimSaldoBalanse(
value1 CHAR, value2 BigInt)
returns (result CHAR)
as
begin
..blah...
if (blahblah) then
result = 'T' ;
else
result = 'F';
end

And the calling procedure then does this:

create procedure .....

as
declare variable Reskontrolinje CHAR = '';
declare Bunke_id integer = -999;
declare counter integer = 0;
BEGIN
FOR SELECT Id
FROM Bunke
WHERE Aar = :Aar
AND Bilagsserie = :Bilagsserie
AND :Bilag = Bilag
AND Motpartkategori IS NOT NULL
AND ( Konto = :Konto AND Dato = :Dato AND Linje < :Bunkelinje )
INTO :Bunke_id
DO
BEGIN
if (Bunke_id >= 0) then
begin
counter = counter + 1;
EXECUTE PROCEDURE HentDimSaldoBalanse( 'B', :Bunke_id)
RETURNING_VALUES(:Reskontrolinje);
if (Reskontrolinje <> '') then
begin
/* do your stuff, whatever you wanted to do */
end
Bunke_id = -999;
Reskontrolinje = '';
end
....
....
END
if (counter > 0) then
....
END

./heLen