The Firebird Database Engine & The Frustrations of The Long Distance Database Application Developer

https://www.firebirdsql.org/

Author’s Notes…

Though I have extensive database application development experience, I do not consider myself an expert with the Firebird Database Engine to any degree.  The following article is completely based upon observations and experiences during my attempts to learn and understand this highly capable engine.  As a result, if there are misunderstandings or mistakes I have promoted in my writing, please do not hesitate to send in comments either correcting them or offering alternatives.

Please also note that this publication is based upon work done with Firebird’s Embedded Server\Edition Version 2.5.  However, the latest release of the Firebird Database Engine is version 3.0.3.  Prior to the 3.0.x release(s) of Firebird there was no support for standard user-defined SQL functions.  With Firebird 3.0.x there is now such support.

Some of my experience with Firebird comes from the development of an ADO.NET data access layer that is available as a free Open Source project from my business site at the following link… https://blackfalconsoftware.com/software/

As a result, being a specialist in Microsoft .NET development, all my references to programming with Firebird are based upon ADO.NET.  Though I can’t say with any degree of certainty, I would I imagine that developers using JDBC or some similar method of data access for Firebird will experience similar results…

Steve Naidamast
Sr. Software Engineer
blackfalconsoftware@outlook.com

 

Overview

Taken from the very early phrase attributed to long distance runners back in the late 1960s and 1970s, the second part of the title of this article does have some very prescient meaning for those developers who have worked with other database engines in their careers and have now decided to try their luck with the freely available, Open Sourced, Firebird Database Engine.

However, before we get into any particulars, I want to state up front that the Firebird Database Engine is as good and well designed as any competing engine available today, whether it be a commercial engine or another from the Open Source Community.  Like PostgreSQL, which has its foundations taken from the excellent Ingres database, Firebird’s foundations are taken from the highly innovative database technology that was designed by the people who introduced us to Interbase.  Hailed for its innovative technologies, Interbase was first introduced by Groton Database System in 1986.  By the early 1990s it had been picked up by Borland International and had shown a similar tendency towards “ease of use” that was only predominant in Microsoft’s SQL Server engine, which still remains probably the easiest to use RDBMS of all time, though it has become increasingly powerful over its many years in existence.

In 2000, Borland International announced that the Interbase database Engine would from then on be released under the auspices of the Open Source Community, though Borland continued to work on their own in-house version of the engine.  As a result, the Firebird Database Engine was born as a fork of the original Interbase project.

Seeing the early successes of Firebird, Embarcadero Corporation re-introduced Interbase under its own corporate banner and is still the current owner of the original database system.

The Firebird Project Infrastructure

The Firebird development team took a somewhat different tact from other organizations that would enter the Open Source field of software products.  To this day, this approach has acted in part as an impediment to the greater exposure that the Firebird database actually deserves.

In this respect then, the Firebird team maintains a web site like any other organization but like most early Open Source organizations what is made available to the technical community at large is more or less the raw foundations of the software.  In short, this means that potential developers who want to use this database engine should be well versed in the implementation and coding practices that go with developing database-based applications.  Though there is ample documentation for technicians to avail themselves, it is mostly geared towards those who have experience with such development.

Developers new to database development will not find any real documentation that will provide “noobies” with a basis for learning database development while at the same time learning to handle the Firebird Database Engine.  This lack of any good “learning” documentation is probably the Firebird Organization’s biggest drawback to its very fine engine, dissuading new entries into the supporting communities of the product.  And even long time developers experienced with database fundamentals will find working with Firebird a very frustrating experience at times.  This latter point has a basis in the fact that Firebird operates somewhat differently than what professionals are used to when coming from working with the major engines available today such as Oracle, SQL Server, IBM’s DB2, and the very popular Open Source database engine, MySQL.

Learning Firebird without Necessary Learning Documentation

For all developers new to this database engine there are two approaches that can be taken to more easily get one started with Firebird.

The first approach would be the common one if one wants a more formal foundation for learning Firebird; buy a book.  Even here we have somewhat of a limitation on the matter as there aren’t really many books available on this engine due to its lack of general popularity; especially in the United States.  However, there is one excellent set of books for those looking to make such a purchase should consider; the Helen Borrie series of manuals.  There are two versions of these manuals available; the first publications from Apress that were released in and around 2005 and the IBPhoenix bookset, which is produced as a three-volume paperback series, which was released around 2013; the latter set taking readers through the 2.5 version of the engine (the latest version is 3.0.3).

Helen Borrie is one of the senior lead developers of Firebird and her original manual was well written so I would recommend these books to newcomers.  Both the Apress and IBPhoenix publications can be found at Amazon at the following link…

https://www.amazon.com/s/ref=nb_sb_noss_1?url=search-alias%3Dstripbooks&field-keywords=firebird+database

For those who feel that their experience and backgrounds have prepared them to tackle Firebird, by all means dive in but be prepared for a rocky ride.  As I stated previously, Firebird does not operate on the same wave-lengths as the other major engines as it most definitely marches to a “different drummer”.

This is the approach I took and even with over 42 years in professional database development I found various issues with Firebird that at times drove me stark raving nuts.  As a result, I had to turn to the only online support resource the Firebird team provides; their email lists.  However, there is a rather substantial support area on StackOverflow.

