Writing UDF's for Local InterBase and InterBase for NT with Delphi 2.0

UDF's the why and wherefores

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.

BD there was C (Before Delphi there was C)

In the past only C compilers were capable of producing DLL's on the Win32 platform. Now with Delphi 2.0 we can produce DLL's at a drop of a hat. So what once was the area of an elite has now been thrust into the public arena. Creating UDF's have become more accessible!!!

Using Delphi 2.0 to create DLL's

This can be done by clicking few menu items, File|New|DLL. A DLL shell will be produced as follows:

library udfs;
uses
  SysUtils,
  Classes;

begin
end.

Once DLL shell has been generated we add the functions (UDF's) we want to implement.

library udfs;
uses
  SysUtils;

{removed the Classes unit it is not needed and the DLL size is reduced}
// ==============================================================
//   fn_dow() return the day of today. e.g., Monday, Friday. ...
// ==============================================================
function fn_dow : PChar; cdecl;
begin
   Result := PChar(LongDayNames[DayOfWeek(now)]);
end;

exports
  fn_dow;

begin
end.

To make functions accesible outside the DLL they need to be exported. This is done by merely adding the function name to the exports section.

That is basically all you need to know about writing DLL's in Delphi 2.0. OK, OK this is too easy, there has to be a catch somewhere? Well there is, one, UDF's has to use "C" calling convention. In Delphi 2.0 a function is specified as using C calling convention using the cdecl keyword.

Compare the above Delphi code for the same C example:

/* ==============================================================
   fn_dow() return the day of today. e.g., Monday, Friday. ...
   ============================================================== */
char* EXPORT fn_dow()
{
      time (&time_sec);
      tbuf = localtime(&time_sec);

      switch (tbuf->tm_wday) {
      case 1: return "Monday";
      case 2: return "Tuesday";
      case 3: return "Wednesday";
      case 4: return "Thursday";
      case 5: return "Friday";
      case 6: return "Saturday";
      case 7: return "Sunday";
      default: return "Error in Date";
      }
}

How do we get InterBase to use these UDF's

All that is needed to use a UDF in a database is to copy the DLL containing the UDFs onto the server, add a reference to the function in the database using a short SQL script, containing the following information:

DECLARE EXTERNAL FUNCTION name  [Paramter list]
RETURNS {<datatype> [BY VALUE] | CSTRING (int)}
ENTRY_POINT "<entryname>"
MODULE_NAME "<modulename>"

and run it, from WISQL. Following on from our short Delphi example: The Delphi UDF can be added to a database using the following SQL script:

DECLARE EXTERNAL FUNCTION dow
RETURNS CSTRING(12)
ENTRY_POINT "fn_dow" MODULE_NAME "udfs"

• Where "dow" is the name of the UDF InterBase uses, and takes no parameters.

• The function returns a NULL terminated string.

• The exported function name is "fn_dow".

• The DLL name is UDFS.DLL

• Copy the DLL to the same directory as the server (this will guarantee faster load times).

• In WISQL connect to a database. (I used the EMPLOYEE.GDB in C:Program FilesBorlandIntrBaseEXAMPLES)

• Run the SQL script, this will add the UDF to the database

• Use the function in a simple SQL statement like:

  select first_name, last_name, dow() from employee
  

• You will get output as follows:

  FIRST_NAME      LAST_NAME            DOW
  =============== ==================== ===========
  Robert          Nelson               Thursday
  Bruce           Young                Thursday
  Kim             Lambert              Thursday
  Leslie          Johnson              Thursday
  

You as the developer can now add any functionality to Local InterBase or InterBase NT you like. Wanted that nifty function in Sybase? well write it yourself. More information on UDF's can be found in the Interbase programmers guide and in the IB32.HLP in the Win32 Local Interbase INTRBASEBIN directory.

The online help states:

Creating a UDF is a three-step process:

1. Writing and compiling a UDF in a programming language such as C.
2. Building a dynamically linked library containing the UDF.
3. Declaring the UDF to the database.

We can now say: Creating a UDF is a two-step process:

1. Writing and building a dynamically linked library containing the UDF in a programming language such as Object Pascal in Delphi.
2. Declaring the UDF to the database.