SQLite 3.45.1

Xojo 2024 Release 1 includes an updated version of SQLite for your Desktop, Web, Console and iOS apps. We have upgraded the library from 3.39.4 (which is from 2022-09-29) to 3.45.1 (released 2024-01-30). Here are a few of the improvements.

Functions

There are a few new SQLite functions that can now be used in your SQL statements.

concat()

The concat() function takes an arbitrary number of string parameters and concatenates them all together. Previous versions of SQLite required you to instead use the concat operator, this is the double-pipe: ||. Concat() is a standard function on many other databases (PostgreSQL, MySQL and SQL Server) so it is nice to finally see it available on SQLite as it allow more consistent SQL code.

Using the Chinook sample database, this SQL concatenates the Name and Composer columns from the Track table:

SELECT concat(Name, ‘ by ‘, Composer) FROM Track

Here is the output:

concat_ws()

The concat_ws() method is concat but with a separator. Strange naming aside, it works the same except that the first parameter is used a separator that is inserted between each string. This SQL separates the Name, Composer and Milliseconds with “—“:

SELECT concat_ws(‘---‘, Name, Composer, Milliseconds) FROM Track

Here is the output:

timediff()

The timediff() function calculates the elapsed time between two dates and returns the result in a human-readable format.

This SQL displays the time difference between Dec 12, 2023 and March 26, 2024:

SELECT timediff('2023-12-12','2024-03-26')

Here is the output of 3 months and 14 days (note that it is negative because the earlier date is first in the parameter list):

Other Changes

Overall I didn’t notice many other other significant new features. However, many bugs have been fixed, especially around database integrity checks and the somewhat new JSON support.

You can find the full list of SQLite changes on their Release History page.

Paul learned to program in BASIC at age 13 and has programmed in more languages than he remembers, with Xojo being an obvious favorite. When not working on Xojo, you can find him talking about retrocomputing at Goto 10 and on Mastodon @lefebvre@hachyderm.io.