Configuration & connections: MQL recipes

Recipes

Store an API URL and a private API key as parameters
Generate the code that deploys your parameters to PROD
Review the parameters of a project and output an audit JSON
Make sure a batch never runs twice at the same time
Session variables: counters, accumulators and cleanup
Stamp an action with the user, session and process
Declare a local folder connection for your file scripts
A local H2 database through a connection: create, insert, query, export
Transactions: auto_commit off, rollback, commit, parse
Move a connection to another server without leaking its password
Export a query in every format and stream big results by blocks
Declare all the connections of a project, passwords from private parameters

executed on MentDB

Store an API URL and a private API key as parameters

Your script calls a partner REST API. The URL and the key must not be hard-coded: the script stores them as server parameters, flags the key as private so the administration never shows it, reads both back like a production script would, and only ever prints a masked key.

Pipeline

01 · storePublic URL with a descriptionparameter add→02 · secretPrivate keyparameter is_private→03 · readRead as a script doesget_param→04 · maskMask before printingstring repeat

Script

admin

#1. A public setting with a description, private flag false;
parameter add "tuto_cf_partner_url" "https://api.partner.test/v1" "Base URL of the partner REST API" false;
#2. A secret: private flag true, never shown in the administration;
parameter add "tuto_cf_partner_key" "pk_test_8f3a91c27d" "API key of the partner" true;
#3. Read them back as a production script would;
-> "[url]" (get_param "tuto_cf_partner_url");
-> "[key]" (parameter get value "tuto_cf_partner_key");
#4. Never print a secret: keep 4 chars and mask the rest;
-> "[masked]" (concat (string left [key] 4) (string repeat "*" (- (string length [key]) 4)));
-> "[report]" (concat "url = " [url] " private=" (parameter is_private "tuto_cf_partner_url") [_n_] "key = " [masked] " private=" (parameter is_private "tuto_cf_partner_key") [_n_] "key description = " (parameter get description "tuto_cf_partner_key"));
#5. Cleanup;
parameter remove "tuto_cf_partner_url";
parameter remove "tuto_cf_partner_key";
[report];

Result

mentdb

url = https://api.partner.test/v1 private=0
key = pk_t************** private=1
key description = API key of the partner

Go further

In a web service, build the Authorization header with concat "Bearer " (get_param "...") and never return it
List every parameter with parameter show: private values stay hidden in the administration screens

executed on MentDB

Generate the code that deploys your parameters to PROD

On DEV you tuned two settings of a nightly batch. Instead of retyping them on the production server, the script generates the MQL that recreates them (merge is idempotent, update changes an existing value), removes them, then replays the generated code to prove it works on an empty server.

Pipeline

01 · tuneIdempotent merge, then updateparameter merge→02 · generateCode for the target serverparameter generate merge→03 · generate2Update requestparameter generate update→04 · replayReplay with evaleval

Script

admin

#1. Tune the settings on DEV: merge creates or replaces;
parameter merge "tuto_cf_batch_size" "500";
parameter merge "tuto_cf_batch_size" "1000";
parameter add "tuto_cf_export_dir" "tmp/tuto_cf_exports" "Folder of the nightly exports" false;
parameter update "tuto_cf_export_dir" "tmp/tuto_cf_exports_v2";
#2. Generate the deployment code;
-> "[merge]" (parameter generate merge "tuto_cf_batch_size");
-> "[update]" (parameter generate update "tuto_cf_export_dir");
#3. Simulate an empty target server, then replay the merge;
parameter remove "tuto_cf_batch_size";
parameter remove "tuto_cf_export_dir";
-> "[before]" (parameter exist "tuto_cf_batch_size");
eval [merge];
-> "[after]" (get_param "tuto_cf_batch_size");
parameter remove "tuto_cf_batch_size";
concat "#Deployment script for PROD;" [_n_] [merge] [_n_] [update] [_n_] "#Replay: exists before=" [before] ", value after eval=" [after];

Result

mentdb

#Deployment script for PROD;
parameter merge "tuto_cf_batch_size" "1000";
parameter update "tuto_cf_export_dir" "tmp/tuto_cf_exports_v2";
#Replay: exists before=0, value after eval=1000

Go further

parameter generate update assumes the parameter already exists on the target: prefer parameter generate merge for a first deployment
Save the generated code with file create "tmp/tuto_deploy.mql" ... and run it on the target from the editor