Firebird Support is Excellent!

As stated previously, the Firebird Organization took a different approach to their infrastructure.  And where practically all major database vendors now offer large support forums, the Firebird people decided to currently remain with the email lists.

I was quite skeptical of such a method of support since when I have had to submit queries directly to a vendor for resolution it has often taken over 24 hours for responses.

Where the Firebird team lacks in learning documentation, they more than make up for it with their rapid responses on their email lists.  Having to use this method a number of times to resolve issues I was confronting with a conversion from SQL Server’s LocalDB to Firebird as a result of its excellent Embedded Database Engine, I found the quick responses not only very helpful towards resolutions but friendly as well.  Even Helen Borrie responded to one of my queries.

So for those who, like me, intend on taking on Firebird head-on, ensure that you sign up for the respective email list that is offered for your region of the world.  Simply go to the link at the top of this document and select the “Support” option on the web site’s master menu.

The Firebird Reference Manual

Though the latest version of Firebird is 3.0.3, everyone should make sure they download the 2.5 language reference manual, which is in PDF format.  At almost 500 pages it should be sent out to your local printer for hard-copy publication.  It is most definitely worth the approximate $45.00 USD that will be charged for a nicely printed copy.  Keep the manual by your side as you begin your experimentation as you will find yourself looking things up regularly to resolve issues based on what you thought you already knew but found is no longer working.

As to when a 3.0 version of this manual will be released there is no date that I am aware of.  However, the 2.5 version of the manual should suit everyone’s needs just fine considering that there are no real major changes to the engine requiring large documentation.

The one major change however, is how the Embedded version of Firebird is now being handled.  To address this, Helen Borrie has released the information online at the web-site in PDF format, which can be found at the following link…  http://www.ibphoenix.com/files/Embedded_fb3.pdf

In addition to the language reference manual, the Quick Start and Developer guides for the 3.0 version of the database (both also in PDF format) should also be downloaded as additional documentation for review.

Embedded & Server

My change from using Microsoft’s SQL Server LocalDB to Firebird came after a lot of thought and consideration not only for the overall user experience I wanted my current project to offer but the technical convenience using Firebird would provide me as a developer.  From my previous attempts at using Firebird I was quite reticent in attempting such a conversion.  However, in the end, the use of SQL Server LocalDB for anything literally made no sense when SQL Server Express is available and both required the application access an external process while taking up what would be considered a substantial amount of storage space for a desktop application.

As a result, in the past two months I decided to finally “bite the bullet” and tackle Firebird in earnest and have so far been very pleasantly rewarded for my efforts.

Though I am working with the embedded edition of the Firebird Database Engine, there is a lot more to this database than just a version for the desktop.

The Firebird Database Engine is the only database system available today that whether one uses the embedded version or one of the server versions offered, the implementation of all the various database objects and scripts is exactly the same.  Thus, when using a database manager for handling the embedded version of Firebird, to the developer it will appear as if he or she is using a server version.  The only difference is the configuration of the implementation (which would be expected) and the protocols for the connection strings.

Even Microsoft has not been able to match this unique capability found with Firebird, though they appear to have tried with the SQL Server LocalDB implementation.

With Firebird (in addition to the embedded version of the database), a developer receives a choice of three different server engines that can be installed for remote database access requirements.

As noted from the Quick Start Guide (3.0) below, these three types of servers are described as follows and are available for the different considerations an implementation may be based upon…

Some installers ask you to choose between Classic, SuperClassic and Superserver mode. What are they?

•  Classic (aka MultiProcess) involves a single listening process that spawns off an additional process for each client connection. Using a locking mechanism, it allows shared connections to database files.
•  SuperClassic (ThreadedShared) is a single server process. Client connections are handled by separate threads, each  having  their  own  database  page  cache.  Other  processes  (e.g.  embedded  servers)  may  open  the  same database simultaneously (hence the Shared).
•  Superserver (ThreadedDedicated) is also a single server process with threads handling client connections. There is a single, common database page cache. The server requires exclusive access to each database file it opens (hence the Dedicated).

 Each mode is fully stable and there is no reason to categorically prefer one to the other. Of course you may have your own specific considerations. When in doubt, just follow the installer default for now. Changing the server mode later can be done via the configuration file firebird.conf  and requires a restart but not reinstallation.

The server mode can even be configured per database (consult the Release Notes for details).

For additional documentation on these server modes, please see the Addendum Notes at the end of this article…

Firebird Database Management

Like any major database system, Firebird has its own ISQL utility.  However, if one is going to do heavy database development, using this utility will be more inhibiting than helpful.  As a result, there are three database management tools I would recommend for consideration.

The first is the freely available FlameRobin DB-Manager, which apparently has received very good reviews from people who use it.  The software can be downloaded from the following link… https://sourceforge.net/projects/flamerobin/

I have never used this tool so I cannot comment on it from my own experience but if others are saying good things about it there is no reason not to try it.

The next recommended db-manager is Database.net, which I have used and is very well reviewed.  Like FlameRobin, the 32bit version of this application is available freely and supports not only Firebird but approximately 20 or so different database engines.  Developed by a fellow called Fish in Taiwan, this piece of software will get the job done as well as any db-manager commercially available.  The software can be obtained from the following link… http://fishcodelib.com/database.htm

