SQLite 3.35 now has Math functions, Column Removal, RETURNING clause & CTE's (Common Table Expression)

1

SQLite Release 3.35.0 On 2021-03-12

SQLite Download Page
x32 500MB
x64 850MB

About SQLite
#### Executive Summary

* [Full-featured SQL](https://sqlite.org/fullsql.html)
* [Billions and billions of deployments](https://sqlite.org/mostdeployed.html)
* [Single-file database](https://sqlite.org/onefile.html)
* [Public domain source code](https://sqlite.org/copyright.html)
* All source code in one file ([sqlite3.c](https://sqlite.org/amalgamation.html))
* [Small footprint](https://sqlite.org/footprint.html)
* Max DB size: [281 terabytes](https://sqlite.org/limits.html) (2<small>48</small> bytes)
* Max row size: [1 gigabyte](https://sqlite.org/limits.html)
* [Faster than direct file I/O](https://sqlite.org/fasterthanfs.html)
* [Aviation-grade quality and testing](https://sqlite.org/testing.html)
* [Zero-configuration](https://sqlite.org/zeroconf.html)
* [ACID transactions, even after power loss](https://sqlite.org/transactional.html)
* [Stable, enduring file format](https://sqlite.org/fileformat.html)
* [Extensive, detailed documentation](https://sqlite.org/doclist.html)
* [Long-term support](https://sqlite.org/lts.html)

I was surprised to learn its now got the Maths library. Download seems large, I hope a deployed db is alot smaller.

This blog post explains the latest features in a nice way.
What’s new in SQLite 3.35 | Anton Zhiyanov (nalgeon.github.io)

2

Sadly I don’t think CTE’s will work with Clarion’s PROP:SQL due to the following mention:

In order to return a result set, you must begin your statement with either SELECT or CALL

Just ran into this myself as I use UltimateSQL and it just uses PROP:SQL and I tried to figure out why my SELECT with CTE didn’t give any results back…

3

Don’t know about UltimateSql.

But Clarion drivers work with CTE if you use prop:SQLRowSet =

4

I like it when old threads are resurrected with new information. Really helps in a world where things move forward.

Jane is right - prop:sqlrowset can be used in place of prop:sql.

Also the new SQLite2 driver does not require this (prop:sql and prop:sqlrowset are the same there, and both are better with the SQL() command.

The new driver also has RETURNING functionality built in. Server side auto numbering, default values etc are all returned for you automatically into the record after an ADD.

CTEs are also generated and used internally in a number of the commands, but that is invisible to you.

https://capesoft.com/accessories/filedriverkitsp.htm

1 Like