executed on MentDB

Review the parameters of a project and output an audit JSON

Before a release, a reviewer asks for a description on every parameter and the private flag on every password. The script fixes the two remarks with set_description and set_private, then loops over the expected keys and builds a JSON audit (exists, private, description) that can be pasted in the release ticket.

Pipeline

01 · fixAdd the missing descriptionparameter set_description→02 · secureFlag the password as privateparameter set_private→03 · checkExists / private / descriptionparameter exist→04 · flagsin_mem flag of each keyparameter show→05 · reportAudit in JSONjson iobject

Script

admin

#1. Two parameters created too quickly;
parameter add "tuto_cf_smtp_host" "smtp.example.test" "todo" false;
parameter add "tuto_cf_smtp_pwd" "s3cr3t-pwd" "SMTP password" false;
#2. Apply the review remarks;
parameter set_description "tuto_cf_smtp_host" "SMTP relay of the invoice mailer";
parameter set_private "tuto_cf_smtp_pwd" true;
#3. Audit the expected keys, one is missing on purpose;
#parameter show returns every parameter with its value: read only the flags;
json load "tuto_cf_all" (parameter show);
json load "tuto_cf_audit" "{}";
-> "[keys]" "tuto_cf_smtp_host,tuto_cf_smtp_pwd,tuto_cf_smtp_port";
for (-> "[i]" 1) (<= [i] (atom size [keys] ",")) (++ "[i]") {
	-> "[k]" (atom get [keys] [i] ",");
	if (parameter exist [k]) {
		json iobject "tuto_cf_audit" / [k] (concat "{\"private\": " (parameter is_private [k]) ", \"in_mem\": " (json select "tuto_cf_all" (concat "/" [k] "/in_mem")) ", \"description\": \"" (parameter get description [k]) "\"}") OBJ;
	} {
		json iobject "tuto_cf_audit" / [k] "MISSING: add it before the release" STR;
	};
};
-> "[audit]" (json doc "tuto_cf_audit");
#4. Cleanup;
json unload "tuto_cf_audit";
json unload "tuto_cf_all";
parameter remove "tuto_cf_smtp_host";
parameter remove "tuto_cf_smtp_pwd";
[audit];

Result

mentdb

{
  "tuto_cf_smtp_pwd": {
    "private": 1,
    "description": "SMTP password",
    "in_mem": 0
  },
  "tuto_cf_smtp_port": "MISSING: add it before the release",
  "tuto_cf_smtp_host": {
    "private": 0,
    "description": "SMTP relay of the invoice mailer",
    "in_mem": 0
  }
}

Go further

Run the same loop on the real keys of your project and fail the release with exception when one is MISSING
parameter is_private returns 1 or 0: use it in an if to hide values in your own admin pages

executed on MentDB

Make sure a batch never runs twice at the same time

A nightly import is triggered by a job and sometimes by hand. A parameter used as a lock guarantees a single run: lock_if_null takes the lock only if it is free (returns 1), a second caller gets 0 and stops, and the lock is always released, even when the import fails inside try.

Pipeline

01 · lockTake the lock if freeparameter lock_if_null→02 · ownerWho holds itwho→03 · workWork protected by trytry→04 · releaseRelease with nullparameter update

Script

admin

#1. The lock parameter exists with a null value;
parameter add "tuto_cf_import_lock" null "Lock of the nightly import" false;
#2. First run: takes the lock and records the owner;
-> "[run1]" (parameter lock_if_null "tuto_cf_import_lock" (concat "Locked by: " (who) " session " (sid)));
-> "[owner]" (get_param "tuto_cf_import_lock");
#3. A second run while the first one works;
-> "[run2]" (parameter lock_if_null "tuto_cf_import_lock" "another caller");
#4. The work fails, the lock is still released;
try {
	exception (507) ("Disk full while importing");
} {-> "[status]" (string regexp_extract [err] "([0-9]+: [^\n]+)\s*$" 1);} "[err]";
parameter update "tuto_cf_import_lock" null;
-> "[run3]" (parameter lock_if_null "tuto_cf_import_lock" "next night");
parameter remove "tuto_cf_import_lock";
concat "run 1 got the lock: " [run1] " (" (string regexp_replace [owner] "session [0-9]+" "session N") ")" [_n_] "run 2 got the lock: " [run2] [_n_] "import status: " [status] [_n_] "after release, next run got the lock: " [run3];

