looks like a bug in the change below from the 2.1 release notes, any idea
how I can get round it?


Feature request CORE-779

Introduced a type flag for stored procedures, adding column
RDB$PROCEDURE_TYPE to the table RDB

$PROCEDURES. Possible values are:
*

- 0 or NULL -
*

legacy procedure (no validation checks are performed)
*

- 1 -
*

selectable procedure (one that contains a SUSPEND statement)
*

- 2 -
*

executable procedure (no SUSPEND statement, cannot be selected from)


2008/9/19 Nick Upson <nick.upson@...>

> using fb2.1 on fedora 8, trying to build a new database from a script of
> a 2.0 database. I get the following message
> which is incorrect, the Procedure PR_OS_AUTHSTATUS_FLTR **does** contain a
> suspend statement
>
> Statement failed, SQLCODE = -104
> invalid request BLR at offset 259
> -Procedure PR_OS_AUTHSTATUS_FLTR is not selectable (it does not contain a
> SUSPEND statement)
> After line 32444 in file db.ddl.sql
> which relates to an SP that contains:
>
> for select OUTSTATIONID, OSIDENT, BASEDESC, CONNECTIONSTATUS, PORT,
> AUTHSTATUS,
> PASSWORD1, PASSWORD2, DATEADDED, LASTTXMSG, LASTRXMSG
> from PR_OS_AUTHSTATUS_FLTR(:BASEDESC_IN, :AUTHSTATUS_IN)
> into :OUTSTATIONID, :OSIDENT, :BASEDESC, :CONNECTIONSTATUS, :PORT,
> :AUTHSTATUS,
> :PASSWORD1, :PASSWORD2, :DATEADDED, :LASTTXMSG, :LASTRXMSG
> do
> begin
> SP code:
>
> ALTER PROCEDURE PR_OS_AUTHSTATUS_FLTR (BASEDESC_IN Varchar(40),
> AUTHSTATUS_IN Varchar(15))
> returns (OUTSTATIONID Integer,
> OSIDENT Integer,
> BASEDESC Varchar(150),
> CONNECTIONSTATUS Smallint,
> PORT Smallint,
> AUTHSTATUS Smallint,
> PASSWORD1 BIGINT,
> PASSWORD2 BIGINT,
> DATEADDED Char(19),
> LASTTXMSG Char(19),
> LASTRXMSG Char(19))
> AS
> declare variable authentication varchar(15);
> begin
> for select OUTSTATIONID, OSIDENT, BASEDESC, CONNECTIONSTATUS, PORT,
> AUTHSTATUS,
> PASSWORD1, PASSWORD2, DATEADDED, LASTTXMSG, LASTRXMSG
> from PR_OS_BASESTATION_FLTR(:BASEDESC_IN)
> into :OUTSTATIONID, :OSIDENT, :BASEDESC, :CONNECTIONSTATUS, :PORT,
> :AUTHSTATUS,
> :PASSWORD1, :PASSWORD2, :DATEADDED, :LASTTXMSG, :LASTRXMSG
> do
> begin
> authentication = AUTHSTATUS_IN;
>
> if (authentication is null) then
> suspend;
> else if (authentication = 'authenticated') then
> begin
> if (AUTHSTATUS = 1) then
> suspend;
> end
> end
> end^
>
>

[Non-text portions of this message have been removed]