My preferred db-manager is that which is available from EMS Corporation. EMS provides free versions of their tools, which will suffice for light to medium levels of database development but if one is doing this professionally and is used to the equivalent tools, than a purchase of the commercial version of the software would be the recommended route.  Since I have worked with SQL Server, MySQL, and now Firebird, I own all three of the corresponding tools all of which have exactly the same interface making learning any other database engine manager for any of the major database engines a relatively easy task. Starting with the free version of the db-manager for Firebird will let one evaluate the software completely to see if they want to make a purchase or keep using the free version itself.  This software can be obtained at the following link… https://www.sqlmanager.net/

Firebird PSQL; Let the games begin…

Now that we have gotten through a lot of the peripheral information regarding Firebird we can now move on to the material that will drive many experienced people completely “bats” with this database engine.

As mentioned, Firebird “marches to its own drummer” and with its implementation of PSQL it brings this point home quite often to those learning how it works.  A lot of this disorientation, for want of a better word, is a result of the inconsistency between what works in script within a db-manager and what works within a stored procedure or a function.

Let’s begin simply with the SELECT statement…

Inline SELECT statements & the Selectable Procedure

With most database engines, the following SELECT clause will work no matter where you put it…

SELECT * FROM MY_TABLE_NAME

Everyone (or most everyone) would agree that this statement will return all of the rows or records from any particular table and they would be correct.  If we were to send this as a query-string to the Firebird’s ADO.NET provider it would also be expected to return all rows back to a data-reader or a data-set, which it does.  So far so, so good…

One caveat, while we’re here. is that one should get used to ending their PSQL statements with a semi-colon(;).  However, this can become a “gotcha” dependent on where you do this.

In the sample pseudo-PSQL code above it doesn’t matter as long as the statement is being passed from a db-manager query-screen or to the Firebird ADO.NET provider.

However, let us see what happens when we place this statement into a stored procedure.  In essence we will be creating a T-SQL View or something similar in the other major database engines (Firebird has .Views as well.  However, with this type of functionality, they would appear to be redundant.).

For this simplistic procedure we enter the following PSQL code…

CREATE PROCEDURE SP_WORK_SIMPLE_SELECT
AS
BEGIN
SELECT * FROM RI_CATEGORY_NODES

SUSPEND;
END;

Note that we have not entered a semi-colon at the end of the SELECT statement. When we compile this basic procedure in EMS’ Firebird DB-Manager we get the following error…

Note the specific culprit to this particular error is the lack of a semi-colon at the end of the SELECT statement.  Let’s add one in and recompile this code and see what happens…

Surprisingly, the same exact error occurs…

However, note where the error occurred on this most recent compile of the code as compared to the initial compile previously.  When the SELECT statement was not ended with a semi-colon, the statement was flagged as having a syntax error, though as we demonstrated previously using a db-manager or ADO.NET there was no issue with what would be commonly perceived as a correctly coded SQL statement.

Yet, in the stored procedure here, the lack of a semi-colon flags it as such.  When the semi-colon is added we still get the same error but for the SUSPEND statement, which does in fact have a semi-colon following it.  Why is that?

 The Selectable Procedure

Unlike most other major database engines, Firebird differentiates between a stored procedure that supports returning rows of data and one that provides modifications to one or more tables.  Though, for example, Microsoft’s SQL Server  provides a differentiation between such processes as that of a view and a procedure, this engine does not do so at the procedure level as Firebird does.

This differentiation in Firebird is performed by the SUSPEND statement, which is used to inform the Firebird engine that it is expected to return rows of data and as a result processes\flushes the collected results from the data internal buffer so that the calling client of the procedure can retrieve the data into either a data-reader, a data-set, or a db-manager’s query screen.

The question now is, if we have entered the proper PSQL syntax for this sample procedure, why then is it still yielding an error?

The reason is that when an error of this type is returned and the syntax appears correct, one has just run into the notorious poor error reporting in the Firebird Database Engine.

What this second semi-colon error is actually reporting is that there is something missing from the SELECT statement leading the Firebird error reporting sub-system to basically interpret the issue incorrectly.

The reason why this seemingly correct syntax does not work within the stored procedure construct is because with Firebird, a SELECT statement requires that the row data being returned be placed INTO internal buffers so that the SUSPEND statement can find the data and return it to the client.

We have to set up such buffers manually as a result of the fact that internally a SELECT statement within a stored procedure relies on the use of an internalized cursor to read through the data.  As a result, our SELECT statement will now be made more complex than necessary.  And even the Oracle Database Engine, with its high level of PL-SQL complexity, makes no such requirement of a simple query in a stored procedure.  And Oracle is heavily reliant on cursor processing as well.

In any event, to circumvent this issue we must create the necessary variables that will be used for the internal buffers and be returned to the client. This we do by creating OUTPUT variables that stored procedures in Firebird are indicated in the block of SQL code marked as RETURNS…

CREATE PROCEDURE SP_WORK_SIMPLE_SELECT
RETURNS
(
 PI_KEY BIGINT,
 PI_PARENT_KEY BIGINT,
 PI_ROOT_KEY BIGINT,
 PI_DEPTH INTEGER,
 PS_CATEGORY VARCHAR(500)
)
AS
BEGIN
 SELECT * FROM RI_CATEGORY_NODES
 
 SUSPEND;
END;

Now to get the data into these output buffers\variables we have to not only add a list of fields we want to return but the INTO clause list as well, telling Firebird where to put the returned data.  Now our modified procedure will look as follows…

CREATE PROCEDURE SP_WORK_SIMPLE_SELECT
RETURNS(
  PI_KEY BIGINT,
  PI_PARENT_KEY BIGINT,
  PI_ROOT_KEY BIGINT,
  PI_DEPTH INTEGER,
  PS_CATEGORY VARCHAR(500))
AS
BEGIN
    SELECT CN_KEY,
         CN_PARENT_KEY,
         CN_ROOT_KEY,
         CN_DEPTH,
         CN_CATEGORY
      FROM RI_CATEGORY_NODES
      INTO :PI_KEY,
            :PI_PARENT_KEY,
            :PI_ROOT_KEY,
            :PI_DEPTH,
            :PS_CATEGORY;
  
  SUSPEND;
END;

Two additional notes to be aware of…

• We add colons(:) as prefixes to our output buffer variables.  Though this is considered optional, it is still a good idea to get used to doing this.
• We add a semi-colon(;) to the end of the SELECT statement to ensure that it does not promote another ambiguous run-on syntax error as was done previously.

Testing A Selectable Stored Procedure

If you are used to just entering a stored procedure name in your favorite db-manager’s query or action-script screen you will be surprised to find out what happens when doing this with a db-manager for Firebird.  So let’s try that…

SP_WORK_SIMPLE_SELECT

Yields the following error…

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown – line 1, column 1.
SP_WORK_SIMPLE_SELECT.

As I only use EMS’ Firebird DB-Manager for all of my current Firebird database development, I cannot speak for how other Firebird db-managers will respond.  However, I have used Database.NET’s db-manager in the past and the responses were the same as was found with the EMS db-manager.  Some managers may assume you are entering a stored procedure name and fill in the rest for you. I believe the SQL Server DB-Managers are fairly powerful in this regard, though I cannot remember offhand.

To avoid any further confusion, there are two ways one can test their new procedure in a Firebird db-manager, the first being the following…

EXECUTE PROCEDURE SP_WORK_SIMPLE_SELECT

However, if we enter the above procedural call (which is in fact correct) we again land up with an error result as shown below…

Multiple rows in singleton select.
At procedure ‘SP_WORK_SIMPLE_SELECT’ line: 10, col: 5.

SQL Code: -811
IB Error Number: 335544652
Multiple rows in singleton select.
At procedure ‘SP_WORK_SIMPLE_SELECT’ line: 10, col: 5.

SQL Code: -811
IB Error Number: 335544652

Why???

If you are starting to feel slightly confused as to why such a simple SELECT statement that we initially started with has become so onerous to process once it is made to be part of a stored procedure than you are beginning to get a feeling why this database engine has never reached its popular potential without a document like this to help explain away such impediments to attempted development with this engine.

Just note that this type of error will be returned if the EXECUTE PROCEDURE statement is passed to Firebird’s ADO.NET provider for the same stored procedure.

But let’s get back to the construct that was previously mentioned that when returning rows of data, Firebird uses an internal, implied cursor to do this. As a result we have to accommodate for this within the stored procedure.  And we do this with Firebird’s PSQL construct of the “FOR DO” loop.

However the “FOR DO” loop isn’t actually a true looping construct as found with other versions of SQL code.  This construct actually means the following…

FOR

  Execute statement or block of statements (for retrieving row data)

DO

  Execute statement or block of statements for each record retrieved…

If you enter more than one statement either after the “FOR” part of the construct or the “DO” part, the statements must be enclosed within a BEGIN…END block construct.  However, in this case, since we are only concerned with a single SELECT statement, we only need do the following additions to our procedure…

CREATE PROCEDURE SP_WORK_SIMPLE_SELECT
RETURNS(
  PI_KEY BIGINT,
  PI_PARENT_KEY BIGINT,
  PI_ROOT_KEY BIGINT,
  PI_DEPTH INTEGER,
  PS_CATEGORY VARCHAR(500))
AS
BEGIN
	   FOR
      SELECT CN_KEY,
           CN_PARENT_KEY,
           CN_ROOT_KEY,
           CN_DEPTH,
           CN_CATEGORY
        FROM RI_CATEGORY_NODES
        INTO :PI_KEY,
             :PI_PARENT_KEY,
             :PI_ROOT_KEY,
             :PI_DEPTH,
             :PS_CATEGORY
  	DO
  	  SUSPEND;
END;

Now for one last bit of confusion…  Note that at the end of the SELECT statement we have removed the semi-colon(;).  This is because when contained within a “FOR…DO” construct an ending semi-colon(;) is not required.  If you leave it in, you will get the following error…

Note that the error is again about the semi-colon(;) but in this case the Firebird PSQL parser is claiming that it is an unknown token in the syntax.

If by this point you are starting to see the inconsistencies in how Firebird processes its PSQL and inline SQL db-manager calls, you would not be alone.

In any event let’s return to the original db-manager call to process our stored procedure and see what happens now that we have added a “FOR…DO” construct in it…