Result

mentdb

run 1 got the lock: 1 (Locked by: admin session N)
run 2 got the lock: 0
import status: 507: Disk full while importing
after release, next run got the lock: 1

Go further

The SCRUD generators of MentDB use exactly this pattern for their DB_TO_DB and CSV_TO_DB jobs
Store a timestamp with the owner (date systimestamp) to detect a lock left by a crashed run

executed on MentDB

Session variables: counters, accumulators and cleanup

A picking script updates a stock counter and keeps a trace of what happened. It uses the environment functions explicitly: env set var, env incr var and env decr var for the counter, concat_var to append to the trace, env exist var and env get var to read them, and env del var to clean the session before the next call.

Pipeline

01 · setCreate the variablesenv set var→02 · countDecrement and incrementenv decr var→03 · appendAppend to the traceconcat_var→04 · cleanDelete and checkenv del var

Script

admin

#1. Create the variables, same as the arrow;
env set var "[stock]" 120;
env set var "[trace]" "start=120";
#2. 15 units picked, 40 units restocked;
env decr var "[stock]" 15;
env incr var "[stock]" 40;
#3. Append strings at the end of the trace;
concat_var "[trace]" " > picked 15" " > restocked 40";
#4. Read and check;
-> "[seen]" (string contains (env show) "[stock]");
-> "[report]" (concat "stock = " (env get var "[stock]") [_n_] "trace = " (env get var "[trace]") [_n_] "listed by env show: " [seen]);
#5. Clean the session;
env del var "[stock]";
env del var "[trace]";
concat [report] [_n_] "after env del var, [stock] exists: " (env exist var "[stock]");

Result

mentdb

stock = 145
trace = start=120 > picked 15 > restocked 40
listed by env show: 1
after env del var, [stock] exists: 0

Go further

++ "[i]" and -- "[i]" are the short forms of env incr var and env decr var with a step of 1
Inside a called script, env show lists its parameters and the [PID] of the process: handy to debug

executed on MentDB

Stamp an action with the user, session and process

Every change made by an admin script must be traceable. The script builds an audit stamp from the current user, the session id, the number of commands already run in the session and the Java PID, then reads the list of open sessions to count who is connected to the server right now.

Pipeline

01 · userCurrent userwho→02 · sessionSession and command idssid→03 · countCommands executedcmdid→04 · allOpen sessionssessions

Script

admin

#1. Who and where;
-> "[stamp]" (concat (who) " / session " (sid) " / command " (cmdid) " / jvm pid " (pid));
#2. All open sessions of the server;
-> "[nb]" (count sessions);
json load "tuto_cf_sessions" (sessions);
-> "[users]" "";
json parse_array "tuto_cf_sessions" "/" "[s]" {
	json load "tuto_cf_one" [s];
	-> "[users]" (concat [users] (json select "tuto_cf_one" "/user") "(" (json select "tuto_cf_one" "/origin") ") ");
	json unload "tuto_cf_one";
};
json unload "tuto_cf_sessions";
concat "audit stamp: " [stamp] [_n_] "open sessions: " [nb] [_n_] "connected: " [users];

Result

mentdb

audit stamp: admin / session 438 / command 7 / jvm pid 88950
open sessions: 7
connected: mentdb(MENTDB) admin(MQL) admin(MQL) admin(MQL) admin(MQL) admin(MQL) admin(MQL)

Go further

The mentdb session with origin MENTDB is the internal session of the server itself
From a web socket client, use @sid, @cmdid, @sessions and @count sessions instead: they fail from the editor

executed on MentDB

Declare a local folder connection for your file scripts

File scripts should read their folder from a connection, not from a path written in the code. The script declares a file connection with the file.local.config.get helper, checks it exists and is listed among the file connections, uses its folder to drop and list a file, then removes everything.

Pipeline

01 · declareFile connectioncm set file→02 · checkExists for the type filecm exist→03 · listListed with the filescm show→04 · useFolder read from the connectioncm get→05 · removeDelete the connectioncm remove

Script

admin

