Firebird Documentation IndexUsing FirebirdAbout Clients and Servers → Application development
Firebird Home Firebird Home Prev: Firebird Embedded ServerFirebird Documentation IndexUp: About Clients and ServersNext: Server-side programming

Application development

Embedded SQL in Firebird applications
Predefined vs. dynamic queries
RAD environments and component suites
Other connectivity components and drivers
API applications

Once a database has been created and populated, its information can be accessed through a client application. Some applications – such as the Firebird isql tool, EMS SQL Manager, IB_SQL, IBAccess, Database Workbench, FlameRobin and IBOConsole – provide the capability to query data interactively and to create new metadata.

Any application developed as a user interface to one or more Firebird databases will use the SQL query language, both to define the sets of data that can be stored and to pass requests to the server about rows it wants to update, insert into or delete from. SQL statements also convey the values which the application wants to be applied to those rows.

Firebird implements a set of SQL syntaxes which have a high degree of compliance with the recognised SQL-92 and SQL-99 standards. The Firebird API provides complete structures for packaging SQL statements and the associated parameters, as well as for receiving the results.

Embedded SQL in Firebird applications

Firebird provides the capability to embed SQL statements in applications written in C/C++ and some other programming languages. The code is then passed through gpre, the pre-processor, which substitutes the embedded SQL statements with equivalent host language code that calls functions in Firebird's client API library. The gpre pre-processor generates a file that the host language compiler can compile.

A special, extra subset of SQL-like source commands is available for this style of application, which are pre-processed into internal macro calls to the API. Known as Embedded SQL (ESQL), it provides a simpler, high-level language syntax for the programmer, that gpre can interpret and re-code according to the more complex structure of the equivalent API calls.

Note