Another issue pops up as we are getting only a single row returned, though in the small table we are accessing actually has two rows.  Why?  This is because though one can execute a query procedure with an EXECUTE PROCEDURE call, the type of procedure it is expecting to process is an action procedure or the return of a single row of data (ie: scalar process), not one returning multiple rows of data.  So let’s try a better way to perform this procedure call…

SELECT * FROM SP_WORK_SIMPLE_SELECT

Remember earlier we called this type of procedure a selectable procedure?  And it is called such because we select data from it.  Now let’s see what happens when we use this method to call the procedure…

Now we get both rows returned as expected…

Interestingly enough, we can pass this same construct to the Firebird ADO.NET provider or we can simply pass the procedure name and get the same results as shown immediately above.  However, if we pass the entire construct with a SELECT to ADO.NET with parameters, the process will fail since ADO.NET will not be able to recognize the parameters as part of this construct.  As a result, if there are required parameters for a query\selectable procedure, simply pass the procedure name to ADO.NET along with the required parameter list.

On a final note about selectable stored procedures, be aware that the data being returned will reflected column names of the return variables that are set up within the procedure and not the actually column names defined in the tables being accessed.  This would be equivalent to as aliasing the column names within a SELECT statement…

SELECT CN_KEY AS KEY1,
       CN_PARENT_KEY,
       CN_ROOT_KEY,
       CN_DEPTH,
       CN_CATEGORY
	FROM RI_CATEGORY_NODES
    ORDER BY CN_DEPTH,
             CN_KEY

The results being…

At this point I have gone through a lot of the initial issues that many experienced newcomers to Firebird will most likely find in their initial forays into working with this database engine.  Once overcome, developers will find Firebird a very stable platform to build upon.  Now we can move on to a number of less idiosyncratic capabilities…

Firebird Views

One would question that with all the work that has gone into the development of selectable stored procedures, why would the Firebird development team continue to support a standard View construct.  With selectable stored procedures there is all the power to support complexities that Views would not necessarily have in them.  As a result, Views could be considered somewhat redundant to the Firebird internals.

Nonetheless, we have to remember that Firebird is a fork of the original Interbase engine, which most likely supported Views as well.  As a result, the Firebird project members rightfully saw to it that such an extension be supported in this newer version of Interbase.

Despite whatever reasoning was used to maintain the View construct, it does have its advantages in that they are very simple to create and deploy without the additional requirements of making such PSQL code meet the requirements for selectable stored procedures.  Thus, there is no need for capturing data into return variables through an INTO clause with the SELECT statement within a procedure.  There is also no requirement for the additional SUSPEND statement as well.

A Firebird View then is merely a way to set up data retrieval in a much less complex manner than that of a selectable stored procedure.

At its most basic, here is an example of a simple Firebird View.  Creating it, I entered the following PSQL code into my db-manager’s Create View screen…

SELECT * FROM RI_CATEGORY_NODES

However, the EMS Firebird DB-Manager took this code and modified it to contain the entire fields list and returned as the final, compiled PSQL the following…

CREATE VIEW VW_SIMPLE_VIEW(
  CN_KEY,
  CN_PARENT_KEY,
  CN_ROOT_KEY,
  CN_DEPTH,
  CN_CATEGORY)
AS
SELECT *
  FROM RI_CATEGORY_NODES;

Executing the View from a db-manager is done in the same fashion as with a selectable stored procedure…

SELECT * FROM VW_SIMPLE_VIEW;

And the results are as below (the two records found in the table)…

Notice in this case that the actual column names of the table are returned and not the return variable names that are found within a selectable stored procedure.

For additional notes on Firebird Views, please see the Addendum Notes at the end of this article…

The Insert Statement

With Firebird, the INSERT statement is relatively straight forward as shown with the simple insert stored procedure shown below…

CREATE PROCEDURE SP_INSERT_CAT_NODE(
  PI_PARENT_KEY BIGINT NOT NULL,
  PI_ROOT_KEY BIGINT NOT NULL,
  PI_DEPTH INTEGER NOT NULL,
  PS_CATEGORY VARCHAR(500) NOT NULL)
AS
BEGIN
  INSERT INTO RI_CATEGORY_NODES
  (
      CN_PARENT_KEY,
      CN_ROOT_KEY,
      CN_DEPTH,
      CN_CATEGORY
  )
  VALUES
  (
      :PI_PARENT_KEY,    
      :PI_ROOT_KEY,
      :PI_DEPTH,
      :PS_CATEGORY
  );
     
  SUSPEND;
END;

Notice the semi-colon at the end of the INSERT statement as well as prefix colon preceding the inputted values’ variables. And also be aware that the Firebird INSERT statement requires the INTO clause, where some engines, such as SQL Server, do not.

The SUSPEND statement was accidentally left in but since this procedure is not returning any data it will not have any effect on the processing.  The EMS Firebird DB-Manager places a SUSPEND statement in any new procedure when using the New Procedure option.  As a result, it can be safely removed making this procedure a formal action process.

Though the INSERT statement is used for the obvious addition of data, within a stored procedure it can also return a single row of data through the use of the additional RETURNING clause (see page 214 of the Firebird 2.5 Language Reference manual).  This clause allows for the return of values from the recently inserted row such as an auto-increment key.