#1. Declare the connection with the helper script;
cm set "tuto_cf_inbox" {execute "file.local.config.get" "[dir]" "tmp/tuto_cf_inbox";};
#2. Check it;
-> "[exists]" (cm exist "tuto_cf_inbox" "file");
-> "[listed]" (string contains (cm show "file") "tuto_cf_inbox");
-> "[in_obj]" (string contains (cm show_obj "file") "tuto_cf_inbox");
#3. Use the folder of the connection, never a hard-coded path;
json load "tuto_cf_cm" (cm get "tuto_cf_inbox");
-> "[dir]" (json select "tuto_cf_cm" "/localDir");
json unload "tuto_cf_cm";
file mkdir [dir];
file create (concat [dir] "/order_1001.csv") "id;amount\n1001;99.90";
-> "[files]" (file dir_list [dir]);
#4. Cleanup;
file delete [dir];
cm remove "tuto_cf_inbox";
concat "exists=" [exists] " listed=" [listed] " in show_obj=" [in_obj] [_n_] "folder=" [dir] [_n_] "files=" (string regexp_replace [files] "\s+" "") [_n_] "after cm remove: " (cm exist "tuto_cf_inbox");

Result

mentdb

exists=1 listed=1 in show_obj=1
folder=tmp/tuto_cf_inbox
files=["order_1001.csv"]
after cm remove: 0

Go further

Remote folders use the same pattern with cm set sftp, cm set ftp or cm set cifs: only the helper script changes
Read only the field you need from cm get: never return the whole object, it can hold passwords

executed on MentDB

A local H2 database through a connection: create, insert, query, export

To prototype without a real database server, the script declares an embedded H2 database under tmp/, connects with sql connect, creates an orders table, inserts rows with sql encode for the quotes, reads a total with sql value, one row with sql row and exports the table as CSV, then disconnects and removes the database.

Pipeline

01 · declareEmbedded H2 connectioncm set h2_embedded→02 · connectOpen an SQL sessionsql connect→03 · writeDDL and insertssql dml→04 · readValue, row, CSVsql value→05 · closeDisconnectsql disconnect

Script

admin

#1. Embedded H2 database, the file goes under tmp;
cm set "tuto_cf_shop_db" {execute "db.h2.embedded.config.get" "[database]" "./tmp/tuto_cf_h2/shop" "[user]" "sa" "[password]" "sa";};
sql connect "tuto_cf_s1" {cm get "tuto_cf_shop_db"};
#2. Create and fill the table, sql encode quotes the values;
sql dml "tuto_cf_s1" "create table orders (id int primary key, customer varchar(40), amount decimal(10,2))";
sql dml "tuto_cf_s1" (concat "insert into orders values (1, " (sql encode "Alice") ", 120.50), (2, " (sql encode "Bob") ", 75.00), (3, " (sql encode "O'Neil") ", 310.25)");
#3. Read;
-> "[total]" (sql value "tuto_cf_s1" "select sum(amount) from orders");
-> "[big]" (sql row "tuto_cf_s1" (concat "select customer, amount from orders where id=" (sql encode 3)));
-> "[csv]" (sql to csv "tuto_cf_s1" "orders" "select * from orders order by id" ";" "\"");
#4. Close and clean;
sql disconnect "tuto_cf_s1";
cm remove "tuto_cf_shop_db";
file delete "tmp/tuto_cf_h2";
concat "total = " [total] [_n_] "row 3 = " (string regexp_replace [big] "\s+" " ") [_n_] [csv];

Result

mentdb

total = 505.75
row 3 = { "amount": "310.25", "customer": "O\u0027Neil" }
id;customer;amount
1;Alice;120.50
2;Bob;75.00
3;O'Neil;310.25

Go further

Swap the connection for cm set mysql or cm set postgresql and the rest of the script does not change
Export the same query with sql to excelx or sql to pdf into tmp/ for a report

executed on MentDB

Transactions: auto_commit off, rollback, commit, parse

A purge must be all or nothing. With auto_commit off, the script deletes the orders, checks the count, rolls back, then applies a real change and commits it. It finishes by walking the rows with sql parse (one MQL action per row) and listing the distinct customers.

Pipeline

01 · txManual transactionsql auto_commit→02 · undoUndo the purgesql rollback→03 · saveCommit a real changesql commit→04 · loopOne action per rowsql parse→05 · distinctDistinct valuessql col_distinct

Script

admin

