- Register a local H2 database connection
- Create a table and load rows safely
- Transactions: commit, rollback, all or nothing
- One value, one row, distinct values
- Query to JSON, then enrich the document
- Read a large result set by blocks
- Run MQL on each row with sql parse
- Export a query to CSV, Excel and PDF files
- CSV, XML and HTML for three partners
- sql encode or sql encode_std: the backslash trap
- Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
- Use sql connect, sql dml and sql to json on a SQLite file
Register a local H2 database connection
Before writing any SQL, a developer declares the connection once in the connection manager: here an embedded H2 database in tmp/, built with the db.h2.embedded.config.get script. The recipe checks the registration and runs a first query, without ever printing the password.
#1. Build the H2 connection with the config script and register it; cm set "tuto_db_h2" {execute "db.h2.embedded.config.get" "[database]" "./tmp/tuto_db_h2/shop" "[user]" "tuto" "[password]" "tuto_pwd" ;}; #2. Check it: registered, typed db, usable by SCRUD; -> "[exists]" (cm exist "tuto_db_h2" "db"); -> "[as_file]" (cm exist "tuto_db_h2" "file"); -> "[scrud]" (string contains (cm show_scrud) "\"tuto_db_h2\""); #3. Read the non secret fields only; json load "tuto_db_cm" (cm get "tuto_db_h2"); -> "[info]" (concat (json select "tuto_db_cm" /subType) " " (json select "tuto_db_cm" /jdbc) ", properties: " (string regexp_replace (json fields "tuto_db_cm" /properties) "\s+" "")); json unload "tuto_db_cm"; #The update script holds the password: count its lines, never print it; -> "[gen_lines]" (string count (cm generate_update "tuto_db_h2") "json iobject"); #4. First query on the new database; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; -> "[version]" (sql value "tuto_db_s1" "SELECT H2VERSION()"); -> "[sessions]" (sql show); sql disconnect "tuto_db_s1"; concat "Registered as db: " [exists] ", as file: " [as_file] ", SCRUD ready: " [scrud] [_n_] [info] [_n_] "cm generate_update gives " [gen_lines] " field lines to edit" [_n_] "H2 version: " [version] ", open sessions: " (string regexp_replace [sessions] "\s+" "");
Registered as db: 1, as file: 0, SCRUD ready: 1 H2 jdbc:h2:./tmp/tuto_db_h2/shop, properties: ["DATABASE_TO_LOWER","password","MODE","user"] cm generate_update gives 12 field lines to edit H2 version: 2.1.214, open sessions: ["tuto_db_s1"]
- List every declared connection of a type with
cm show "db"orcm show_obj. - For MySQL, PostgreSQL or Oracle the same pattern applies with
cm set mysql,cm set postgresql,cm set oracle.
Create a table and load rows safely
Create the orders table and load six orders that arrive as a JSON array. Every value goes through sql encode, so a customer called O'Brien cannot break the query or open an SQL injection.
#1. Open a session on the local H2 database; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; #2. Recreate the table so that the recipe can be replayed; sql dml "tuto_db_s1" "DROP TABLE IF EXISTS orders"; sql dml "tuto_db_s1" "CREATE TABLE orders (id INT PRIMARY KEY, customer VARCHAR(40) NOT NULL, product VARCHAR(40), qty INT, price DECIMAL(10,2), created DATE)"; #3. The rows to insert come as a JSON array; json load "tuto_db_rows" "[ {\"id\": 1, \"customer\": \"Dupont\", \"product\": \"Keyboard\", \"qty\": 2, \"price\": 49.90, \"created\": \"2026-10-02\"}, {\"id\": 2, \"customer\": \"O'Brien\", \"product\": \"Monitor 27\", \"qty\": 1, \"price\": 289.00, \"created\": \"2026-10-05\"}, {\"id\": 3, \"customer\": \"Dupont\", \"product\": \"Mouse\", \"qty\": 3, \"price\": 19.50, \"created\": \"2026-10-09\"}, {\"id\": 4, \"customer\": \"Nakamura\", \"product\": \"Laptop\", \"qty\": 1, \"price\": 1249.00, \"created\": \"2026-10-12\"}, {\"id\": 5, \"customer\": \"Silva\", \"product\": \"Mouse\", \"qty\": 5, \"price\": 19.50, \"created\": \"2026-10-20\"}, {\"id\": 6, \"customer\": \"O'Brien\", \"product\": \"Keyboard\", \"qty\": 1, \"price\": 49.90, \"created\": \"2026-10-28\"} ]"; #4. One INSERT per row, every value goes through sql encode; -> "[inserted]" 0; json parse_array "tuto_db_rows" "/" "[row]" { json load "tuto_db_row" [row]; -> "[inserted]" (+ [inserted] (sql dml "tuto_db_s1" (concat "INSERT INTO orders (id, customer, product, qty, price, created) VALUES (" (sql encode (json select "tuto_db_row" /id)) ", " (sql encode (json select "tuto_db_row" /customer)) ", " (sql encode (json select "tuto_db_row" /product)) ", " (sql encode (json select "tuto_db_row" /qty)) ", " (sql encode (json select "tuto_db_row" /price)) ", " (sql encode (json select "tuto_db_row" /created)) ")" ))); }; json unload "tuto_db_row"; json unload "tuto_db_rows"; #5. Check what the table now holds; -> "[count]" (sql value "tuto_db_s1" "SELECT COUNT(*) FROM orders"); -> "[obrien]" (sql value "tuto_db_s1" (concat "SELECT COUNT(*) FROM orders WHERE customer=" (sql encode "O'Brien"))); sql disconnect "tuto_db_s1"; concat "Rows inserted: " [inserted] [_n_] "Rows in the table: " [count] [_n_] "Orders of O'Brien: " [obrien] [_n_] "The quote is escaped as: " (sql encode "O'Brien");
Rows inserted: 6 Rows in the table: 6 Orders of O'Brien: 2 The quote is escaped as: 'O''Brien'
- Wrap the loop in a transaction with
sql auto_commitfalse andsql committo load thousands of rows faster. - Read the rows from a CSV file with
csv parseinstead of a JSON array.
Transactions: commit, rollback, all or nothing
With auto-commit off, a test order is inserted then cancelled with rollback; then a batch of two inserts fails on a duplicate key and is rolled back as a whole; finally a valid batch is committed.
#1. Open a session and switch to manual commit; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; sql auto_commit "tuto_db_s1" false; -> "[before]" (sql value "tuto_db_s1" "SELECT COUNT(*) FROM orders"); #2. A test order is inserted then cancelled with rollback; sql dml "tuto_db_s1" (concat "INSERT INTO orders (id, customer, product, qty, price, created) VALUES (100, " (sql encode "Test") ", 'Cable', 1, 9.90, CURRENT_DATE)"); -> "[inside]" (sql value "tuto_db_s1" "SELECT COUNT(*) FROM orders"); sql rollback "tuto_db_s1"; -> "[after_rollback]" (sql value "tuto_db_s1" "SELECT COUNT(*) FROM orders"); #3. All or nothing: the second INSERT reuses id 1, so both are rolled back; try { sql dml "tuto_db_s1" "INSERT INTO orders (id, customer, product, qty, price, created) VALUES (7, 'Silva', 'Webcam', 1, 59.00, '2026-10-30')"; sql dml "tuto_db_s1" "INSERT INTO orders (id, customer, product, qty, price, created) VALUES (1, 'Silva', 'Headset', 1, 79.00, '2026-10-30')"; sql commit "tuto_db_s1"; -> "[batch]" "committed"; } { sql rollback "tuto_db_s1"; -> "[batch]" (concat "rolled back, cause: " (string regexp_extract [err] "([A-Z][a-z ]+violation)" 1) ", SQLSTATE " (string regexp_extract [err] "\[(\d{5})-" 1)); } "[err]"; #4. A valid batch is committed; sql dml "tuto_db_s1" "INSERT INTO orders (id, customer, product, qty, price, created) VALUES (7, 'Silva', 'Webcam', 1, 59.00, '2026-10-30')"; sql commit "tuto_db_s1"; -> "[final]" (sql value "tuto_db_s1" "SELECT COUNT(*) FROM orders"); sql auto_commit "tuto_db_s1" true; sql disconnect "tuto_db_s1"; concat "Before: " [before] " | inside the transaction: " [inside] " | after rollback: " [after_rollback] [_n_] "Batch with a duplicate key: " [batch] [_n_] "After the valid commit: " [final];
Before: 6 | inside the transaction: 7 | after rollback: 6 Batch with a duplicate key: rolled back, cause: Unique index or primary key violation, SQLSTATE 23505 After the valid commit: 7
- Read the SQLSTATE code of an error with
string regexp_extractto decide whether to retry. - Put the batch in a script and run it through the stack with
stackto retry it automatically.
One value, one row, distinct values
The three quick reads of every API: a single value (October turnover), a single row (the biggest order) and the distinct values of a column (customers who bought a mouse).
#1. Open a session; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; #2. One value: the turnover of October; -> "[turnover]" (sql value "tuto_db_s1" "SELECT SUM(qty * price) FROM orders WHERE created BETWEEN '2026-10-01' AND '2026-10-31'"); #3. One row: the biggest order; -> "[top]" (sql row "tuto_db_s1" "SELECT id, customer, product, qty * price AS amount FROM orders ORDER BY qty * price DESC LIMIT 1"); #4. The distinct values of a column: who bought a mouse; -> "[buyers]" (sql col_distinct "tuto_db_s1" (concat "SELECT customer FROM orders WHERE product=" (sql encode "Mouse"))); sql disconnect "tuto_db_s1"; #5. Read one field of the row; json load "tuto_db_top" [top]; -> "[top_customer]" (json select "tuto_db_top" /customer); json unload "tuto_db_top"; #6. col_distinct returns an object whose fields are the distinct values; json load "tuto_db_buyers" [buyers]; -> "[buyer_list]" (string regexp_replace (json fields "tuto_db_buyers" /) "\s+" ""); json unload "tuto_db_buyers"; concat "Turnover October: " [turnover] [_n_] "Biggest order: " (string regexp_replace [top] "\s+" " ") [_n_] "Customer of the biggest order: " [top_customer] [_n_] "Mouse buyers: " [buyer_list];
Turnover October: 1902.70
Biggest order: { "product": "Laptop", "amount": "1249.00", "id": "4", "customer": "Nakamura" }
Customer of the biggest order: Nakamura
Mouse buyers: ["Silva","Dupont"]- Pass user input to the WHERE clause only through
sql encode. - Return the row directly from a get script to build a detail endpoint.
Query to JSON, then enrich the document
The orders of a customer are read as a JSON document, then each line gets a computed amount field and the document gets the customer name: the typical body of a REST answer.
#1. Open a session and get the orders of a customer as JSON; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; json load "tuto_db_res" (sql to json "tuto_db_s1" "orders" (concat "SELECT id, product, qty, price FROM orders WHERE customer=" (sql encode "Dupont") " ORDER BY id")); sql disconnect "tuto_db_s1"; #2. Navigate in the result; -> "[nb]" (json count "tuto_db_res" /data); -> "[cols]" (json select "tuto_db_res" /columns); -> "[first]" (json select "tuto_db_res" /data[0]/product); #3. Add a computed field to each line; for (-> "[i]" 0) (< [i] [nb]) (++ "[i]") { -> "[amount]" (bd* (json select "tuto_db_res" (concat "/data[" [i] "]/qty")) (json select "tuto_db_res" (concat "/data[" [i] "]/price"))); json iobject "tuto_db_res" (concat "/data[" [i] "]") "amount" [amount] NUM; }; json iobject "tuto_db_res" / "customer" "Dupont" STR; -> "[out]" (json doc "tuto_db_res"); json unload "tuto_db_res"; [out];
{
"data": [
{
"product": "Keyboard",
"amount": 99.8,
"price": "49.90",
"qty": "2",
"id": "1"
},
{
"product": "Mouse",
"amount": 58.5,
"price": "19.50",
"qty": "3",
"id": "3"
}
],
"columns": [
"id",
"product",
"qty",
"price"
],
"table": "orders",
"customer": "Dupont"
}- Sort the lines with
json sortbefore returning them. - Use
sql to xmlfor a partner that expects XML.
Read a large result set by blocks
A cursor is opened on the table and read three rows at a time, as a batch would do on millions of rows without loading everything in memory. Each block is summed and logged.
#1. Open a cursor on the whole table; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; sql block open "tuto_db_s1" "orders" "SELECT id, customer, qty, price FROM orders ORDER BY id"; #2. Read it 3 lines at a time, as a batch job would do with millions of rows; -> "[page]" 0; -> "[total]" 0; -> "[log]" ""; -> "[nb]" 1; while (> [nb] 0) { json load "tuto_db_blk" (sql block to_json 3); -> "[nb]" (json count "tuto_db_blk" /data); if (> [nb] 0) { ++ "[page]"; -> "[ids]" ""; for (-> "[i]" 0) (< [i] [nb]) (++ "[i]") { -> "[ids]" (concat [ids] (json select "tuto_db_blk" (concat "/data[" [i] "]/id")) " "); -> "[total]" (bd+ [total] (bd* (json select "tuto_db_blk" (concat "/data[" [i] "]/qty")) (json select "tuto_db_blk" (concat "/data[" [i] "]/price")))); }; -> "[log]" (concat [log] "Block " [page] ": " [nb] " rows, ids " [ids] [_n_]); }; }; #3. Close the cursor and the session; sql block close; json unload "tuto_db_blk"; sql disconnect "tuto_db_s1"; concat [log] "Total amount: " [total];
Block 1: 3 rows, ids 1 2 3 Block 2: 3 rows, ids 4 5 6 Block 3: 1 rows, ids 7 Total amount: 1902.70
- Write each block to a file with
file appendto export a huge table. - Commit every block in a target database to copy a table.
Run MQL on each row with sql parse
sql parse calls an MQL block for every row, with each column in a T_ variable. The recipe flags the orders above 200 and accumulates the amount per customer in a JSON report.
#1. Run an MQL action on each row, each column arrives as a T_ variable; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; json load "tuto_db_report" "{\"big\": [], \"by_customer\": {}}"; -> "[nb]" 0; sql parse "tuto_db_s1" "T" (concat "SELECT customer, product, qty, price FROM orders ORDER BY id") { ++ "[nb]"; -> "[amount]" (bd* [T_qty] [T_price]); #2. Flag the orders above 200; if (> [amount] 200) { json iarray "tuto_db_report" /big (concat [T_customer] ": " [T_product] " for " [amount]) STR; }; #3. Accumulate the amount per customer; -> "[prev]" (json select "tuto_db_report" (concat "/by_customer/" [T_customer])); if (is null [prev]) {-> "[prev]" 0;}; json uobject "tuto_db_report" /by_customer [T_customer] (bd+ [prev] [amount]) NUM; }; sql disconnect "tuto_db_s1"; json iobject "tuto_db_report" / "rows" [nb] INT; -> "[out]" (json doc "tuto_db_report"); json unload "tuto_db_report"; [out];
{
"big": [
"O\u0027Brien: Monitor 27 for 289.00",
"Nakamura: Laptop for 1249.00"
],
"rows": 7,
"by_customer": {
"Silva": 156.5,
"Dupont": 158.3,
"Nakamura": 1249.0,
"O\u0027Brien": 338.9
}
}- Call another script per row with
executeto keep the action reusable. - Compare with
sql block to_jsonwhen the action needs several rows at once.
Export a query to CSV, Excel and PDF files
The same query is exported to CSV, XLS, XLSX and PDF in a work folder, then each file is read back to check it: CSV lines, Excel sheet and last cell, PDF pages and text. The folder is deleted at the end.
#1. Prepare the output folder and a query timeout; -> "[dir]" "tmp/tuto_db_export"; file mkdir [dir]; sql set_timeout 30; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; -> "[q]" "SELECT id, customer, product, qty, price, qty * price AS amount FROM orders ORDER BY id"; #2. The same query in four file formats; sql to csv_file "tuto_db_s1" (concat [dir] "/orders.csv") [q] ";" "\""; sql to excel "tuto_db_s1" "orders" [q] (concat [dir] "/orders.xls"); sql to excelx "tuto_db_s1" "orders" [q] (concat [dir] "/orders.xlsx"); sql to pdf "tuto_db_s1" "orders" [q] (concat [dir] "/orders.pdf"); sql disconnect "tuto_db_s1"; #3. Read the files back to check them; -> "[csv_lines]" (file count_lines (concat [dir] "/orders.csv")); excelx load "tuto_db_xl" (concat [dir] "/orders.xlsx"); -> "[sheets]" (excelx sheet show "tuto_db_xl"); -> "[sheet]" (json load select "tuto_db_sh" [sheets] /[0]); -> "[last_row]" (excelx sheet max_row "tuto_db_xl" [sheet]); -> "[cell]" (excelx cell get "tuto_db_xl" [sheet] [last_row] 1); excelx close "tuto_db_xl"; json unload "tuto_db_sh"; -> "[pages]" (pdf page_count_file (concat [dir] "/orders.pdf")); -> "[pdf_has_laptop]" (string contains (pdf extract_text (concat [dir] "/orders.pdf")) "Laptop"); -> "[xls_ok]" (file exist (concat [dir] "/orders.xls")); #4. Clean up; file delete [dir]; concat "CSV lines: " [csv_lines] [_n_] "Excel sheet: " [sheet] ", last row " [last_row] ", customer of that row: " [cell] [_n_] "PDF pages: " [pages] ", contains Laptop: " [pdf_has_laptop] [_n_] "XLS created: " [xls_ok] [_n_] "Folder still there: " (file exist [dir]);
CSV lines: 8 Excel sheet: orders, last row 7, customer of that row: Silva PDF pages: 1, contains Laptop: 1 XLS created: 1 Folder still there: 0
- Send the files with
mail sendor drop them on an SFTP server. - Generate a styled PDF from your own HTML with
pdf from html.
CSV, XML and HTML for three partners
One query feeds three partners: accounting gets a CSV file (re-read with csv parse to check the total), the ERP gets XML, the sales manager gets the HTML table converted into a PDF.
#1. One query, three text formats for three partners; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; -> "[q]" "SELECT id, customer, qty, price FROM orders WHERE created >= '2026-10-10' ORDER BY id"; -> "[csv]" (sql to csv "tuto_db_s1" "orders" [q] ";" "\""); -> "[xml]" (sql to xml "tuto_db_s1" "orders" [q]); -> "[html]" (sql to html "tuto_db_s1" "orders" [q]); sql disconnect "tuto_db_s1"; #2. Accounting gets the CSV file, re-read here line by line with csv parse; file mkdir "tmp/tuto_db_share"; file create "tmp/tuto_db_share/orders.csv" [csv] "UTF-8"; -> "[check]" 0; csv parse (mql "R") (mql "tmp/tuto_db_share/orders.csv") (mql ";") (mql "\"") (mql "") { -> "[check]" (bd+ [check] (bd* [R_qty] [R_price])); }; #3. The HTML table becomes a PDF for the sales manager; pdf from html [html] "tmp/tuto_db_share/orders.pdf"; -> "[pages]" (pdf page_count_file "tmp/tuto_db_share/orders.pdf"); file delete "tmp/tuto_db_share"; #4. The XML goes to the ERP: count its items; concat "CSV header: " (atom get [csv] 1 [_n_]) [_n_] "Amount re-read from the CSV file: " [check] [_n_] "XML rows for the ERP: " (string count [xml] "<customer>") [_n_] "PDF pages: " [pages];
CSV header: id;customer;qty;price Amount re-read from the CSV file: 1455.40 XML rows for the ERP: 4 PDF pages: 1
- Write the CSV straight to disk with
sql to csv_file. - Parse the XML with the XML family before sending it.
sql encode or sql encode_std: the backslash trap
A Windows path with a quote is stored in H2 twice. sql encode also doubles backslashes, which is right for MySQL only; sql encode_std doubles only the quotes, which is right for H2, PostgreSQL, Oracle or SQL Server.
#1. A value with a quote and backslashes, as typed by a user; -> "[path]" "C:\exports\l'ete.csv"; -> "[mysql_style]" (sql encode [path]); -> "[std_style]" (sql encode_std [path]); #2. Store it twice in a work table of the H2 database; sql connect "tuto_db_s1" {cm get "tuto_db_h2";}; sql dml "tuto_db_s1" "CREATE TABLE IF NOT EXISTS tuto_notes (id INT PRIMARY KEY, txt VARCHAR(100))"; sql dml "tuto_db_s1" "DELETE FROM tuto_notes"; sql dml "tuto_db_s1" (concat "INSERT INTO tuto_notes VALUES (1, " [mysql_style] ")"); sql dml "tuto_db_s1" (concat "INSERT INTO tuto_notes VALUES (2, " [std_style] ")"); #3. Read back and compare with the original; -> "[back1]" (sql value "tuto_db_s1" "SELECT txt FROM tuto_notes WHERE id=1"); -> "[back2]" (sql value "tuto_db_s1" "SELECT txt FROM tuto_notes WHERE id=2"); -> "[null_sql]" (sql encode null); sql dml "tuto_db_s1" "DROP TABLE tuto_notes"; sql disconnect "tuto_db_s1"; concat "sql encode : " [mysql_style] " -> stored " [back1] " -> same: " (equal [back1] [path]) [_n_] "sql encode_std : " [std_style] " -> stored " [back2] " -> same: " (equal [back2] [path]) [_n_] "A null value becomes: " [null_sql];
sql encode : 'C:\\exports\\l''ete.csv' -> stored C:\\exports\\l'ete.csv -> same: 0 sql encode_std : 'C:\exports\l''ete.csv' -> stored C:\exports\l'ete.csv -> same: 1 A null value becomes: null
- Choose the encode function from the connection subType read with
cm get. - Null values become the SQL keyword null with both functions.
Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
A small site needs a database without a server: one SQLite file, which any other program (Python, the sqlite3 command line, DB Browser) can open. The table is created by a script, the readings are inserted in one transaction with bound parameters (one array per row, never values pasted into the SQL), a GROUP BY gives the mean and the maximum per sensor and per day, and the readings over 25 degrees go to a CSV file. A missing value stays NULL and is left out of the mean.
if (equal (file exist "tmp/rec_sqlite") "0") {file mkdir "tmp/rec_sqlite";}; -> "[db]" "tmp/rec_sqlite/readings.db"; sqlite script [db] "DROP TABLE IF EXISTS reading; CREATE TABLE reading (id INTEGER PRIMARY KEY, sensor TEXT NOT NULL, at TEXT NOT NULL, celsius REAL); CREATE INDEX reading_sensor_at ON reading(sensor, at)"; -> "[rows]" "[[\"T1\", \"2026-11-09 08:00:00\", 21.4], [\"T1\", \"2026-11-09 12:00:00\", 26.1], [\"T1\", \"2026-11-09 16:00:00\", 24.8], [\"T2\", \"2026-11-09 08:00:00\", 19.0], [\"T2\", \"2026-11-09 12:00:00\", 22.5], [\"T1\", \"2026-11-10 08:00:00\", 20.9], [\"T1\", \"2026-11-10 12:00:00\", 27.3], [\"T2\", \"2026-11-10 12:00:00\", null]]"; json load "rx_ins" (sqlite execute [db] "INSERT INTO reading (sensor, at, celsius) VALUES (?, ?, ?)" [rows]); json load "rx_day" (sqlite query [db] "SELECT sensor, date(at) AS day, count(celsius) AS n, round(avg(celsius), 2) AS mean, max(celsius) AS top FROM reading WHERE at >= ? GROUP BY sensor, day ORDER BY sensor, day" "[\"2026-11-09\"]"); json load "rx_csv" (sqlite export_csv [db] "SELECT sensor, at, celsius FROM reading WHERE celsius > ? ORDER BY at" "tmp/rec_sqlite/hot.csv" "{\"params\": [25], \"overwrite\": true}"); -> "[res]" (concat "rows inserted " (json select "rx_ins" "/changes") " | T1 on " (json select "rx_day" "/[0]/day") ": mean " (json select "rx_day" "/[0]/mean") ", max " (json select "rx_day" "/[0]/top") " | T2 on " (json select "rx_day" "/[3]/day") ": " (json select "rx_day" "/[3]/n") " readings with a value | readings over 25 in hot.csv: " (json select "rx_csv" "/rows")); json unload "rx_ins"; json unload "rx_day"; json unload "rx_csv"; [res]
rows inserted 8 | T1 on 2026-11-09: mean 24.1, max 26.1 | T2 on 2026-11-10: 0 readings with a value | readings over 25 in hot.csv: 2
- Dates are text in SQLite: write them as 2026-11-09 08:00:00 and date(), strftime() and the comparisons with >= work on them.
- sqlite query opens the file read only: a DELETE there is refused. Use sqlite execute to write, and sqlite backup to copy the file while it is in use.
Use sql connect, sql dml and sql to json on a SQLite file
The sql functions work on any JDBC database. sqlite config gives the connection object of a SQLite file, so sql connect, sql dml, sql value and sql to json work on it as on MySQL or H2, with the same file checks as the sqlite functions. After sql disconnect the same file is read by sqlite query.
if (equal (file exist "tmp/rec_sqlite") "0") {file mkdir "tmp/rec_sqlite";}; -> "[db]" "tmp/rec_sqlite/stock.db"; sql connect "rx_sq" (sqlite config [db]); sql dml "rx_sq" "CREATE TABLE IF NOT EXISTS stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL)"; sql dml "rx_sq" "DELETE FROM stock"; sql dml "rx_sq" "INSERT INTO stock VALUES ('A-1', 5), ('A-2', 0), ('B-7', 12)"; -> "[total]" (sql value "rx_sq" "SELECT sum(qty) FROM stock"); -> "[empty]" (sql to json "rx_sq" "stock" "SELECT sku, qty FROM stock WHERE qty = 0"); sql disconnect "rx_sq"; json load "rx_s" (sqlite query [db] "SELECT count(*) AS n FROM stock"); -> "[res]" (concat "total quantity " [total] " | out of stock " [empty] " | rows read by sqlite query " (json select "rx_s" "/[0]/n")); json unload "rx_s"; [res]
total quantity 17 | out of stock {
"data": [
{
"qty": "0",
"sku": "A-2"
}
],
"columns": [
"sku",
"qty"
],
"table": "stock"
} | rows read by sqlite query 3- ATTACH, VACUUM, PRAGMA temp_store_directory and the backup to / restore from commands of the driver are refused on this connection: use sqlite backup and sqlite vacuum. sql dml runs every statement separated by ; and keeps those before an error.
- sql show tables does not know SQLite: sqlite tables gives the tables, their columns and indexes.
