SQL2GDB UDF Discovery

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.

Overview

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.

Discovery

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.