#1. A local H2 database with 3 orders;
cm set "tuto_cf_tx_db" {execute "db.h2.embedded.config.get" "[database]" "./tmp/tuto_cf_h2tx/shop" "[user]" "sa" "[password]" "sa";};
sql connect "tuto_cf_tx" {cm get "tuto_cf_tx_db"};
sql set_timeout 30;
sql dml "tuto_cf_tx" "create table orders (id int primary key, customer varchar(40), amount decimal(10,2))";
sql dml "tuto_cf_tx" "insert into orders values (1, 'Alice', 120.50), (2, 'Bob', 75.00), (3, 'Alice', 310.25)";
#2. Start a transaction and purge;
sql auto_commit "tuto_cf_tx" false;
sql dml "tuto_cf_tx" "delete from orders";
-> "[during]" (sql value "tuto_cf_tx" "select count(*) from orders");
sql rollback "tuto_cf_tx";
-> "[after_rb]" (sql value "tuto_cf_tx" "select count(*) from orders");
#3. A real change, committed;
sql dml "tuto_cf_tx" "update orders set amount = amount * 0.9 where customer = 'Alice'";
sql commit "tuto_cf_tx";
#4. Walk the rows;
-> "[lines]" "";
sql parse "tuto_cf_tx" "T" (concat "select id, customer, amount from orders order by id") {
	-> "[lines]" (concat [lines] "#" [T_id] " " [T_customer] " " [T_amount] "; ");
};
-> "[open]" (sql show);
-> "[customers]" (string regexp_replace (sql col_distinct "tuto_cf_tx" "select customer from orders") "\s+" "");
sql disconnect "tuto_cf_tx";
cm remove "tuto_cf_tx_db";
file delete "tmp/tuto_cf_h2tx";
concat "during the purge: " [during] " rows, after rollback: " [after_rb] " rows" [_n_] "after commit: " [lines] [_n_] "distinct customers: " [customers] [_n_] "open sql sessions: " (string regexp_replace [open] "\s+" "");

Result

mentdb

during the purge: 0 rows, after rollback: 3 rows
after commit: #1 Alice 108.45; #2 Bob 75.00; #3 Alice 279.23;
distinct customers: {"Bob":"customer","Alice":"customer"}
open sql sessions: ["tuto_cf_tx"]

Go further

Wrap the dml in try and call sql rollback in the error branch: nothing half-done stays in the database
sql show tables "<cm>" and sql show desc "<cm>" "orders" open the structure in the editor

executed on MentDB

Move a connection to another server without leaking its password

cm generate_update writes the MQL that rebuilds a connection on another server. The script generates it for a local H2 connection, masks the password with a regex before the code is shared in a ticket, and checks the connection is known to the SCRUD generator with cm show_scrud.

Pipeline

01 · declareA connection to deploycm set h2_embedded→02 · scrudKnown to the SCRUD generatorcm show_scrud→03 · generateRebuild codecm generate_update→04 · maskHide the passwordstring regexp_replace

Script

admin

#1. The connection prepared on DEV;
cm set "tuto_cf_report_db" {execute "db.h2.embedded.config.get" "[database]" "./tmp/tuto_cf_report/reports" "[user]" "report" "[password]" "Rp-2026-secret";};
-> "[scrud]" (string contains (cm show_scrud) "tuto_cf_report_db");
#2. Generate the rebuild code;
-> "[code]" (cm generate_update "tuto_cf_report_db");
#3. Mask the password before sharing it;
-> "[shared]" (string regexp_replace [code] "\"password\" \"[^\"]*\"" "\"password\" \"<set on the target>\"");
#4. Cleanup;
cm remove "tuto_cf_report_db";
concat "usable by the SCRUD generator: " [scrud] [_n_] [shared];

Result

mentdb

usable by the SCRUD generator: 1
json load "tmpCm" "{}";
json iobject "tmpCm" / "driver" "org.h2.Driver" STR;
json iobject "tmpCm" / "defaultSchema" "public" STR;
json iobject "tmpCm" / "subType" "H2" STR;
json iobject "tmpCm" / "loginTimeout" "10000" STR;
json iobject "tmpCm" / "jdbc" "jdbc:h2:./tmp/tuto_cf_report/reports" STR;
json iobject "tmpCm" / "k" "tuto_cf_report_db" STR;
json iobject "tmpCm" / "type" "db" STR;
json iobject "tmpCm" / "properties" "{}" OBJ;
json iobject "tmpCm" /properties "DATABASE_TO_LOWER" "TRUE" STR;
json iobject "tmpCm" /properties "password" "<set on the target>" STR;
json iobject "tmpCm" /properties "MODE" "MYSQL" STR;
json iobject "tmpCm" /properties "user" "report" STR;
cm set "tuto_cf_report_db" {json doc "tmpCm"};

