FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Connecting to Firebird from ApplicationsFirebird Events 

Cursors

In the current Firebird ODBC/JDBC driver, the Dynamic and Keyset cursors are modified to use the Static cursor, through which it is not possible to update sets.

For best performance, use the cursor ForwardOnly.

The read operators SQLFetch, SQLExtendedFetch and SQLScrollFetch use SQL_ROWSET_SIZE and SQL_ATTR_ROW_ARRAY_SIZE.

For best performance using BLOB fields, use the operator SQLBindParameter, regardless of the size of the BLOB field, as this will work much faster than using SQLPutData/SQLGetData.

To use the Firebird driver’s cursors, call the following statements:

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

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

ARRAY Data Type

To modify single dimension array data type fields, you need to conform to the following rules:

  • Specify simple types (INTEGER, etc.) as {1, 2, 3}

  • Specify string types (CHAR, etc.) as {'1', '2', '3'}

Warning
TRAPS!

If you edit an element of the array e.g. element 1, 2 and 5, and do not specify the other elements of the array, e.g. 3 and 4, then the other elements of the array will be zeroed (integer), or blank (string).

With some programs where columns are dependent on array data, it is possible to enter array data into a currently NULL array column without a validity check being made on the various array elements.Under these circumstances it is essential to enter the array elements before entering the column data.
fb odbc ModArrayField
Figure 1. Data loss when updating an ARRAY field (1)
fb odbc ruleModArrayField
Figure 2. Data loss when updating an ARRAY field (2)

Usage with Clarion

Jorge Brugger; Vernon Godwin; Vladimir Tsvigun

Clarion users can work with mixed-case object names in Firebird.

  1. Create your database in Firebird.You can use table names like "Pending_Invoices" and fields like "Order_Number".

  2. Create the DSN for the Database, making sure to check all options in “Extended Identifier Properties”

  3. Open your dictionary, and import multiple tables as normal from the odbc source.It will work, but do not try to browse or use the files in an application yet.

  4. For every field, type in the “External Name” the name of the field surrounded by quotes (for example, type "Order_Number" in the external name).

That’s it!Now use your dictionary with mixed case identifiers, without problems.But remember — you must use double quotes around object names in all SQL statements from inside Clarion.