Home
JAQForum Ver 24.01
Log In or Join  
Active Topics
Local Time 12:15 18 Nov 2026 Privacy Policy
Jump to

Notice. New forum software under development. It's going to miss a few functions and look a bit ugly for a while, but I'm working on it full time now as the old forum was too unstable. Couple days, all good. If you notice any issues, please contact me.

Forum Index : Microcontroller and PC projects : SQL databases on the CMM2 accessible from Basic

Author Message
matherp
Guru

Joined: 11/12/2012
Location: United Kingdom
Posts: 12242
Posted: 03:37pm 13 Nov 2026
Copy link to clipboard 
Print this post

SQL databases in MMBasic on the Colour Maximite 2

New in firmware 7.00.00RC6, for the Generation 1 and the Generation 2. It needs the sql folder of CMM2_Programs_7.00.00RC6.zip on the SD card.

https://github.com/UKTailwind/CMM2V1.5/releases/tag/v7.00.00RC6


What it is
MMBasic programs can now keep their data in an SQL database: a file on the SD card or USB flash drive holding tables of rows, which a program adds to, changes, searches and sorts with statements in SQL, the standard language for databases. A club's members, a collection, a log of readings from a sensor, a game's high scores: anything that is a list of records.

The database engine is SQLite 3.47.0, the same engine that is in phones, web browsers and much else. A database file made on the Colour Maximite 2 opens in SQLite on a PC (DB Browser for SQLite, the sqlite3 shell, Python), and one made on a PC opens on the CMM2.

