ANN: kbmSQLiteMan v. 1.80 released!

Our free cool tool for managing SQLite databases has been released in v. 1.80.

This includes a new local SQL filter capability in addition to being compiled with latest version of kbmMW and kbmMemTable.

To download it, login at https://portal.components4developers.com and it is readily available for you to download.

If you do not have a login already, you can easily and for free sign up same place.

The existing filtering capability

kbmSQLiteMan has for a long time had the ability to locally add an additional filter on result sets returned from the SQLite database.

In this filter are, a fairly complex expression can be type, ranging from simple SIZE<10 which filters the result set to only show the 3 records with a SIZE value less than 10. AND, OR, NOT, (), LIKE “P*” etc. are all supported.

The new local SQL “filtering” capability

The above filtering is fine for simply ensuring some records in the result set are not displayed. However if you want to do something more complex with the result set, like complex filtering, grouping, calculations or custom ordering etc, you may want to use the new SQL filtering capability. It internally use kbmMemTable’s SQL ability which is fairly close to regular SQL.

Here we combine the result set’s NAME and SIZE column to one column called NAMESIZE. This simple example could of course just as well have been handled in the original SQLite SQL statement, but there may be situations where you want to keep the original data, but do something more with it.

kbmMemTable SQL supports SELECT statements with optional sub select, CASE/WHEN/ELSE, IN, BETWEEN, LIKE, MOD, DIV, ORDER BY, GROUP BY, HAVING, LIMIT, OFFSET, || (concat), +, -, *, / in addition to a good number of functions:

SIN(x), COS(x), TAN(x), LOG(x), LOG2(x), EXP(x), TRUNC(x), FRAC(x), MOD(x), DIV(x), SQRT(x), SQR(x), ROOT(x,y), MIN(x,…), MAX(x,…), AVG(x,…), SUM(x,…), ABS(x), POW(x,y)

UPPER(x), LOWER(x), TRIM(x), TRIMLEFT(x), TRIMRIGHT(x), MID(x,p,n), LEFT(x,n), RIGHT(x,n), LENGTH(x), LEFTPAD(x,c,n), RIGHTPAD(x,c,n), CHR(x), POS(sx,x), REPLACE(x,so,sr [,o1][,o2]), SPLIT(x,sx, OUT v), REGEXP(re,x [,OUT v]),

NOW, DATE(x), TIME(x), YEAR(x), MONTH(x), DAY(x), HOURS(x), MINUTES(x), SECONDS(x), DATESTRING(x), TIMESTRING(x)

CASTTODATETIME(x), CASTTOSTRING(x), CASTTONUMBER(x), CASTTOFLOAT(x), CASTTOINT(x)

IF(x,te,fe [,ne]), NULLIF(x1,x2), ISNULL(x), COALESCE(x,…)

FIELDVALUE(fn), FIELDEXISTS(fn)

The FROM clause must always refer to the virtual table named DATA which represents the complete original result set.

Examples of valid SQL filter statements

Happy filtering with the new kbmSQLiteMan release!

As usual… if you like our products and what you read, please share the blog posts with whom you know.
Without you C4D will not produce great products!

609 total views, 1 views today

Author: kimbomadsen

Leave a Reply

Your email address will not be published. Required fields are marked *

twenty − 6 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.