Click here to Skip to main content
Click here to Skip to main content

Writing UDFs for Firebird Embedded SQL Server

, , 23 Oct 2009
Rate this:
Please Sign up or sign in to vote.
We will describe how to create your own native Firebird extension and show some approaches how to use it in managed code applications

Table of Contents

  • Who may be interested
  • What is UDFs
  • What do you require
  • Sample project structure
  • About the sample
  • Creating UDFs-based binary data parser
    • UDFs Declaration
    • UDFs implementation
    • Using UDFs
  • Outside initialization
  • Conclusion

Who May Be Interested

This article was written mainly for developers who use Firebird Embedded SQL Server in .NET Framework applications and want to speed up or optimize DB queries.

We will describe how to create your own native Firebird extension and show some approaches how to use it in managed code applications.

What are UDFs

User-defined functions (UDFs) are host-language programs for performing frequently needed tasks, supplementing built-in SQL functions such as LOWER() and UPPER(). UDFs are extensions to the Firebird server and execute as part of the server process. In other words, UDFs are compiled functions and linked to a dynamically-linked library.

What Do You Require

To effectively read this article, you need some advanced knowledge of C++, C# and Firebird SQL. Also you require to have VS 2005 with C# and C++ installed to build sample. The sample already has “batteries inside”: all files required for Firebird embedded server and ADO.NET provider are placed in the sample's Firebird folder and used in post-build event.

Sample Project Structure

.\Firebird – folder with all files required for Firebird Embedded SQL Server
   |? .\include – folder with *.h files required for UDFs compiling
   |? .\lib – folder with static libraries required for UDFs linking
.\MainApp – sample managed application
.\ SampleUdf – sample UDFs dll

About the Sample

The sample project shows how to transfer binary data (BLOB) from one table using UDFs-based parser object to another table:

[Code from .\MainApp\batch.sql]

CREATE TABLE RowDataTable (
    Id INTEGER NOT NULL PRIMARY KEY
  , Data BLOB
  )

CREATE TABLE FSTable
 (
    Id INTEGER NOT NULL PRIMARY KEY
  , FileName VARCHAR(256)
  , FullPath VARCHAR(256)
  , CreationTime TIMESTAMP 
  , Attributes INTEGER
  , FileSize BIGINT
  )

Creating UDFs-based Binary Data Parser

UDFs Declaration

Firebird SQL has the following syntax to declare UDF:

DECLARE EXTERNAL FUNCTION name [datatype | CSTRING (int)
[, datatype | CSTRING (int) ...]]
RETURNS {datatype [BY VALUE] | CSTRING (int)} [FREE_IT]
[RETURNS PARAMETER n]
ENTRY_POINT 'entryname'
MODULE_NAME 'modulename';

Argument Description
name Name of the UDF to use in SQL statements; can be different from the name of the
function specified after the ENTRY_POINT keyword
datatype Datatype of an input or return parameter
• All input parameters are passed to the UDF by reference
• Return parameters can be passed by value
• Cannot be an array element
RETURNS Specifies the return value of a function. Pay attention that syntax does not allow to declare UDF that returns nothing
BY VALUE Specifies that a return value should be passed by value rather than by reference
CSTRING (int) Specifies the UDF that returns a null-terminated string int bytes in length
FREE_IT Frees memory allocated for the return value after the UDF finishes running. This parameter should be used with ib_util_malloc memory allocation function in UDF implementation, It’s contained in:
  • Header: ib_util.h
  • Library: ib_util_ms.lib
  • DLL: ib_util.dll
RETURNS PARAMETER n Specifies that the function returns the nth input parameter; is required for returning
Blobs
'entryname' Quoted string specifying the name of the UDF in the source code and as stored in
the UDF library
'modulename' Quoted file specification identifying the DLL that contains the UDF

All DLLs with UDFs should be placed to UDF folder in the application root where fbembedded.dll is stored. When declaration query is going for execution, Firebird engine does not require UDF DLL to be placed in UDF folder in that moment. But when executing some stored procedure creation query that contains UDF call engine will check required external function in DLL.

Here are some UDF declaration examples from the sample project:

[Code from .\MainApp\batch.sql]

DECLARE EXTERNAL FUNCTION CreateParser
	BLOB
        RETURNS INTEGER BY VALUE
  ENTRY_POINT 'SampleUdf_CreateParser'  MODULE_NAME 'SampleUdf'

DECLARE EXTERNAL FUNCTION DestroyParser
	INTEGER
	RETURNS INTEGER BY VALUE
  ENTRY_POINT 'SampleUdf_DestroyParser'  MODULE_NAME 'SampleUdf'