Go further

On the target server, paste the code and replace the placeholder with a value read from a private parameter
cm exist "<key>" "db" lets a deployment script skip connections already present

executed on MentDB

Export a query in every format and stream big results by blocks

A reporting script must serve the same query as JSON for an API, HTML for a mail, XML for a partner, CSV, Excel and PDF files for the accounting team, and read a big table without loading it at once. The script does all of it on a local H2 database, after checking the table structure, and deletes the files.

Pipeline

01 · structureTables and columnssql show tables→02 · formatsJSON and HTMLsql to json→03 · filesCSV file and Excelsql to excelx→04 · streamBlocks of 2 rowssql block open

Script

admin

cm set "tuto_cf_rep_db" {execute "db.h2.embedded.config.get" "[database]" "./tmp/tuto_cf_h2rep/sales" "[user]" "sa" "[password]" "sa";};
sql connect "tuto_cf_rep" {cm get "tuto_cf_rep_db"};
sql dml "tuto_cf_rep" "create table sales (id int primary key, region varchar(20), amount decimal(10,2))";
sql dml "tuto_cf_rep" (concat "insert into sales values (1, 'North', 1200.00), (2, 'South', 830.50), (3, 'North', 415.25), (4, 'East', 990.00), (5, 'South', 120.00)");
#1. Structure;
-> "[tables]" (string regexp_replace (sql show tables "tuto_cf_rep_db") "\s+" "");
json load "tuto_cf_desc" (sql show desc "tuto_cf_rep_db" "sales");
-> "[cols]" (json count "tuto_cf_desc" "/data");
json unload "tuto_cf_desc";
#2. JSON for an API, HTML for a mail;
json load "tuto_cf_north" (sql to json "tuto_cf_rep" "sales" "select * from sales where region = 'North'");
-> "[north]" (json count "tuto_cf_north" "/data");
json unload "tuto_cf_north";
-> "[html]" (string contains (sql to html "tuto_cf_rep" "sales" "select region, sum(amount) total from sales group by region") "<table");
-> "[xml]" (string regexp_count (sql to xml "tuto_cf_rep" "sales" (concat "select * from sales where region = " (sql encode_std "South"))) "<region>South</region>");
#3. Files for the accounting team;
sql to csv_file "tuto_cf_rep" "tmp/tuto_cf_sales.csv" "select * from sales order by id" ";" "\"";
sql to excelx "tuto_cf_rep" "sales" "select * from sales order by id" "tmp/tuto_cf_sales.xlsx";
sql to pdf "tuto_cf_rep" "sales" "select * from sales order by id" "tmp/tuto_cf_sales.pdf";
-> "[csv_lines]" (file count_lines "tmp/tuto_cf_sales.csv");
-> "[xlsx]" (> (file size "tmp/tuto_cf_sales.xlsx") 0);
-> "[pdf]" (> (file size "tmp/tuto_cf_sales.pdf") 0);
#4. Stream by blocks of 2 rows;
sql block open "tuto_cf_rep" "sales" "select id, amount from sales order by id";
-> "[blocks]" "";
-> "[go]" 1;
while (equal [go] 1) {
	json load "tuto_cf_blk" (sql block to_json 2);
	-> "[nb]" (json count "tuto_cf_blk" "/data");
	if (> [nb] 0) {-> "[blocks]" (concat [blocks] [nb] " ");} {-> "[go]" 0;};
	json unload "tuto_cf_blk";
};
sql block close;
#5. Cleanup;
sql disconnect "tuto_cf_rep";
cm remove "tuto_cf_rep_db";
file delete "tmp/tuto_cf_sales.csv";
file delete "tmp/tuto_cf_sales.xlsx";
file delete "tmp/tuto_cf_sales.pdf";
file delete "tmp/tuto_cf_h2rep";
concat "tables: " [tables] ", columns of sales: " [cols] [_n_] "North rows as JSON: " [north] ", HTML table built: " [html] ", South rows in XML: " [xml] [_n_] "CSV lines: " [csv_lines] ", xlsx written: " [xlsx] ", pdf written: " [pdf] [_n_] "rows per block: " [blocks];

