FreeAdhocUDF for InterBase and FireBird in deutschin englishen françaisen españolin italianoa português
homeprevious pageÜbersicht • overview • aperçunext pagelast page Seite empfehlen • advice website •Forum mailing-listwas ich vermisse • what I missed •eMail Kommentar • eMail commentprintsitemap
 
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
fromobsolete, 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;
nach oben • go top •
fromobsolete, 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;
nach oben • go top •
fromobsolete, 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.
nach oben • go top •
 
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;
nach oben • go top •
fromsubstitutable with F_MID
F_BLOBMID
compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint blobmid not compatible with UTF-8 - use U_MID
fromsubstitutable 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;
nach oben • go top •
from substitutable with F_RIGHT
from substitutable with RIGHT
F_BLOBRIGHTcompatibility 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;
nach oben • go top •
fromobsolete,  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
nach oben • go top •
fromobsolete,  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'
nach oben • go top •
from
substitutable with REPLACESTRING
F_BLOBREPLACESTRING  function from adhoc
Entrypoint blobreplacestring not compatible with UTF-8 - use U_BLOBREPLACESTRING
U_BLOBREPLACESTRINGfunction from adhoc
Entrypoint u_blobreplacestringcompatible 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;
nach oben • go top •
fromsubstitutable 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_BLOBSUBSTRfunction from adhoc
Entrypoint u_blobsubstrcompatible 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;
nach oben • go top •
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;
nach oben • go top •
 
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
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;
nach oben • go top •
fromsubstitutable 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;
nach oben • go top •
 
BLOb-functions: Compare
fromobsolete, 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;
nach oben • go top •
fromobsolete, 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;
nach oben • go top •
 
BLOb-functions: Find / Determin
fromsubstitutable 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;
nach oben • go top •
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;
vorige Seite • previous page • passée sitenach oben • go top • vers le hautnächste Seite • next page • prochain site