Functions

AggregateCRC32MBS(data as any) as integer

Version: 1.0

Returns the aggregated CRC checksum of data. The return value is a 32 bit unsigned integer which is stored in a 64 bit integer.

Works with blob, text, integer, null and double values.

Example query:

select AggregateCRC32MBS(data) from test

AggregateMD5MBS(data as any) as integer

Version: 1.0

Returns the aggregated MD5 checksum of data. The return string is a 32 character long hex string.

Works with blob, text, integer, null and double values.

Example query:

select AggregateMD5MBS(data) from test

CeilMBS(value as double) as double

Version: 1.0

The ceil() functions return the smallest integral value greater than or equal to x.

Example queries:

select CeilMBS(5)
select CeilMBS(4.5)

ComputerNameMBS as string

Version: 1.0

Returns the name of the computer the plugin is running on.

Example queries:

select ComputerNameMBS()

CountFieldsMBS(text as string, delimiter as string) as integer

Version: 1.0

For a text with delimiters like "Only;a;test;string" with 4 words and the semicolon as delimiter, this function can count the number of words between delimiters. If text is empty, the result is zero. If delimiter is empty, the result is 1. For the example string above the result is 4.

This function can be used to count the number of times a substring is inside a bigger string if you take the result and subtract one. CountFieldsMBS("Only;a;test;string", ";")-1 is 3 as we have three semicolon.

This function is optimized for UTF8 and UTF16.

Example queries:

select CountFieldsMBS("Hello;World",";")
select CountFieldsMBS("a b c"," ")

CRC32MBS(data as any) as integer

Version: 1.0

Returns CRC of data in the blob. The return value is a 32 bit unsigned integer which is stored in a 64 bit integer.

Works with blob, text, integer, null and double values.

Example query:

select name, CRC32MBS(data) from test
select name from test where CRC32MBS(data)=219276033

DecodeHexMBS(text as string) as blob

Version: 1.0

Decodes the data in a hex string into a blob.

DeleteBlobMBS(path as string) as string

Version: 1.0

Deletes the blob file on the given path.

Returns "OK" or "Failed".

Be careful with this function. If used wrong, the user can delete every file on the computer. Especially if the server runs with admin previleges.

EncodeHexMBS(data as blob) as string

Version: 1.0

Encodes the data as a string in the hex format.

ExpMBS(value as double) as double

Version: 1.0

The exp() function computes e**x, the base-e exponential of x.

Example queries:

select ExpMBS(5)
select ExpMBS(4.5)

FloorMBS(value as double) as double

Version: 1.0

The floor() functions return the largest integral value less than or equal to x.

Example queries:

select FloorMBS(5)
select FloorMBS(4.5)

LeftMBS(text as string, count as integer) as string

Version: 1.0

Returns a string with the first count number of characters from the source string.

Example queries:

select LeftMBS("Hello World",5)
select LeftMBS("Hello World",100)

LicenseInfoMBS as string

Version: 1.0

Returns text information from the license status.

Example querys:

select LicenseInfoMBS()

Log10MBS(value as double) as double

Version: 1.0

The log10() function computes the value of the logarithm of argument x to base 10.

Example queries:

select Log10MBS(5)
select Log10MBS(4.5)

LogMBS(value as double) as double

Version: 1.0

The log() function computes the value of the natural logarithm of argument x.

Example queries:

select LogMBS(5)
select LogMBS(4.5)

MD5MBS(data as any) as integer

Version: 1.0

Returns MD5 checksum of data in the blob. The return string is a 32 character long hex string.

Works with blob, text, integer, null and double values.

Example query:

select name, MD5MBS(data) from test
select name from test where MD5MBS(data)="70F9E7AC5FF10D98971FAFBA98C8D141"

MidMBS(text as string, position as integer) as string

Version: 1.0

Returns a string with the characters from the source string starting with the character at the given position.

Example queries:

select MidMBS("Hello World",3)
select MidMBS("Hello World",0)

MidMBS(text as string, position as integer, count as integer) as string

Version: 1.0

Returns a string with the characters from the source string starting with the character at the given position and with the given maximum length.

Example queries:

select LeftMBS("Hello World",5)
select MidMBS("Hello World",3,50)

NthFieldMBS(text as string, delimiter as string, fieldNumber as integer) as string

Version: 1.0

Returns a field from a row of data. The first field is numbered 1.

The NthField function returns the field value from the source that precedes the fieldNumber occurrence of the separator in the source. If fieldNumber is out of bounds, an empty string is returned. NthField is not case-sensitive.

If the text is empty, the delimiter is empty of the index is negative, an empty string is returned.

