A user-defined function (UDF) is used to perform tasks that Firebird/InterBase® can't. It is an external database function written entirely in another language, such as C++ or Pascal, to perform data manipulation tasks not directly supported by Firebird/InterBase®.

UDFs can be called from Firebird/InterBase® and executed on the server. These functions can exist on their own or be collected into libraries. UDFs offer the possibility to create your own functions (such as SUBSTR) and integrate them in the database itself. Each UDF is arranged as a function, belonging to a DLL (Linux: .SO). Thus one dynamically loaded library consists of at least one function.

UDFs can be incorporated into the database using the IBExpert DB Explorer, IBExpert SQL Editor, or IBExpert Script Executive.

The IBExpert UDF Editor displays those UDFs inserted into the list, by double-clicking on the UDF name in the DB Explorer, or alternatively using the navigation icons in the editor toolbar to insert single or all UDFs. The grid display can also be filtered or grouped if wished. The grid displays key information, including name, library, entry point, input parameters, returns, return mechanism (drop-down list of options), whether freed (checkbox), and description. Further information is displayed on the Description, Dependencies, DDL, Comparison and To-Do pages.

UDF definitions are database dependent and not server dependent, i.e. they need to be registered for each database individually. Since InterBase® 6/Firebird, the libraries need to be stored in the Firebird/InterBase® UDF folder. This is not critical when working with older InterBase® versions.

Please refer to the DECLARE EXTERNAL FUNCTION statement for details of incorporating UDFs in Firebird/InterBase®.

It is important to note that the majority of UDFs, when used in a WHERE condition, prevent indices being used during execution.

New to Firebird 2.0: The following is a summary of the major changes, the details of which can be found in the Firebird 2.0.4 Release Notes in the External functions (UDFs) chapter:

• Ability to signal SQL NULL via a null pointer
• UDF library diagnostic messages improved
• UDFs added and changed
  • IB_UDF_rand() vs IB_UDF_srand()
  • IB_UDF_lower
• General UDF changes
  • Build changes

and ALTER EXTERNAL FUNCTION in the Firebird 2.1 Release Notes.

An ideal example of a UDF library is RFunc (written in C++) containing over 80 UDFs (although some of these are only applicable for older InterBase® versions or for different SQL dialects). It is available for both Windows and Linux platforms in English and Russian and can be downloaded free of charge from https://www.ibexpert.com/download/udf/. FreeUDFLib is an example of a UDF library written in Delphi, and can also be downloaded from this link.

For further functions please refer to IBEBlock Functions and the Firebird documentation: Firebird built-in functions.

back to top of page

The DROP EXTERNAL FUNCTION command removes the declaration of the UDF, specified by an additional parameter, from the database.

The dropped function can no longer be reached by the database, as the relevant reference to the UDF library is deleted. However the UDF still exists in the UDF library, so that it can still be used by other databases.

In IBExpert, a UDF can be dropped from the DB Explorer by selecting the UDF to be deleted and using the right-click menu item Drop UDF or [Ctrl + Del].

IBExpert asks for confirmation

before finally dropping.

The SQL syntax is:

DROP EXTERNAL FUNCTION <external_function_name>

An exception can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.

back to top of page

RFunc is a UDF library containing over 80 UDFs (although some of these are only applicable for older InterBase® versions or for different SQL dialects). It is available for both Windows and Linux platforms in English and Russian. It can be downloaded free of charge from https://www.ibexpert.com/download/udf/. The most up-to-date version of this library can found at https://rfunc.sourceforge.net/.

It represents a set of user's (UDF) string, bit, numerical functions, and can also be used for operations with DATEs and TIME and blobs. Also contains PARSER, i.e. calculator of expressions.

InterBase® 4.2, 5.x, 6.x, 7.0 (Windows 9x, NT, 2000) and InterBase® 5.x, 6.x, 7.0 (Linux) or Firebird are supported. The library is written in C++ and is delivered with source codes.

The ZIP-file should be selected (Windows or Linux; English or Russian) and downloaded.

1. The RFUNC.DLL file needs to be copied into a folder:

• Variant 1: <IB path>IB_path\bin (for IB6: IB_path\UDF), where IB_path is the path to a folder, in which Firebird/InterBase® is installed (recommended).
• Variant 2: Windows\System (for Windows 9x) or WinNT\System32 (Windows NT, 2k).

2. only for IB 5.x: copy ib_util.dll file from <IB path>\Lib to \Bin.

If several versions of InterBase® servers are installed on one computer, it is necessary to use the RFunc library appropriate to the installed client IB (GDS32.DLL).

It is recommended before starting the Firebird/InterBase® server to substitute GDS32.DLL appropriate to the version of the server.

IB 5.x:

• Variant 1: Copy the RFunc file into directory /usr/lib.
• Variant 2: Copy the RFunc file into any directory, for example, /home/rFunc. Create the reference to the library by using the \ln -s /home/rFunc/rfunc /usr/lib/rfunc\ command. The user should own the right to create references in the directory /usr/lib.

InterBase® 6-7 und Firebird (Windows und Linux):

Copy the RFunc file into directory \UDF.

The rfuncx.sql (x = InterBase® version; use rfunc6.sql for all Firebird versions) script, found in the UDF\sql directory, should then be copied into the IBExpert Script Executive (found in the Tools menu), and executed [F9]. A database connection must exist, as UDF libraries need to be registered for each database (i.e. they are database-dependent and not server-dependent).

It is then necessary to disconnect and reconnect to the database so that the full list of RFunc UDFs can be viewed in the DB Explorer under the DB object branch UDF.

back to top of page

FreeUDFLib is a free UDF library (October 1998) containing many useful UDFs for use with InterBase® 4.2 and 5.0 under the Win32 platforms (unfortunately no UNIX support with this). It is written entirely in Delphi and all source code is provided.

It can be downloaded free of charge from https://www.ibexpert.com/download/udf/.

Everything in this release is completely free. However, it's not a PUBLIC DOMAIN. Please refer to the license.txt, included in the ZIP file for more information on licensing.

After unzipping FreeUDFLib.zip, copy FreeUDFLib.dll to the Firebird/InterBase® bin or udf directory, for example: C:\Program Files\InterBase Corp\InterBase\bin, C:\Program Files\Borland\InterBase\udf\bin or C:\Program Files\Firebird\udf\bin.

The ext_funcs.sql script should then be copied into the IBExpert Script Executive (found in the IBExpert Tools menu), and executed using [F9]. A database connection must exist, as UDF libraries need to be registered for each database (i.e. they are database-dependent and not server-dependent). If necessary, use the Script Executive menu item Add CONNECT statement to connect to the desired database, before executing.

It is then necessary to disconnect and reconnect to the database so that the full list of FreeUDF external functions can be viewed in the DB Explorer under the DB object branch UDF.