Firebird Documentation IndexFirebird ODBC/JDBC Driver ManualDeveloping with the Firebird ODBC/JDBC Driver → Stored Procedures
Firebird Home Firebird Home Prev: CursorsFirebird Documentation IndexUp: Developing with the Firebird ODBC/JDBC DriverNext: ARRAY Data Type

Stored Procedures

In Firebird we can have two types of stored procedures, known as executable and selectable. Both types can take input parameters and return output but they differ both in the way they are written and in the mechanism for calling them.

In this example we have a selectable procedure from which we expect to receive a set of zero or more rows based on the input parameters:

select * from MyProc(?,?)
      

Microsoft Excel and some other applications use this statement to call a stored procedure:

{[? =] Call MyProc (?,?)}.
      

The Firebird ODBC/JDBC driver determines what call to use when executing a stored procedure, from the metadata obtained from the Firebird engine. Firebird flags a procedure as 'executable' or 'selectable' according to count of SUSPEND statements in the assembled (BLR) code of its definition. For a trivial example:

create procedure TEST
  as
    begin
    end
      

Because the procedure has no SUSPEND statements, the ODBC driver knows to pass the call as execute procedure TEST.

For this procedure:

create procedure "ALL_LANGS"
   returns ("CODE" varchar(5),
         "GRADE" varchar(5),
         "COUNTRY" varchar(15),
         "LANG" varchar(15))
   as
   BEGIN
     "LANG" = null;
     FOR SELECT job_code, job_grade, job_country FROM job
     INTO :code, :grade, :country
     DO
       BEGIN
         FOR SELECT languages FROM show_langs(:code, :grade, :country)
         INTO :lang
           DO
             SUSPEND;
             /* Put nice separators between rows */
             code = '=====';
             grade = '=====';
             country = '===============';
             lang = '==============';
             SUSPEND;
       END
     END
      

the BLR code for the stored procedure contains more than zero SUSPEND statements, so the ODBC Driver will use select * from "ALL_LANGS".

Prev: CursorsFirebird Documentation IndexUp: Developing with the Firebird ODBC/JDBC DriverNext: ARRAY Data Type
Firebird Documentation IndexFirebird ODBC/JDBC Driver ManualDeveloping with the Firebird ODBC/JDBC Driver → Stored Procedures