Result

mentdb

tables: ["sales"], columns of sales: 3
North rows as JSON: 2, HTML table built: 1, South rows in XML: 2
CSV lines: 6, xlsx written: 1, pdf written: 1
rows per block: 2 2 1

Go further

sql to pdf takes the same arguments as sql to excelx for a printable report
Inside a block loop, insert each block into another database with sql dml: big tables never fill the memory

executed on MentDB

Declare all the connections of a project, passwords from private parameters

A project talks to MySQL, PostgreSQL, an SFTP drop zone, an SMTP relay and an embedded HSQL cache. The script declares the five connections with the helper scripts, takes every password from a private parameter instead of the code, checks each one with cm exist and its type, counts them with cm show_obj, then removes them. Declaring a connection does not open it: nothing is contacted.

Pipeline

01 · secretPassword in a private parameterparameter add→02 · dbMySQL and PostgreSQLcm set mysql→03 · filesSFTP drop zonecm set sftp→04 · mailSMTP relaycm set smtp→05 · checkExists with the right typecm exist

Script

admin

#1. The secrets live in private parameters;
parameter add "tuto_cf_db_pwd" "Db-2026-secret" "Password of the project databases" true;
parameter add "tuto_cf_ftp_pwd" "Ftp-2026-secret" "Password of the SFTP drop zone" true;
#2. Declare the connections, nothing is opened here;
cm set "tuto_cf_erp" {execute "db.mysql.config.get" "[hostname]" "erp.example.test" "[port]" "3306" "[database]" "erp" "[user]" "erp_reader" "[password]" (get_param "tuto_cf_db_pwd");};
cm set "tuto_cf_dwh" {execute "db.postgresql.config.get" "[hostname]" "dwh.example.test" "[port]" "5432" "[database]" "dwh" "[user]" "loader" "[password]" (get_param "tuto_cf_db_pwd");};
cm set "tuto_cf_drop" {execute "file.remote.sftp.config.get" "[hostname]" "sftp.example.test" "[port]" "22" "[user]" "drop" "[password]" (get_param "tuto_cf_ftp_pwd") "[connectTimeout]" 10000 "[dataTimeout]" 30000;};
cm set "tuto_cf_mail" {execute "mail.smtp.config.get" "[hostname]" "smtp.example.test" "[port]" "587" "[sender]" "noreply@example.test" "[user]" "noreply@example.test" "[password]" (get_param "tuto_cf_ftp_pwd") "[authentication]" true "[tls]" true "[connectTimeout]" "10000" "[sessionTimeout]" "60000";};
cm set "tuto_cf_cache" {execute "db.hsql.embedded.config.get" "[database]" "./tmp/tuto_cf_hsql/cache" "[user]" "SA" "[password]" "";};
#3. Check each connection with its type;
-> "[checks]" (concat "erp db=" (cm exist "tuto_cf_erp" "db") ", dwh db=" (cm exist "tuto_cf_dwh" "db") ", drop sftp=" (cm exist "tuto_cf_drop" "sftp") ", mail smtp=" (cm exist "tuto_cf_mail" "smtp") ", cache db=" (cm exist "tuto_cf_cache" "db") ", mail as db=" (cm exist "tuto_cf_mail" "db"));
-> "[nb]" (string regexp_count (cm show_obj) "\"tuto_cf_[a-z]+\"");
#4. Cleanup;
cm remove "tuto_cf_erp";
cm remove "tuto_cf_dwh";
cm remove "tuto_cf_drop";
cm remove "tuto_cf_mail";
cm remove "tuto_cf_cache";
parameter remove "tuto_cf_db_pwd";
parameter remove "tuto_cf_ftp_pwd";
concat [checks] [_n_] "tuto_cf connections declared: " [nb] [_n_] "left after cleanup: " (string regexp_count (cm show_obj) "\"tuto_cf_[a-z]+\"");

Result

mentdb

erp db=1, dwh db=1, drop sftp=1, mail smtp=1, cache db=1, mail as db=0
tuto_cf connections declared: 5
left after cleanup: 0

Go further

Change a password in one place: parameter update "tuto_cf_db_pwd" ... then replay the cm set lines
Open them with sql connect "s1" {cm get "tuto_cf_erp"} or send a mail through tuto_cf_mail