Main rFunc UDF Library for InterBase
(version 2.0.1)

    The library represents a set of user's (UDF) string, bit, numerical functions, and also for operation with dates&time and BLOBs. And also PARSER, i.e. calculator of expressions. Are supported IB Database 4.2, 5.x, 6.0 (Windows 9x, NT, 2k) and IB Database 5.x, 6.0 (Linux). The library include more than 90 functions and "pseudo-functions" (it when some string UDFs have one base function, in main it is functions with the LongXXXX name). The library is written on C++ and is delivered with source codes.

The order of compilation and installation is described in the readmeeng.txt file from the distribution archive.

functions

Legend:
f The parameter such as DOUBLE PRECISION
si The parameter such as SMALLINT
i The parameter such as INTEGER
d The parameter such as DATE for IB4.2 and IB5.x or TIMESTAMP for IB6
s The parameter such as VARCHAR(255)
c The parameter such as CHAR(1)
s' The parameter such as CHAR(8)
s" The parameter such as CHAR(32)
S The parameter such as VARCHAR(16383)
S** The parameter such as VARCHAR(8127) for IB4.2 or VARCHAR(16383) for IB5.x and IB6
B The parameter such as BLOB

Function Type Returns
Numerical
Abs(f) f The module of number
Ceil(f) f Minimum greater whole
Floor(f) f The maximum smaller whole
MaxNum(f, f) f Greater of numbers
MinNum(f, f) f Smaller of numbers
Power(f1, f2) f Number f1 in a degree f2
Round(f, i) f The rounded off value f up to i signs, and for want of i > 0 is rounded off a fractional part, and for want of i < 0 - whole
SoftRound(f, i) f Similarly Round, but if the outcome of roundoff is equal 0, the roundoff is not produced.
Z(f) f If the parameter has value NULL, 0. In all other cases that is transferred comes back.
IIf(i1, i2, i3) i If i1=0, i3, in other cases - i2
DIf(i, f1, f2) f If i=0, f2, in other cases - f1
DZero(f1, f2, f3) f Quotient f1 and f2. If f2 = 0, returns f3.
CalcExpr(S, S)

For example:
CalcExpr('Sin( Pi*:p11/3)*:P2', 'P11=2;P2=10')
f The unpretentious analyzer of expressions.
Supported functions and operations

+ - * /
Sin, Cos, tg, ctg, ArñCos, ArcSin, ArcTg, sh, ch, th,
exp, lh, lg, sqrt, sqr, abs(x1)
sgn(x1) - the sign of number x1
log(x1,x2) - log x1 on the basis x2
power(x1,x2) - exponentation x1 in a degree x2
if(expr, x1, x2) - if expr=0, then x2, else x1
Min(x1, ...) - minimum from numbers
Max(x1, ...) - maximum from numbers
Sum(x1, ...) - toting of arguments
Avg(x1, ...) - average arithmetic
Pi - number Pi.

     For usage of variables in expression - before them the colon is put, and they are described in the second parameter (see example).