DECLARE EXTERNAL FUNCTION GetName
	INTEGER
	RETURNS CSTRING(256)
  ENTRY_POINT 'SampleUdf_GetName'  MODULE_NAME 'SampleUdf'

DECLARE EXTERNAL FUNCTION GetCreationTime
	INTEGER
	RETURNS TIMESTAMP FREE_IT
  ENTRY_POINT 'SampleUdf_GetCreationTime'  MODULE_NAME 'SampleUdf'

DECLARE EXTERNAL FUNCTION GetSize
	INTEGER
	RETURNS BIGINT
  ENTRY_POINT 'SampleUdf_GetSize'  MODULE_NAME 'SampleUdf'	

UDFs Implementation

As you may guess, I'm using integer type parameter to send parser object that was created in SampleUdf.dll heap. With parser object, everything is very simple:

[Code from .\SampleUdf\main.cpp]

class SampleParser
{
	std::vector<char> _buffer;
	size_t _pathLen;
	char * _name;
public:
	SampleParser(std::vector<char> & buffer)
	{
		_buffer.swap(buffer);
		char * path = (char*)&_buffer.at(0);
		_pathLen = strlen(path);
		if(_pathLen < 1 ||  _pathLen >= _buffer.size())
			throw std::exception("Invalid buffer format");
		_name = strrchr(path, '\\');
		if(!_name) _name = path;
		else ++_name;
		ON_MESSAGE("--SampleParser created--")
	}
	~SampleParser()
	{
		ON_MESSAGE("--SampleParser destroyed--")
	}

	char * GetName() { return _name; }
	char * GetFullPath() { return (char*)&_buffer.at(0); }
	__int64 * GetCreationTime() { return (__int64*)&_buffer.at(_pathLen + 1); }
	int * GetAttributes() { return (int*)&_buffer.at
				(_pathLen + 1 + sizeof(__int64)); }
	__int64 * GetSize() { return (__int64*)&_buffer.at
				(_pathLen + 1 + sizeof(__int64) + sizeof(int)); }
};

The next UDF demonstrates how a parser is created and also the work with BLOB data:

extern "C" __declspec(dllexport) SampleParser * 
	SampleUdf_CreateParser(BLOBCALLBACK data)
{
	try
	{
		std::vector<char> buffer(data->blob_total_length);
		ISC_UCHAR * p = (ISC_UCHAR*)&buffer.at(0);
		for(ISC_LONG i=0; i < data->blob_number_segments; ++i)
		{
			ISC_USHORT length = 0;
			data->blob_get_segment(data->blob_handle, p, 
					data->blob_max_segment, &length);
			p+= length;
		}
		return new SampleParser(buffer);
	}
	catch(std::exception & ex)
	{
		ON_ERROR(ex.what());
	}
	catch(...)
	{
		ON_ERROR("Unknown error");
	}
	return 0;
}

And now let’s show how to use parser object. The following function also shows how to:

  • convert FILETIME structure to Firebird TIMESTAMP
  • use embedded server memory allocation, when you transfer memory ownership to the database engine
extern "C" __declspec(dllexport) ISC_TIMESTAMP * SampleUdf_GetCreationTime(int * ptr)
{
	try
	{
		SampleParser * self = (SampleParser*)(*ptr);

		FILETIME localTime;
		if(!::FileTimeToLocalFileTime((const FILETIME*)self->
					GetCreationTime(), &localTime))
			return 0;
		SYSTEMTIME st;
		if(!::FileTimeToSystemTime(&localTime, &st))
			return 0;

		ISC_TIMESTAMP * timeStamp = (ISC_TIMESTAMP*)
				ib_util_malloc(sizeof(ISC_TIMESTAMP));

		timeStamp->timestamp_time = (st.wHour * 3600000 + 
		    st.wMinute * 60000 + st.wSecond * 1000 + st.wMilliseconds) * 10;

		WORD day = st.wDay;
		WORD month = st.wMonth;
		WORD year = st.wYear;

		//some magic calculations from ADO.NET Provider code
		if (month > 2)
			month -= 3;
		else
		{
			month += 9;
			year -= 1;
		}
		WORD c = year / 100;
		WORD ya = year - 100 * c;

		timeStamp->timestamp_date = ((146097 * c) / 4 + 
		    (1461 * ya) / 4 + (153 * month + 2) / 5 + day + 1721119 - 2400001);
		return timeStamp;
	}
	catch(std::exception & ex)
	{
		ON_ERROR(ex.what());
	}
	catch(...)
	{
		ON_ERROR("Unknown error");
	}
	return 0;
}