However, the use of the RETURNING clause is limited to the insertion of a single row of data.  If an INSERT statement processes more than one data insert and contains a RETURNING clause, the inline SQL or the procedure will yield an error.

It should be noted, that unlike other major database engines (SQL Server, Oracle, MySQL), the use of the ADO.NET provider with Firebird will NOT return a Records Affected number with the INSERT, UPDATE, or DELETE statements (PostgreSQL has the same design constructs for its corresponding DML statements.). This is the reason that Firebird provides the RETURNING clause for these DML statements.

The Update Statement

Like the INSERT statement, the UPDATE statement is also fairly straightforward in Firebird and mirrors what an experienced database developer would expect from an engine’s SQL.  The simple example below demonstrates this…

CREATE PROCEDURE SP_UPDATE_CAT_NODE(
  PI_KEY BIGINT NOT NULL,
  PS_CATEGORY VARCHAR(500) NOT NULL)
AS
BEGIN
  UPDATE RI_CATEGORY_NODES
     SET CN_CATEGORY = :PS_CATEGORY
     WHERE CN_KEY = :PI_KEY;
END;

In this case, I removed the automatically inserted SUSPEND statement since there is no need for it as the procedure does not return any data..

The DELETE Statement

As with the preceding two statements just discussed, the DELETE statement follows fairly straightforward SQL protocols in Firebird as shown in the example below…

CREATE PROCEDURE SP_DELETE_CAT_NODES_BYKEY(
  PI_KEY BIGINT NOT NULL)
AS
BEGIN
    DELETE FROM RI_CATEGORY_NODES
    	WHERE CN_KEY = :PI_KEY;
END;

Also note that the Firebird DELETE statement requires the FROM clause where some engines don’t; again as with SQL Server.

Error Trapping

Firebird’s PSQL error trapping is not as nearly robust when compared to other database engines, which have implemented TRY…CATCH blocks. However, Firebird does in fact give you the ability to mirror the TRY…CATCH block to some degree as the following example stored procedure demonstrates.  Unlike the previous PSQL, which has been taken from my current work, the example below is taken from the Firebird 2.5 Language Reference manual…

CREATE PROCEDURE ADD_COUNTRY (
    ACountryName COUNTRYNAME,
    ACurrency VARCHAR(10) )
AS
BEGIN
  INSERT INTO country (country,
                       currency)
  VALUES (:ACountryName,
          :ACurrency);

  WHEN ANY DO
  BEGIN
    -- write an error in log
    IN AUTONOMOUS TRANSACTION DO
      INSERT INTO ERROR_LOG (PSQL_MODULE,
                             GDS_CODE,
                             SQL_CODE,
                             SQL_STATE)
      VALUES ('ADD_COUNTRY',
              GDSCODE,
              SQLCODE,
              SQLSTATE);
    -- Re-throw exception
    EXCEPTION;
  END
END

Basically, the WHEN ANY DO statement allows a procedure to trap any exception that is thrown during the primary task processing.  In the example above, the code provides a way to not only trap an exception but to log it as well as re-throw it back to the client so it can handle the result within the application interface.

The concept of re-throwing an exception may sound a bit odd but it is what in fact happens as the WHEN ANY DO statement catches the exception causing it to execute, which in turn allows the procedure to re-throw the exception back to the client.

Executing Scripts in Your DB-Manager

Working with single DML statements such as SELECT, INSERT, UPDATE, or DELETE tend to be fairly straightforward in most db-managers.  However, when the need arises to test out a script comprised of multiple statements, it can depend on the db-manager being used as well as the database engine as to whether or not this rather basic task is made relatively easy or not.

If I remember correctly as I haven’t used it in a while, Microsoft’s SQL Server Studio provides a single screen for both querying and action processes. EMS’ Firebird DB-Manager, like all of its db-managers, provides one screen for queries and another dedicated for action scripting.

If one were to use the corresponding EMS db-manager for SQL Server, entering T-SQL statements into the SQL Editor or the SQL Script Editor tool is fairly straightforward.  However, with Firebird, to run a script comprising of multiple statements, whether in SQL Editor or the SQL Script Editor requires that the engine is informed that it will be running a multiple statement script.

From what I have read on a cursory manner on this subject, it appears that the Firebird parser can make mistakes in its interpretation as to where one statement ends and another begins unless specific terminators are used.  And as we have seen, the use of the semi-colon(;) statement terminator isn’t always used under all circumstances making such interpretation based not only upon the semi-colon(;) but the implementation of BEGIN…END blocks that do not appear to use a semi-colon(;) for its own termination.

This seemingly inconsistency in the ways that the Firebird parser understands the PSQL being sent to it is what probably gave rise to the technique of running PSQL scripts in a db-manager either intentionally or unintentionally as a result of the internals passed on to it from its predecessor, Interbase.

Thus, to successfully run multiple statements of PSQL in a db-manager, one must initiate multiple statement scripts with the EXECUTE BLOCK…END construct.

For an example of the EXECUTE BLOCK…END with a selectable stored procedure that would run as a query, the following example uses the SELECT variable FROM STORED-PROCEDURE (PARAM) construct…

EXECUTE BLOCK
RETURNS (RS_STRING VARCHAR(200))
AS
    DECLARE MYVAR VARCHAR(10);