ExprIsValid(S, S) i Check on a correctness of the given expression. Returns 0, if all is right, differently - code of an error.
Div(i1, i2) i The whole quotient i1 and i2
Mod(i1, i2) i Residual from division i1 on i2
InitRandom(i) i Initialization of the generator of pseudorandom numbers.
GetRandom(i) i Pseudorandom numbers in a range 0..i.
String
Chr(si) c Character on the code
Ord(c) si The code of character
StrReplace(s1, s2, s3) s Substitutes in string s1 a substring s2 on s3
LongStrReplace(S1, S**2, S**3) S
StrStuff(s1, i1, i2, s2) s In string s1 deletes i2 of characters, since i1, and inserts on this place s2
LongStrStuff(S1, i1, i2, S2) S
LTrim(s) s Delete blanks at the left
LongLTrim(S) S
RTrim(s) s Delete blanks on the right
LongRTrim(S) S
StrLen(S) i Length of string
StrPos(s1, S2) i Position of string s1 in string s2. If not is found, 0.
StrRepeat(s, i) s Duplicates string s i times
LongStrRepeat(S, i) S
SubStr(s, i1, i2) s Substring of string s, since character i1 and length a maxima i2.
I1 < 0 - counting happens from an extremity of string,
I2 < 0 - the characters to the left of a point of counting are output.
LongSubStr(S, i1, i2) S
Trim(s) s Delete all blanks on the right and at the left
LongTrim(S) S
CIf(i, s1, s2) s If i = 0, s2, in remaining cases - s1
LongCIf(i, S1, S2) S
StrCount(s1, S2) i Amount of entrances s1 in S2
WordCount(S, s", si) i Amount of words in string S, divided in characters from string s". If si <> 0, some separators the contract are considered as a word.
WordNum(S, i, s", si) s i-th word in string S, divided in characters from string s". If si <> 0, some separators the contract are considered as a word.
LongWordNum(S, i, s", si) S
RLower(s) s String in lowercase (WIN1251 only)
LongRLower(S) S
RUpper(s) s String in uppercase (WIN1251 only)
LongRUpper(S) S
PadLeft(s, i, c) s Supplements string s up to length in i character c at the left.
LongPadLeft(S, i, c) S
PadRight(s, i, c) s Supplements string s up to length in i character c at the right.
LongPadRight(S, i, c) S
RLatin(s) s Substitutes characters from win1251 on "similar" of a Latin.
LongRLatin(S) S
RTranslit(s) s Transliteration from win1251 in a Latin.
LongRTranslit(S) S
Dates & Time
DateToStr(d, s)

For example: DateToStr('10.02.99','%d.%m. %y') = '10.02.99' DateToStr('10.02.04 08:23','%d %B %Y, %X') =
'10 February 2004, 08:23:00'
s
Conversion of date d in string on a format s.
Formatting strings
(correspond with c-function strftime):

%% - characterl %,
%c - date and time,
%x - date,
%d - day of a month (01-31),
%j - day of year (000-366),
%w - day of week (0-6), 0 - Sunday,
%U - number of week in one year (00-53), Sunday - the first day of week,
%W - number of week in one year (00-53), Monday - the first day of week,
%a - the short name of day of week,
%À - the full name of day of week,
%m - month (01-12),
%b - the short name of day of month,
%B - the full name of day of month,
%y - year (00-99),    %Y - four-digit year,
%X - time,
%H - hour (00-23),   %I - hour (01-12),
%M - minute (00-59),
%p - AM or PM,
%S - second (00-59).

DayPerMonth(i1, i2) i Number of days in i1 a month i2 of a year
Dow(d) i Day of week
EncodeDate(i1, i2, i3) d Date on format i1.i2.i3 = dd.mm.yyyy
EncodeDateTime(i1, i2, i3, i4, i5, i6) d Date and time on format
i1.i2.i3 i4:i5:i6 = dd.mm.yyyy hh:nn:ss
ExtractDate(d) d Extracts date from date+time
Day(d) i Day of date [IB 4.2 and 5.x only]
Month(d) i Month of date [IB 4.2 and 5.x only]
Year(d) i Year of date [IB 4.2 and 5.x only]
Hour(d) i Hour of time [IB 4.2 and 5.x only]
Minute(d) i Minute of time [IB 4.2 and 5.x only]
Second(d) i Second of time [IB 4.2 and 5.x only]
WeekDay(d) i Day of week [IB 4.2 and 5.x only]. Similar DOW.
YearDay(d) i Day of year [IB 4.2 and 5.x only]
Quarter(d) i Quarter of a year
FirstDayMonth(d) d The first day of a month
LastDayMonth(d) d The last day of a month
IsLeapYear(i) i 1 - leap-year, 0 - unleap
DaysBetween(d, d) i Amount of days between dates
DtIf(i, d1, d2) d If i = 0, d2, in other cases - d1
IncDate(d, i1, i2, i3) d Change of date on i1 days, i2 months, i3 years.
BLOB
B_Line(B, i) s i-th line of B
B_LongLine(B, i) S
B_Line_Count(B) i Amount of lines
B_Max_Segment(B) i Maximum length of a segment
B_Number_Segments(B) i Amount of segments
B_Put_Segment(S) B Put a segment in a blob-field (example see in rtest.sql)
B_SubStr(B, i1, i2) s Similar SubStr
B_LongSubStr(B, i1, i2) S
B_Total_Length(B) i Amount of bytes in B
Bit
BitAnd(i1, i2) i i1 and i2
BitNot(i) i not(i)
BitOr(i1, i2) i i1 or i2
BitXor(i1, i2) i i1 xor i2
GetBit(i1, i2) i i2-th bit of number i1
SetBit(i1, i2, i3) i Substitutes i2-th bit of number i1 on i3. i3 can accept values 0 and 1.
API

Add_User(i, s, s"1, s'1, s"2, s"3, s"4, s"5, s"6, s'2)

For example:
Add_User(1, 'ibserver', 'name', 'password', '', 'alex', 'v', 'petrov', 'sysdba', 'masterke')

i Addition and change of properties of the user. API IB Database. Adds (changes) the user with the s"1 name on the s server from UNIX-group s"2. The connection is produced under protocol i (1-TCPIP, 2-NetBEUI, 3-SPX, 4-locally) user s"6 with the password s'2. Parameters: s'1 - password, s"3 - name, s"4 - patronymic, s"5 - surname. Returns an exit code (0-normal). [only IB 5.x and is higher]
Modify_User(i, s, s"1, s'1, s"2, s"3, s"4, s"5, s"6, s'2) i
Delete_User(i, s, s"1, s"6, s'2) i Deleting of the user. API IB Database. Deletes the user with the s"1 name on the s server. The connection is produced under protocol i (1-TCPIP, 2-NetBEUI, 3-SPX, 4-locally) user s"6 with the password s'2. [only IB 5.x and is higher]
MsgBox(S, s, i) i

The window of the message with possibility of a choice. It is not recommended to use on remote servers. API Windows. Outputs the window with header s and text S. [only for Windows]

changes history

On all questions and remarks connected with rFunc UDF Library, please, access to the address rFunc@mail.ru.

Copyright © 2001 Polaris Software