(redirected from Doc.15400)
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
.SO). Thus one dynamically loaded library consists of at least one function.
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.
- Ability to signal SQL
NULLvia a null pointer
- UDF library diagnostic messages improved
- UDFs added and changed
- General UDF changes
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 http://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.
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.
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 http://www.ibexpert.com/download/udf/. The most up-to-date version of this library can found at http://rfunc.sourceforge.net/.
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.
RFUNC.DLLfile needs to be copied into a folder:
- Variant 1:
<IB path>IB_path\bin(for IB6:
IB_pathis 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).
- only for IB 5.x:
copy ib_util.dllfile from
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 (
It is recommended before starting the Firebird/InterBase® server to substitute
GDS32.DLL appropriate to the version of the server.
- Variant 1: Copy the RFunc file into directory
- 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
InterBase® 6-7 und Firebird (Windows und Linux):
Copy the RFunc file into directory
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.
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 http://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.
FreeUDFLib.dll to the Firebird/InterBase®
udf directory, for example:
C:\Program Files\InterBase Corp\InterBase\bin,
C:\Program Files\Borland\InterBase\udf\bin or
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.
DECLARE EXTERNAL FUNCTION (incorporating a new UDF library)
Using descriptors with UDFs
Threaded Server and UDFs
External functions (UDFs)
ALTER EXTERNAL FUNCTION
DECLARE EXTERNAL FUNCTION
NULL to UDFs in Firebird 2
How to write an internal UDF function
Java UDF functional specification
Creating UDFs in Delphi
Firebird Null Guide:
NULL <–> non-
NULL conversions you didn't ask for