As you can see, all input and output parameters in UDF implementations are pointers, except output parameters declared with BY VALUE modifier. But this modifier can be used not with all data types (for example, it’s incompatible with TIMESTAMP, but compatible with BIGINT – it is strange behavior for the types of the same size, isn’t it?)

Using UDFs

The next stored procedure demonstrates how to use created parser:

[Code from .\MainApp\batch.sql]

CREATE PROCEDURE TransferData
RETURNS (counter INTEGER)
AS
DECLARE VARIABLE tmp INTEGER;
DECLARE VARIABLE parserPtr INTEGER;
DECLARE VARIABLE Data BLOB;
DECLARE VARIABLE FileName VARCHAR(256);
DECLARE VARIABLE FullPath VARCHAR(256);
DECLARE VARIABLE CreationTime TIMESTAMP;
DECLARE VARIABLE Attributes INTEGER;
DECLARE VARIABLE FileSize BIGINT;
BEGIN
	counter = 0;
	FOR SELECT Data FROM RowDataTable INTO :Data DO	BEGIN
		parserPtr = CreateParser(:Data);
		IF (parserPtr IS NOT NULL) THEN BEGIN
			FileName = GetName(:parserPtr);
			FullPath = GetFullPath(:parserPtr);
			CreationTime = GetCreationTime(:parserPtr);
			Attributes = GetAttributes(:parserPtr);
			FileSize = GetSize(:parserPtr);
			
			tmp = GEN_ID(FSTable_Generator, 1);
			INSERT INTO FSTable (Id, FileName, FullPath, _
				CreationTime, Attributes, FileSize)
			VALUES (:tmp, :FileName, :FullPath, _
				:CreationTime, :Attributes, :FileSize);
			counter = counter + 1;

			tmp = DestroyParser(:parserPtr);
		END
	END
	SUSPEND;
END

If you want to use SELECT for UDF calling, use the following syntax:

SELECT <UDF_Name>(<Parameters_List>) FROM rdb$database INTO <Output_parameter>

Outside Initialization

Thanks to Windows caching, there is a possibility to load and initialize UDFs DLL before it is loaded by Firebird database engine. In my sample, I used this possibility to make some useful callbacks:

[Code from .\MainApp\SampleProvider.cs]

private delegate void MessageCallbackDelegate
([MarshalAs(UnmanagedType.LPStr)] string message);

private static MessageCallbackDelegate messageCallback;
private static MessageCallbackDelegate errorCallback;

[DllImport("udf/SampleUdf")]
private static extern void RegisterCallbacks
	(MessageCallbackDelegate messageCallback, 
	MessageCallbackDelegate errorCallback);

static SampleProvider()
{
    messageCallback = MessageCallback;
    errorCallback = ErrorCallback;
    RegisterCallbacks(messageCallback, errorCallback);
}

public static void MessageCallback(string message)
{
    Trace.WriteLine(message, "Message");
}
public static void ErrorCallback(string message)
{
    Trace.WriteLine(message, "Error");
}  

[Code from .\SampleUdf\main.cpp]

typedef void (__stdcall * FCallback)(const char * message);
FCallback g_messageCallback = 0;
FCallback g_errorCallback = 0;
//---------------------------------------------------------------------------
#define ON_MESSAGE(mess) { if(g_messageCallback) g_messageCallback(mess); }
#define ON_ERROR(mess) { if(g_errorCallback) g_errorCallback(mess); }
//---------------------------------------------------------------------------
extern "C" __declspec(dllexport) void RegisterCallbacks
	(FCallback messageCallback, FCallback errorCallback)
{
	g_messageCallback = messageCallback;
	g_errorCallback = errorCallback;
}

And when Firebird database engine will try to load UDFs DLL, it will use your already loaded and initialized library.

Conclusion

So with this article, you see how process shared address namespace allows you to use native or managed objects in your UDFs.

Read about more development tips at Apriorit official site.

History

  • Initial version - 21/10/2009
  • Fixed UDF calling syntax. Thanks to Dmitry Kuzmenko for review - 23/10/2009
  • Some syntax changes - 24/10/2009

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Authors

Ivan_Komarov

Ukraine Ukraine
No Biography provided

Apriorit Inc
Apriorit Inc.
Ukraine Ukraine
ApriorIT is a Software Research and Development company that works in advanced knowledge-intensive scopes.
 
Company offers integrated research&development services for the software projects in such directions as Corporate Security, Remote Control, Mobile Development, Embedded Systems, Virtualization, Drivers and others.
 
Official site http://www.apriorit.com
Group type: Organisation

31 members

Follow on   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 24 Oct 2009
Article Copyright 2009 by Ivan_Komarov, Apriorit Inc
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid