for InterBase and FireBird

Functions enclosed in FreeAdhocUDF.dll/.so/.dylib :
BLOb functions		20 functions and 3 variants
	Transform	3 functions
	Edit	8 functions and 3 variants
	Calculate	5 functions
	Compare	2 functions
	Find / Determin	2 functions
		function can't (yet) return <null> in FireBird 2.0
		since InterBase 2007 this function is substitutable with a native SQL statem.
		since FireBird 2.1 this function is substitutable with a native SQL statement
		Output RETURN mechanism if nothing other is published: FREE_IT TestSQLs with NULL run only in FireBird 2.0

 

BLOb-functions: Transform
from		obsolete, BLObs could be managed like strings
F_BLOBASPCHAR		compatibility to FreeUDFLib, FreeUDFLib AvERP, GrUDF
Entrypoint	blobaspchar	not compatible with UTF-8 - use BLOB like a string
Input	BLOB	TextBlob to convert to VarChar
Output	CSTRING(32760)	String
TestSQL (to use with TestISO.GDB) SELECT 'ein einzeiliger TextBLOb' AS ISCORRECT, TEXTBLOB, F_BLOBASPCHAR(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 1; SELECT '' AS ISCORRECT, F_BLOBASPCHAR(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 11; SELECT NULL AS ISCORRECT, F_BLOBASPCHAR(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
from		obsolete, BLObs could be managed like strings
F_STRBLOB		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc (B_PUT_SEGMENT)
Entrypoint	strblob	not compatible with UTF-8 - use BLOB like a string
Input	CSTRING(32760)	String to convert to BLOb
Output	BLOB	BLOb
TestSQL SELECT 'soviel für heute' AS ISCORRECT, F_BLOBASPCHAR(F_STRBLOB('soviel für heute')) FROM RDB$DATABASE;
from		obsolete, BLObs could be managed like strings
F_BLOB2EXCEL		function from adhoc
Entrypoint	excelblob	not compatible with UTF-8 - if needed please ask
Input	BLOB	BLOb to convert to Excel
Output	CSTRING(32760)	String to use with Excel
To convert multiline texts and texts with converted commas to Excel, it is required to transform the blob. This function will do the following: -    appends a double inverted comma at the beginning and the end of the blob -    doubles all inverted commas in the blob -    deletes all CHR(13) in the blob -    limits the input blob to 32000 characters (limitation in Excel)     - limit of Excel97 for one field 32000 characters     - limit of Excel2000 for one field 32767 characters TestSQL SELECT '"ein dreizeiliger TextBLOb' || F_LF() || 'mit einer zweiten Zeile' || F_LF() || 'und einer dritten Zeile"' AS ISCORRECT, F_BLOB2EXCEL(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3; SELECT NULL AS ISCORRECT, F_BLOB2EXCEL(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10; Note: Actually it doesn’t make any sense to export a very large text from the database to excel cell, this function may be practicable in combinatin with F_LEFT or F_RIGHT for instance:    SELECT F_RIGHT(F_BLOB2EXCEL(BLObFeldTAGEBUCH), 1000) FROM ... exportiert z.B. die letzten 1000 Zeichen des Tagebuchs.

 

BLOb-functions: Edit
from		substitutable with F_LEFT
from		substitutable with LEFT
F_BLOBLEFT		compatibility to FreeUDFLib, FreeUDFLib AvERP, GrUDF
Entrypoint	blobleft	not compatible with UTF-8 - use U_LEFT
Input	BLOB INTEGER	TextBLOb Length of returned string
Output	CSTRING(32760)	string which is cut by the indicationnumber from left gives from parameter 2
Counting starts with 1 TestSQL (to use with TestISO.GDB) SELECT 'ein einzeiliger' AS ISCORRECT, F_BLOBLEFT(TEXTBLOB, 15) FROM BLOBTEST WHERE BLOBTESTID = 1; SELECT '' AS ISCORRECT, F_BLOBLEFT(TEXTBLOB, 15) FROM BLOBTEST WHERE BLOBTESTID = 11; SELECT NULL AS ISCORRECT, F_BLOBLEFT(TEXTBLOB, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;
from		substitutable with F_MID
F_BLOBMID		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint	blobmid	not compatible with UTF-8 - use U_MID
from		substitutable with F_COPY or F_STRCOPY
F_RBLOBMID		input/output-compatibility to rFunc (B_SUBSTR, B_LONGSUBSTR)
Entrypoint	r_blobmid	not compatible with UTF-8 - use U_MID
Input	BLOB INTEGER INTEGER	TextBLOb Position, at which the determined string starts Length of returned string
Output	CSTRING(32760)	number of letters (parameter 3) of the entered text starting from the entered letternumber (parameter 2)
F_BLOBMID - counting for parameter 2 starts with 0 F_RBLOBMID - counting for parameter 2 starts with 1 TestSQL (to use with TestISO.GDB) SELECT 'zwei' AS ISCORRECT, F_BLOBMID(TEXTBLOB, 4, 4) FROM BLOBTEST WHERE BLOBTESTID = 2; SELECT NULL AS ISCORRECT, F_BLOBMID(TEXTBLOB, NULL, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;
from		substitutable with F_RIGHT
from		substitutable with RIGHT
F_BLOBRIGHT		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint	blobright	not compatible with UTF-8 - use U_RIGHT
Input	BLOB INTEGER	TextBLOb Number of indications from right
Output	CSTRING(32760)	string which is cut to the number of indications counted from right from parameter 2
Counting starts with 1 TestSQL (to use with TestISO.GDB) SELECT 'dritten Zeile' AS ISCORRECT, F_BLOBRIGHT(TEXTBLOB, 13) FROM BLOBTEST WHERE BLOBTESTID = 3; SELECT NULL AS ISCORRECT, F_BLOBRIGHT(TEXTBLOB, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;
from		obsolete,  substitutable with || (like strings)
F_BLOBCAT		compatibility to zu GrUDF
Entrypoint	blobcat	not compatible with UTF-8 - use ||
Input	BLOB BLOB	TextBLOb to join with TextBLOb from parameter 2 TextBLOb to append to TextBLOb from parameter 1
Output	BLOB	Joins two TextBLObs to one TextBLOB
This function appends an CRLF and than append the 2nd BLOb TestSQL (to use with TestISO.GDB) INSERT INTO BLOBTEST (TEXTBLOB) SELECT F_BLOBCAT(TEXTBLOB, (SELECT TEXTBLOB FROM BLOBTEST WHERE BLOBTESTID = 2)) FROM BLOBTEST WHERE BLOBTESTID = 1; Insert a new record in table BLOBTEST with the content of the field TEXTBLOB form datarow with ID = 1 append with the datarow ID = 2
from		obsolete,  substitutable with || (like strings)
F_BLOBCATSTR		compatibility to GrUDF
Entrypoint	blobcatstr	not compatible with UTF-8 - use ||
Input	BLOB CSTRING(32760)	TextBLOb to join with String from parameter 2 String to append to TextBLOb from parameter 1
Output	BLOB	Joins a TextBLObs and a string to a TextBLOB
This function appends an CRLF and than append the string. TestSQL (to use with TestISO.GDB) INSERT INTO BLOBTEST (TEXTBLOB) SELECT F_BLOBCATSTR(TEXTBLOB, 'Diese Zeile wurde angehängt') FROM BLOBTEST WHERE BLOBTESTID = 1; Insert a new record in table BLOBTEST with the content of the field TEXTBLOB form datarow with ID = 1 append with tthe string ‘Diese Zeile wurde angehängt'
from		substitutable with REPLACESTRING
F_BLOBREPLACESTRING		function from adhoc
Entrypoint	blobreplacestring	not compatible with UTF-8 - use U_BLOBREPLACESTRING
U_BLOBREPLACESTRING		function from adhoc
Entrypoint	u_blobreplacestring	compatible with UTF-8
Input	BLOB CSTRING(254) CSTRING(254) INTEGER INTEGER	TextBLOb to be modified by replacing a string the string to be replaced in BLOb new string 0 = just replace the first appearance, 1 = replace all appearance 0 = case sensitive, 1 = not case sensitive
Output	BLOB	TextBLOb with replaced one/all character strings fromParameter 2 of the the BLOB by the character string from Parameter 3
TestSQL (to use with TestISO.GDB) SELECT 'vier einzeiliger TextBLOb' AS ISCORRECT, F_BLOBREPLACESTRING(TEXTBLOB, 'ein', 'vier', 0, 0) FROM BLOBTEST WHERE BLOBTESTID = 1; SELECT 'vier vierzeiliger TextBLOb' AS ISCORRECT, F_BLOBREPLACESTRING(TEXTBLOB, 'ein', 'vier', 1, 0) FROM BLOBTEST WHERE BLOBTESTID = 1;
from		substitutable with F_SUBSTR
F_BLOBSUBSTR		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc (B_STRPOS)
Entrypoint	blobsubstr	not compatible with UTF-8 - use U_BLOBSUBSTR
U_BLOBSUBSTR		function from adhoc
Entrypoint	u_blobsubstr	compatible with UTF-8
Input	BLOB CSTRING(1024)	TextBLOb (in which you are looking for the string from parameter 2) String (you are looking for in the BLOb)
Output	INTEGER	position in the BLOb where the string starts
Counting starts with 0. TestSQL (to use with TestISO.GDB) SELECT 4 AS ISCORRECT, F_BLOBSUBSTR(TEXTBLOB, 'einzeiliger') FROM BLOBTEST WHERE BLOBTESTID = 1; SELECT NULL AS ISCORRECT, F_BLOBSUBSTR(TEXTBLOB, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;
F_BLOBLINE		compatibility to FreeUDFLib, FreeUDFLib AvERP input/output-compatibility to rFunc (B_LINE, B_LONGLINE)
Entrypoint	blobline	compatible with UTF-8
Input	BLOB INTEGER	TextBLOb No. of row for output
Output	CSTRING(32760)	the content of the <parameter 2> row of the TextBLOb
TestSQL (to use with TestISO/UTF8.GDB/FDB) SELECT 'mit einer zweiten Zeile' AS ISCORRECT, F_BLOBLINE(TEXTBLOB, 2) FROM BLOBTEST WHERE BLOBTESTID = 3; SELECT NULL AS ISCORRECT, F_BLOBLINE(TEXTBLOB, NULL) FROM BLOBTEST WHERE BLOBTESTID = 10;

 

BLOb-functions: Calculate
F_BLOBSIZE		compatibility to FreeUDFLib, FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc (B_TOTAL_LENGTH)
Entrypoint	blobsize	compatible with UTF-8
Input	BLOB	TextBLOb
Output	INTEGER	For TextBLOb: returns the size/length (similar F_STRINGLENGTH) For BinarBLOb: returns the size of file in byte
CRLF are countered as 2 characters TestSQL (to use with TestISO.GDB) For a TextBLOb: SELECT 50 AS ISCORRECT, F_BLOBSIZE(TEXTBLOB), F_STRINGLENGTH(F_BLOBASPCHAR(TEXTBLOB)) FROM BLOBTEST WHERE BLOBTESTID = 2; SELECT 0 AS ISCORRECT, F_BLOBSIZE(TEXTBLOB), F_STRINGLENGTH(F_BLOBASPCHAR(TEXTBLOB)) FROM BLOBTEST WHERE BLOBTESTID = 11; SELECT NULL AS ISCORRECT, F_BLOBSIZE(TEXTBLOB), F_STRINGLENGTH(F_BLOBASPCHAR(TEXTBLOB)) FROM BLOBTEST WHERE BLOBTESTID = 10; For a BinärBLOb: SELECT 1426 AS ISCORRECT, F_BLOBSIZE(BINAERBLOB) FROM BLOBTEST WHERE BLOBTESTID = 4;
F_BLOBMAXSEGMENTLENGTH		compatibility to FreeUDFLib input/output-compatibility to rFunc (B_MAX_SEGMENT)
Entrypoint	blobmaxsegmentlength	not compatible with UTF-8 - no UTF-8 function planed
Input	BLOB	TextBLOb or BinärBLOb
Output	INTEGER	Countered bytes of the largest BLOB-segment
TestSQL (to use with TestISO.GDB) For a TextBLOb: SELECT 16384 AS ISCORRECT, F_BLOBMAXSEGMENTLENGTH(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 8; SELECT NULL AS ISCORRECT, F_BLOBMAXSEGMENTLENGTH(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10; For a BinärBLOb: SELECT 16384 AS ISCORRECT, F_BLOBMAXSEGMENTLENGTH(BINAERBLOB) FROM BLOBTEST WHERE BLOBTESTID = 7;
F_BLOBSEGMENTCOUNT		compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF input/output-compatibility to rFunc (B_NUMBER_SEGMENTS)
Entrypoint	blobsegmentcount	not compatible with UTF-8 - no UTF-8 function planed
Input	BLOB	TextBLOb or BinärBLOb
Output	INTEGER	No. of  BLOb-segments
TestSQL (to use with TestISO.GDB) For a TextBLOb: SELECT 3 AS ISCORRECT, F_BLOBSEGMENTCOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 8; SELECT NULL AS ISCORRECT, F_BLOBSEGMENTCOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10; For a BinärBLOb: SELECT 2 AS ISCORRECT, F_BLOBSEGMENTCOUNT(BINAERBLOB) FROM BLOBTEST WHERE BLOBTESTID = 7;
F_BLOBLINECOUNT		function from adhoc input/output-compatibility to rFunc (B_LINE_COUNT)
Entrypoint	bloblinecount	compatible with UTF-8
Input	BLOB	TextBLOb
Output	INTEGER	No. of lines in a TextBLOb
TestSQL (to use with TestISO.GDB) SELECT 3 AS ISCORRECT, F_BLOBLINECOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3; SELECT NULL AS ISCORRECT, F_BLOBLINECOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
from		substitutable with F_WORDCOUNT
F_BLOBWORDCOUNT		function from adhoc
Entrypoint	blobwordcount	compatible with UTF-8
Input	BLOB	TextBLOb
Output	INTEGER	No. of words in a TextBLOb
A word ends with one ore more spaces, TAB or CRLF. TESTSQL SELECT 11 AS ISCORRECT, F_BLOBWORDCOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3; SELECT NULL AS ISCORRECT, F_BLOBWORDCOUNT(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;

 

BLOb-functions: Compare
from		obsolete, BLOBs could be compared like strings
F_BLOBCOMPARE		compatibility to FreeUDFLib, GrUDF (BLOBICOMP)
Entrypoint	blobcompare	compatible with UTF-8
Input	BLOB BLOB	BLOB 1 BLOB 2
Output	INTEGER	1 = identically, 0 = not identically
TestSQL (to use with TestISO.GDB) SELECT 1 AS ISCORRECT, F_BLOBCOMPARE(TEXTBLOB, TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3; SELECT 0 AS ISCORRECT, F_BLOBCOMPARE(TEXTBLOB, (SELECT TEXTBLOB FROM BLOBTEST WHERE BLOBTESTID = 2)) FROM BLOBTEST WHERE BLOBTESTID = 3; SELECT NULL AS ISCORRECT, F_BLOBCOMPARE(TEXTBLOB, TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
from		obsolete, BLOBs could be compared like strings
F_BLOBCMP		input/output-compatibility to rFunc (B_STRCMP)
Entrypoint	b_strcmp	not compatible with UTF-8 - if needed please ask
Input	BLOB BLOB	BLOb 1 BLOb 2
Output	INTEGER	output > 0 : BLOb 1 is smaller than BLOb 2 output = 0 : BLOb 1 = BLOb 2 output < 0 : BLOb 1 is greater than BLOb 2
Attention: in original rFunc-functions B_STRCMP the output positiv or negativ is different to the rFunc-String-function STRCMP - in string-function a positiv output meens, that the first string is greater, in BLOb-function it meens, that the second BLOb is greater. For compatibility reasons the FreeAdhocUDF do exactly the same. In Linux the value of output is other than in Windows! TestSQL (für TestISO.GDB/FDB) SELECT 0 AS ISCORRECT, F_BLOBCMP(TEXTBLOB, TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3; SELECT 1 AS ISCORRECT, F_BLOBCMP(TEXTBLOB, (SELECT TEXTBLOB FROM BLOBTEST WHERE BLOBTESTID = 2)) FROM BLOBTEST WHERE BLOBTESTID = 3; SELECT -1 AS ISCORRECT, F_BLOBCMP(TEXTBLOB, (SELECT TEXTBLOB FROM BLOBTEST WHERE BLOBTESTID = 3)) FROM BLOBTEST WHERE BLOBTESTID = 2; SELECT NULL AS ISCORRECT, F_BLOBCMP(NULL, NULL) FROM BLOBTEST WHERE BLOBTESTID = 3;

 

BLOb-functions: Find / Determin
from		substitutable with F_STRPOS
F_BLOBSTRPOS		input/output-compatibility to rFunc (B_STRPOS)
Entrypoint	b_strpos	not compatible with UTF-8 - if needed please ask
Input	CSTRING(8190) BLOB	String to search in BLOb BLOb in which is to search
Output	INTEGER	starting-position of the string in BLOb, 0 if not found
case/uppercase for search-string is important. Counting for starting-position starts at 1. TestSQL (for TestISO.GDB/FDB) SELECT 5 AS ISCORRECT, F_BLOBSTRPOS('zwei', TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 2; SELECT 0 AS ISCORRECT, F_BLOBSTRPOS('Zwei', TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 2; SELECT NULL AS ISCORRECT, F_BLOBSTRPOS(NULL, NULL) FROM BLOBTEST WHERE BLOBTESTID = 2;
F_BLOBTEXTPOS		input/output-compatibility to rFunc (B_TEXTPOS)
Entrypoint	b_textpos	not compatible with UTF-8 - if needed please ask
Input	CSTRING(8190) BLOB	String to search in BLOb BLOb in which is to search
Output	INTEGER	starting-position of the string in BLOb, 0 if not found
case/uppercase for search-string is not important. Counting for starting-position starts at 1. TestSQL (for TestISO.GDB/FDB) SELECT 5 AS ISCORRECT, F_BLOBTEXTPOS('zwei', TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 2; SELECT 5 AS ISCORRECT, F_BLOBTEXTPOS('Zwei', TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 2; SELECT NULL AS ISCORRECT, F_BLOBTEXTPOS(NULL, NULL) FROM BLOBTEST WHERE BLOBTESTID = 2;