|Last modified: 14-11-2012|
When connecting to a remote FB server with Delphi + ZeosLib, since ZConnection has a Hostname property, do I need to specify the hostname in the connection string? servername:/filesystem-path/database-file
How to add a trigger to the server so that it will autoincrement ID's in a table?
Firebird is a fork (followed by a rewrite in C++) from the Interbase 6.0 code base that was open-sourced by Borland in 2000 but not followed upon by Borland. More on the history of Interbase here and here.
By default, the server listens on TCP3050. The network protocol is pretty chatty, so when accessing the server through the Net, you might want to use some middleware, write a Web-based app, use web services (SOAP, XMLRPC, etc.), or try Firebird 2.x.
The main files that make up Firebird:
Here are the files that make up Firebird:
To install the server, it is recommended that you run the installer instead of copying an existing install from another host. Once the installer is done, and you are running NT/W2K/XP, you should have two services registered: "Firebird Guardian Service", and "Firebird Server". Some applets are available that install in Control Panel to offer an easier access to the Firebird server.
To check that the server is running, go to Firebird's bin/ directory and run the CLI client isql.exe using the example database:
c:\Program Files\Firebird\Firebird_2_1\bin>isql.exe localhost:C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB -u sysdba -pas masterkey
SQL > SHOW TABLE country;
SQL > SELECT * FROM country;
SQL > QUIT;
The default option only installs the server. Rerun the installer, and choose "Installation of Client tools for Developers and database administrators".
To install the client, run the installer, and select "CLIENT ONLY". As of May 2004, there is no setup program to install just the client.
The default administrative account is sysdba, with password masterkey (masterke, actually, since passwords are eight-characters long). Change this ASAP using the gsec.exe command-line utility ("gsec -user sysdba -password masterkey"; once in the shell, "modify sysdba -pw mynewpassword", followed by "quit").
A test database ./examples/employee.gdb is available. If you are running the server under XP or ME, rename the extension from gdb to fdb (more information on this issue in the release notes.)
To connect to a Firebird database using a command-line interface, use ./bin/isql ("interactive SQL utility"). Once in the shell, run CONNECT "C:\FIREBIRD\EXAMPLES\EMPLOYEE.GBD" user 'SYSDBA' password 'masterkey'; . If you forget to end a statement with a semi-colon, the prompt changes to CON>. Just type the missing semi-colon to get back to the SQL prompt. To check that it's working, type SELECT * FROM RDB$RELATIONS; which lists all the rows in the system database (The identifiers for the metadata of a database begin with RDB$. Do not edit the metadata yourself.) End the session with QUIT; .
To create a database, launch isql, and type the following: CREATE DATABASE 'C:\FIREBIRD\TEST.FDB' page_size 8192 'SYSDBA' password 'masterkey'; .
The Windows Embedded Server is a Superserver engine plus client rolled into one library, called fbembed.dll. It is available as a separate download package. The embedded server (introduced with Firebird 1.5) was specifically designed to facilitate deployment of applications that ship with a Firebird server included. Installation is just a matter of unpacking the DLL and a few other files to the desired location. The security database is not used at all: anyone can connect to any database, as long as the user running the application has filesystem-level access rights to the database(s) in question. The embedded server has no facility to accept any network connections. Only true local access is possible, with a connect string that doesn't contain a host name (not even localhost). In Firebird 2.0, use of the embedded server as a network client is no longer possible.
Summary of the Connectivity Approaches:
A list of drivers that connect directly to FB (no ADO, ODBC) can be found here:
Here's how to install the ZeosLib and configure it to work with Delphi2007:
Unlike MySQL, Firebird requires specifying the full path to the database file
And unlike MySQL again, the connection and the database selection occur in one step.
As an example of a trigger, you can use a generator to create a unique index in a table. Many tables use a unique index as a primary key. InterBase doesn't have an AutoInc field. Because multiple clients cannot generate unique identifiers, you can rely on the server to do this. Almost all SQL servers offer a counter you can call to ask for a new ID, which you should later use for the table. InterBase calls these automatic counters generators.
(How to create an autoincrement column?) "Firebird does support autoincrement columns via BEFORE INSERT TRIGGERs and GENERATORs. Generators are also named SEQUENCES in Firebird 2.0 and above - and are compliant to the SQL standard."
A generator can either be called with each INSERT, or be located in a trigger in the server:
Finally, Firebird stores the entire database in a single file, which has default extension .gdb.
Here's how to use the ZeosLib components to connect to the FB server, create a database, create a table, add a couple of records, SELECT them into a dataset with a query, attach this dataset to a datasource, connect this datasource to a NextDBGrid data-aware grid, let the user make changes through the grid, and save those changes back to the database with UpdateSQL:
Note: As of May 2009, ZeosLib seems like a bit of a messy project, so if you want to save time, you might want to check out commercial alternatives such as IBDAC:
Here's a version of fbdll4vb.dll using the fbembed.dll. Please note that the fbembed.dll and it's relative file are in the same directory of the vb projects.
Classic architecture, the design in InterBase 4.0 and earlier, was process-based. For every client connection, a separate server process was started to execute the database engine, and each server process had a dedicated database cache. The server processes contended for access to the database, so a Lock Manager subsystem was required to arbitrate and synchronize concurrent page access among the processes. If you have only a few connections, the Classic Server is fine, and uses less ressources than the newer, Super Server architecture.
More recently a multi-threaded architecture has been implemented where all of the separate server tasks share the same address space, and where possible multiple threads can be used in a single client request. In this mode the server runs as a privileged user (usually as either a root/firebird/interbase or interbas user) and only that privileged user needs to have access to the Firebird/Interbase system files.
SuperServer is a multi-client, multi-threaded implementation of the InterBase server process. This implementation replaces the "Classic" implementation used for previous versions of InterBase.
The Classic implementation predates the SuperServer implementation, and the SuperServer implementation is the future of InterBase.
Classic configuration is used on operating systems that currently don't have the technology for threaded applications, which is required for SuperServer. InterBase also distributes the Classic version on platforms that have threading technology, but which benefit from the low-profile implementation.
SuperServer has a greater ability to meet the demands of a growing multi-user system, while retaining good performance and efficiency. SuperServer is implemented in InterBase product on all platforms where it is technically practical. It is the intention that SuperServer is the future direction of InterBase on all platforms.
Embedded applications work by accessing API-like functions via internal macro calls. You use a specialised form of SQL (called ESQL) to code static queries into your source. You pass your sources through a precompiler, which parses out the ESQL and replaces it with host code for onward compilation with your other sources to generate your embedded server application object.
The name of the precompiler is gpre - you will find it in the /bin directory, along with the other tools. The bad news for you is that it works only with C/C++ and Cobol as host languages - no VB, not even plain ol' BASIC.
You could try writing your own precompiler for VB. AFAIK, the gpre sources are in the Firebird tree, in the gpre branch. At the following link you will find some function and migration specs for gpre:
You should also be able to find the Embedded SQL Guide amongst the IB6 beta docs set at the InterBase Downloads page of the same site.
Firebird 1.5 Embedded Server notes (Beta and RC builds)
1. GENERIC INFORMATION
The embedded server is a fully functional server linked as a dynamic library (fbembed.dll). It has exactly the same features as the usual server and exports the standard Firebird API entrypoints.
2. ISSUES AND LIMITATIONS
The Firebird registry entries are ignored. The root directory of the embedded server is the directory of its binary file (library).
2.2. Database access
Only true local access is allowed. The embedded server doesn't have any support for remote protocols, so even access via "localhost" won't work.
2.3. Authentication and security
The security database (namely security.fdb) is not used in the embedded server and hence is not required. Any user is able to attach to any database. Since both the server and the client run in the same address space, the security becomes just an agreement between both sides which can be easily compromised.
But note that SQL privileges are still checked.
You may run any number of applications with the embedded server without any conflicts. Having IB/FB server running is not a problem either.
But you should be aware that you cannot access single database from a number of the embedded servers simultaneously, because they have SuperServer architecture and hence exclusively lock attached databases.
Just copy fbembed.dll into the directory with your application. Then rename it to either fbclient.dll or gds32.dll depending on your database connectivity software. Then start your application and it will use the embedded server as a client library and will be able to access local datasases. You should also copy firebird.msg and firebird.conf (if necessary) to the same directory.
If external libraries are required for your application, then you should have them separately. Most probably, it will be INTL support (fbintl.dll) or UDF libraries. To be able to use them, you should place them into the directory tree which emulates the Firebird server one, i.e. has subdirectories like /intl or /udf. Then open your firebird.conf and set RootDirectory to the aforementioned directory tree. Example:
RootDirectory = /my_app/fb
InterBase and MySQL, a Technical Comparison by Bill Todd
drop table mytable;
"Firebird doesn't provide a way to create database using SQL. You need to either use the Services API, or external tool. As API for database creation is often not available in libraries, you can call Firebird's bin\isql.exe tool to do it for you", eg.
CREATE DATABASE 'C:\dbases\database.fdb' user 'SYSDBA' password 'masterkey';
Note: In Windows at least, the filename is in capital letters.
Strangely enough, Firebird requires telling the server the absolute path to the database file, eg.
where "sofa" is the hostname.
In addition, connecting to a database requires a user/password, and be granted permissions to work with whatever the database contains (tables, indexes, etc.)
Helen Borrie's "The Firebird Book: a Guide for Database Developers" (2004).
A rewrite in C++ of 1.0, which was written in C.
Three kinds. If you are not familiar with them, go for dialect #3.
Where the client application access the .gdb/fdb files directly instead of through the Firebird server.
Don't even think about it. Synchronous writes are good for you.
Take a look at IBReplicator.
You can purchase support from IBPhoenix
IBObjects data access components for InterBase® & Firebird. InterBase Objects
Not long after the release of Delphi 2, Jason discovered that the best capabilities of InterBase were virtually inaccessible to the RAD developer because of the constraints imposed by the BDE. In 1996 he determined to start his own suite of components and data-aware GUI controls to bypass the BDE and work directly with the InterBase API. He chose to develop a data access hierarchy that was independent of the TDataset and the standard Delphi data-aware visual controls, thus affording himself the opportunity to design a library of components and visual controls specifically for the client/server architecture, which could be fully customized through succeeding releases of InterBase.
Performance (IBO vs BDE)
Is direct access to InterBase's Application Programming Interface (API) better than using the BDE and, if so, why?
Borland Database Engine (BDE)
The BDE has a life stream that goes back many years before the advent of RAD tools, predating even 16-bit Windows by some years. It first came to prominence as the engine behind Paradox 3.5 and Quattro Pro, was modified a little later to provide an API for DBaseIII-Plus, FoxBase (later DBaseIV and FoxPro) and some other ISAM databases, as well as an API to SQLLinks pass-through drivers for several SQL engines in the pre-Windows era. It moved onto the Windows platform with the first, unhappy, premature Paradox for Windows release. A little after that, at version 3.something, it redeemed its glorious DOS reputation with a very fast, stable release accompanying the 16-bit Paradox 5 for Windows.
From Delphi 1 forward, the BDE has continued to grow as an API layer between the RAD platform and native or open drivers for an increasing number of database platforms. Although it has not been free of aches and pains, it has had a significant part to play in the success of Delphi in the database arena over the past five years.
Several factors about a BDE implementation inhibit the developer from optimizing InterBase for performance and effectiveness. The BDE-plus-driver combination adds two layers of interface code to slow down the connection between the client application and InterBase's own API layer.
Performance (IBX vs BDE) Converting a BDE Application to IBX
Two major suites of Delphi database components are available for InterBase - the InterBase Express set (IBX), that shipped as a Beta with Delphi 5, and the IB Objects set (IBO), which has been in production releases since 1997
Borland Delphi and C++Builder developers can now use the InterBase Express (IBX) components to build application with InterBase through the InterBase API, improving performance and capabilities.
During the period when Delphi 3 and 4 were the current releases, Greg Deatz and a group of helpers and testers developed the FreeIBComponents suite of data access classes, including datasets inheriting from the native VCL's TDataset, for connecting directly to the InterBase API.
Towards the end of 1998, with Delphi 5 on the drawing board, Borland/Inprise decided to license the rights to the FreeIB suite's data access classes and develop them to ship as components of the new version's VCL. The somewhat transformed FreeIB became part of the "Kinobi" project for InterBase 6. The developer was Ravi Kumar. A beta of the components, renamed InterBase Express, shipped with Delphi 5 in the Fall of 19
The InterBase server implements a Multi-Generational Architecture [MGA]. This MGA provides unique 'versioning' capabilities that result in high data availability for transaction processing users and decision-support users simultaneously.
Traditional database servers support the On-Line Transaction Processing (OLTP) model of database interaction, with many short, simple transactions. The InterBase MGA engine performs well on short OLTP-style transactions, but it excels in real world applications, outperforming other databases because concurrent long-duration, decision-support transactions do not degrade its performance.
User-Defined Functions (UDFs) are libraries of functions that you can add to extend the set of functions that the InterBase server supports. The functions in your UDF library execute within the process context of the InterBase server.
A user defined function (UDF) in InterBase is merely a function written in any programming language that is compiled into a shared library. Under Windows platforms, shared libraries are commonly referred to as dynamic link libraries (DLL's).
The client component is just the library that surfaces the Application Programming Interface function structures to a client application, allowing it to connect to and communicate with the server.
Firebird has left InterClient and Interserver alone, because the amount of effort required to get it working and up to date with latest standards just wasn't considered worth it. Instead, a Client-Java project branch formed and has spent two years developing a totally new JDBC Type 4 driver. The driver is named JBird - pronounced "Jaybird".
Borland still markets its commercial InterClient 2 products.
There's no such thing as FBX. Borland still develops and markets the IBX components for its InterBase products and some versions are available free from their CodeCentral repository.
Server = ibserver OR fbserver.exe
Client = gds32.dll (if > 1.5, add fb32.dll or fbclient.dll)
Guardian = process watcher
Default accountr = sysdba/masterkey
To change password, gsec.exe -user sysdba -passwd mypass ; gsec modify sysdba -pw test
sample = employee.gdb
ibexpress = stuff for Delphi
isc4.gdb = security DB
isql.exe and wisql32.exe = command-line clients
either direct api calls, or through sql -> gpre
Different ways to access : BDE (middleware), embedded SQL, native API, ODBC, Java
Since 2K and XP can restart services that died, Guardian is not strictly necessary.
Does the server require running an installer, or is there just a single EXE like the Fossil SCM?
... so as to avoid hard-coding the IP/name or prompting the user for it.