How to SELECT a PROCEDURE in Firebird 2.5

Question

I'm using Firebird Embedded v2.5. How to use procedures in query (SELECT) ?

My procedure:

SET TERM ^ ;
CREATE PROCEDURE FN_TEST( Y INTEGER )
RETURNS( X INTEGER)
AS
BEGIN
X = Y + 1;
END^
SET TERM ; ^

I want to list some field of table modified by some procedure, like this:

SELECT some_table_field_1, 
       fn_test( 4 ) AS zzz, 
       some_table_field_2,
       fn_test( some_table_field_2 ) AS field_2_modified
FROM   tb_test

Need results (table):

some_table_field_1     zzz         some_table_field_2      field_2_modified
---------------------------------------------------------------------------
aaa                    5           14                      15
bbb                    5           23                      24
www                    5           75                      76

This thing works fine in PostgreSQL, but I don't know how to do this in Firebird.

What exactly is your problem? Do you get an error message? If yes, post it — , Mar 03 '11 at 08:12

score 6 · Answer 1 · edited Oct 22 '16 at 07:47

6

SELECT some_table_field_1, 
   (select X from fn_test( 4 )) AS zzz, 
   some_table_field_2,
   (select X from fn_test( some_table_field_2 )) AS field_2_modified
FROM   tb_test

edited Oct 22 '16 at 07:47

Mark Rotteveel

100,966
191
140
197

answered Feb 21 '12 at 07:45

rstrelba

1,838
15
16

1

`fn_test` is not selectable (it's an [executable procedure](https://ib-aid.com/download/docs/firebird-language-reference-2.5/fblangref25-psql-storedprocs.html)). The proposed solution produces an _invalid request BLR at offset..._ error. – manlio Apr 26 '17 at 13:29
Add a SUSPEND; statement just before the END^ statement and the function becomes selectable. – WeststarEric Dec 26 '19 at 18:47

score 4 · Answer 2 · edited May 23 '17 at 11:46

FN_TEST is an executable procedure: it can be called via the EXECUTE PROCEDURE statement and it returns a single set of output parameters.

In Firebird 2.x only a selectable stored procedure can be "used" as a view / table (see Firebird Stored Procedures).

So:

SELECT FN_TEST(some_table_field) AS field_modified
FROM   tb_test

produces an invalid request BLR at offset... error.

You could change your procedure as suggested but, actually, the feature you need has been introduced in Firebird 3 in the form of stored function:

CREATE FUNCTION FN_TEST(Y INT) RETURNS INT
AS
BEGIN
  RETURN Y + 1;
END;

SELECT FN_TEST(4) AS zzz
FROM   tb_test

Further details in Functions with PSQL in Firebird 3.

score 3 · Answer 3 · edited Oct 22 '16 at 07:47

3

Try

SELECT some_table_field_1, 
   fn_test.x AS zzz, 
   some_table_field_2,
FROM   tb_test
LEFT JOIN fn_test(some_table_field_1) ON (1 = 1)

edited Oct 22 '16 at 07:47

Mark Rotteveel

100,966
191
140
197

answered Mar 30 '11 at 11:26

To horse

31
1

`fn_test` is not selectable (it's an [executable procedure](https://ib-aid.com/download/docs/firebird-language-reference-2.5/fblangref25-psql-storedprocs.html)). The proposed solution produces an _invalid request BLR at offset..._ error. – manlio Apr 26 '17 at 13:31

score 1 · Answer 4 · edited Oct 22 '16 at 07:48

1

Try this

SET TERM ^ ;
CREATE PROCEDURE FN_TEST( Y INTEGER )
RETURNS( X INTEGER)
AS
BEGIN
X = Y + 1;
SUSPEND;
END^
SET TERM ; ^

edited Oct 22 '16 at 07:48

Mark Rotteveel

100,966
191
140
197

answered Jul 18 '14 at 09:09

FlixLux

156
2
8

score 1 · Answer 5 · edited Oct 22 '16 at 07:50

1

You can't call stored procedures in Firebird's select list. You have to write the selectable procedure with desire result or write an UDF function to do what you have in fn_test procedure.

For your case the simplest way is:

SELECT some_table_field_1, 
       5 AS zzz, 
       some_table_field_2,
       ( some_table_field_2 + 1) AS field_2_modified
FROM   tb_test

edited Oct 22 '16 at 07:50

Mark Rotteveel

100,966
191
140
197

answered Mar 10 '11 at 10:40

JustMe

2,329
3
23
43

score 1 · Answer 6 · edited May 23 '17 at 12:09

As JustMe said, you can't call stored procedures in a select. You can call stored procedure only in the FROM section. Another solution for your problem is to create a selectable procedure like this:

create or alter procedure myproc (
 n integer)
returns (
 field_1 integer,
 zzz integer,
 field_2 integer,
 modified_field_2 integer)
as
declare variable i integer;
begin
  for
    select some_table_field_1, :n+1 as zzz, some_table_field_2, (some_table_field_2+1) as field_2_modified
    from tb_test
    into :field_1, :zzz, :field_2, :modified_field_2
  do begin
    suspend;
  end
end

After run that code, you can simply query select * from myproc(4) and get what you want.

score 1 · Answer 7 · answered Feb 28 '12 at 22:33

1

Use UDF in order to manage calculation on fields. Stored procedure are admited only in the FROM Clause.

answered Feb 28 '12 at 22:33

Marco

338
2
6

score 0 · Answer 8 · answered Jun 17 '13 at 04:35

0

You can use EXECUTE BLOCK Please have a look EXECUTE BLOCK

answered Jun 17 '13 at 04:35

Biju Soman

428
4
19

How to SELECT a PROCEDURE in Firebird 2.5

8 Answers8

Linked