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.
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.
The InterBase Embedded SQL Guide (http://www.ibphoenix.com/downloads/60EmbedSQL.zip) provides extensive documentation on this subject.
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.
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.
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.
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 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 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.
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:
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.
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.
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.
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.
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:
The UIB components are also contained in the JEDI Visual Component Library (JVCL):
Both UIB and the JVCL are freely available open-source products.