Automatic Firebird Upgrader

reelingripehalfSoftware and s/w Development

Dec 14, 2013 (3 years and 9 months ago)

83 views

Firebird /
Interbase

UDF


What are UDF’s


1.
User Defined Functions are DLL’s that perform specialised
functions inside the Firebird /
Interbase

engine.




Firebird /
Interbase

UDF


What are UDF’s


1.
User Defined Functions are DLL’s that perform specialised
functions inside the Firebird /
Interbase

engine.



Why do we need UDF’s


1.
The Firebird is very limited in the range of functions available
out of the box






Firebird /
Interbase

UDF


What are UDF’s


1.
User Defined Functions are DLL’s that perform specialised
functions inside the Firebird /
Interbase

engine.



Why do we need UDF’s


1.
The Firebird is very limited in the range of functions available
out of the box


2.
Every application area has its own specific requirements

e.g. Rounding a date back to a given day in the week


Presenting a date as just the month name and year






Firebird /
Interbase

UDF


What are UDF’s


1.
User Defined Functions are DLL’s that perform specialised
functions inside the Firebird /
Interbase

engine.



Why do we need UDF’s


1.
The Firebird is very limited in the range of functions available
out of the box


2.
Every application area has its own specific requirements

e.g. Rounding a date back to a given day in the week


Presenting a date as just the month name and year


3.
String handling functionality is difficult to achieve in pure
SQL






Firebird /
Interbase

UDF


Criteria for writing UDF’s


1.
Control remains with UDF until completion







Firebird /
Interbase

UDF


Criteria for writing UDF’s


1.
Control remains with UDF until completion


2.
Should be regarded as a critical section








Firebird /
Interbase

UDF


Criteria for writing UDF’s


1.
Control remains with UDF until completion


2.
Should be regarded as a critical section


3.
No state is preserved and the code should be thread safe








Firebird /
Interbase

UDF


Criteria for writing UDF’s


1.
Control remains with UDF until completion


2.
Should be regarded as a critical section


3.
No state is preserved and the code should be thread safe


4.
No database activity should take place as the DLL should
run quickly to completion







Firebird /
Interbase

UDF


Key Issues when creating UDF’s


1.
Most important
-

function must be exported and declared as
cdecl


Function will work if
cdecl

is omitted, but the stack will be
mashed on return from the function (DLL)







Firebird /
Interbase

UDF


Key Issues when creating UDF’s


1.
Most important
-

function must be exported and declared as
cdecl


Function will work if
cdecl

is omitted, but the stack will be
mashed on return from the function (DLL)



2.
Memory management is the next critical issue


Passing integers /
reals

is the simple case









Firebird /
Interbase

UDF


Key Issues when creating UDF’s


1.
Most important
-

function must be exported and declared as
cdecl


Function will work if
cdecl

is omitted, but the stack will be
mashed on return from the function (DLL)


2.
Memory management is the next critical issue


Passing integers /
reals

is the simple case


3.
Strings can be passed two ways



1.
Via the
ShareMem

unit in the BorlandMM.dll

2.
Via
pchar

or
ShortString

parameters












Firebird /
Interbase

UDF


Key Issues when creating UDF’s


4.
Memory allocation for strings


only needed when returning
string results


ib_util_malloc
(
size_of_memory_needed
)














Firebird /
Interbase

UDF


Key Issues when creating UDF’s


4.
Memory allocation for strings


only needed when returning
string results


ib_util_malloc
(
size_of_memory_needed
)


Must have
ib_util

in the uses clause














Firebird /
Interbase

UDF


Key Issues when creating UDF’s


5.
Timestamps are handled via the TM record structure. This
must be defined as a type


TM = record


tm_sec

: integer; // Seconds


tm_min

: integer; // Minutes


tm_hour

: integer; // Hour (0
--
23)


tm_mday

: integer; // Day of month (1
--
31)


tm_mon

: integer; // Month (0
--
11)


tm_year

: integer; // Year (calendar year minus 1900)


tm_wday

: integer; // Weekday (0
--
6) Sunday = 0)


tm_yday

: integer; // Day of year (0
--
365)


tm_isdst

: integer; // 0 if daylight savings time is not in effect)


end;


PTM = ^TM















Firebird /
Interbase

UDF


Key Issues when creating UDF’s


6.
Timestamps are defined as the type


PISC_TIMESTAMP


Example of two different function declarations



function
Date_align
(
var

ib_date
: PISC_TIMESTAMP;
var

wk_end
,
day_no:integer
): PISC_TIMESTAMP;
cdecl
; export;




function
Date_mn_yr
(
var

ib_date
: PISC_TIMESTAMP):
pchar
;
cdecl
; export;


7.
Needed units in the uses clause

uses
ib_util
,
IBHeader
,
Sysutils
;


















Firebird /
Interbase

UDF


Key Issues when creating UDF’s


6.
Returning result by value or by pointer


How the function returns the result must be declared in the
function definition in the actual database where the function
is declared


Two key words


FREE_IT use when value returned on stack (
malloc
)

BY VALUE use when simple value
is returned



control how the stack is cleaned after the execution of the
function. Critical to get this right else memory leaks and or
exceptions will occur