[Ed. - more correctly called External Functions. ] Our development project uses a UDF library called UUIDLIB to generate UUID and GUID unique id strings. This UDF library can be downloaded here and comes with source code, plus out-of-the-box binaries for Windows and Linux. In order for me to develop/test our project under OSX, I would need to build the UUIDLIB library from source, and then configure Firebird-OSX to use my library.

This led me on a voyage of discovery which ultimately ended in success, but first some background info ....

In this article, Roy Nelson describes UDFs as follows ....

“A User Defined Function (UDF) is a mechanism provided to extend the built-in functions InterBase provide. A UDF is written in a "host language" i.e. a language which compiles to libraries usable by InterBase on the host platform. UDF's can be written to provide custom statistical, string, date or performance monitoring functions. Once a UDF is created, it can be used in a database application anywhere that a built-in SQL function can be used. On the NT and Windows 95 platforms native libraries normally take the form of Dynamic Link Libraries or simply DLL's these libraries are loaded by the operating system on a "as needed" basis.”

In summary, a UDF library is a shared library, dynamically loaded by Firebird at runtime. Under Windows, shared libraries have a .DLL extension. Under Linux, shared libraries have a .so extension. However, under OSX, the type of shared library we want has a .dylib extension.

The MacOSX Firebird package ships two UDF libraries installed in the directory /Library/Frameworks/Firebird.framework/Versions/Current/Resources/English.lproj/var/UDF. These libraries are contained in the files fbudf.dylib and ib_udf.dylib.

Based on documentation for other platforms, I should be able to load and use the rpad() function from the supplied ib_udf library by entering the following SQL fragment into ISQL ..

DECLARE EXTERNAL FUNCTION rpad
        CSTRING(80), INTEGER, CSTRING(1)
        RETURNS CSTRING(80) FREE_IT
        ENTRY_POINT 'IB_UDF_rpad' MODULE_NAME 'ib_udf';
      

This command appears to succeed and no error messages are printed. However, when trying to use the newly installed rpad() function, the following output is produced:

SQL> select rpad('test',10,'.') from RDB$DATABASE;

Statement failed, SQLCODE = -902
Access to UDF library "ib_udf.so" is denied by server administrator
SQL>
      

Not the result we expected! This error message is a bit misleading and seems to be a generic message produced whenever there is a problem with a UDF. It suggests a permission problem but in our case, it is because Firebird couldn't load the UDF library because it simply does not exist.

Recall that our library file is called ib_udf.dylib. Firebird is looking for a file called ib_udf.so which it cannot find.

OK, so the first quick fix we try is to copy or rename the ib_udf.dylib file to ib_udf.so and then try again... Strangely, we get exactly the same error message ...

Access to UDF library "ib_udf.so" is denied by server administrator
SQL>
      

Within this file, there is a section that defines the location and access level of UDF libraries. The access level can be one of 'None, Restrict or Full'. In the case of Restrict, a list of paths, seperated by semi-colons is required to indicate the search locations for UDF libraries. In the MacOSX Firebird install, the default setting is ...

UdfAccess = Restrict UDF
      

... which tells Firebird that access to UDF functions is to be Restricted to the directory UDF. The intention seems to be that this path is relative to the Firebird Install directory; however, under OSX, this does not seem to work.

So, I tried adding the full path of the UDF directory as follows:

UdfAccess = Restrict UDF;
  /Library/Frameworks/Firebird.framework/Resources/English.lproj/var/UDF;
      

Now, when we try and use the rpad() function we get the following results:

SQL> select rpad('test',10,'.') from RDB$DATABASE;

RPAD
==========================================================

test......
      

SUCCESS !! We can now use UDF's under MacOSX.

Whenever you connect to a Firebird database, the full path and filename of the database file must be specified. Apart from being inconvenient and non-intuitive, when accessing a database on a remote server, it presents a bit of a security risk.

You can make life a lot easier if you use the 'Alias' capability of Firebird as follows:

Then add lines of the format

    alias = full_path_to_database_file
      

For example ...

    testdb=/Users/dwp/fbdata/testdb.fdb
      

Now, when using isql to access the testdb database, instead of ...

SQL> connect '/Users/dwp/fbdata/testdb.fdb'
      

you can use ...

SQL> connect 'testdb'
      

or to connect via the network socket ...

SQL> connect 'localhost:testdb'