The InterBase Embedded SQL Guide (http://www.ibphoenix.com/downloads/60EmbedSQL.zip) provides extensive documentation on this subject.

Predefined vs. dynamic queries

Some queries have to be run in exactly the same form every time they are needed. Queries like this are good candidates for embedding in the host language and pre-processing by gpre. The pre-processor turns them into API function calls, giving a somewhat better performance than SQL that has to be interpreted at runtime.

But many applications need to build queries that are at least partially dependent on information provided by the user – freely entered in a text box, or selected from a list of options. This is called Dynamic SQL or DSQL; that is, SQL code whose form is not (or not exactly) known at design time. DSQL can be embedded and preprocessed too, but some additional requirements and restrictions apply. More on this – again – in the InterBase Embedded SQL Guide.

Delphi and C++ data access components provide properties and methods to analyse and parse DSQL request statements and manage the results passed back. Applications that use ODBC or other generic interfaces always work with DSQL statements, even if the user doesn't always see them. Query-by-example and other visual query tools for instance provide the user with a convenient, easy to use, and often “SQL-free” interface to extract, modify or delete data from the database. Yet the underlying code translates the user's input into DSQL statements, which are subsequently passed to the ODBC (or other) layer.

Component interfaces provide methods and properties for building and preparing SQL template statements, allowing you to use placeholders for value criteria. At run-time, the application supplies input parameters of the appropriate data type to complete the statement. Provision is made also for retrieving output parameters from statements that return results after they are executed.

Note

Of course the use of data access components isn't limited to dynamic SQL. You can also store static SQL strings – known at design time – in them.

RAD environments and component suites

With the rise of rapid application development (RAD) tools in the past decade, the encapsulation of the API functions in suites of components presents a variety of attractive application development options for Firebird developers.

The Borland Database Engine (BDE)

Borland markets “enterprise versions” of a number of integrated development tools – Delphi, Kylix, C++Builder, JBuilder and some older products – which can use the proprietary Borland Database Engine and native SQL Links InterBase drivers as a “black box” middleware layer to make InterBase and, latterly, Firebird databases behave like desktop databases. BDE version 5.2 and its associated InterBase driver, which first shipped with Delphi 6E, supports both Firebird and InterBase version 6, although it has known bugs affecting the Dialect 3 date and time data types.

Because the BDE's purpose is to surface a generic, database-vendor-independent, client-centered data access layer to the IDE tools, it flattens out the differences between a wide range of different database systems. Hence, it limits the capability of applications to exploit the best features of Firebird, particularly multiple transactions per connection, control of transaction aging and concurrency control.

The BDE can be useful where you need to develop an application that might be used with a choice of back-ends, of which Firebird is only one. Be warned however that people have reported problems with Firebird database access via the BDE, and these are likely to increase in number and severity as Firebird continues to move further away from InterBase.

SQLDirect

SQLDirect is a shareware, lightweight replacement for the BDE. It supports Firebird at least up to and including version 1.5. Visit http://www.sqldirect-soft.com for more information and free trial versions.

DBExpress and Datasnap

DBExpress and Datasnap were introduced in later versions of the Borland tools to provide alternative generic interfaces to databases. They replace the BDE by moving its functionality into expanded native drivers for supported databases. Like the BDE, they do not support multiple concurrent transactions. They are of especial use where a data interface is required that is independent of the idiosyncrasies of different database management systems. The InterBase native drivers should provide adequate support for Firebird databases where optimising client/server performance is not high among the objectives.

Direct-to-API Components

In response to the shortcomings of the BDE, a number of component suites have become available for Delphi and Borland C++Builder that bypass the BDE layer completely and encapsulate the Firebird API directly:

IBObjects

IB Objects is a rich set of visual and non-visual database components that has been stable since 1997. It offers two BDE-free suites for data access; one compatible with the native Delphi and C++ Builder TDatasource and visual controls, the other completely independent of the Delphi data access architecture and supplied with its own visual controls.

http://www.ibobjects.com

FIBPlus

FIBPlus is another well-known and stable suite for Delphi and BCB. Developed from Gregory Deatz's FreeIBComponents suite, it offers a connectivity based on TDataset. It doesn't include any visual components, but it can work perfectly together with the Borland visual database classes, as well as with third-party visual data-aware components.

http://www.devrace.com/en/fibplus/

ZeosLib

ZeosLib is a set of free, open-source database connectivity components for Delphi, FreePascal/Lazarus, Kylix and C++ Builder. It supports a number of database systems, including Firebird. Because the ZeosLib components are based on TDataset, you can use them together with the Borland visual database controls.

http://zeos.firmos.at/portal.php

IBX (InterBase Express)

IBX was also developed from the FreeIBComponents. Its TDataset-based data access components were purchased for developing as a proprietary product by Borland. Components encapsulating the new Services API were added. It was left unfinished in 1999. The IBX source code was opened under the InterBase Public License in 2000 and it continues to be developed as an open source project. Borland distributes versions of IBX with some Delphi, Kylix and C++ Builder versions.

Caution

Since InterBase and Firebird are diverging more and more, and Borland has (quite understandably) no intention to keep IBX Firebird-compatible, you should probably not use it with Firebird versions 1.5 and higher (although most features will still be supported).

UIB (Unified InterBase)

This is a set of non-visual components for Delphi, BCB, Kylix, Lazarus and FPC, supporting Firebird, InterBase and Yaffil. A ready-to-use SQL monitor (Windows only) is also available:

http://www.progdigy.com/modules.php?name=UIB

The UIB components are also contained in the JEDI Visual Component Library (JVCL):

http://homepages.borland.com/jedi/jvcl/

Both UIB and the JVCL are freely available open-source products.

Other connectivity components and drivers

Microsoft “open connectivity

Third-party ODBC and OLE-DB drivers are available for Windows programmers using Microsoft and other vendors' programming tools. The Firebird ODBC driver development page is at http://www.firebirdsql.org/index.php?op=devel&sub=odbc.

Java

A pure Java Type 4 JDBC driver called Jaybird is developed within the Firebird project. Jaybird is compliant with both the new JCA standard for application server connections to enterprise information systems and the established JDBC standard.

Note

Firebird has abandoned re-implementation of the Borland InterClient and Interserver platform-independent client layers.

Official documentation and download links for Jaybird are at http://jaybirdwiki.firebirdsql.org.

.NET

The Firebird ADO.NET Data Provider, developed as a Firebird subproject, re-implements the client API functions in C#. Consequently, .NET developers only need the data provider to talk to a Firebird server; there's no need to install a regular Firebird client. Home page: http://www.firebirdsql.org/index.php?op=devel&sub=netprovider.

API applications

The Firebird client program supports two discrete application programming interface (API) modules. The most important is the core API, through which all database work is performed. A much smaller API module, the Services API, provides functions for accessing various command-line and other utilities from application programs. Developers can write high-level programming or script language applications that populate the data structures and call the API functions directly.

The Firebird core API

Programmers who want to use the core API have to write code for allocating and populating the data structures that provide the communication layer between the client library and the server. Interactive SQL clients, component interfaces and embedded SQL “hide” these structures from the programmer by encapsulating them in their own higher level interfaces. Writing code that calls the API functions directly can be more powerful and flexible, with the following benefits:

  • Memory allocation control

  • No precompiling necessary

  • Access to transaction handles and options

  • Full access to error messages

Core API function categories

Based on their operation targets, we can divide the API functions into the following categories:

  • Database connection control

  • Transaction control

  • Statement execution

  • Blob functions

  • Array functions

  • Security functions

  • Informational functions

  • Type conversions

The Services API

The opened InterBase 6.0 code from which Firebird was developed surfaced for the first time an application programming interface (API) providing a function call interface to certain server activities such as backup/restore, statistics and user management. Many of these calls provide programming interfaces to the code in the command-line tools. A few lower-level server functions are included as well, some of which overlap functions already available in the core API.

Important

Before Firebird 1.5, the Services API was only available with Firebird Superserver. Support for the entire Services API in Classic Server versions was completed in Firebird 1.5.1.

Borland's InterBase Express (IBX) components include a subset – known as the Service components – encapsulating access to services API calls from some versions of their Delphi, Kylix and C++Builder development environments. Be aware however that IBX does not officially support Firebird. The higher your Firebird version, the more chance of incompatibilities and errors if you use IBX.

IBPP

IBPP is a C++ interface to the Firebird API. It is a class library written in “pure” C++ and hence not dependent on any specific programming environment, component set or even OS. You can use it anywhere you want Firebird connectivity in your C++ programs.

IBPP was created and is maintained independently of the Firebird project. It is available for free and comes with its own very liberal license. The IBPP home page is at http://www.ibpp.org.

Prev: Firebird Embedded ServerFirebird Documentation IndexUp: About Clients and ServersNext: Server-side programming
Firebird Documentation IndexUsing FirebirdAbout Clients and Servers → Application development