Firebird’s procedure language (PSQL) Programming - Firebird

Among Firebird’s powerful features for dynamic client/server application programming is its capability to compile source code on the server into a binary form for runtime interpretation. Such procedures and functions are executed completely on the server, optionally returning values or datasets to the client application. Firebird provides two styles of server-side programming capability: stored procedures and triggers. In addition, external functions, also known as user-defined functions , can be written in a high-level language and made available to the server for use in SQL expressions.

Stored Procedures

Firebird’s procedure 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 that 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.

Stored procedures can embed other stored procedures and can be recursive. All stored procedure execution, including selection of datasets 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 canceled totally if the client rolls back the transaction.

Triggers

Triggers are special procedures created for specific tables, for automatic execution during the process of posting insertions, updates, and deletions to the server. Any table can have any number of triggers to be executed before or after insertions, updates, and deletions. Execution order is determined by a position parameter in the trigger’s declaration. Triggers have some language extensions not available to regular stored procedures or to dynamic SQL, 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.

User-Defined Functions

By design, in order to preserve its small footprint, Firebird comes with a modest arsenal of internally defined (native) data transformation functions. Developers can write their own 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, available for both Windows and Linux, and fbudf, currently available for Windows and Linux in v.1.5 and Windows only in v.1.0.x. Firebird looks for UDFs in libraries stored in the /udf directory of its installation or in other directories configured by the UdfAccess (1.5) or external _function _directory (1.0.x) parameter in the Firebird configuration file.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics