Firebird Documentation IndexUsing FirebirdAbout Clients and Servers → Server-side programming
Firebird Home Firebird Home Prev: Application developmentFirebird Documentation IndexUp: About Clients and ServersNext: Multi-database applications

Server-side programming

Stored procedures
Triggers
PSQL limitations
User-defined functions

Among Firebird's powerful features for dynamic client/server application programming is its capability to precompile source code on the server, storing the object code right inside the database in most cases. Such procedures and functions are executed completely on the server, optionally returning values or data sets to the client application. Firebird provides three styles of server-side programming capability: stored procedures, triggers and user-defined functions (UDFs).

Stored procedures

Firebird's procedural language (PSQL) implements extensions to its SQL language, providing conditional logic, flow control structures, exception handling (both built-in and user-defined), local variables, an event mechanism and the capability to accept input arguments of almost any type supported by Firebird. It implements a powerful flow control structure for processing cursors which can output a dataset directly to client memory without the need to create temporary tables. Such procedures are called from the client with a SELECT statement and are known to developers as selectable stored procedures. Procedures that don't return a dataset (although they may return result variables) are called executable stored procedures; they are called with EXECUTE PROCEDURE.

Stored procedures can call other stored procedures and can be recursive. All stored procedure execution, including selection of data sets from procedures and embedded calls to other procedures, is under the control of the single transaction that calls it. Accordingly, the work of a stored procedure call will be cancelled totally if the client rolls back the transaction.

Triggers

Triggers are special procedures created for specific tables, for automatic execution during the process of committing DML work to the server. Any table can have any number of triggers to be executed before or after inserts, updates and deletions. Execution order is determined by a position parameter in the trigger's declaration. Triggers have some PSQL extensions not available to regular stored procedures or to dynamic SQL, most notably the context variables OLD and NEW which, when prefixed to a column identifier, provide references to the existing and requested new values of the column. Triggers can call stored procedures, but not other triggers.

Work performed by triggers will be rolled back if the transaction that prompted them is rolled back.

PSQL limitations

Stored procedures and triggers can not start transactions, since they are under transaction control themselves.

PSQL does not allow the execution of DDL (Data Definition Language) statements: it is strictly meant to operate on data, not on the structure of your database. Although you can circumvent this limitation with the EXECUTE STATEMENT syntax introduced in Firebird 1.5, it is generally considered unwise to do so. (Just because we give you a spade, it doesn't mean that you have to dig your own grave.)

User-defined functions

By design, in order to preserve its small footprint, Firebird comes with a very modest arsenal of internally-defined (native) data transformation functions. Developers can write their own very precise functions in familiar host-language code such as C/C++, Pascal or Object Pascal to accept arguments and return a single result. Once an external function – UDF – is declared to a database, it becomes available as a valid SQL function to applications, stored procedures and triggers.

Firebird supplies two libraries of ready-to-use UDFs: ib_udf and fbudf. Firebird looks for UDF libraries in its own UDF subdirectory or in other directories specified in the Firebird configuration file. In Firebird 1.5 and upward this is done with the UDFAccess parameter; in earlier versions with external_function_directory.

Note

In versions prior to 1.5 the fbudf library is only available on Windows.

Prev: Application developmentFirebird Documentation IndexUp: About Clients and ServersNext: Multi-database applications
Firebird Documentation IndexUsing FirebirdAbout Clients and Servers → Server-side programming