Gerhardus Geldenhuis describes how he taught himself to write UDFs, using the resources available around the community web sites

First things first

This document is a summary of my experiences and the functions I wrote. It is not intended to be a complete guide to writing UDF's but merely an introduction. If you have something to add please feel free to do so I can be contacted at [email protected]. If you find any errors I would also appreciate you letting me know. Please include UDFPAPER somewhere in the subject line when sending a email it helps me to filter and respond timely.

Before jumping in and writing code start reading first. This is what should be read without exception:
  • Interbase 6.0 Language Reference
  • InterBase 6.0 Developer’s Guide
This is what I think should also be read about or have some knowledge about, depending on the function you want to write:
  • Pointers
  • Var Parameters
  • PChars
  • Threads
I am assuming you are using at least Delphi4 hopefully Delphi5. Also IB6 or FB1.

The stuph that matters

Lets assume that IB can't add two numbers together so we must write a function that does that.

Create a new dll project. If your dll is only chatting with IB then you can ignore the ShareMem clause, because IB uses PChars.

You can write the function code directly in the dll but it is more ordered to write it in a unit and include the unit in the uses clause, it makes it easier when your dll start containing 20+ functions.

Here is some sample code:

library Project1;
uses
Unit1 in 'Unit1.pas';
exports
AddFunction;
begin
end.


Here is the code for unit 1.

unit Unit1;
interface
function AddFunction(var Width,Height:Integer):Integer;stdcall;
implementation
function AddFunction(var Width,Height:Integer):Integer;
begin
Result:=Width+Height;
end;
end.


That is all there is to it basically. It is very important to remember that all parameters passed from IB is passed by reference hence the use of var parameters. Another important thing to remember is that pchar is already a reference so it should not be passed as a var parameter that would result in a pointer to a pointer to data, a recipe for disaster. If you don't want to use the var parameter your code will look like this. There is two versions of AddFunction

unit Unit1;
interface
type
PInteger = ^Integer;
function AddFunction(Width,Height:PInteger):Integer;stdcall;
implementation
function AddFunction(Width,Height:PInteger):Integer;
begin
Result:=Width^+Height^;
end;
function AddFunction(Width,Height:Integer):Integer;
var
K,J:^Integer;
begin
K:=Pointer(Width);
J:=Pointer(Height);
Result:=K^+J^;
end;
end.


How do I import it?

To import a function is actually the easiest part. Firstly again, read the documentation. Here is the script for importing the function.

declare external function f_AddFunction
Integer, Integer
returns
Integer by value
entry_point 'AddFunction' module_name 'Project1.dll';

Just a few things to take note of:
  • The function name and the entry point can be the same.
  • You can return either by value or by reference in which case you must make the result of your function of a pointer type.
  • I have not done it by reference so I cannot give you an example it is probably very much the same as the second example. Eg:
AddFunction(Width,Height:PInteger):PInteger;
begin
Result^:=Width^+Height^;
end;

Using PChars

Using PChars is actually not that difficult. I did not use it extensively so this is not a complete example but it will get you underway.

unit Unit1;
interface
uses
SysUtils;
type
PInteger = ^Integer;
function AddFunction(var Width,Height:Integer;Operation:PChar):Double;stdcall;
implementation
function AddFunction(var Width,Height:Integer;Operation:PChar):Double;
var
S:String;
begin
S:=Trim(UpperCase(String(Operation)));
Result:=0;
if S = 'DIVIDE' then
Result:=Width/Height
else
if S = 'MULTIPLY' then
Result:=Width*Height;
end;
end.


When you pass pchars its is better to remove spaces depending on what you are going to do. This gave me some trouble until I realised that there were extra unwanted spaces in the variable. Also mystring:=String(ThePCharVariable) is the correct and new way to cast pchars according to the Delphi help file. Please read it for further information.

How do I import it?

PChars is also very easy here is the script for the procedure.

declare external function f_AddFunction
Integer, Integer, CString(15)
returns
Double Precision by value
entry_point 'AddFunction' module_name 'Project1.dll';

Just a few things to take note of:
  • The length of CString must be the same value as the maximum size of the field which value you will use to send data with. If you know 100% that you will never send a string longer than x characters then make it x characters.
Things
  • The compiler of IB also uses the stdcall calling convention so that is the right one to use
  • Near, far and export have no effect so don't use it.
  • When you loose a connection while debugging(calling the procedure) it means your dll has raised an exeption or performed an illegal operation. It basically means your not yet finished coding.
  • The dll should be placed under the UDF directory of IB, otherwise you need to specify it in the startup files of IB. See the Language Reference
  • Corresponding Types
DelphiInterbase
IntegerInteger
PCharChar or VarChar
DoubleDouble Precision

  • When changing the parameters of a procedure after you have imported it and then dropping it andre importingg it can create problems. The best solution for me was to remove all reference to the UDF then to drop it and then re importing it.
  • Remember to copy the dll to the UDF directory after compiling. When IB is using the UDF, Windows will not let you copy the dll exit all relevant programs or disconnectt and then copy the file.
  • You don't need to re-import a UDF after compiling and copying a dll.
Debugging

A stupid mistake I made during debugging was to use MessageDlg. It works but if your server is not very near you will need to run to it everytime, to press Ok. Also it effectively halts your session because it waits for the UDF to exit and it will not do so until you press OK or any of the other buttons you specified.

I used a textfile with great success. Here is a code snippet of how you might do it. If the procedure is called multiple times in succesion it you should not call rewrite but append to add the data to the end. Remember to check for file existence when appending.

unit Unit1;
interface
uses
SysUtils;
type
PInteger = ^Integer;
function AddFunction(var Width,Height:Integer;Operation:PChar):Double;stdcall;
implementation
function AddFunction(var Width,Height:Integer;Operation:PChar):Double;
var
S:String;
K:TextFile;
begin
try
AssignFile(K,'C:\ProgramFiles\Borland\Interbase\UDF\Debug.txt');
Rewrite(K);
Writeln(K,'In Width:'+IntToStr(Width));
//or
Writeln(K,Width);
Writeln(K,String(Operation));
finally
CloseFile(K);
end;//try finally
S:=Trim(UpperCase(String(Operation)));
Result:=0;
if S = 'DIVIDE' then
Result:=Width/Height
else
if S = 'MULTIPLY' then
Result:=Width*Height;
end;
end.

Opinions
  • I use UDF's when I need to do a lot of calculations between Double Precision and Integer because in Delphi you dont need to cast the whole time.
  • When a double value in Delphi is very very very small it will be 0 in IB even if passing it as Double Precision. I have not confirmed this conclusively so it is an opinion.
Further reference and reading

Most of these sites have sections where you can join mailinglists for further information. Mailinglist is a very valuable place to get help. Just dont be lazy, try to find the answer yourself first, and then if that did not succeed ask your question clearly and tell what have tried already.
Thanks
  • Interbase Workbench my exclusive tool for modifying/creating my databases!
  • IB Expert for a very nice procedure debugging tool.
  • Thanks to the help of the people at [email protected].
  • Thanks to Gregory Deatz programmer of FreeUDFLib for making his code open so I could study it to see how things were done.
  • Thanks to IBObjects for the best way to access Interbase/Firebird.