Writing UDFs for InterBase under Windows and Linux

By: Conference Speaker

Abstract: A user defined function (UDF) in InterBase is merely a function written in any programming language that is compiled into a shared library.

This is the technical paper from a talk given at the 10th Annual Inprise & Borland Developer's Conference
By Gregory Deatz - Hoagland, Longo, Moran, Dunst & Doukas
Gregory Deatz is a senior programmer/analyst at Hoagland, Longo, Moran, Dunst & Doukas, a law firm in Central New Jersey. His current focus is database applications for the legal profession. He is the author of FreeUDFLib and FreeIBComponents, both of which are tools written in Delphi, specifically for use with InterBase, and they are available on the InterBase Web-site at www.interbase.com/download.


Introduction

What is a UDF?

A user defined function (UDF) in InterBase is merely a function written in any programming language that is compiled into a shared library. Under Windows platforms, shared libraries are commonly referred to as dynamic link libraries (DLL's).

Why write them?

After all, stored procedures can accomplish quite a bit on their own.

The truth of the matter is, InterBase does not come with a very rich set of built-in functions. Some common functions that are missing are modulo arithmetic, floating point formatting routines, date manipulation routines and string manipulation routines.

It just so happens that programming languages like Delphi and C can produce amazingly fast code to do modulo arithmetic, and other various date processing, floating point formatting and string manipulation routines.

It's also a well known fact that writing UDFs is an insanely easy task; however, the inexperienced DLL/shared library writer might be uneasy and uncomfortable with some of the requirements...

Some do's, some don't's

Once you get the hang of writing UDFs, you will probably think that a whole world of InterBase extensibility has opened up to you through UDFs.

On the one hand, it has... The mechanisms for invoking UDFs are quite simple, and since a UDF is simply a routine written in your favorite programming language, you can do virtually anything, right?

Well, yes and no... One thing you can't do with UDFs: You can't pass NULLs to them. Likewise, a UDF cannot return a NULL value. Also, a UDF does not work within the context of a transaction. That is, transaction level information cannot be passed to a UDF, and therefore, a UDF isn't able to "dig back" to the database.

Sort of. A UDF can establish a new connection to the database and start another transaction if it so desires, but this is where we come to the "do's and don'ts", not to the "can'ts".

When you write UDFs, you should follow these two simple rules:

  • A UDF should be a simple, quick function.
  • A UDF should not attempt to directly affect the state of the database.

What does this mean?

Well, a function that trims a string, performs modulo arithmetic, performs fancy date arithmetic or evaluates aspects of dates are all nice, simple, quick functions. They are good examples of candidate UDFs.

Now, a function that attaches to a database, and inserts, deletes or updates data is probably a bad idea. A function that launches a program that performs a series of complex tasks is probably a bad idea. Why? Quite simply because these types of functions might stop a database from (a) doing transactional stuff or (b) even worse, they could significantly damage the performance of your server: As soon as a UDF is called, the thread that called that UDF blocks until the UDF returns.

Remember, of course, that these are general guidelines. Your particular business case might dictate a need to do something that is generally bad because in your case it is specifically good, kind of like a glaucoma patient smoki... O yeah, stay on topic now.

Writing UDFs in Delphi for Windows platforms

Start a Delphi project

Start a Delphi DLL project (a special type of project, when you click "F"ile "N"ew).

Create a new unit for your functions

  • Now, do "F"ile, "N"ew... UNIT.
  • It might be wise to do a Save All at this point... put your project where you feel is a good spot...

Create a modulo routine

  • In the newly created unit:
    • Declare the routine in the interface section:
    • function Modulo(var i, j: Integer): Integer; cdecl; export;
      
    • Implement the routine in the implementation section:
    • function Modulo(var i, j: Integer): Integer;
      begin
        if (j = 0) then
          result := -1 // just check the boundary condition, and
                       // return a reasonably uninteresting answer.
        else
          result := i mod j;
      end;
      
  • In the newly created project source:
    • Type the following immediately preceding the "begin end.":
    •   exports
          Modulo;
      

Build it, use it

  • So, build the project, and you now have a working DLL.
  • To use the UDF.... do the following:
    • Connect to a new or existing database using ISQL.
    • Type the following:
    • declare external function f_Modulo
        integer, integer
        returns
        integer by value
        entry_point 'Modulo' module_name 'path to dll';
      
    • Commit your changes.
    • Now test it...
    • select f_Modulo(3, 2) from rdb$database
      
Whew! That was really easy, wasn't it?

But what about strings and dates?

What about 'em anyway? A string and a date are not considered "scalar" values in InterBase-ese, so special care must be taken when returning their values to InterBase.

Let's build a "Left" routine.

Memory allocation issues

  • If you have IB 5.1 or lower, then type the following declaration in the interface section of your unit:
  • function ib_util_malloc(Bytes: Integer): Pointer; cdecl; external 'msvcrt.dll';
    
  • If you have InterBase 5.5 or better, then you don't really need this bit of coding magic. Instead, make sure that the ib_util.pas file is in your compiler search path, and that ib_util.dll is in your real search path.

    The simplest way to do this is to put

    c:Program FilesInterBase CorpInterBaseinclude
    
    in Delphi's library search path. Then, copy
    c:Program FilesInterBase CorpInterBaselibib_util.dll
    
    to your windows system directory (typically c:WindowsSystem).
  • Then, put ib_util.pas in your uses clause of your interface section.
    uses
      ...,
      ib_util;
    
What are all these strange memory allocation details? Why can't I just allocate memory with AllocMem, or whatever? The simple answer is: You can't, so don't ask! The more complicated answer is that every compiler uses it's own favorite algorithms for managing memory that has been given to it by the OS. As an example, MSVC manages memory differently from Delphi. Guess what? IB is compiled with MSVC. In pre-5.5 version, you have to link directly to the MS VC Run-time DLL, and in post-5.5 days, InterBase gives you an IB call to make this possible. So, let's get on with building a string-ish function!

Building the function

  • In the interface section of the newly created unit, type the following declaration:
  • function Left(sz: PChar; Cnt: Integer): PChar; cdecl; export;
    
  • In the implementation section of the unit, type:
  • (* Return the Cnt leftmost characters of sz *)
    function Left(sz: PChar; var Cnt: Integer): PChar;
    var
      i: Integer;
    begin
      if (sz = nil) then
        result := nil
      else begin
        i := 0;
        while (sz[i] <> #0) do Inc(i);
        result := ib_util_malloc(i);
        Move(sz[0], result[0], i);
      end;
    end;
    
  • In your project source, in the "exports" section, type:
  • exports
      Modulo,
      Left;
    
  • Now, build the project again...
  • Now, to use the routine, go to ISQL, and reconnect to the database you used above, and type:
  • declare external function f_Left
      cstring(64), integer
      returns cstring(64) free_it
      entry_point 'Left' module_name 'path to dll';
    
  • And test it...
  • select f_Left('Hello', 3) from rdb$database
    
Still pretty simple, huh?

Let's build a "Year" routine

  • FreeUDFLib, and FreeIBComponents both come with a Delphi port of the ibase.h header file. To deal effectively with dates, you must have access to the InterBase API, so you'll need to get your hands on this file!
  • Make sure you use ibase.pas!
    uses
      ...,
      ibase;
    
  • In the interface section of the newly created unit, type the following declaration:
  • function Year(ib_date: PISC_QUAD): PISC_QUAD; cdecl; export;
    
  • In the implementation section of the unit, type:
  • (* Return the Cnt leftmost characters of sz *)
    function Year(ib_date: PISC_QUAD): Integer;
    var
      tm_date: tm;
    begin
      isc_decode_date(ib_date, @tm_date);
      result := tm_date.tm_year + cYearOffset;
    end;
    
  • In your project source, in the "exports" section, type:
  • exports
      Modulo,
      Left,
      Year;
    
  • Now, build the project again...
  • Now, to use the routine, go to ISQL, and reconnect to the database you used above, and type:
  • declare external function f_Year
      date
      returns integer
      entry_point 'Year' module_name 'path to dll';
    
  • And test it...
  • select f_Year('12/31/99') from rdb$database
    
Still pretty simple, huh?

Writing UDFs for Linux/Unix platforms

Create a C-file

This much is easy, right? Just open a text file with a .c extension.

Create the modulo routine

int modulo (int a, b) {
  return a % b;
}

Build it, use it

At the command-line
gcc -c -O -fpic -fwritable-strings <your udf>.c
ld -G <your udf>.o -lm -lc -o <your udflib>.so
In ISQL
declare external function f_Modulo
  integer, integer
  returns
  integer by value
  entry_point 'modulo' module_name 'path to so file';

select f_Modulo(3, 2) from rdb$database;
Holy guacamole, Batman! That was really easy.

Now, instead of going through the motions of writing the other routines, I'll leave it as an exercise for the reader.

Conclusions

Wow! Writing UDFs is really easy--there isn't much to it--and look! Linux development ain't so difficult after all.

And, of course, we can make the following brain-dead conclusions about developing UDFs for InterBase:

They are easy. There is no excuse for not building them if you need them.

Don't get carried away. As powerful as UDFs can be, don't get carried away. Be very objective when deciding where you should place little tidbits of functionality: Am I better served by a UDF or a stored procedure?

And that, my friends, is UDF development.

Server Response from: ETNASC04