BEGIN    
	    MYVAR = '10';
    SELECT PS_OUT_STRING FROM SP_GET_VARIABLE(:MYVAR) INTO RS_STRING;
    
    SUSPEND;
END

The result is as expected…

Dealing With The Lack of a PRINT Statement

Some database engines and their corresponding db-managers provide for some type of inline technique that will return results to a console window within the manager.  To date, I have not been able to find a similar capability for Firebird.  However, there is a simple way to get around this lack of capability by writing a simple selectable stored procedure that will provide any calculated results with an EXECUTE BLOCK to be returned.  The code for this procedure is below…

CREATE PROCEDURE SP_GET_VARIABLE(
  PS_IN_STRING VARCHAR(200) NOT NULL)
RETURNS(
  PS_OUT_STRING VARCHAR(200) NOT NULL)
AS
BEGIN
  PS_OUT_STRING = :PS_IN_STRING;
  SUSPEND;
END;

This is by no means a perfect solution but it does work.  However, if you want to set up an inline SQL EXECUTE BLOCK with multiple statements that develop a final single result, this small procedure should do the trick for most circumstances.

If a reader has found a better technique, I would be happy to hear about it.

Firebird Functions

As mentioned in the Author’s Notes at the beginning of this paper, this writing is based upon my experience with the Firebird Embedded Edition (also called the Embedded Server) version 2.5.  The only support for user-defined functions (UDFs) is access to external libraries much like that which was pioneered by Oracle, Microsoft, and IBM with their database engines.

As a result of the powerful capabilities of Firebird stored procedures, it may have been believed that there was no need to add support for UDFs but whatever the reason was, UDFs are now supported in Firebird 3.0.x.

Complete notes on how to implement such functions within a Firebird database can be found in the Firebird 3.0.3 Release Notes (see the Documentation section at the Firebird web site above), authored by Helen Borrie.  She provides more than enough detail in easy to read technical documentation so that developing one’s own UDFs will not be a difficult endeavor.

Stored Functions, as they are called in Firebird terminology, can be both internal to a stored procedure as well as external, as is the common practice in most cases.

Sample External Function  (From the 3.0.3 Release Notes Document)

CREATE FUNCTION F(X INT) RETURNS INT
AS
BEGIN
RETURN X+1;
END;

Sample Internal\Sub Function  (From the 3.0.3 Release Notes Document)

--
-- Sub-function in EXECUTE BLOCK
--
EXECUTE BLOCK RETURNS (N INT)
AS
DECLARE FUNCTION F(X INT) RETURNS INT
AS
BEGIN
RETURN X+1;
END

-- block process statements
BEGIN
N = F(5);
SUSPEND;
END

--
-- Sub-function inside a stored function 
-- (same for a stored procedure, trigger, etc.)
--
CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
RETURNS INTEGER
AS
DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
RETURNS INTEGER
AS
BEGIN
RETURN n1 + n2;
END

-- function process statements
BEGIN
RETURN SUBFUNC(n1, n2);
END

Please note that in Firebird terminology an internal function is also called a sub-function.

Common Table Expressions (CTEs)

Common Table Expressions or CTEs are commonly used for processes that may require recursion to gather data for such requirements as processing data in a hierarchical storage format..  Happily, Firebird provides this ability to the same level as is found in Microsoft’s SQL Server making this rather complex extension easily understood for SQL Server developers and users of the other major database engines.

The example below is practically a direct copy of the working CTE I had developed for my current project when it was to work under the SQL Server LocalDB Database Engine.

CREATE PROCEDURE SP_GET_CAT_CHILD_NODES_BYKEY(
  PI_KEY_IN BIGINT NOT NULL)
RETURNS(
  PI_KEY_OUT BIGINT,
  PI_PARENT_KEY_OUT BIGINT)
AS
BEGIN
  FOR
    WITH RECURSIVE HIERARCHY_TABLE AS
    (
        SELECT RCN1.CN_KEY, 
               RCN1.CN_PARENT_KEY
            FROM RI_CATEGORY_NODES RCN1
            WHERE RCN1.CN_KEY = :PI_KEY_IN
            UNION ALL
                SELECT RCN2.CN_KEY, 
                       RCN2.CN_PARENT_KEY
                    FROM RI_CATEGORY_NODES RCN2
                    JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY
    )
    SELECT CN_KEY, 
           CN_PARENT_KEY
        FROM HIERARCHY_TABLE
        INTO :PI_KEY_OUT,
             :PI_PARENT_KEY_OUT
  DO
    BEGIN
      IF (PI_KEY_IN = 0) THEN
        EXCEPTION ROOT_CAT_NODE_DELETE; 
          
      SUSPEND;
    END  	
END;

The PSQL code above only differs in the need to attribute Firebird PSQL conventions to this type of processing.  For example, the key-word RECURSIVE is applied to the WITH statement.  The  other expected differences would be the RETURNS field list as well as the Firebird FOR…DO construct, which in this case is required for the return of multiple data rows.

I also added some very limited error trapping, which only ensures that if a zero-key is passed into this PSQL module it will throw an exception (which is predefined as a user-defined Firebird exception).  However, probably a better way to incorporate this would be to replace my error trapping with the previously described WHEN ANY…DO error trapping construct.

Finally, note that the SUSPEND statement is part of the DO block of code so that it is performed for every returned data row to the client.

