this is the technical paper from a talk given at the 11th Annual Inprise & Borland Developer's Conference
By Gregory Deatz
Gregory Deatz is a senior programmer/analyst at Hoagland, Longo, Moran, Dunst, and Doukas, a law firm in Central New Jersey. His current focusis 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.
o What is a UDF?
o Why write them?
o Some do's, some don't's
• Writing UDFs in Delphi for Windows platforms
o Start a Delphi project
oCreate a new unit for your functions
o Create a modulo routine
o Build it, use it
o But what about strings and dates?
o Let's build a "Left" routine.
o Let's build some date routines.
• Writing UDFs for Linux/Unix platforms
o Create a C-file
o Create the modulo routine
o Build it, use it
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, InterBasedoes 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 formattingand 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
Before we start going through some examples of writing UDFs, let's talk about what you should be doing, and what you should not be doing.
Onceyou 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: Youcan'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 tothe "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,...