kbmMemTable v. 7.77.60 Standard and Professional Edition released

This is a minor release:

  • Changed FindNearest and GotoNearest to find first matching record, rather than last.
    Notice that this change may break existing code behavior. See next line for fix!
  • Added ASearchType:TkbmSearchType argument to FindNearest and GotoNearest default mtstNearestBefore. Set it to mtstNearestAfter to get old behavior.

kbmMemTable is the premier high performance, high functionality in
memory dataset for Delphi and C++Builder with kbmMemTable Professional
topping the scales as being the worlds fastest!

If you have an up to date Service and Update (SAU) subscription, then
you can immediately visit https://portal.components4developers.com to
download the latest kbmMemTable release.

If not, please visit our shop at http://www.components4developers.com
and extend your SAU with another 12 months.

kbmMemTable v. 7.77.50 Standard and Professional Edition released

This is a minor release, required for kbmMW v. 5.04.30:

  • Added support for DEFAULT column value in SQL CREATE and ALTER TABLE.

kbmMemTable is the premier high performance, high functionality in
memory dataset for Delphi and C++Builder with kbmMemTable Professional
topping the scales as being the worlds fastest!

If you have an up to date Service and Update (SAU) subscription, then
you can immediately visit https://portal.components4developers.com to
download the latest kbmMemTable release.

If not, please visit our shop at http://www.components4developers.com
and extend your SAU with another 12 months.

ANN: kbmMemTable v. 7.77.10 Standard and Professional Edition released!

We are happy to announce the latest and greatest release of our memory table.

Whats new in 7.77.10 September 16 2017

  • Added support for SQL DDL statements:
    LIST TABLES, LIST INDEXES FOR TABLE xxx,
    DESCRIBE TABLE xxx, DESCRIBE INDEX xxx FOR TABLE xxx
    and some variations (ON instead of FOR, TABLE keyword
    optional in INDEX statement).
  • Added support for CASE WHEN THEN ELSE END in both forms.
  • Added support for NOT IN, NOT BETWEEN, NOT LIKE
  • Fixed CREATE TABLE issues.
  • Added support for SELECT INTO
  • Added support for SQL multistatements. Statements separated
    by ; (semicolon)
  • Added support for ALTER TABLE ADD COLUMN, ALTER TABLE DROP COLUMN,
    ALTER TABLE MODIFY/ALTER COLUMN, ALTER TABLE RENAME TO
  • Added support for EXISTS TABLE and EXISTS INDEX
  • Added support for DEFAULT value in CREATE TABLE
  • Added support for UNIQUE constraint in CREATE TABLE
  • Improved SQL field datatype parsing.
  • Added support for OUT parameters in SQL custom functions.
  • Fixed SQLReplace (Replace) incorrect argument index.
  • Added SQLSplit (Split) custom SQL function to split strings.
  • Added SQLRegExp (RegExp) custom SQL function for pattern
    matching and splitting
  • Added SQLDataType (DataType) custom SQL function for splitting
    SQL datatype declaration.
  • Added Options:TkbmSQLOptions property to TkbmMemSQL.
    soOrderByNullFirst – Default Null orders last in comparison
    soOldFieldNamingSyntax – Revert to old field naming syntax
    soOldLikeSyntax – Revert to old wildcard style like syntax
    else use true SQL style format using % and ?.
  • Added multiple overloaded ExecSQL functions to TkbmMemSQL to
    allow easy one line calls. If source table names are not provided
    they will be named T1..Tn.
  • Changed to support multiple SQL parsing errors before erroring out.

Professional Edition is released with source and additional performance enhancement features to holders of an active kbmMW Pro/Ent Service and Update subscription (SAU).

A free CodeGear Edition can be found bundled with kbmMW CodeGear Edition
for specific Delphi versions.

