--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

>
> 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
> >
> >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.

<..last fragment removed..>

Thank you for your time and effort in helping me with this case, but
could you please elaborate a bit more on my questions below ?

>
> 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.

According to the Interbase language reference (which is also the
reference for Firebird?), the valid syntax for a select is like this:

SELECT [TRANSACTION transaction]
[DISTINCT | ALL]
{* | <val> [, <val> …]}
[INTO :var [, :var …]]
FROM <tableref> [, <tableref> …]
[WHERE <search_condition>]
[GROUP BY col [COLLATE collation] [, col [COLLATE collation] …]

<tableref> = <joined_table> | table | view | procedure
[(<val> [, <val> …])] [alias]

<val> = {
col [<array_dim>] | :variable
| <constant> | <expr> | <function>
| udf ([<val> [, <val> …]])
| NULL | USER | RDB$DB_KEY | ?
} [COLLATE collation] [AS alias]

Nothing here preventing the argument to a select procedure from being
a column.
My procedure does as SELECT COUNT(*) FROM Bunke in this query, so it
has to know about the table.
EXISTS have to be evaluated for each record in the SELECT, thereby I
had the impression that using a column from the main query was no
problem (and the reference doesn't prohibit it).
I have a similar singleton query in the same procedure that does work.

> 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.
>

Why is it not? The point of a selectable procedure is to make it
behave like at table, is it not?
Re. correlation, all identifiers are unique so aliases should not be
necessary (but I will give it a try and see what happens).


frode