This document describes the user-defined function (UDF) discovery mechanism that the MS SQL and Access To InterBase Wizard (SQL2GDB for short) uses. This document is meant for developrs or administrators who wish to add new libraries to the SQL2GDB wizard, troubleshoot migration problems, and the terminally curious.
Before any other step takes place, SQL2GDB enables the user to load User-Defined Functions into the database. This is done simply by declaring them before any other script is executed.
Using UDF libraries allows the developer to create more complex scripts and have additional functionality when developing stored procedures and triggers. It also enables SQL2GDB to do some advanced tricks, like importing blobs from text files or (in the future) enabling advanced features such as mailing and scripting on the database server.
SQL2GDB has no hardcoded libraries with UDFs to declare to the database. Rather, it dynamically inspects the directory it runs in, and allows the user to select which UDF libraries to declare in one of the customization steps.
SQL2GDB will examine the directory from which it is executed, listing all files with a sql extension. These are candidates for migration.
Because an SQL script needs to be executed for each
function to be imported, a file named
library_name_setup.sql
is expected
to be found on that same folder. For example, the
SQL file describing the required SQL statements to
declare functions from MyUDFs.dll
should
be named MyUDFs_setup.sql
. This condition
is enforced to enable later versions of SQL2GDB to
deploy the libraries as needed.
The files with declarations should have the statements one after the other, finishing with a semicolon. The wizard does not check whether the syntax is correct or the files are available, but will detect the server's complaint if this is the case.
The following listing is used, for example, to import some of the FreeUDFLib functions, available from ftp://firebird.sourceforge.net/pub/firebird/download/freeudflib.zip.
/* Blob functions */ declare external function f_BlobMaxSegmentLength blob returns integer by value entry_point 'BlobMaxSegmentLength' module_name 'FreeUDFLib.dll'; declare external function f_BlobSegmentCount blob returns integer by value entry_point 'BlobSegmentCount' module_name 'FreeUDFLib.dll';
In particular, for the FreeUDFLib library, the supplied ext_funcs.sql works fine. It should be renamed FreeUDFLib_setup.sql, however, to have the wizard recognize it. The renamed FreeUDFLib_setup.sql file is supplied with this distribution.
Important: to be used with InterBase 6, libraries should be copied to the UDF directory under the installation. They won't run if they are placed anywhere else.