Firebird Sequences/Generators

Firebird sequences or generators, as they are called, are mostly aligned with the Oracle database, which has a similar construct.

However, unlike Oracle where the internal code is more or less hidden from the developer, with Firebird, a sequence\generator is created as part of a trigger for a table definition when defining a field within that table as an AUTO-INCREMENT field.  This trigger’s code is provided in the table’s DDL scripts,which can be updated directly by the developer if need be.  A table definition with a sequence\generator can be found below.

CREATE TABLE RI_CATEGORY_NODES (
  CN_KEY BIGINT NOT NULL,
  CN_PARENT_KEY BIGINT NOT NULL,
  CN_ROOT_KEY BIGINT NOT NULL,
  CN_DEPTH INTEGER NOT NULL,
  CN_CATEGORY VARCHAR(500) NOT NULL);


SET TERM ^ ;

CREATE TRIGGER BI_RI_CATEGORY_NODES_CN_KEY FOR RI_CATEGORY_NODES
ACTIVE BEFORE 
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.CN_KEY IS NULL) THEN
      NEW.CN_KEY = GEN_ID(RI_CATEGORY_NODES_CN_KEY_GEN, 1);
END^

SET TERM ; ^

Note the sequence name in the DDL code above highlighted in blue implemented in a trigger created by the EMS Firebird DB-Manager for the table field that was selected to be an AUTO-INCREMENT field.

The actual sequence\generator, which provides the trigger with the ability to increment the table’s auto-increment field is also defined as a separate module, which is compiled into the Firebird’s database section, entitled, “Generators”, which will be listed as a separate programmable entity in a Firebird DB-Manager.

Conversely, developers can define their own generators without selecting an AUTO-INCREMENT field within a table definition..

An interesting experiment would be to see if Firebird sequences\generators could be used to increment a count in a stored procedure or function. Since I have not attempted such an implementation, I cannot say whether it work or not.

Firebird Dynamic PSQL

Like all major database engines, Firebird provides the ability to incorporate dynamic PSQL for executable code block within a db-manager or directly within a stored procedure, function, or even a trigger.

Below is a sample of a working stored procedure that implements Dynamic PSQL that modifies the way Firebird stores dates in order to successfully compare a retrieved record’s date to that of one that is inputted as a parameter.

CREATE PROCEDURE SP_GET_MSGLOG_DISTINCT_DATES(
  PS_DATE_IN VARCHAR(10) NOT NULL)
RETURNS(
  PS_DATE_OUT VARCHAR(10) NOT NULL)
AS
DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL;
BEGIN
  PS_SQL = 'SELECT DISTINCT';
  PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG';
  
  PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM ML_CREATE_DATE) AS 
VARCHAR(2)))' || '/';
  PS_SQL = PS_SQL || '       ' || '(TRIM(CAST(EXTRACT(DAY FROM ML_CREATE_DATE) AS 
VARCHAR(2)))' || '/';
  PS_SQL = PS_SQL || '       ' || '(TRIM(CAST(EXTRACT(YEAR FROM ML_CREATE_DATE) AS 
VARCHAR(4)))) = ' || :PS_DATE_IN;  
  
  FOR
  	  EXECUTE STATEMENT (PS_SQL) INTO :PS_DATE_OUT
  DO	
    BEGIN
  	    SUSPEND;  
    END
END;

Conclusion

As I found working with Firebird, it is not the most intuitive database engine available for transferring one’s current experience with coding SQL modules.  When learning Oracle, I found that learning to adopt to packages and the recommended use of cursors (which with SQL Server is not the preferred way to do such processing, though it is now substantially optimized for greater performance), were the only significant upgrades to a fairly extensive knowledge base on my part.  MySQL, for all intents and purposes had minor issues to deal with considering that SQL Server’s T-SQL and MySQL’s version of SQL were quite similar.  Nonetheless, Sybase’s SQL Anywhere Database Engine was the most convenient database to work with when coming from an extensive SQL Server background.

At first glance, Firebird appears to be a database engine that can be easily adopted if one comes to it with extensive SQL experience.  However, the many substantial differences both the few major as well as the mostly minor ones can be enough to frustrate any developer who is trying to apply their previous knowledge and experience to learning it rapidly.

This article was designed with the express purpose of aiding experienced database development professionals who want to tackle Firebird without the many “gotchas” that I experienced.

It is true that I did not cover every aspect of the database engine (ie. triggers).  However, there should be enough here to get one started successfully, making their use of the Firebird Database Engine a pleasant and enjoyable experience.

Hopefully, this article will also provide some impetus to growing the popularity of this highly efficient, small footprint database engine…

 

 

Addendum Notes

• For an in-depth discussion on the three server modes of the Firebird Database Engine, please see the relevant section in the commercially released Firebird Migration Guide at the following link…  Firebird Migration Guide link
• Karol Bieniaszewski, from the Firebird Developers Community has added the following information regarding Firebird Views…

Views can be optimized by Firebird, compared to selectable stored procedures, if you join with it with other tables or other views(with one exception if view contain order by).

Firebird Views can also implement Triggers to make them editable or take different actions
(see… Firebird Views & Triggers)

It is simpler to manage privileges with Views when compared to Stored Procedures.