There is one new command, SQL, and one new function, SQL(. Their best part is that a table's row and an MMBasic structure (TYPE) are treated as the same thing: rows go straight into structures and out of them, matched by the members' names.

SQLite is not in the firmware. It is a module, A:/sql/sql.mod, which SQL OPEN loads and SQL CLOSE unloads, so it costs nothing until a program uses it.


Getting started
Write the 7.00.00RC6 firmware, and copy the sql folder of CMM2_Programs_7.00.00RC6.zip to the root of the SD card. Then run the example on its sample database:

RUN "A:/sql/computers.bas"



A first database
SQL OPEN "A:/data/club.db"
SQL EXEC "CREATE TABLE IF NOT EXISTS member(id INTEGER PRIMARY KEY, name TEXT, born INTEGER)"
SQL EXEC "INSERT INTO member(name, born) VALUES(?, ?)", n$, year%
SQL QUERY "SELECT name, born FROM member WHERE born > ? ORDER BY born", 1950
DO WHILE SQL(NEXT)
 PRINT SQL(1), SQL(2)
LOOP
SQL CLOSE


• SQL OPEN opens the database file, and makes it if it is not there.
• SQL EXEC runs statements. Each ? takes the next value after the statement, a number or a string, so there are no quotes to put round a string, and a value with quotes in it does no harm.
• SQL QUERY starts a query. SQL(NEXT) moves to each row in turn, returning 1, then 0 when there are no more, and SQL(n) is column n of the row, as an integer, a float or a string, as the column holds it.
• SQL(SELECT puts all of a query's rows into an array at once, and returns how many. A one dimensional array takes the first column, a two dimensional one (row, column) as many columns as it has:

DIM names$(100)
n = SQL(SELECT "SELECT name FROM member ORDER BY name", names$())


• SQL(COLUMNS), SQL(NAME n), SQL(ISNULL n), SQL(ROWS) (the rows the last INSERT, UPDATE or DELETE changed) and SQL(ID) (the key of the last row inserted) tell the rest.
• SQL CLOSE closes the database. RUN, NEW and CHAIN close it too, and undo any transaction not finished.


Tables and structures
TYPE Member
 id AS INTEGER
 name AS STRING LENGTH 40
 born AS INTEGER
END TYPE
DIM m AS Member, list(100) AS Member

SQL CREATE Member                   ' a table MEMBER with the type's members as its columns
m.name = "Ann" : m.born = 1961
SQL INSERT m                        ' a row from m; m.id is now its key
SQL INSERT list()                   ' a row for each element of the array
SQL QUERY "SELECT * FROM member ORDER BY name"
DO WHILE SQL(FETCH m)               ' as SQL(NEXT), the row put into m
 PRINT m.name, m.born
LOOP
n = SQL(SELECT "SELECT * FROM member WHERE born > ?", list(), 1950)
SQL EXEC "UPDATE member SET born = :born WHERE id = :id", m


• SQL CREATE makes a table from a TYPE, named after it (or SQL CREATE type, table$). INTEGER members are INTEGER columns, FLOAT members REAL and STRING members TEXT. A member named ID that is an integer is the table's key.
• SQL INSERT adds a row from a structure, or a row for each element of an array of structures, all in one transaction. When ID is 0, the database gives the row a new key and puts it into the member.
• SQL(FETCH, and SQL(SELECT with an array of structures, fill each member from the column of its name, in any case.
• A structure among a statement's values gives its :name parameters from the members of those names.
• Nested structures and array members work too: their columns are named PARENT_CHILD, and NAME_0, NAME_1 and so on.


The example
computers.db, in the sql folder, is a sample database with a table of 18 home computers. Its columns have the names of the members of the type Computer, so each row goes straight into a structure. computers.bas, beside it, is the manual's example:

TYPE Computer
 id AS INTEGER
 name AS STRING LENGTH 30
 maker AS STRING LENGTH 20
 year AS INTEGER
 cpu AS STRING LENGTH 10
 ramkb AS INTEGER
END TYPE
DIM c AS Computer, z80(20) AS Computer
DIM INTEGER i, n

SQL OPEN "A:/sql/computers.db"

PRINT "By year:"
SQL QUERY "SELECT * FROM computer ORDER BY year, name"
DO WHILE SQL(FETCH c)
 PRINT c.year, c.name, c.maker
LOOP

n = SQL(SELECT "SELECT * FROM computer WHERE cpu = ? ORDER BY ramkb DESC", z80(), "Z80")
PRINT : PRINT n; " with a Z80:"
FOR i = 0 TO n - 1
 PRINT z80(i).name, z80(i).ramkb; "K"
NEXT i

c.id = 0 : c.name = "ZX80" : c.maker = "Sinclair" : c.year = 1980 : c.cpu = "Z80" : c.ramkb = 1
SQL EXEC "DELETE FROM computer WHERE name = ?", c.name
SQL INSERT c
PRINT : PRINT "The ZX80 added as number"; c.id

PRINT : PRINT "Makers:"
SQL QUERY "SELECT maker, count(*) FROM computer GROUP BY maker ORDER BY 2 DESC, 1"
DO WHILE SQL(NEXT)
 PRINT SQL(1), SQL(2)
LOOP
SQL CLOSE


It lists the computers in the order they came out, finds the four with a Z80 (filling an array of the type), adds the ZX80 from a structure (SQL INSERT puts its new key, 19, into c.id; the DELETE before it stops a second run adding it twice) and counts each maker's computers.


Transactions
Each statement on its own is a transaction: when it ends, its change is safely on the card. That takes about 20ms. Many changes are far faster inside one transaction, which is written to the card once, at its end:

SQL EXEC "BEGIN"
FOR i = 1 TO 1000
 SQL EXEC "INSERT INTO reading(t, value) VALUES(?, ?)", i, v(i)
NEXT i
SQL EXEC "COMMIT"


A thousand inserts take about a third of a second like this, against 20 seconds one at a time. ROLLBACK in place of COMMIT undoes them all. If the power fails during a transaction, the next SQL OPEN finds it unfinished and undoes it, so the database is never left half changed.


Generation 1 and Generation 2
• Generation 2: the engine runs in the 8MB kept for loadable programs, with about 7MB for itself, and BASIC keeps all of its own memory. The one cost: a BASIC program with a database open cannot run a loadable program (.app) until it closes it (the error "SQL CLOSE first"). At the command prompt, running one closes a database that a program left open.
• Generation 1: the engine has 1MB for its work, and with its code it takes about 1.7MB of the 3.7MB that BASIC has, while a database is open. It is fast only with small databases: one of up to about 1MB (roughly 8,000 to 12,000 rows of 100 characters, with an index) works well. With a larger one, finding a row by its key or an index stays quick, but anything that has to look at the whole of a large table (an unindexed sort, GROUP BY, DISTINCT, a join of two big tables) reads the SD card all over again and can take minutes. A loadable program can still run with a database open, in the memory that is left.

From BASIC, on each:

                                   Generation 2   Generation 1
1,000 inserts in one transaction    0.32s          0.38s
One insert committed on its own     17ms           23ms


With a table of 50,000 rows (6.5MB, with a 6MB index):

                                   Generation 2   Generation 1 (1MB)
PRAGMA integrity_check              32s            160s
SELECT count(DISTINCT ...)          34s            460s
CREATE INDEX                        21s            23s


The Generation 1's figures for the big table were measured on a Generation 2 given the Generation 1's 1MB: the SD card, not the processor, sets them. Indexes make the difference: put one on the columns a program searches, sorts or joins by.


sql.app at the command prompt
sql.app runs SQL statements on any database, to look into one or set one up without writing a program. Each row is printed as a line, its columns separated by |:

RUN "A:/sql/sql.app", A:/sql/computers.db "SELECT name, year FROM computer"


-h prints the columns' names first, -t the time each statement took, and @ with a file's name runs the statements in the file.


Errors
A statement the database cannot run is an MMBasic error, "SQL: " followed by SQLite's own message (SQL: no such table: member), which ON ERROR SKIP and MM.ERRMSG$ catch. Ctrl-C stops a long statement.


Limits
• One database is open at a time; ATTACH reaches others through it.
• A statement is a string, so at most 255 characters, with up to 20 values after it.
• A structure used with SQL can have at most 64 columns, counting nested members and array elements.
• A large sort writes temporary files (sqlite_....tmp) to the root of the current drive, and removes them after.
• SQL( is a new function: an old program with an array named SQL() must rename it.


The commands and functions
SQL OPEN file$ [, poolKB [, cacheKB]]
SQL CLOSE [QUERY]
SQL EXEC sql$ [, value ...]
SQL QUERY sql$ [, value ...]
SQL CREATE type [, table$]
SQL INSERT var [, table$]

SQL(NEXT)   SQL(n)   SQL(FETCH var)
SQL(SELECT sql$, array() [, value ...])
SQL(COLUMNS)   SQL(NAME n)   SQL(ISNULL n)   SQL(ROWS)   SQL(ID)


poolKB and cacheKB set the engine's memory and its cache, and are not normally needed. The User Manual's new chapter, SQL Databases, has all of this and more.


Licences
SQLite is in the public domain. The module's settings follow usqlite's (Elvin Slavik), under the MIT licence; the module and the SQL command are the firmware's own, in apps/sql and Src/SQL.c of its repository.
 
homa

Guru

Joined: 05/11/2021
Location: Germany
Posts: 771
Posted: 05:43pm 14 Nov 2026
Copy link to clipboard 
Print this post

  matherp said  
...
SQLite is not in the firmware. It is a module, A:/sql/sql.mod, which SQL OPEN loads and SQL CLOSE unloads, so it costs nothing until a program uses it.
...


Hi Peter,

I'm excited about this feature of the SQLite database - thanks for that. Can you explain how these modules work? Will something like this also be available for a potential Wi-Fi expansion? Can you create these "modules" yourself, and if so, how does that work?

Matthias
 
homa

Guru

Joined: 05/11/2021
Location: Germany
Posts: 771
Posted: 12:31pm 15 Nov 2026
Copy link to clipboard 
Print this post

RTFM — I found it. Still, I can't stop wondering: is this concept also possible for the PC3 or RP2350 with PSRAM? I mean just the MODULE part—not necessarily the APPS.
It would be really cool to have SQLite for the RP2350!

Matthias
 
matherp
Guru

Joined: 11/12/2012
Location: United Kingdom
Posts: 12242
Posted: 01:17pm 15 Nov 2026
Copy link to clipboard 
Print this post

The thing you can't do at the moment on the cmm2 is write your own loadable command. I'll have a look at how/if that could work.
 
homa

Guru

Joined: 05/11/2021
Location: Germany
Posts: 771
Posted: 08:19pm 15 Nov 2026
Copy link to clipboard 
Print this post

mmsql 1.0 - an SQLite manager for the CMM2

Hi all, hi Peter,

thank you for SQL on the CMM2 - it is great! To learn it, I wrote a small tool
that has grown into a complete SQLite manager in MMBasic: mmsql.

It is a command line tool in the style of the sqlite3 shell (commands start
with a dot, anything else is SQL) and works on the screen and on a serial
terminal at the same time:

- look: .tables .indexes .triggers .schema .columns .info .databases
- browse tables page by page, with WHERE and ORDER BY
- edit: .insert .edit .delete row by row in a line editor, .create .alter
- check/repair: .check [full] .fkcheck .reindex .vacuum .backup (VACUUM INTO)
- export: .mode csv|list|insert|line|table, .headers, .separator, .once,
 .output, .dump
- import: .import file.csv table, .read file.sql
- line editor with history, options in mmsql.cfg, help in English and
 German (.help name)

Exports and imports were checked byte for byte against SQLite on a PC
(500 rows with quotes, line breaks, tabs, UTF-8 and 64-bit integers),
including the round trips CSV -> database -> CSV and dump -> database.

Files: mmsql.bas, mmsql_en.hlp, mmsql_de.hlp, README.md, LICENSE.txt
(MIT License). Needs 7.00.00RC6 and the sql folder on the card.

Peter, while writing it I found three things you may want to look at:

1. LINE INPUT # turns a tab into spaces (up to the next tab stop):
    OPEN "t.txt" FOR OUTPUT AS #1 : PRINT #1, "a"+CHR$(9)+"b" : CLOSE #1
    OPEN "t.txt" FOR INPUT AS #1 : LINE INPUT #1, s$ : CLOSE #1
    ? LEN(s$), INSTR(s$, CHR$(9))     ' gives 3  0
  Is this intended? mmsql now reads files with INPUT$ instead.

2. Ctrl-C during a long SQL statement: the manual says it gives the error
  "SQL: stopped by Ctrl-C", which ON ERROR SKIP can catch. Here (G2, RC6)
  it stops the statement but also ends the BASIC program, with or without
  ON ERROR SKIP. With OPTION BREAK 0 (or 4) Ctrl-C does not stop the
  statement at all. Could the break during SQL become only the catchable
  error, so that a tool like mmsql can carry on?

3. Texts longer than 255 characters: SQL(n) into a string gives an error,
  and for an arbitrary query there is no way round it. Would a way to read
  a value into a LONGSTRING array be possible, e.g. SQL(LONG n, a%())?

Small notes: PRAGMA encoding returns nothing in the module (SQLITE_OMIT_UTF16,
I guess), and under OPTION PICOMITE MM.DEVICE$ returns the PicoMite name, so
mmsql uses MM.INFO(HARDWARE) to tell G1 and G2 apart.

Feedback and tests (especially on a Generation 1) are very welcome!

Matthias


mmsql.zip
README.pdf
 
homa

Guru

Joined: 05/11/2021
Location: Germany
Posts: 771
Posted: 09:01pm 15 Nov 2026
Copy link to clipboard 
Print this post

mmsql – tutorial for beginners

You don’t know SQL yet? Then this is a good moment to try the brand-new mmsql.bas, the SQLite manager for the CMM2 from my post above! To make the start easy, here is a short tutorial for everyone, even if you have never written a single line of SQL before.

"Getting to know SQL and mmsql" (7 pages, PDF) walks you through it step by step:

- Two minutes of theory: tables, rows, columns, NULL
- Looking around in the sample database A:/sql/computers.db from the firmware zip
- Asking questions with SELECT, WHERE, ORDER BY and GROUP BY
- Building your own small database, a retro computer club, with demo.sql
- Changing data with .insert, .edit and .delete
- Connecting both databases with ATTACH and JOIN, e.g. "who in the club owns a Z80 machine?"
- Export and import as CSV, plus dump and backup
- Keeping the database healthy

The last page is an SQL cheat sheet.

At the important steps, a screen box shows what you should see, so you know you are on the right track. The outputs come from a real run on my CMM2 G2.

Files in ZIP:
TUTORIAL_mmsql.pdf: the tutorial
demo.sql: the club database, with comments. Copy it next to mmsql.bas.

Tips:
Use the unchanged computers.db from the firmware zip, so your results match the tutorial.
The steps work exactly as written on the first run. To start again, delete the club database with KILL "club.db".

Feedback is very welcome, especially from SQL beginners. Was anything unclear?

Matthias


TUTORIAL_mmsql.zip
 
zeitfest
Guru

Joined: 31/07/2019
Location: Australia
Posts: 703
Posted: 11:36pm 15 Nov 2026
Copy link to clipboard 
Print this post

[ I realise it was not in the intended scope but I think it will need standard joins, views, triggers and so on ]
Edited 2026-11-16 09:39 by zeitfest
 
matherp
Guru

Joined: 11/12/2012
Location: United Kingdom
Posts: 12242
Posted: 09:38am 16 Nov 2026
Copy link to clipboard 
Print this post

Matthias

Thanks for mmsql and the tutorial, and for the careful reports. I have been through each one: two were real faults and one a missing feature, and all three are fixed. The other two are as intended.


1. Ctrl-C during a long statement
You were right. The statement stopped, but the program then ended at its next statement, whatever ON ERROR said. The SQL code did raise "SQL: stopped by Ctrl-C", but MMBasic's break flag was still set, and the break check before the next statement took it. Now the flag is cleared when a statement is interrupted, so it is an ordinary error, as the manual says:

ON ERROR SKIP
SQL EXEC "..."                     ' Ctrl-C pressed here
PRINT MM.ERRMSG$                   ' SQL: stopped by Ctrl-C, and the program goes on


Without ON ERROR the program stops with that message. A statement stopped while it was changing the database leaves nothing behind, and inside a BEGIN SQLite undoes the whole transaction. Ctrl-C between the statements of one SQL EXEC, or between the rows of an SQL INSERT of an array, gives the same error.

With OPTION BREAK 4 the break key is Ctrl-D, not Ctrl-C, which is why Ctrl-C did nothing there; Ctrl-D stops a statement in the same way. With OPTION BREAK 0 nothing can stop a statement, as for any other command.


2. Text longer than 255 characters
New: SQL(LONGSTRING n, a%()) puts column n, at any length, into a LONGSTRING and returns its length. It works for any column: a number comes as its text, and a NULL as length 0.

DIM t%(1000)                       ' room for 8,000 characters
SQL QUERY "SELECT body FROM note"
DO WHILE SQL(NEXT)
 n = SQL(LONGSTRING 1, t%())      ' the text, of any length; n is its length
 PRINT n, LGETSTR$(t%(), 1, 60)
LOOP


When SQL(n) meets a value that is too long, its error now leaves the query on its row, so a program can catch it with ON ERROR SKIP and read the same column again with SQL(LONGSTRING).

It works the other way too. A LONGSTRING among the values gives its text to a ? parameter, and a statement can itself be a LONGSTRING, for statements over 255 characters:

SQL EXEC "INSERT INTO note(body) VALUES(?)", t%()
SQL EXEC s%()                      ' also SQL QUERY s%() and SQL(SELECT s%(), ...)


That should help .import and .dump with long rows.


3. PRAGMA encoding
A good guess: the module was built with SQLITE_OMIT_UTF16, as usqlite has it. It was worse than an empty PRAGMA. A UTF-16 database made on a PC opened, but every query of it failed with "malformed database schema". UTF-16 is now in sql.mod, which is about 2KB larger. Such a database opens and reads, PRAGMA encoding answers, and text still reaches BASIC as UTF-8. This one needs no new firmware: I have replaced sql.mod and sql.app in CMM2_Programs_7.00.00RC6.zip on the RC6 release today, so copy those two to A:/sql.


LINE INPUT and tabs
That is standard MMBasic: LINE INPUT turns a tab into spaces, up to the next multiple of OPTION TAB, and always has; the PicoMite does the same. It stays as it is, and the manual now says so. INPUT$, as you used, is the way to read a file unchanged.


MM.DEVICE$ under OPTION PICOMITE
That one is intended: under OPTION PICOMITE the CMM2 presents itself as a PicoMite. MM.INFO(HARDWARE) is the right test, as you found: 1 on a Generation 1, 0 on a Generation 2.


One more, found on the way
An SQL INSERT of an array that failed part way (a duplicate key, say) left its transaction open, so every change after it was lost at SQL CLOSE. Now none of that INSERT's rows are kept, and the changes after it are saved as they should be. Until the next firmware, after such an error:

SQL EXEC "ROLLBACK TO sql_insert" : SQL EXEC "RELEASE sql_insert"



zeitfest: joins (inner and LEFT), views, triggers and window functions all work. I tried each while checking the above.

The Ctrl-C, LONGSTRING and INSERT fixes are in the firmware, so they need the next release. They are on GitHub now (main for 7.00, and v8-dev), with the manual's SQL chapter updated: a new section, Long Text, and what Ctrl-C does. All of it has been tested on a Generation 2 and a Generation 1.
 
homa

Guru

Joined: 05/11/2021
Location: Germany
Posts: 771
Posted: 09:24pm 16 Nov 2026
Copy link to clipboard 
Print this post

Thank you, Peter, for the very fast and thorough answers, and for fixing Ctrl-C and adding SQL(LONGSTRING) so quickly! Both remove the two biggest limits of mmsql. I will build mmsql 1.1 for firmware 8 with them. Version 1.0 stays available for 7.00.00RC6.

@zeitfest: thanks for the feedback. As Peter wrote, joins, views and triggers all work, and mmsql handles them too. The beginners’ tutorial shows a JOIN across two databases and a view.

Matthias
 
homa

Guru

Joined: 05/11/2021
Location: Germany
Posts: 771
Posted: 12:48am 18 Nov 2026
Copy link to clipboard 
Print this post

Peter,
I found a problem in 8.00.00b1: COMMIT fails with "SQL: disk I/O error" when a program reads a SQL file line by line during a transaction and one line is longer than 255 characters (collected in a LONGSTRING). It is reproducible, also right after a restart. With the same lines read before BEGIN, or with only short lines, everything works. The same worked on 7.00.00RC6.

The attached ZIP has the details (BUGREPORT.txt) and three small programs. To reproduce:

RUN "mkio3"
RUN "iotest5", "io3.sql"

RUN "mkio4" and RUN "iotest5", "io4.sql" are the counter-check with short lines only.

Thanks!
Matthias

fw8_commit_ioerror.zip
 
Print this page


To reply to this topic, you need to log in.

The Back Shed's forum code is written, and hosted, in Australia.
© JAQ Software 2026