kbmMemTable supports the following development environments:

  • RAD Studio Delphi/C++ 10.2 Tokyo
  • RAD Studio Delphi/C++ 10.1 Berlin
  • RAD Studio Delphi/C++ 10 Seattle
  • RAD Studio Delphi/C++ XE8
  • RAD Studio Delphi/C++ XE7
  • RAD Studio Delphi/C++ XE6
  • RAD Studio Delphi/C++ XE5
  • RAD Studio Delphi/C++ XE4
  • RAD Studio Delphi/C++ XE3
  • RAD Studio Delphi/C++ XE2
  • Lazarus 1.2.4 with FPC 2.6.4

And can be used on Android, IOS, Linux, OSX and Windows 32/64 bit platforms.

kbmMemTable is the premier high performance, high functionality in memory dataset for Delphi and C++Builder with kbmMemTable Professional topping the scales as being the worlds fastest!

If you have an up to date Service and Update (SAU) subscription, then you can immediately visit https://portal.components4developers.com to download the latest kbmMemTable release.

If not, please visit our shop at http://www.components4developers.com and extend your SAU with another 12 months.

User defined functions and kbmMemSQL

[updated with additional standard functions]

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:

kbmSQLFunctionRegistrations.DisableFunction(‘MONTH’)

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:

initialization
   kbmSQLFunctionRegistrations.RegisterFunction(‘MATH.TRIG’,’SIN’,@SQLSin);

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;

And register it:

initialization
   kbmSQLFunctionRegistrations.RegisterFunction(‘MYFUNCS’,’SPLIT’,@SQLSplit);

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!

ANN: kbmMemTable v. 7.76.00 Standard and Professional Edition released!

We are happy to announce the latest and greatest release of our memory
table.

Whats new in 7.76.00 July 25 2017

  • Fixed bug related to ADT fields.
  • Added support for SQL DDL statements:
    CREATE TABLE…
    CREATE INDEX…
    DROP TABLE xxx
    DROP INDEX xxx
    ALTER TABLE xxx DROP COLUMN xxx,
    ALTER TABLE xxx ADD COLUMN xxx
    ALTER TABLE xxx MODIFY COLUMN xxxxxxxxx
  • Added naive (non optimized) support for SQL joins.
  • Improved to allow LIMIT or TOP or OFFSET at start of
    SELECT stattement in addition to at the end.
  • Split Calculate and Evaluate statements up into
    CompileCalculate, ExecuteCalculate
    and CompileEvaluate and ExecuteEvaluate to allow
    reusing compiled expressions.
    Calculate and Evaluate still exists for one shot runs.
  • Standard Edition is released with source to holders of an active
    kbmMemTable Service and Update subscription (SAU).

Professional Edition is released with source and additional performance
enhancement features to holders of an active kbmMW Pro/Ent Service and
Update subscription (SAU).

A free CodeGear Edition can be found bundled with kbmMW CodeGear Edition
for specific Delphi versions.

kbmMemTable supports the following development environments:

  • RAD Studio Delphi/C++ XE2
  • RAD Studio Delphi/C++ XE3
  • RAD Studio Delphi/C++ XE4
  • RAD Studio Delphi/C++ XE5
  • RAD Studio Delphi/C++ XE6
  • RAD Studio Delphi/C++ XE7
  • RAD Studio Delphi/C++ XE8
  • RAD Studio Delphi/C++ 10 Seattle
  • RAD Studio Delphi/C++ 10.1 Berlin
  • RAD Studio Delphi/C++ 10.2 Tokyo
  • Lazarus 1.2.4 with FPC 2.6.4

And can be used on mobile, Linux, OSX and Windows 32/64 bit platforms.

kbmMemTable is the premier high performance, high functionality in
memory dataset for Delphi and C++Builder with kbmMemTable Professional
topping the scales as being the worlds fastest!

If you have an up to date Service and Update (SAU) subscription, then
you can immediately visit https://portal.components4developers.com to
download the latest kbmMemTable release.

If not, please visit our shop at http://www.components4developers.com
and extend your SAU with another 12 months.