Creating UDFs with Delphi 2.0

by Robert J. Love

• Introduction
• Datatype Conversion
• Working with Pointers
• Writing the Functions
• Declaring the Functions
• Using the Functions

User defined functions (UDFs) (As defined in the Interbase Data Definition Guide) are host- language programs for performing customized , often-used tasks in applications. Simply, a UDF is a dynamic linked library (DLL) written in Delphi (or other language) that exports functions for use with the SQL Language.

A UDF can perform functions that are not normally available in the SQL Language. This allows you to extend the architecture of the server to meet your needs. It will allow you to remove dependencies on your applications to control specific features of your data, hence removing vital business rules from your application providing for more accurate data.

A UDF can perform routine tasks, such as forcing a string to upper case or trimming blank spaces. It can also perform functions are that too complex for the SQL Language. For example, adding one month to the current date can be difficult within the Interbase SQL Language, but performing the function with a UDF is somewhat easier.

UDFs should be used to reduce network bandwidth and distribute the applications load. Some functions naturally fit your data model and should be places in the database architecture instead of your application.

UDFs can help you in several ways, however there are some possible draw backs to using UDFs. With Delphi you can only compile Windows based DLL’s. This can limit you, if you could be upgrading your server architecture to another platform, such as UNIX or VMS. Also UDFs on UNIX and VMS are called using blocking asynchronous trap (AST) routines, that means that while a UDF is executing the rest of the database is not available. The Windows NT version of Interbase does not present this problem, which is the required platform if Delphi is used for creating Interbase.

All of the documentation on creating UDFs has been written from a C Programmers prospective, which has resulted in a belief that It requires C to write a UDF. With the release of Delphi 2.0 and its 32 bit compiler, creating UDFs with Delphi is now possible. The rest of this paper should get you well on your way to creating UDFs using Delphi. For an example we will be creating a UDF that performs the following Functions :

• YEAR which returns the year from a given date.
• TRIM which returns a string with the Leading and trailing spaces removed.
• ABS which returns the absolute value of a double precision value

When creating UDFs there are a few issues to be aware of, the first is which Interbase data types match to which Delphi data types, the second is how to declare your exported DLL Function, the third is how to declare it to the database. To concern the first issue of data type conversion please see the chart below.

Interbase   <-->   Delphi

CSTRING            PCHAR
INTEGER            ^INTEGER;
DOUBLE PRECISION   ^DOUBLE
DATE               ^ISC_QUAD *

* Record structure for dates.
Type
  ISC_QUAD =  record
         isc_quad_high: Integer;
         isc_quad_low: Cardinal;
      end ;

tm = record
   tm_sec : integer;   { Seconds }
   tm_min : integer;   { Minutes }
   tm_hour : integer;  { Hour (0--23) }
   tm_mday : integer;  { Day of month (1--31) }
   tm_mon  : integer;    { Month (0--11) }
   tm_year : integer;    { Year (calendar year minus 1900) }
   tm_wday : integer;  { Weekday (0--6) Sunday = 0) }
   tm_yday : integer;   { Day of year (0--365) }
   tm_isdst : integer;   { 0 if daylight savings time is not in effect) }
end;

All values are passed to a UDF by reference. Return values can be passed by either value or by reference. To many Delphi programmers working with pointers initially can be difficult, although pointer usage with a UDF can be quickly mastered. There are two basic operators used in pointer operations. The ^ operator in a Type statement such as:

Type 
   Pinteger = ^Integer;

To address the second issue of writing the exported DLL function see the following code segment.

Type 
  PISC_QUAD = ^ISC_QUAD;
  PDouble = ^Double;

Function UDF_Year(D : PISC_Quad) : Integer;  cdecl; export;
var
CDT : TM;
begin
isc_decode_date(D,@CDT);
result := CDT.tm_year  + 1900;
end;

Function UDF_ABS(X : PDouble) : PDouble; cdecl; export;
Begin
 X^ := ABS(X^);
 result := X;
end;

Function UDF_Trim(S1 : PChar) : PChar; cdecl; export;
begin
 result := Pchar(Trim(StrPas(S1)));
end;

Exports
 UDF_Hour     name 'UDF_hour' resident,
 UDF_abs      name 'UDF_abs' resident,
 UDF_trim     name 'UDF_trim' resident;

The third issue to address is making the function available to your database using the Data Definition Language (DDL)

The Syntax to declare a UDF to a database is:

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

DECLARE EXTERNAL FUNCTION YEAR Date 
        RETURNS Integer by Value
        ENTRY_POINT "UDF_year" MODULE_NAME "SAMPLE.DLL";

DECLARE EXTERNAL FUNCTION abs
        DOUBLE PRECISION
        RETURNS DOUBLE PRECISION
        ENTRY_POINT "UDF_abs" MODULE_NAME "SAMPLE.DLL";

DECLARE EXTERNAL FUNCTION TRIM CSTRING(256)
        RETURNS CSTRING(256)
        ENTRY_POINT "UDF_trim" MODULE_NAME "SAMPLE.dll";

Select Statement

SELECT * FROM sales WHERE YEAR(order_date) = '1996'

Insert Statement

INSERT INTO clients (company_name, first_name, last_name)
 VALUES (trim(:company_name),trim(:first_name), trim(:last_name)

Computed Field

CREATE TABLE new_sales ( first_name VARCHAR(20)), 
                         order_date DATE,
                         order_year  COMPUTED BY (year(order_date)));

Robert J. Love
rlove@pobox.com

PO Box 160085
Freeport, UT
84016-0085