FirebirdSQL logo
 Connecting to Firebird from ApplicationsFirebird Events 

ODBC Cursor Library

This topic is well documented in MSDN.However, we must stress the absolute requirement to use these statements before connecting:

// Specify that the ODBC Cursor Library is always used, then connect.
SQLSetConnectAttr( hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)SQL_CUR_USE_ODBC, 0 );
SQLConnect( hdbc, (UCHAR*)connectString, SQL_NTS, NULL, 0, NULL, 0 );

That data sets keys (?) in the rowset buffers.Updating the buffers requires this statement:

SQLFetchScroll( hstmtSel, SQL_FETCH_RELATIVE, 0 );

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.

  • Output from an executable procedure is optional and any output returned is a set of not more than one “row” of values.If output is defined and none is produced, the output is null.

    Returning data is not the primary goal of an executable procedure.Its purpose is to perform data operations that are invisible to the user.

    The mechanism for calling an executable procedure is the SQL statement EXECUTE PROCEDURE.For example,

    execute procedure MyProc(?,?)

  • A selectable procedure is written with the objective of returning a set of zero, one or many rows of data.It can be used to change data, but it should not be written to do that.The PSQL statement SUSPEND is used in this style of procedure to pass a row of output that has been collected inside an iteration of a FOR SELECT.. loop out to a buffer.

    The mechanism for calling a selectable procedure is the SQL statement SELECT.

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