Example queries:

select NthFieldMBS("Hello;World",";",1)
select NthFieldMBS("Hello;World",";",2)
select NthFieldMBS("Hello;World",";",3)

PowMBS(x as double, y as double) as double

Version: 1.0

The pow() functions compute x raised to the power y.

Example queries:

select PowMBS(5,2)
select PowMBS(25,0.5)

ReadBlobMBS(data as blob, path as string) as blob

Version: 1.0

Returns the blob if it is not null.
Or reads the file from the given path and returns the content as blob.
Path must be native path and should only contain ASCII characters.

Be careful with this function. If used wrong, the user can read every file on the computer. Especially if the server runs with admin previleges.

Example queries:

select ReadBlobMBS(null,name) as rdata from test
select ReadBlobMBS(null,name) from test where LeftMBS(name,1)="/"

ReadBlobPartMBS(data as blob, path as string, offset as integer, size as integer) as blob

Version: 1.0

Returns the blob if it is not null.
Or reads the file from the given path and returns the content as blob.
Path must be native path and should only contain ASCII characters.

This variant of the ReadBlobMBS function reads only a part of the blob file. If the file size is not big enough, a smaller chunk is returned.

Be careful with this function. If used wrong, the user can read every file on the computer. Especially if the server runs with admin previleges.

Example queries:

select ReadBlobPartMBS(null,name,0,10000) as rdata from test
select ReadBlobPartMBS(null,name,20000,10000) from test where LeftMBS(name,1)="/"

RegExP

Version: 1.0

Performs a regular expression match.

Example query:

select * from myTable where someValue regexp "some regexp "(.*)e(.*)"

RegisterMBS(text as string) as string

Version: 1.0

Registers the plugin.

You can register by placing the Registration file in some location as described in the Installation readme file (better on the server). Or you can register with calling the RegisterMBS function (better on local database).

RemoveAccentsCaseInsensitiveMBS(text as string) as string

Version: 1.0

Returns text without accents. for example É to E and Ä to A

Example query:

select RemoveAccentsCaseInsensitiveMBS("Würstchen")

RemoveAccentsMBS(text as string) as string

Version: 1.0

Returns text without accents. for example é to e

Example query:

select RemoveAccentsMBS("Würstchen")

RightMBS(text as string, count as integer) as string

Version: 1.0

Returns a string with the last count number of characters from the source string.

Example queries:

select RightMBS("Hello World",5)
select RightMBS("Hello World",100)

RoundMBS(value as double) as double

Version: 1.0

The round() functions return the integral value nearest to x rounding half-way cases away from zero, regardless of the current rounding direction.

Example queries:

select RoundMBS(5)
select RoundMBS(4.5)

SetRegExCompileOptionsMBS(options as integer)

Version: 1.0

Sets the regex compile options.

RegEx options values:

const PCRE_CASELESS = &h00000001
const PCRE_MULTILINE = &h00000002
const PCRE_DOTALL = &h00000004
const PCRE_EXTENDED = &h00000008
const PCRE_ANCHORED = &h00000010
const PCRE_DOLLAR_ENDONLY = &h00000020
const PCRE_EXTRA = &h00000040
const PCRE_NOTBOL = &h00000080
const PCRE_NOTEOL = &h00000100
const PCRE_UNGREEDY = &h00000200
const PCRE_NOTEMPTY = &h00000400
const PCRE_UTF8 = &h00000800
const PCRE_NO_AUTO_CAPTURE = &h00001000
const PCRE_NO_UTF8_CHECK = &h00002000
const PCRE_AUTO_CALLOUT = &h00004000
const PCRE_PARTIAL = &h00008000
const PCRE_DFA_SHORTEST = &h00010000
const PCRE_DFA_RESTART = &h00020000
const PCRE_FIRSTLINE = &h00040000
const PCRE_DUPNAMES = &h00080000
const PCRE_NEWLINE_CR = &h00100000
const PCRE_NEWLINE_LF = &h00200000
const PCRE_NEWLINE_CRLF = &h00300000
const PCRE_NEWLINE_ANY = &h00400000
const PCRE_NEWLINE_ANYCRLF = &h00500000
const PCRE_BSR_ANYCRLF = &h00800000
const PCRE_BSR_UNICODE = &h01000000
const PCRE_JAVASCRIPT_COMPAT = &h02000000

PCRE_UTF8 should be set always. Default Compile settings are PCRE_UTF8 + PCRE_UNGREEDY. Default Execute options are 0.

Example query:

select SetRegExCompileOptionsMBS(2560)

SetRegExExecuteOptionsMBS(options as integer)

Version: 1.0

Sets the regex execute options.

RegEx options values:

const PCRE_CASELESS = &h00000001
const PCRE_MULTILINE = &h00000002
const PCRE_DOTALL = &h00000004
const PCRE_EXTENDED = &h00000008
const PCRE_ANCHORED = &h00000010
const PCRE_DOLLAR_ENDONLY = &h00000020
const PCRE_EXTRA = &h00000040
const PCRE_NOTBOL = &h00000080
const PCRE_NOTEOL = &h00000100
const PCRE_UNGREEDY = &h00000200
const PCRE_NOTEMPTY = &h00000400
const PCRE_UTF8 = &h00000800
const PCRE_NO_AUTO_CAPTURE = &h00001000
const PCRE_NO_UTF8_CHECK = &h00002000
const PCRE_AUTO_CALLOUT = &h00004000
const PCRE_PARTIAL = &h00008000
const PCRE_DFA_SHORTEST = &h00010000
const PCRE_DFA_RESTART = &h00020000
const PCRE_FIRSTLINE = &h00040000
const PCRE_DUPNAMES = &h00080000
const PCRE_NEWLINE_CR = &h00100000
const PCRE_NEWLINE_LF = &h00200000
const PCRE_NEWLINE_CRLF = &h00300000
const PCRE_NEWLINE_ANY = &h00400000
const PCRE_NEWLINE_ANYCRLF = &h00500000
const PCRE_BSR_ANYCRLF = &h00800000
const PCRE_BSR_UNICODE = &h01000000
const PCRE_JAVASCRIPT_COMPAT = &h02000000

PCRE_UTF8 should be set always. Default Compile settings are PCRE_UTF8 + PCRE_UNGREEDY. Default Execute options are 0.

Example querys:

select SetRegExExecuteOptionsMBS(2560)

SizeBlobMBS(data as any) as blob

Version: 1.0

If the data is a blob, it returns the size of the blob. If the data is a string it is used as path and the file size is returned. Path must be native path and should only contain ASCII characters.

Be careful with this function. If used wrong, the user can check every file on the computer. Especially if the server runs with admin previleges.

Example queries:

select SizeBlobMBS(mydata) as rdata from test
select SizeBlobMBS(name) from test where LeftMBS(name,1)="/"

SoundExMBS(text as string) as string

Version: 1.0

Returns soundex string for the given text.

Example query:

select SoundExMBS("Würstchen")

SqrtMBS(value as double) as double

Version: 1.0

sqrt(x) returns a NaN and generates a domain error for x < 0.

Example queries:

select SqrtMBS(5)
select SqrtMBS(25)

TruncMBS(value as double) as double

Version: 1.0

The trunc() functions return the integral value nearest to but no larger in magnitude than x.

Example queries:

select TruncMBS(5)
select TruncMBS(4.5)

UserNameMBS as string

Version: 1.0

Returns the name of the user name which runs the application the plugin is working in.

In a GUI app loading the plugin directly the name is the logged in user. For the REAL server the name is often "System Administrator" or something like that.

Example queries:

select UserNameMBS()

VersionMBS() as string

Version: 1.0

Returns version and date of the plugin in the format "x.y, Day Month Year".

Example query:

select VersionMBS()

WriteBlobMBS(data as blob, path as string) as string

Version: 1.0

Writes the blob to the given file path. On success returns the path. On failure returns error.
Path must be native path and should only contain ASCII characters.

If you use insert to add blob data as hex strings, you can use this function to write the blob to the file system instead of a blob field. Note that queries have a text limit in length! If you use ReadBlobMBS again passing the path you can later read the same file.

Example query:

update test set data=NULL,name=WriteBlobMBS((select data from test where RowID=5),"/datafolder/5.dat") where RowID=5
Insert into "test" (name) values (WriteBlobMBS(x'48656C6C6F20576F726C64', "/test.txt"))

WriteBlobPartMBS(data as blob, path as string, offset as integer) as string

Version: 1.0

Writes the blob to the given file path. On success returns the path. On failure returns error.
Path must be native path and should only contain ASCII characters.

Use the offset to write chunk after chunk to a file.

If you use insert to add blob data as hex strings, you can use this function to write the blob to the file system instead of a blob field. Note that queries have a text limit in length! If you use ReadBlobMBS again passing the path you can later read the same file.

Example queries:

update test set data=NULL,name=WriteBlobPartMBS((select data from test where RowID=5),"/datafolder/5.dat"; 0) where RowID=5
Insert into "test" (name) values (WriteBlobPartMBS(x'48656C6C6F20576F726C64', "/test.txt"; 100000))
38 functions.