[UDF series five]: How to create Interbase UDF
By Paul McGee (c) Borland International Inc.
Warton translation
[Overview]:
User-defined function (UDF) is a function written in compile language, which is designed for user-defined features and common task. UDF allows programmers to modularize a database application and can embed the database to enhance database itself. UDF is always executed at the database server. This reduces network traffic.
UDF can perform a transaction, for example, obtain a space on the server, cleaning the string space, calculating standard deviation for a series of values. UDF can complete any functionality as long as the programming language can be expressed. Such programming languages can often be selected between C and C (of course, users can use other languages, such as Pascal Translator Note).
UDF is very effective in providing SQL languages that cannot be handled, queries, update the database, and providing universal functions for helicic client workstations.
Compared with other functions, use UDF to spend some cost. This is mainly manifested in two aspects: First, in the UNIX or VMS platform, UDF is a modular AST program, which means that when UDF is executed, no other access operation can occur. This requires us to try to make UDF as small and efficient. Second, if the database server crashes, you need to transfer the data to another machine. You must first install UDF libraries on a new server. This is not difficult to in the same operating system. But when transferring to another operating system, you only need to compile the source code of the UDF library.
[UDF example]:
Interbase contains several built-in SQL functions: Upper, Gen_ID, CAST. Upper converts a string to uppercase. GEN_ID generates a unique long integer value because a special generator is already defined in the database. This is very useful when generating primary keys, such as customer numbers or staff numbers. CAST converts a type column into another type of column.
Interbase also provides additional UDF source code in an Examples directory. They are included in the udflib.c file. The UDFs defined here are: Lower, Strcat, Substr, Trim, Trunc, Doy, Moy, Dow, Sysdate, Add2, Mul, Fact, ABS, MaxNum, SQRT, BLOB_LINECUNT, BLOB_BYTECUNT, SUBSTR_BLOB. LOWER is transformed into a lower string. STRCAT connects two strings. Substr returns a part of the string. Trim empties spaces in the string. Trunc returns a string after deletion. Doy (Day of Year), MOY (MONTH OF Year), DOW (DAY OF WEEK). Sysdate returns the current date in the form of a string ("mmm-dd-yyyy"). Add2 adds two integers. MUL multiplies two Double numbers. MaxNum returns two larger. SQRT is a square. blob_bytecount returns the size of the blob. Substr_blob, part of the text of BLOB.
We will add a few new UDF: RTRIM, Left, Right, Swapcase, IMONTH, IDAY, IYEAR. RTRIM removes the space on the right side of the string. LEFT returns a string of n characters before entering string. Right is a string that returns to the n identical. Swapcase will write lowercase and turn the capital to lowercase. IMONTH returns the value of the month (1-12). IDay returns the value of DAY (1-31). Iyear returns the value of the year, such as: 2002.
[Write UDF]:
Once you have written UDF, you must create a dynamic link library so that UDF can be used. Then you have to define it in the database.
Define UDFS in a data definition language is very simple, basic syntax is:
Define External Function Name [
Returns {
Entry_Point "
Module_name "
Name refers to the name of the function that reaches 31 characters. The first DATATYPE is an input parameter. DataType indicator of Interbase number: Integer, char, varchar, etc. Or, you can use the CString this C style without using the end of the character array ã, Entry_Point refers to the actual function name. In Interbase providing example, the SQL function name is LOWER and the actual function name is FN_Lower_c in udflib.c. The module name refers to the function name after the function is compiled. For example: The function Lower and Substr are defined here with SQL.
Define External Function Lower
VARCHAR (256)
Returns cstring (80)
Entry_Point "fn_lower_c" module_name "funclib";
Define External Function SUBSTR
CString (256), Smallint, Smallint
Returns cstring (80)
Entry_point "fn_substr" module_name "funclib";
In order to add a function to a library, use Borland C on NT, we should let the lib module has a local copy:
Implib mygds32.lib /interbas/bin/gds32.dll
Then connect the necessary options to generate our new library Funclib.dll.
BCC32 -V -A4 -DWIN32 -TWM -TWCD-EfunClib.dll udf.c mygds32.lib
In order to use the DLL locally, it must be in the bin directory (or the path to your environment). If you want to use remotely, it must be in the environment variable path of the user who can run the Interbase service, in default, it is in the system account's PATH.
[Use UDF]:
One but compile, connection, definition, one UDF can be used in the SQL statement.
They can do the following: Define the calculation of the field as part of the table definition, as a column expression in the View definition or in the stored procedure and trigger as part of SELECT, INSERT, UPDATE, and DELETE operations.
For example, use the calculation result as a field:
Create Table Name (First_name Varchar (20), Last_name Varchar (20),
Full_name_upper computed by
(Upper (first_name) | "" | Upper_name));
Be a column expression in View:
Create View Upper_Names (first_name, last_name) AS
Select Upper (n.first_name), Upper (n.last_name) from name n;
In the selection operation:
Select Substr (n.first_name, 2, 4) from name n where
Upper (n.last_name) = '
Moore
'; In insertion operation:
INSERT INTO NAME (first_name, last_name)
VALUES (RTRIM (: new_fname), RTRIM (: new_lname));
In the update operation:
Update name set last_name = r = r r (: new_lname) Where
Upper (n.last_name) = 'Jones'
In delete operation:
Delete from name where left (last_name, 3) = 'SMI';
Starters from: Mer Systems INC .. http://www.mers.com
