As you may know, the widely used kbmMemTable also supports querying and more using a SQL92 like syntax via the component TkbmMemSQL.
This article explains how to create UDF (user defined functions) that can be used as part of the SQL statement. Although UDF has been supported for a long time, it has been somewhat expanded in the upcoming release, why this article do reference features not available in the current kbmMemTable v. 7.76.
Standard UDF functions
kbmMemTable already comes with a fairly extensive set of standard UDF functions which can be used as part of your query, but you can expand and enable and disable which UDF functions should be available for your purpose.
Include kbmSQLStdFunc in your projects uses clause to get access to all the standard UDF functions, which includes the following, grouped by their logical category names:
MATH.TRIG
SIN(x)
COS(x)
TAN(x)
LOG(x)
LOG2(x)
EXP(x)
MATH
TRUNC(x) Returns integer part
FRAC(x) Returns fractional part
MOD(x,y) Return remainder after integer division
DIV(x,y) Integer division
SQRT(x) Square root
SQR(x) Square. Same as x*x
ROOT(x,y) Calculate y’th root of x.
MIN(x1..,xn) Return minimum argument value
MAX(x1..,xn) Return maximum argument value
AVG(x1..xn) Return average value of all arguments
SUM(x1..,xn) Return sum of all arguments
ABS(x) Return absolute (Non negative) value of x
POW(x,y) Return x in the power of y
STRING
UPPER(x) Return uppercase value
LOWER(x) Return lowercase value
TRIM(x) Return value trimmed for leading and trailing spaces
MID(x,y,z) Return z characters from x starting at y (first character is 1)
LEFT(x,y) Return y leftmost characters of x
RIGHT(x,y) Return y rightmost characters of x
LENGTH(x) Return the length in characters of x
LEFTPAD(x,y,z) Pad left of x with the character y until the complete result has a length of z
RIGHTPAD(x,y,z) Pad right of x with the character y until the complete result has a length of z
CHR(x) Convert the integer value x to a unicode character
POS(x,y) Return the position in y, where the substring x is to be found. 0 is returned if x is not in y
REPLACE(x,y,z,v,w) Search x for value y. When found, replace y with z. If v (optional) is true, then all occurrences are replaced. If w (optional) is true, then search is case insensitive
SPLIT(x,y,OUT z) Search x for the sub string y. When found return the leading part as result or null if nothing found. If z is provided (optional) then the trailing part is returned in the variable given by z. *NEW IN 7.77*
DATETIME
NOW Returns current date and time as a Delphi TDateTime type floating point value.
DATE(x) Returns the date part of x which is a Delphi TDateTime type floating point value, as an integer. Same as TRUNC(somedate)
TIME(x) Returns the time part of x which is a Delphi TDateTime type floating point value as a float. Same as FRAC(somedate)
YEAR(x) Returns the year as an integer (2017) of the Delphi TDateTime type floating point value.
MONTH(x) Returns the month as an integer (1 = Jan, 12=Dec) of the Delphi TDateTime type floating point value.
DAY(x) Returns the day of month as an integer (1..31) of the Delphi TDateTime type floating point value.
HOURS(x) Returns the hour value as an integer (0..23) of the Delphi TDateTIme type floating point value.
MINUTES(x) Returns the minutes part as an integer (0..59) of the Delphi TDateTime type floating point value.
SECONDS(x) Returns the seconds part as an integer (0..59) of the Delphi TDateTime type floating point value.
DATESTRING(x) Returns the date of a Delphi TDateTime type floating point value as a formatted string according to the FormatSettings defined on the TkbmMemSQL component.
TIMESTRING(x) Returns the time of a Delphi TDateTime type floating point value as a formatted string according to the FormatSettings defined on the TkbmMemSQL component.
CAST
CASTTODATETIME(x) Casts x to a Delphi TDateTime floating point value. If x is a string, it will be parsed according to the FormatSettings.
CASTTOSTRING(x) Casts x to a string. If x is a TDateTime value, it will be converted to a date/time string value according to the FormatSettings.
CASTTONUMBER(x) Casts x to a number. If x is a string, it will be converted to a floating point value according to the FormatSettings.
CONDITIONAL
IF(x,y,z,v) Depending on x, either y, z or v (optional) will be returned. If x is true, then y will be returned. If x is false, then z will be returned and if x is null then v alternative z will be returned if v is not specified.
ISNULL(x) Return true or false depending on if x is null.
CONVERSION
DATATYPE(x,OUT y, OUT z) Parses the SQL style datatype (eg. VARCHAR(30)) given in x, and returns the Delphi TFieldType best matching as an integer or NULL if the given SQL datatype is invalid. If y is provided (optional), the size of the declaration (eg 30) is returned in the referenced variable. If z is provided (optional), the precision of the declaration (eg 0) is returned in the referenced variable. *NEW IN 7.77*
Please take notice to the functions category names. They can be used to enable or disable whole groups of functions. E.g.
kbmSQLFunctionRegistrations.DisableGroup(‘MATH.TRIG’) would result in all the trigonometrical functions being unavailable for SQL expressions.
Obviously there is also an EnableGroup function. All registered functions are default enabled.
If you want to only disable a certain function, you can use:
Now the MONTH function will not be available for SQL expressions.
Creating a new custom UDF function
It is pretty easy to create a custom UDF function. Basically you will need to create a globally accessible function, and register it to kbmMemSQL.
Typically an UDF takes zero or more arguments and until and including v. 7.76 returns one single value. The UDF function is usually called many times depending on the actual SQL statement.
Let us look at how the SIN standard function is implemented.
function SQLSin(const AOperation:TkbmSQLCustomOperation; const ASituation:TkbmSQLFunctionSituation; const AArgs:TkbmSQLNodes; var AResult:variant):boolean;
begin
kbmSQLCheckArgs(AArgs,1);
case ASituation of
fsWidth:
AResult:=0;
fsExecute:
begin
AResult:=AArgs.Node[0].Execute;
if not VarIsNull(AResult) then
AResult:=Sin(AResult);
end;
fsDataType:
AResult:=ftFloat;
end;
Result:=true;
end;
The SIN UDF function takes 4 arguments:
AOperation is the current operation the function is being called from (it can be a select, insert etc. operation). This argument can usually be ignored, unless you want to get to the FormatSettings which is available as a property in the operation instance.
ASituation which indicates which situation the UDF is being called in. It can be one of fsWidth, fsDataType or fsExecute. The UDF will be called during compilation of the SQL statement to determined what type of data it will return, and what it expects the width of the returned data to be. And it will finally be called a number of times to execute the actual function at SQL execution time.
AArgs provides the arguments for the UDF function. Your function may require a minimum number of arguments, and to check for that it is recommended to use the kbmSQLCheckArgs function as shown in the example. The SIN function only expects one argument.
AResult is the parameter that should receive the outcome of the function. It must adhere to the data type which the UDF function provided at compilation time.
The SIN function is registered to kbmMemSQL by calling RegisterFunction, typically in the units Initialization section. E.g:
Check the kbmSQLStdFunc.pas unit to see the how other standard functions has been implemented and registered.
The SIN function can for example be used like this:
SELECT SIN(field1) AS “Sineof”, field1 FROM sometable
But it can also be part of the condition and any other place in the SQL statement, where an expression can be given.
But what if you want to split a value into multiple values using an UDF function, how is that done? For example an UDF splitting a string in two parts.
SPLIT(x) Lets say we want to split x into two parts, exactly where there is a colon (:), but how will we get 2 strings back when we can only return one?
From kbmMemTable v. 7.77 it is possible (optionally) to use a sligltly different syntax in the SQL statement when calling UDF functions.
SELECT SPLIT(field1, OUT $var1) AS “Left”, $var1 as “Right” FROM sometable
The trick is to use the new OUT syntax. Now we will let the split function return all the data left of the colon (:), or if no colon is found all the data in x, and send the remaining data back as the contents of a variable called var1.
function SQLSplit(const AOperation:TkbmSQLCustomOperation; const ASituation:TkbmSQLFunctionSituation; const AArgs:TkbmSQLNodes; var AResult:variant):boolean;
var
v:variant;
s:string;
i:integer;
begin
kbmSQLCheckArgs(AArgs,2);
case ASituation of
fsWidth:
begin
AResult:=AArgs[0].Width;
end;
fsExecute:
begin
v:=AArgs[0].Execute;
if VarIsNull(v) then
Result:=Null
else
begin
s:=v;
i:=pos(':',s);
if i<0 then
AResult:=s
else
begin
AResult:=copy(s,1,i-1);
kbmSQLSetVariableValue(AArgs[1],copy(s,i+1,length(s)));
end;
end;
end;
fsDataType:
begin
kbmSQLSetVariableMetaData(AArgs[1],ftString,AArgs[0].Width);
AResult:=ftString;
end;
end;
Result:=true;
end;
You can have as many OUT variables as you need, but remember that the function MUST return one value in the old fashioned way.
Also notice that the variable names must start with $, and that the variable values are not available until the UDF function has been called. Usually it’s thus recommended to call the UDF function as early as possible in your SQL statement, so the variables are available for the remainder of the current statement processing.
The variables are automatically cleared before attempting to process another record.
If you create some cool, can’t live without, UDF functions you think ought to be part of the standard SQL function library, ping us.
If you like this blog post or other posts on our blog, please spread the word and let others know about them!
