Writing UDFs for Firebird Embedded SQL Server

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Written by:
Ivan Komarov,
Deputy Team Leader of Network Security Team

Table of content

1. Who may be interested
2. What is UDFs
3. What do you require
4. Sample project structure
5. About the sample
6. Creating UDFs-based binary data parser

• UDFs Declaration
• UDFs implementation
• Using UDFs

7. Outside initialization
8. 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 is UDFs

User-defined functions (UDFs) are host-language programs for performing frequently needed tasks, supplementing built-in SQL functions such as MIN() and MAX(). UDFs are extensions to the Firebird server and execute as part of the server process. Another 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 having 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 .MainAppbatch.sql]

CREATE TABLE "RowDataTable" (
	"Id" INTEGER NOT NULL PRIMARY KEY
      "Value" BLOB
  )

CREATE TABLE "FSTable" (
   "Id" INTEGER NOT NULL PRIMARY KEY
  , "Name" VARCHAR(256)
  , "FullPath" VARCHAR(256)
  , "CreationTime" TIMESTAMP
  , "Attributes" INTEGER
  , "Size" BIGINT
  )

Creating UDFs-based binary data parser

UDFs Declaration

Firebird SQL has 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';

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 sample project:

[Code from .MainAppbatch.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 .SampleUdfmain.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)); }
};