Next release of kbmMemTable is closing in.

Along with fixes for reported bugs, there is going to be a significant new feature included, dynamic functions.

Contents

## Dynamic functions

So what is a dynamic function?

It is a script like based mathematic function as opposed to the native built in functions like SIN, COS, COALESCE etc. or any of your own custom native functions.

A number of dynamic functions can be collected in a function library, and any number of function libraries can be registered.

In case a function is declared in two different function libraries, the one that is registered last, is taking precedence, in reality overriding the original function.

Since dynamic functions can call both other dynamic and native functions, you can this way “override” the meaning of centrally used function with some other meaning, which could be a formula for calculating price for something based on various factors, that may be different from customer to customer or contract to contract etc. in case you were creating a customer or contract based application, that needed to do price calculations.

So how does dynamic functions look like?

mysum(a,b) = a + b mysine(a) = sin(a) myselection(x,y) = if(x<y, 10, 20) mycomplex(x,y) = myselection(x,y)*mysum(x,y)

These are examples of dynamic functions, defined with a name, a number of named arguments and the expression, which uses those named arguments.

A line can start with a # to define a comment.

The first one will work for both number and string arguments, the next one only number argument. The next one, which contains a condition (via the IF statement), is shown split up in multiple lines. That is perfectly legal, as long as the lines of the expression is indented by at least one space.

Function names can not be indented but must start at start of line.

## Conditional statements

Conditional statements includes:

**=**,**<>**,**<**,**<=**,**>**,**>=**- Example:
**10<>20**

- Example:
**AND, OR, XOR**(boolean operators)- Example:
**TRUE AND FALSE**

- Example:
**IF**(condition,iftrueexpression,iffalseexpression,[ifnullexpression])- Example:
**IF(a<b,10,20)**or**IF (a<b,10,20,-1)**

- Example:
**CASE**variable/constant**WHEN**expression**THEN**expression

…**ELSE**expression**END**- Example:

CASE a

WHEN 10 THEN 100

WHEN 20 THEN 130

ELSE 140

END

- Example:
**CASE**(expression)**WHEN**expression**THEN**expression

…**ELSE**expression**END**- Example:

CASE (a+b)

WHEN 10 THEN 100

WHEN 20 THEN 130

ELSE 140

END

- Example:
**CASE****WHEN**expression**THEN**expression

…**ELSE**expression**END**- Example:

CASE

WHEN a<b THEN -1

WHEN a>b THEN 1

ELSE 0

END

- Example:
**BETWEEN**expression**AND**expression- Example (returns a boolean true/false or NULL if any arguments are NULL):

a BETWEEN 10 AND 20

- Example (returns a boolean true/false or NULL if any arguments are NULL):
**LIKE**stringexpression- Example:

a LIKE “%some?alue%”

- Example:
**IN**- Example (returns boolean true, false or NULL):

a IN (10,20,40)

- Example (returns boolean true, false or NULL):
**IS NULL**,**IS NOT NULL**,**IS TRUE**,**IS NOT TRUE**,**IS FALSE**,**IS NOT FALSE**- Example (returns boolean true, false or NULL):

a IS NULL

- Example (returns boolean true, false or NULL):

## Classes

There exists a number of classes which one can use to evaluate expressions, and all of them will now accept sets libraries containing functions.

### TkbmMemSQL

**TkbmMemSQL **is one class that contain features to evaluate expressions, either as part of its SQL operation, or as part of its additional **Calculate **and **Evaluate **methods. Both **Calculate** and **Evaluate **compiles a given expression, takes some arguments and executes the compiled expressionto finally return a result.

The difference between the two is that **Calculate **do NOT allow for conditional features, and thus only simple math calculations, except if those conditional features are used within a library, but the expression given to the **Calculate **statement must not contain any conditional features.

**Evaluate **on the other hand, allow for being fed a conditional expression.

Both methods default accepts only numeric processing, however that can be overruled by setting the **AOptions **argument to not include **seoOnlyNumericExpressions** which is otherwise included by default.

### TkbmCalculator

TkbmCalculator is a simple and easy way to take advantage of the expression evaluator for non conditional calculations.

var result:variant; begin // Simple calculation result:=TkbmCalculator.Calc('sin(100)'); // Calculation with arguments // Providing a=45, x=10 and y=20 to the expression. result:=TkbmCalculator.Calc('sin(a)*x+y',['a','x','y'],[45,10,20]) end;

If the specific expression is complex and needs executed many times, it may be performance wise a good idea to precompile it and then use the precompiled variant like this:

var c:TkbmCalculator; result:variant; begin c:=TkbmCalculator.Create(nil); try c.Compile('sin(a)*x+y'); result:=c.Calculate(['a','x','y'],[45,10,20]); ... Many calculate statements with differing argument values. finally c.Free; end; end;

### TkbmEvaluator

The **TkbmEvaluator **class is used in the same way as the **TkbmCalculator **class, except the methods are called **Eval **and **Evaluate **instead of Calc and Calculate. It allows for conditional expressions.

var result:variant; begin result:=TkbmEvaluator.Eval('if(sin(100)<0.5,10,20)'); // Evaluation with arguments // Providing a=45, x=10 and y=20 to the expression. result:=TkbmEvaluator.Eval('if((sin(a)*x+y)<50,10,20)',['a','x','y'],[45,10,20]) end;

## Making dynamic function libraries

When you have chosen which class you will want to use for your purpose, you can start to define your dynamic function libraries. It is really simple.

