SQLite
Run statements and queries on SQLite database files and read their tables, columns and information. Each call opens and closes the file. Typical uses: keep a small local database, read a SQLite file produced by another program.
sqlite versionsqlite scriptsqlite scriptsqlite executesqlite executesqlite executesqlite querysqlite querysqlite querysqlite tablessqlite tablessqlite infosqlite configsqlite config
sqlite version
Description
The version of SQLite and of the driver, and true when the native library is loaded
The version of SQLite and of the driver, and true when the native library is loaded
admin
sqlite version
mentdb
{"sqlite_version": "3.54.1", "driver": "sqlite-jdbc 3.54.1.0", "native": true}
sqlite script <path> <sql>
Description
Run several statements separated by ; (16 MB of text at most; \n between the lines of a one-line MQL text is a line break, see the top of the page), in one transaction: an error rolls everything back and nothing is written. BEGIN, COMMIT, END, ROLLBACK, SAVEPOINT and RELEASE are refused here, as the script is already one transaction. The file is created if missing. Result: {changes, transaction}. changes counts the rows inserted, updated or deleted, also by triggers
Run several statements separated by ; (16 MB of text at most; \n between the lines of a one-line MQL text is a line break, see the top of the page), in one transaction: an error rolls everything back and nothing is written. BEGIN, COMMIT, END, ROLLBACK, SAVEPOINT and RELEASE are refused here, as the script is already one transaction. The file is created if missing. Result: {changes, transaction}. changes counts the rows inserted, updated or deleted, also by triggers
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: The statements, without parameters - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: The statements, without parameters - String - required
admin
sqlite script "tmp/doc_sqlite.db" "PRAGMA auto_vacuum = FULL; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS shops; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS products_draft; DROP TABLE IF EXISTS orders_csv; CREATE TABLE shops (code TEXT PRIMARY KEY, city TEXT NOT NULL); CREATE TABLE orders (id INTEGER PRIMARY KEY, shop TEXT REFERENCES shops(code), day TEXT NOT NULL, amount REAL, note TEXT, scan BLOB); CREATE INDEX orders_day ON orders(day); INSERT INTO shops VALUES ('SD', 'Saint-Denis'), ('LT', 'Le Tampon')"
mentdb
{"changes": 2, "transaction": true}
An error rolls back the whole script
admin
sqlite script "tmp/doc_sqlite.db" "INSERT INTO shops VALUES ('SP', 'Saint-Pierre'); INSERT INTO nope VALUES (1)"
mentdb
ERROR: Sorry, an error appears (no such table: nope (SQLITE_ERROR) (the script was rolled back: nothing was written)).
sqlite script <path> <sql> <json_options>
Description
Run statements with options: transaction (true; false runs each statement on its own: the statements before an error stay written, the script may then manage its own BEGIN and COMMIT, and a transaction it leaves open is rolled back with an error), create (true: a missing file is created), foreign_keys (false: SQLite checks the foreign keys only when this is true), max_db_mb (2048, 1 to 1048576: the file cannot grow over this size; PRAGMA max_page_count is refused for this reason), timeout_s (stops the statement after this time, also a statement that only computes) (300), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)
Run statements with options: transaction (true; false runs each statement on its own: the statements before an error stay written, the script may then manage its own BEGIN and COMMIT, and a transaction it leaves open is rolled back with an error), create (true: a missing file is created), foreign_keys (false: SQLite checks the foreign keys only when this is true), max_db_mb (2048, 1 to 1048576: the file cannot grow over this size; PRAGMA max_page_count is refused for this reason), timeout_s (stops the statement after this time, also a statement that only computes) (300), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: The statements - String - required
json_options: The options, as a JSON object - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: The statements - String - required
json_options: The options, as a JSON object - String - required
admin
sqlite script "tmp/doc_sqlite.db" "CREATE TRIGGER IF NOT EXISTS orders_note AFTER INSERT ON orders WHEN NEW.note IS NULL BEGIN UPDATE orders SET note = 'none' WHERE id = NEW.id; END" "{\"transaction\": false}"
mentdb
{"changes": 0, "transaction": false}
Used in use cases (1 recipe)
SQL developer in MQL › Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
SQL developer in MQL › Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
sqlite execute <path> <sql>
Description
Run one statement without parameters (INSERT, UPDATE, DELETE, CREATE...). Result: {changes, last_insert_rowid}, and rows when the statement returns rows (RETURNING)
Run one statement without parameters (INSERT, UPDATE, DELETE, CREATE...). Result: {changes, last_insert_rowid}, and rows when the statement returns rows (RETURNING)
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
admin
sqlite execute "tmp/doc_sqlite.db" "DELETE FROM orders WHERE amount < 0"
mentdb
{"changes": 0, "last_insert_rowid": 0}
sqlite execute <path> <sql> <json_params>
Description
Run one statement with bound parameters. An array of arrays runs the statement once per inner array, in one transaction: an error names the row and nothing is written. Result: {statements (for several rows), changes, last_insert_rowid, rows (with RETURNING)}
Run one statement with bound parameters. An array of arrays runs the statement once per inner array, in one transaction: an error names the row and nothing is written. Result: {statements (for several rows), changes, last_insert_rowid, rows (with RETURNING)}
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
json_params: The values of the ? in order, as a JSON array: text, number, true or false (1 or 0), null, {"base64": "..."} or {"hex": "..."} for a BLOB - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
json_params: The values of the ? in order, as a JSON array: text, number, true or false (1 or 0), null, {"base64": "..."} or {"hex": "..."} for a BLOB - String - required
admin
sqlite execute "tmp/doc_sqlite.db" "INSERT INTO orders (shop, day, amount, note, scan) VALUES (?, ?, ?, ?, ?)" "[\"SD\", \"2026-11-03\", 120.5, \"Été ☀ first order\", {\"hex\": \"89504e47\"}]"
mentdb
{"changes": 1, "last_insert_rowid": 1}
Three rows in one transaction
admin
sqlite execute "tmp/doc_sqlite.db" "INSERT INTO orders (shop, day, amount) VALUES (?, ?, ?)" "[[\"SD\", \"2026-11-04\", 80], [\"LT\", \"2026-11-04\", 42.25], [\"LT\", \"2026-11-05\", null]]"
mentdb
{"statements": 3, "changes": 6, "last_insert_rowid": 4}
A NOT NULL error in the second row: nothing is written
admin
sqlite execute "tmp/doc_sqlite.db" "INSERT INTO orders (shop, day, amount) VALUES (?, ?, ?)" "[[\"SD\", \"2026-11-06\", 10], [\"SD\", null, 5]]"
mentdb
ERROR: Sorry, an error appears (json_params row 2: NOT NULL constraint failed: orders.day (SQLITE_CONSTRAINT_NOTNULL) (nothing was written)).
UPDATE with RETURNING
admin
sqlite execute "tmp/doc_sqlite.db" "UPDATE orders SET amount = amount * ? WHERE shop = ? AND amount IS NOT NULL RETURNING id, amount" "[1.1, \"LT\"]"
mentdb
{"changes": 1, "last_insert_rowid": 0, "rows": [{"id": 3, "amount": 46.475}]}
sqlite execute <path> <sql> <json_params> <json_options>
Description
Run one statement with options: create (true: a missing file is created), foreign_keys (false: SQLite checks the foreign keys only when this is true), max_db_mb (2048, 1 to 1048576: the file cannot grow over this size; PRAGMA max_page_count is refused for this reason), timeout_s (stops the statement after this time, also a statement that only computes) (300), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file), max_rows, max_result_mb and blob for the rows of RETURNING
Run one statement with options: create (true: a missing file is created), foreign_keys (false: SQLite checks the foreign keys only when this is true), max_db_mb (2048, 1 to 1048576: the file cannot grow over this size; PRAGMA max_page_count is refused for this reason), timeout_s (stops the statement after this time, also a statement that only computes) (300), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file), max_rows, max_result_mb and blob for the rows of RETURNING
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
json_params: The values of the ? in order, as a JSON array: text, number, true or false (1 or 0), null, {"base64": "..."} or {"hex": "..."} for a BLOB - String - required
json_options: The options, as a JSON object - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
json_params: The values of the ? in order, as a JSON array: text, number, true or false (1 or 0), null, {"base64": "..."} or {"hex": "..."} for a BLOB - String - required
json_options: The options, as a JSON object - String - required
admin
sqlite execute "tmp/doc_sqlite.db" "INSERT INTO orders (shop, day, amount) VALUES (?, ?, ?)" "[\"XX\", \"2026-11-07\", 1]" "{\"foreign_keys\": true}"
mentdb
ERROR: Sorry, an error appears (FOREIGN KEY constraint failed (SQLITE_CONSTRAINT_FOREIGNKEY)).
max_db_mb: the file may not grow over 1 MB
admin
sqlite execute "tmp/doc_sqlite.db" "INSERT INTO orders (shop, day, scan) VALUES ('SD', '2026-11-08', zeroblob(2000000))" "[]" "{\"max_db_mb\": 1}"
mentdb
ERROR: Sorry, an error appears (database or disk is full (SQLITE_FULL): the file reached max_db_mb, or the disk is full).
Used in use cases (1 recipe)
SQL developer in MQL › Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
SQL developer in MQL › Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
sqlite query <path> <sql>
Description
Read rows, with the file opened read only. Result: a JSON array of objects, one key per column (a repeated name gets _2). Types: INTEGER is a 64-bit integer (all of it is kept in the JSON), REAL keeps its decimal point (1.0), TEXT is UTF-8, NULL is null, a BLOB is base64 (or hex with the option blob). Infinity has no JSON form and is returned as null. Dates are TEXT in SQLite: write them as ISO 8601 text (2026-11-10 or 2026-11-10 08:30:00), the SQLite functions date(), datetime() and strftime() read this form.
Read rows, with the file opened read only. Result: a JSON array of objects, one key per column (a repeated name gets _2). Types: INTEGER is a 64-bit integer (all of it is kept in the JSON), REAL keeps its decimal point (1.0), TEXT is UTF-8, NULL is null, a BLOB is base64 (or hex with the option blob). Infinity has no JSON form and is returned as null. Dates are TEXT in SQLite: write them as ISO 8601 text (2026-11-10 or 2026-11-10 08:30:00), the SQLite functions date(), datetime() and strftime() read this form.
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
admin
sqlite query "tmp/doc_sqlite.db" "SELECT id, shop, day, amount, note, scan FROM orders ORDER BY id"
mentdb
[{"id": 1, "shop": "SD", "day": "2026-11-03", "amount": 120.5, "note": "Été ☀ first order", "scan": "iVBORw=="}, {"id": 2, "shop": "SD", "day": "2026-11-04", "amount": 80.0, "note": "none", "scan": null}, {"id": 3, "shop": "LT", "day": "2026-11-04", "amount": 46.475, "note": "none", "scan": null}, {"id": 4, "shop": "LT", "day": "2026-11-05", "amount": null, "note": "none", "scan": null}]
The SQLite date and JSON functions
admin
sqlite query "tmp/doc_sqlite.db" "SELECT date('2026-02-28', '+1 day') AS next_day, strftime('%W', '2026-11-10') AS week, json_extract('{\"a\": [1, 2]}', '$.a[1]') AS second"
mentdb
[{"next_day": "2026-03-01", "week": "45", "second": 2}]
sqlite query <path> <sql> <json_params>
Description
Read rows with bound parameters
Read rows with bound parameters
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
json_params: The values of the ? in order, as a JSON array: text, number, true or false (1 or 0), null, {"base64": "..."} or {"hex": "..."} for a BLOB - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
json_params: The values of the ? in order, as a JSON array: text, number, true or false (1 or 0), null, {"base64": "..."} or {"hex": "..."} for a BLOB - String - required
admin
sqlite query "tmp/doc_sqlite.db" "SELECT s.city, count(*) AS orders, round(sum(o.amount), 2) AS total FROM orders o JOIN shops s ON s.code = o.shop WHERE o.day >= ? GROUP BY s.city ORDER BY total DESC" "[\"2026-11-01\"]"
mentdb
[{"city": "Saint-Denis", "orders": 2, "total": 200.5}, {"city": "Le Tampon", "orders": 2, "total": 46.48}]
A query that writes is refused
admin
sqlite query "tmp/doc_sqlite.db" "DELETE FROM orders WHERE id = ?" "[1]"
mentdb
ERROR: Sorry, an error appears (attempt to write a readonly database (SQLITE_READONLY): this function opens the file read only, write with sqlite execute).
sqlite query <path> <sql> <json_params> <json_options>
Description
Read rows with options: max_rows (10000, 1 to 1000000: more rows is an error, add a LIMIT), max_result_mb (64, 1 to 1024), blob (base64 or hex), format (rows: an array of objects; table: {columns, rows as arrays, row_count}), timeout_s (stops the statement after this time, also a statement that only computes) (60), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)
Read rows with options: max_rows (10000, 1 to 1000000: more rows is an error, add a LIMIT), max_result_mb (64, 1 to 1024), blob (base64 or hex), format (rows: an array of objects; table: {columns, rows as arrays, row_count}), timeout_s (stops the statement after this time, also a statement that only computes) (60), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
json_params: The values of the ? in order, as a JSON array: text, number, true or false (1 or 0), null, {"base64": "..."} or {"hex": "..."} for a BLOB - String - required
json_options: The options, as a JSON object - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
sql: One SQL statement, with ? for the parameters - String - required
json_params: The values of the ? in order, as a JSON array: text, number, true or false (1 or 0), null, {"base64": "..."} or {"hex": "..."} for a BLOB - String - required
json_options: The options, as a JSON object - String - required
admin
sqlite query "tmp/doc_sqlite.db" "SELECT id, scan FROM orders WHERE scan IS NOT NULL" "[]" "{\"format\": \"table\", \"blob\": \"hex\"}"
mentdb
{"columns": ["id", "scan"], "rows": [[1, "89504e47"]], "row_count": 1}
More rows than max_rows
admin
sqlite query "tmp/doc_sqlite.db" "SELECT id FROM orders" "[]" "{\"max_rows\": 2}"
mentdb
ERROR: Sorry, an error appears (the result has more than max_rows (2) rows: add a LIMIT or raise max_rows).
A computation stopped by timeout_s
admin
sqlite query "tmp/doc_sqlite.db" "WITH RECURSIVE r(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM r) SELECT max(x) FROM r" "[]" "{\"timeout_s\": 1}"
mentdb
ERROR: Sorry, an error appears (the statement took more than timeout_s (1 s) and was stopped).
Used in use cases (3 recipes)
SQL developer in MQL › Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
Integrator & ETL developer › Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
SQL developer in MQL › Use sql connect, sql dml and sql to json on a SQLite file
SQL developer in MQL › Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
Integrator & ETL developer › Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
SQL developer in MQL › Use sql connect, sql dml and sql to json on a SQLite file
Often used with
file exist · file mkdir · sql connect · sql disconnect · sql dml · sql to json · sql value · sqlite backup
file exist · file mkdir · sql connect · sql disconnect · sql dml · sql to json · sql value · sqlite backup
sqlite tables <path>
Description
The tables and views, with their columns (name, type, not_null, default, primary_key: its position in the key, 0 if not in it), and for a table its indexes, foreign keys and CREATE statement. The internal tables sqlite_* are left out
The tables and views, with their columns (name, type, not_null, default, primary_key: its position in the key, 0 if not in it), and for a table its indexes, foreign keys and CREATE statement. The internal tables sqlite_* are left out
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
admin
sqlite tables "tmp/doc_sqlite.db"
mentdb
[{"name": "orders", "type": "table", "columns": [{"name": "id", "type": "INTEGER", "not_null": false, "default": null, "primary_key": 1}, {"name": "shop", "type": "TEXT", "not_null": false, "default": null, "primary_key": 0}, {"name": "day", "type": "TEXT", "not_null": true, "default": null, "primary_key": 0}, {"name": "amount", "type": "REAL", "not_null": false, "default": null, "primary_key": 0}, {"name": "note", "type": "TEXT", "not_null": false, "default": null, "primary_key": 0}, {"name": "scan", "type": "BLOB", "not_null": false, "default": null, "primary_key": 0}], "indexes": [{"name": "orders_day", "unique": false, "origin": "create index", "partial": false, "columns": ["day"]}], "foreign_keys": [{"column": "shop", "ref_table": "shops", "ref_column": "code", "on_update": "NO ACTION", "on_delete": "NO ACTION"}], "sql": "CREATE TABLE orders (id INTEGER PRIMARY KEY, shop TEXT REFERENCES shops(code), day TEXT NOT NULL, amount REAL, note TEXT, scan BLOB)"}, {"name": "shops", "type": "table", "columns": [{"name": "code", "type": "TEXT", "not_null": false, "default": null, "primary_key": 1}, {"name": "city", "type": "TEXT", "not_null": true, "default": null, "primary_key": 0}], "indexes": [{"name": "sqlite_autoindex_shops_1", "unique": true, "origin": "primary key", "partial": false, "columns": ["code"]}], "foreign_keys": [], "sql": "CREATE TABLE shops (code TEXT PRIMARY KEY, city TEXT NOT NULL)"}]
sqlite tables <path> <json_options>
Description
The tables with options: counts (false: true adds rows, the number of rows of each table), sql (true: the CREATE statement), timeout_s (stops the statement after this time, also a statement that only computes) (60), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)
The tables with options: counts (false: true adds rows, the number of rows of each table), sql (true: the CREATE statement), timeout_s (stops the statement after this time, also a statement that only computes) (60), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
json_options: The options, as a JSON object - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
json_options: The options, as a JSON object - String - required
admin
sqlite tables "tmp/doc_sqlite.db" "{\"counts\": true, \"sql\": false}"
mentdb
[{"name": "orders", "type": "table", "columns": [{"name": "id", "type": "INTEGER", "not_null": false, "default": null, "primary_key": 1}, {"name": "shop", "type": "TEXT", "not_null": false, "default": null, "primary_key": 0}, {"name": "day", "type": "TEXT", "not_null": true, "default": null, "primary_key": 0}, {"name": "amount", "type": "REAL", "not_null": false, "default": null, "primary_key": 0}, {"name": "note", "type": "TEXT", "not_null": false, "default": null, "primary_key": 0}, {"name": "scan", "type": "BLOB", "not_null": false, "default": null, "primary_key": 0}], "indexes": [{"name": "orders_day", "unique": false, "origin": "create index", "partial": false, "columns": ["day"]}], "foreign_keys": [{"column": "shop", "ref_table": "shops", "ref_column": "code", "on_update": "NO ACTION", "on_delete": "NO ACTION"}], "rows": 4}, {"name": "shops", "type": "table", "columns": [{"name": "code", "type": "TEXT", "not_null": false, "default": null, "primary_key": 1}, {"name": "city", "type": "TEXT", "not_null": true, "default": null, "primary_key": 0}], "indexes": [{"name": "sqlite_autoindex_shops_1", "unique": true, "origin": "primary key", "partial": false, "columns": ["code"]}], "foreign_keys": [], "rows": 2}]
Used in use cases (1 recipe)
Integrator & ETL developer › Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
Integrator & ETL developer › Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
Often used with
file exist · file mkdir · sqlite backup · sqlite import_json · sqlite integrity_check · sqlite query
file exist · file mkdir · sqlite backup · sqlite import_json · sqlite integrity_check · sqlite query
sqlite info <path>
Description
Facts on the file: size, SQLite version, page size and count, free pages, journal mode, text encoding, user_version, application_id, auto_vacuum, number of tables, views, indexes and triggers, and whether a -wal file is there
Facts on the file: size, SQLite version, page size and count, free pages, journal mode, text encoding, user_version, application_id, auto_vacuum, number of tables, views, indexes and triggers, and whether a -wal file is there
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
admin
sqlite info "tmp/doc_sqlite.db"
mentdb
{"file": "tmp/doc_sqlite.db", "file_size": 24576, "sqlite_version": "3.54.1", "page_size": 4096, "page_count": 6, "freelist_count": 0, "journal_mode": "delete", "encoding": "UTF-8", "user_version": 0, "application_id": 0, "auto_vacuum": "full", "tables": 2, "views": 0, "indexes": 1, "triggers": 1, "wal_file": false}
sqlite config <path>
Description
The connection object of a SQLite file, for cm set and then sql connect: the sql functions then work on the file. The checks of the sqlite functions apply at sql connect: plain file path with the file rights of the session, protected server files refused, no extension, no ATTACH, no VACUUM, no backup to / restore from, no PRAGMA temp_store_directory or data_store_directory. The query time-out (sql set_timeout) stops any statement. sql dml runs all the statements separated by ; and keeps those before an error. The file is not opened here
The connection object of a SQLite file, for cm set and then sql connect: the sql functions then work on the file. The checks of the sqlite functions apply at sql connect: plain file path with the file rights of the session, protected server files refused, no extension, no ATTACH, no VACUUM, no backup to / restore from, no PRAGMA temp_store_directory or data_store_directory. The query time-out (sql set_timeout) stops any statement. sql dml runs all the statements separated by ; and keeps those before an error. The file is not opened here
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
admin
sqlite config "tmp/doc_sqlite.db"
mentdb
{"type": "db", "subType": "SQLite", "driver": "org.sqlite.JDBC", "jdbc": "jdbc:sqlite:tmp/doc_sqlite.db", "loginTimeout": "10", "defaultSchema": "main", "properties": {"open_mode": "6", "busy_timeout": "5000"}}
Complete: cm set, sql connect, then sql dml and sql to json on the SQLite file
admin
cm set "doc_sqlite_cm" {sqlite config "tmp/doc_sqlite_cm.db"}; sql connect "doc_sq" {cm get "doc_sqlite_cm"}; sql dml "doc_sq" "CREATE TABLE IF NOT EXISTS city (name TEXT PRIMARY KEY, people INTEGER)"; sql dml "doc_sq" "INSERT OR REPLACE INTO city VALUES ('Saint-Denis', 153000), ('Saint-Paul', 105000)"; -> "[r]" (sql to json "doc_sq" "city" "SELECT name, people FROM city ORDER BY people DESC"); sql disconnect "doc_sq"; cm remove "doc_sqlite_cm"; [r]
mentdb
{ "data": [ { "name": "Saint-Denis", "people": "153000" }, { "name": "Saint-Paul", "people": "105000" } ], "columns": [ "name", "people" ], "table": "city" }
sqlite config <path> <json_options>
Description
The connection object with options: read_only (false), create (true), busy_timeout_ms (5000), foreign_keys (false), journal_mode (DELETE, TRUNCATE, PERSIST, MEMORY, WAL or OFF)
The connection object with options: read_only (false), create (true), busy_timeout_ms (5000), foreign_keys (false), journal_mode (DELETE, TRUNCATE, PERSIST, MEMORY, WAL or OFF)
Parameters
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
json_options: The options, as a JSON object - String - required
path: The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
json_options: The options, as a JSON object - String - required
admin
sqlite config "tmp/doc_sqlite.db" "{\"read_only\": true, \"busy_timeout_ms\": 15000}"
mentdb
{"type": "db", "subType": "SQLite", "driver": "org.sqlite.JDBC", "jdbc": "jdbc:sqlite:tmp/doc_sqlite.db", "loginTimeout": "10", "defaultSchema": "main", "properties": {"open_mode": "1", "busy_timeout": "15000"}}
Used in use cases (1 recipe)
SQL developer in MQL › Use sql connect, sql dml and sql to json on a SQLite file
SQL developer in MQL › Use sql connect, sql dml and sql to json on a SQLite file
Often used with
file exist · file mkdir · sql connect · sql disconnect · sql dml · sql to json · sql value · sqlite query
file exist · file mkdir · sql connect · sql disconnect · sql dml · sql to json · sql value · sqlite query
