Home
JAQForum Ver 24.01
Log In or Join  
Active Topics
Local Time 09:19 22 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 : mmsql 1.2 - SQLite manager for the CMM2

Author Message
homa

Guru

Joined: 05/11/2021
Location: Germany
Posts: 777
Posted: 05:27pm 20 Nov 2026
Copy link to clipboard 
Print this post

mmsql 1.2 - SQLite manager for the CMM2

Hi all,

a new release of mmsql, the interactive SQLite manager for the Colour Maximite 2 (G1, G2, G3). It needs MMBasic 8.00.00b1 or later, 8.00.00b2 is recommended.

The big change: SQL is no longer squeezed into one line of 255 characters.

New in 1.2
- A real editor at the prompt: statements over up to 20 lines with a status line - F3 new line, F2 run, Esc discard
- Paste SQL from the PC: pasted lines are recognised and kept together
- One rule for input: one line runs with Enter; several lines are a block, each statement ends with ;
- Statements and queries over 255 characters, with results - at the prompt, in blocks and in scripts
- History of statements of any length, also after a restart; PgUp/PgDn
- Scripts as in sqlite3: .read takes statements over several lines, shows query results and knows dot commands
- No more 20-column limit: .import without limits for columns and long fields, .insert up to 64 columns
- Database in memory (:memory:) - mmsql starts without a file
- New output: .mode markdown, .mode json, .width, .print
- .dump keeps the real type of every value: BLOB or text, 2.0 stays REAL, very large integers stay exact
- More: .restore, .version, search patterns for .tables/.schema/.indexes/.triggers, .set screen, .set width 0
- Shorter prompt [retro.db]>, and terminal and screen show exactly the same

Note: .read now needs a ; at the end of every statement, as sqlite3 does. Old scripts with one statement per line without ; need a ; at the end of each line. Dumps from mmsql have always had it.

A query over several lines looks like this (F3 after each line, F2 to run - or just paste it), here with computers.db from the firmware zip:
SELECT maker, count(*) AS models, min(year) AS since
 FROM computer
 WHERE year < 1990
 GROUP BY maker
 ORDER BY since;


Three downloads
- mmsql.zip - the program, help files (English and German), README, CHANGES, and the full manual as PDF: MANUAL_mmsql.pdf (English) and HANDBUCH_mmsql.pdf (German)
- mmsql_tutorial.zip - TUTORIAL_mmsql.pdf with demo.sql: SQL and mmsql for beginners, step by step, using computers.db from the firmware zip
- mmsql_testsuite.zip - an automatic test suite for the CMM2: 51 tests of SQLite, .read, .dump, .import and export, run from mmsql itself. Expected results are in README.txt.

To start:
RUN "mmsql", "A:/sql/computers.db"

and then type .help

Thanks
A big thank you to Peter for 8.00.00b2: the history at the MMBasic prompt in the terminal and LGETSTR$ with 255 characters are both fixed.

@Peter: two things from the work on 1.2
- OPTION CONSOLE in a program spoils characters arriving on the serial port. If a program switches OPTION CONSOLE while text is pasted into the terminal, characters get lost or arrive as rubbish - as if the port were set up again each time. mmsql 1.2 no longer switches while it reads keys, so it is not urgent for me. Small example in fw8_option_console.zip - still the same on 8.00.00b2. Is this expected, and is there another way to write to the terminal only, or the screen only?
fw8_option_console.zip

- A wish: SQL(TYPE n). SQL(n) cannot tell a BLOB from a TEXT, or REAL 2.0 from INTEGER 2. To dump correctly, mmsql adds a typeof() column for every column to the query. A function next to SQL(ISNULL n) - for example 1 = INTEGER, 2 = FLOAT, 3 = TEXT, 4 = BLOB, 5 = NULL, as sqlite3_column_type() - would make that easy for any query. Small example in fw8_sql_type.zip.
fw8_sql_type.zip


A question to you
SQLite on the CMM2 is still quite new, and I would like to know how it is used out there:
- Do you already use SQLite on the CMM2? What for - a collection, a logger, a game, something else?
- Do you work with the SQL commands in your own programs, with mmsql, or both?
- Is mmsql useful for you, or does something keep you from using it?
- What is missing? A command, an export format, an example in the tutorial?

A one-line answer is fine - it helps me decide what comes next. Bugs and anything unclear in the manual or tutorial are very welcome, too.

Matthias


mmsql.zip
mmsql_tutorial.zip
mmsql_testsuite.zip

MANUAL_mmsql.pdf
 
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