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

1. Outside initialization
2. 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’;

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 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)); }
};

The next UDF demonstrates how 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.front();
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’s 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 .MainAppbatch.sql]

CREATE PROCEDURE TransferData
RETURNS (”counter” INTEGER)
AS
DECLARE VARIABLE “tmp” INTEGER;
DECLARE VARIABLE “parserPtr” INTEGER;
DECLARE VARIABLE “Value” BLOB;
DECLARE VARIABLE “Name” VARCHAR(256);
DECLARE VARIABLE “FullPath” VARCHAR(256);
DECLARE VARIABLE “CreationTime” TIMESTAMP;
DECLARE VARIABLE “Attributes” INTEGER;
DECLARE VARIABLE “Size” BIGINT;
BEGIN
“counter” = 0;
FOR SELECT “Value” FROM “RowDataTable” INTO :”Value” DO BEGIN
SELECT CreateParser(:”Value”) FROM rdb$database INTO :”parserPtr”;
IF (”parserPtr” IS NOT NULL) THEN BEGIN
SELECT GetName(:”parserPtr”) FROM rdb$database INTO :”Name”;
SELECT GetFullPath(:”parserPtr”) FROM rdb$database INTO :”FullPath”;
SELECT GetCreationTime(:”parserPtr”) FROM rdb$database INTO :”CreationTime”;
SELECT GetAttributes(:”parserPtr”) FROM rdb$database INTO :”Attributes”;
SELECT GetSize(:”parserPtr”) FROM rdb$database INTO :”Size”;

“tmp” = GEN_ID(”FSTable_Generator”, 1);
INSERT INTO “FSTable” (”Id”, “Name”, “FullPath”, “CreationTime”,
“Attributes”, “Size”)
VALUES (:”tmp”, :”Name”, :”FullPath”, :”CreationTime”, :”Attributes”, :”Size”);
“counter” = “counter” + 1;
 
SELECT DestroyParser(:”parserPtr”) FROM rdb$database INTO :”tmp”;
END
END
SUSPEND;
END

About strange form of calling :

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

It’s the only way to make it working in embedded dll server

Outside initialization

Thanks to the 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 .MainAppSampleProvider.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);
}

[Code from .SampleUdfmain.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 you UDFs.

Download source code.

Apriorit is an Ukrainian software development company.

Apriorit develops its own products as well as provide offshore development and QA services in the areas of advanced system programming, driver development, software for devices.

One of the key values of Apriorit’s specialists is knowledge generation and sharing of experience.

Learn more about Apriorit and its experience at Apriorit Official site

Article Source:http://www.articlesbase.com/programming-articles/writing-udfs-for-firebird-embedded-sql-server-1376236.html

Written by: OSAblogger / Bill Wardell - Please Read Our Latest OSA eZine Edition

Other Places You Can Find Me…

Digg - LinkedIn - OSA Community - Facebook - StumbleUpon - MyBlogLog

If you're a concerned parent, you may want to subscribe to the: OSA~RSS while your here, please JOIN our: OSA Forum... also Follow Me On Twitter Thanks for visiting!

OSA Technorati Tags: Batteries, Binary Data, Blob, Code Applications, Deputy Team Leader, Dll, Firebird, Framework Applications, Host Language, Ivan, Key Value, Komarov, Language Programs, Network Security Team, Parser Object, Project Structure, Sql Functions, Static Libraries, Table Of Content, Udfs

OSA Related Posts

• Sales Tsunami and Internet Marketing Secrets: Best instrument of CPA marketing Easy Steps To Benefit From Google Adwords TrackingAdwords tracking is called conversion tracking by Google.Google offers many free tools to their clients, but this is the tool you need to become successful with Adwords.Adwords tracking helps the website owner track the number of conversions their ad prompts, and thus With......
• Virtosoftware has released Virto Password Reset web part for SharePoint Virtosoftware extends its product line by offering a new web part for managing Active Directory (AD) and Form Based Authentication (FBA) user passwords. Virto Password Reset web part is a powerful and extremely simple tool for helping SharePoint users who forget their passwords or let them expire due to password......
• Dot Net Development: An Integral Part of Web Application Development What is Dot Net Development Service? Dot Net development services signify the development of web applications with the help of Dot Net Architecture released by the Microsoft Corporation. A bunch of the web application development work that is being outsourced to low cost locations is generally framed on the Dot......
• Website Design in Dallas – Functionality & Performance are Key Website design in Dallas must focus on functionality and performance.  Too many website designers focus on the bells and whistles that bog down websites and turn-off website visitors.  Most consumers make a decision on whether or not to stay on a website in less than two seconds.   The most......
• 5 ways to prevent IT failure Rather than acknowledge their own management shortcomings, project participants often blame technology for failed IT projects. This denial commonly arises when an organization’s culture uses blame, and the corresponding implicit threat of job loss, as a political weapon. Denial aside, most failures arise from poor management rather than bad technology.......

OSA Related Websites

• Gold Coins Coins and Paper Money -> Coins: US -> Gold Before you decide to purchase gold coins, there are a few things that you need to know to avoid being taken advantage of. While most dealers are honest, it is vital to take these steps to ensure you are dealing with......
• Setting a Product Price Point One common worksheet task is to calculate a list price for a product based on the result of a formula that factors in production costs and profit margin. If the product is sold at retail, you likely need the decimal (cents) portion of the price to be .95 or .99......
• Quicken Online Review Recently, Quicken Online has switched to a free service in order to compete with other free account aggregators. I have never consistently used an account aggregator to manage my personal finances, I have mostly used the Excel and file folder method. I gave Mint a shot when it first arrived......
• Save Time, Money and Space in Over 80 Ways If you're looking for handy gadgets, tools and various items that can save you time, money or space (or all three!) this list of more than 80 top products is just what you need. Everyone's got saving money on their minds these days. Some of us are always looking to......
• Shopping Professionally pt 2 One of the best ways that you can save money is to shop professionally, which is learning how to develop skills and talents in shopping that allow you to shop smarter and save more money than ever before. When you learn how to work with a tight budget, you can......