dvar c:TkbmCalculator; result:variant; begin c:=TkbmCalculator.Create(nil); try c.AddLibrary( 'mysum(a,b) = a + b'#10#13+ 'mysine(a) = sin(a)'#10#13+ 'myselection(x,y) = if(x<y,10,20)'#10#13+ 'mycomplex(x,y) = myselection(x,y)*mysum(x,y)', 'Base'); c.Compile('mycomplex(x,y)'); result:=c.Calculate(['x','y'],[10,20]); ... Many calculate statements with differing argument values. finally c.Free; end; end;

## Loops and iterations

In addition to having semi simple functions, and calculations, you can even add loops. The upcoming release contains new for and while loops in addition to a couple of neat helper functions for summing data in the loop, and to define variable values.

Example:

SumNum(a,b) = for(a,a+4,inc(b,c,OUT b), OUT c)

We have defined a dynamic function (SumNum) taking two arguments, a start index (a) and a start sum (b).

If we run it like this:

var

eval:TkbmEvaluator;

begin

eval:=TkbmEvaluator.Create(nil);

try

eval.AddLibrary(‘SumNum(a,b) = for(a,a+4,inc(b,c,OUT b), OUT c)’,’mylib’);

ShowMessage(‘SumNum(10,100)=’+VarToStr(eval.Evaluate(‘SumNum(10,100)’));

finally

eval.Free;

end;

end;

It will result in the sum of the values 100 + 10,11,12,13,14 = 160

The For statement takes 3 or 4 arguments:

- first index value
- last index value
- the statement to be executed on each loop
- (optional) the name of an OUT variable. If provided the For statement will put the current index value into this variable (in this case the variable c), which we then use later in the embedded expression.

Why 100 you ask? Well… we need to define a value for all variables before they are used. In this case the value of a is coming from the call, c comes from the for statement, outputting current index value (10,11,12,13,14) before the expression in the for statement is executed, but the initial value for b is not defined anywhere, unless we provide it as an argument to the call.

The expression: **inc(b, c, OUT b)** is executed 5 times, incrementing the variable b with the contents of the variable c, outputting the result in the variable b again, in effect summing the values of the iteration counter.

Let us look at another example:

Product(a) = def(1,b,for(1,a,mul(b,c,OUT b), OUT c))

The function Product is in Danish called Fakultet, and is in mathematical way written as an exclamation mark. Eg. 10! means calculate 1*2*3*4*5*6*7*8*9*10 and that is exactly what this dynamic function does.

In this example I have introduced a new **Def **function. The **Def **function provides a way to set a variable value before use, and then execute an expression.

In this case we start out with setting b=1, so it has an initial value for the **mul **function which operates similar to the **inc **function shown above.

eval:=TkbmEvaluator.Create(nil); try eval.AddLibrary('Product(a) = def(1,b,for(1,a,mul(b,c,OUT b), OUT c))','mylib'); ShowMessage('Product(10)='+VarToStr(eval.Evaluate('Product(10)')); finally eval.Free; end; end;

The result is from running this is: 3628800

Hence by using the **Def **function, we can drop the additional b argument of the function call, since we now define a static value for the variable, and will not get an execution error about variable b not being known. The Def statement returns the value of the expression executed.

Finally we have the new **While **function.

It will evaluate an expression, and while that expression is true, it will continue to loop. The number of loops can be limited by an optional count. The number of times the loop has run can be output to an OUT variable. The last value of the expression is returned as result from the While statement.

Simple example:

Loop(a,b) = while(a<b,inc(a,1))

This statement simply loops a number of times while the expression a<b is true.

If you want to limit the number of loops (perhaps as a failsafe), you can add a number:

Loop(a,b) = while(10,a<b,inc(a,1))

Then the loop will run while a<b, but maximum 10 iterations.

If you want access to the number of loops, use the OUT variable:

Loop(a,b) = while(10,a<b,inc(a,1),OUT c)

## Native functions

In addition to the new dynamic functions, you have access to loads of native functions, which has been preregistered in code. You can even register your own functions that will run in compiled mode in native speed. You can read more about that in this blogpost (https://components4developers.blog/2017/08/20/user-defined-functions-and-kbmmemsql/).

People using kbmMW will also find additional preregistered functions which can be used, including:

```
LOCALDATETIMETOISO8601
ISO8601TOLOCALDATETIME
DATETIMETOISO8601
ISO8601TODATETIME
UTCDATETIMETOISO8601
ISO8601TOUTCDATETIME
DATETIMETOFIXED
FIXEDTODATETIME
LOCALDATETIMETOFIXED
FIXEDTOLOCALDATETIME
UTCDATETIMETOFIXED
FIXEDTOUTCDATETIME
```

PARSEUTCDATETIME

PARSELOCALDATETIME

FORMATUTCDATETIME

FORMATLOCALDATETIME

```
NUMBERTOFIXED
FIXEDTONUMBER
TOHEX
FROMHEX
CREATEGUID
CREATESHORTGUID
GETUSERNAME
GETCOMPUTERNAME
QUOTE
UNQUOTE
```

## Overriding functions

The order of the registration of library functions determines which function is called, if the same function name occurs multiple times in different libraries.

The duplication function in the latest loaded library will always override a matching function name in a previously loaded library.

This way you can create a base library, and augment its functions with variations which may be dependent on for example which customer or order or item you are working with in your end user application.

Even built in native functions can be overridden by a dynamic function.