API & database integrator

B2B checkout: VAT check, payment link, mail and ERP order

A business customer orders online. The script checks its EU VAT number with the VIES SOAP service, creates a Stripe Checkout session, e-mails the payment link, and posts the order to the ERP REST API.

#1. Check the VAT number with the VIES SOAP service;
-> "[vat]" "40303265045";
-> "[answer]" (soap https "https://ec.europa.eu/taxation_customs/vies/services/checkVatService" "{}" "" "text/xml; charset=utf-8" (concat "<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:urn=\"urn:ec.europa.eu:taxud:vies:services:checkVat:types\"><soapenv:Header/><soapenv:Body><urn:checkVat><urn:countryCode>FR</urn:countryCode><urn:vatNumber>" [vat] "</urn:vatNumber></urn:checkVat></soapenv:Body></soapenv:Envelope>"));
xml load "rx_vies" [answer];
-> "[valid]" (xml select text "rx_vies" "//*[local-name()='valid']");
-> "[company]" (xml select text "rx_vies" "//*[local-name()='name']");
#2. Stripe Checkout session, amounts in cents;
json load "rx_pay" (stripe session "[{\"Product\": \"Order WEB-7731\", \"UnitAmount\": \"129000\", \"Currency\": \"eur\", \"Quantity\": \"1\"}]" "https://shop.example.com/paid?o=WEB-7731" "https://shop.example.com/cart" (parameter get value "STRIPE_SECRET_KEY"));
#3. SMTP connection, then the mail with the payment link;
cm set "rx_cm_smtp" {execute "mail.smtp.config.get"
	"[hostname]" "smtp.example.com"
	"[port]" "465"
	"[sender]" "shop@example.com"
	"[user]" "shop@example.com"
	"[password]" "change_me"
	"[authentication]" true
	"[tls]" true
	"[connectTimeout]" "10000"
	"[sessionTimeout]" "60000"
;};
mail send "rx_cm_smtp" "buyer@customer.example.com" "" "sales@example.com" "Your order WEB-7731" (concat "Hello " [company] ",<br>please pay your order here: " (json select "rx_pay" "/url")) "[]";
#4. Order to the ERP;
json load "rx_hdr" (concat "{\"Authorization\": \"Bearer " (parameter get value "ERP_TOKEN") "\"}");
json load "rx_erp" (rest https_json_post "https://erp.example.com/api/v1/orders" (json doc "rx_hdr") "[]" (concat "{\"ref\": \"WEB-7731\", \"vat\": \"FR" [vat] "\", \"vat_valid\": " [valid] ", \"stripe_session\": \"" (json select "rx_pay" "/id") "\", \"amount\": 1290.00}"));
concat "VAT valid: " [valid] ", Stripe session " (json select "rx_pay" "/id") ", ERP answer: " (json select "rx_erp" "/reponse");

Transactional price import, then reports in every format

The pricing team sends a CSV of new prices. The script updates the ERP database in one transaction (all or nothing), checks the result, then produces the reports the teams ask for: xlsx, csv, PDF, XML, HTML and a JSON block for an API.

#1. Connection to the ERP database, manual transactions;
cm set "rx_cm_erp" {execute "db.postgresql.config.get"
	"[hostname]" "10.0.0.5"
	"[port]" "5432"
	"[database]" "erp"
	"[user]" "mentdb"
	"[password]" "change_me"
;};
sql set_timeout 60;
sql connect "rx_erp" {cm get "rx_cm_erp";};
sql auto_commit "rx_erp" false;
#2. All the prices or none;
try {
	csv parse (mql "P") (mql "tmp/rx_prices.csv") (mql ";") (mql "\"") (mql "") {
		sql dml "rx_erp" (concat "update product set price=" (sql encode [P_price]) ", label=" (sql encode_std [P_label]) " where sku=" (sql encode [P_sku]));
	};
	sql commit "rx_erp";
} {
	sql rollback "rx_erp";
} "[err]";
#3. Checks;
-> "[missing]" (sql value "rx_erp" "select count(*) from product where price is null");
json load "rx_top" (sql row "rx_erp" "select sku, price from product order by price desc limit 1");
json load "rx_cats" (sql col_distinct "rx_erp" "select category from product");
-> "[low]" "";
sql parse "rx_erp" "R" "select sku from product where stock < 5" {
	-> "[low]" (concat [low] [R_sku] " ");
};
#4. Reports;
-> "[q]" "select sku, label, category, price from product order by sku";
sql to excelx "rx_erp" "products" [q] "tmp/rx_products.xlsx";
sql to excel "rx_erp" "products" [q] "tmp/rx_products.xls";
sql to csv_file "rx_erp" "tmp/rx_products.csv" [q] ";" "\"";
sql to pdf "rx_erp" "products" [q] "tmp/rx_products.pdf";
-> "[xml]" (sql to xml "rx_erp" "products" [q]);
-> "[html]" (sql to html "rx_erp" "products" [q]);
-> "[csv]" (sql to csv "rx_erp" "products" (concat [q] " limit 10") "," "\"");
json load "rx_json" (sql to json "rx_erp" "products" (concat [q] " limit 100"));
#5. Large table: read it by blocks of 500 rows;
sql block open "rx_erp" "product" "select sku, price from product";
json load "rx_block1" (sql block to_json 500);
sql block close;
sql disconnect "rx_erp";
concat "{\"missing_prices\": " [missing] ", \"top\": " (json doc "rx_top") ", \"low_stock\": \"" (string trim [low]) "\", \"open_sessions\": " (sql show) "}";

Sync an e-shop catalogue into MongoDB

A marketplace keeps a copy of a partner catalogue in MongoDB. The script reads the products from the partner REST API, upserts them in a collection, flags the sold-out ones, removes the discontinued ones, reads the 5 most expensive back and confirms the sync to the partner.

#1. Products from the partner API;
json load "rx_hdr" (concat "{\"Authorization\": \"Bearer " (parameter get value "PARTNER_TOKEN") "\", \"Accept\": \"application/json\"}");
json load "rx_resp" (rest https "get" "https://api.partner.example.com/v2/products" "page=1&per_page=100" (json doc "rx_hdr") "[]");
json load "rx_products" (json select "rx_resp" "/reponse");
#2. MongoDB client, database and collection, loaded once;
if (not (mongodb client_exist "rx_cli")) {mongodb connect "mongodb://localhost:27017" "rx_cli";};
-> "[dbs]" (mongodb database_show "rx_cli");
if (not (mongodb database_exist "rx_db")) {mongodb database_load "rx_cli" "rx_db" "marketplace";};
-> "[cols]" (mongodb collection_show "rx_db");
mongodb collection_load "rx_db" "rx_col" "partner_products";
#3. Write: new products, sold-out flag, discontinued removed;
json parse_array "rx_products" "/items" "[p]" {
	mongodb collection_insert "rx_col" [p];
};
-> "[soldout]" (mongodb collection_update "rx_col" "{\"stock\": 0}" "{\"$set\": {\"available\": false}}");
-> "[removed]" (mongodb collection_delete "rx_col" "{\"status\": \"discontinued\"}");
#4. Read back the 5 most expensive available products, name and price only;
-> "[top]" (mongodb collection_select "rx_col" "{\"available\": {\"$ne\": false}}" "{\"price\": -1}" "{\"name\": 1, \"price\": 1, \"_id\": 0}" null null null 100 0 5);
-> "[stat]" (mongodb database_stat "rx_db");
-> "[loaded]" (concat (mongodb client_show_loaded) (mongodb database_show_loaded) (mongodb collection_show_loaded) (mongodb collection_exist "rx_col"));
#5. Confirm to the partner, to the legacy stock service on HTTP and to the old SOAP ERP;
rest https_json_put "https://api.partner.example.com/v2/sync/acme" (json doc "rx_hdr") "[]" (concat "{\"synced\": " (json count "rx_products" "/items") ", \"sold_out\": " [soldout] "}");
rest http_json_put "http://stock.local:8080/api/sync" "{}" "[]" "{\"source\": \"partner\"}";
-> "[legacy]" (rest http "get" "http://stock.local:8080/api/status" "" "{}" "[]");
soap http "http://erp-legacy.local/ws/catalog.asmx" "{}" "SyncDone" "text/xml; charset=utf-8" (concat "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\"><soap:Body><SyncDone xmlns=\"http://tempuri.org/\"><count>" (json count "rx_products" "/items") "</count></SyncDone></soap:Body></soap:Envelope>");
mongodb collection_unload "rx_col";
mongodb database_unload "rx_db";
mongodb disconnect "rx_cli";
[top];

Read supplier invoices from an Office 365 mailbox

Supplier invoices arrive as PDF attachments in a shared Office 365 mailbox. The script gets an OAuth token from Microsoft Entra ID, stores an IMAP connection with XOAUTH2 on the Invoices folder, downloads the unread mails of the suppliers to disk, moves them to Archive, and extracts the total of each PDF.

#1. OAuth token for IMAP;
-> "[token]" (azure get_token "invoices@acme.onmicrosoft.com" (parameter get value "O365_INVOICES_PWD") "d61b48b2-5bee-4a2e-8ace-99f3c7603426" "076306e2-1bc8-4b4a-b69f-e35a53d4dd69" "[\"https://outlook.office365.com/IMAP.AccessAsUser.All\"]");
#2. IMAP connection: the token is the password, folder INBOX/Invoices;
json load "rx_cm" (execute "mail.imap.config.get"
	"[hostname]" "outlook.office365.com"
	"[port]" "993"
	"[user]" "invoices@acme.onmicrosoft.com"
	"[password]" [token]
	"[auth]" false
	"[tls]" false
	"[ssl]" true
	"[connectTimeout]" "10000"
	"[sessionTimeout]" "60000"
);
json iobject "rx_cm" "/" "xoauth2_scope" "1" STR;
json iobject "rx_cm" "/" "inFolder" "INBOX/Invoices" STR;
if (cm exist "rx_cm_imap_o365" "imap") {cm remove "rx_cm_imap_o365";};
cm set "rx_cm_imap_o365" (json doc "rx_cm");
#3. Unread mails of the suppliers with invoice in the subject, moved to Archive;
json load "rx_state" (mail download imap_disk "tmp/rx_mail" 20 "unread" "Archive|Errors" false true "2026-10-01" null (mql {string ends_with [imap_from] "@supplier.example.com"}) (mql {string contains_ignore_case [imap_subject] "invoice"}) {cm get "rx_cm_imap_o365";});
#4. Total of each PDF saved in the download folder;
-> "[dir]" (json select "rx_state" "/Directory");
json load "rx_out" "[]";
json load "rx_pdfs" (file dir_list_regex [dir] ".*\.pdf" true false);
json parse_array "rx_pdfs" "/" "[f]" {
	-> "[total]" (string regexp_extract (pdf extract_text (concat [dir] "/" [f])) "Total[^0-9]*([0-9]+[.,][0-9]{2})" 1);
	json iarray "rx_out" "/" (concat "{\"file\": \"" [f] "\", \"total\": \"" [total] "\"}") OBJ;
};
concat "{\"received\": " (json select "rx_state" "/NbReceived") ", \"invoices\": " (json doc "rx_out") ", \"connection_saved\": " (cm exist "rx_cm_imap_o365" "imap") "}";

Health check of the outgoing mail queue

An administrator monitors the mails that MentDB sends in the background. The script reads the counters of the queue, checks that the SMTP connection used by the apps exists, reads the oldest error date only when there are errors, and returns a status for the monitoring tool.

#1. Counters of the queue;
-> "[all]" (mail count_all);
-> "[err]" (mail count_error);
-> "[loaded]" (mail count_loaded);
-> "[limit]" (mail process_limit);
#2. Is the SMTP connection of the apps configured;
-> "[smtp]" (cm exist "demo_cm_smtp" "smtp");
#3. The oldest error date exists only when there are errors;
if (> [err] 0) {-> "[oldest]" (mail min_error_date);} {-> "[oldest]" "none";};
if (or (> [err] 0) (== [smtp] 0)) {-> "[status]" "WARNING";} {-> "[status]" "OK";};
concat "{\"status\": \"" [status] "\", \"queued_total\": " [all] ", \"in_error\": " [err] ", \"loaded\": " [loaded] ", \"sent_per_cycle\": " [limit] ", \"oldest_error\": \"" [oldest] "\", \"smtp_connection\": " [smtp] "}";

{"status": "WARNING", "queued_total": 0, "in_error": 0, "loaded": 0, "sent_per_cycle": 2, "oldest_error": "none", "smtp_connection": 0}

Triage the mails in error and replay them

After an SMTP outage, dozens of mails are in error. The script lists them, reads the body and the SMTP error of the oldest one, replays all the mails of a connection when the credentials were the cause, deletes a mail refused by the recipient server, removes the mails of the test connection, and runs the queue now.

#1. Mails in error, with their id;
json load "rx_errs" (mail show_error 15);
-> "[n]" (json count "rx_errs" "/data");
-> "[id]" (json select "rx_errs" "/data/[0]/id");
#2. Body and SMTP error of the first one;
-> "[body]" (string left (mail get_body [id]) 80);
-> "[why]" (mail get_error [id]);
#3. Bad credentials: fixed in the connection, replay all its mails. Refused recipient: delete the mail. Else replay it;
if (string contains [why] "Username and Password not accepted") {
	mail replay_error_cm "demo_cm_smtp";
} {
	if (string contains [why] "550") {mail delete_error_id [id];} {mail replay_error_id [id];};
};
#4. Mails sent by the test connection are not replayed;
mail delete_error_cm "rx_cm_smtp_test";
#5. Run the queue now and show the last mails;
mail process;
json load "rx_last" (mail show 5);
concat "{\"errors_before\": " [n] ", \"errors_after\": " (mail count_error) ", \"first_subject\": \"" (json select "rx_errs" "/data/[0]/subject") "\", \"last_mails\": " (json count "rx_last" "/") "}";

Declare the legacy ERP databases: AS/400, DB2, Oracle, Firebird

An integration project must reach four legacy databases. Declare them with their config scripts (password from a private parameter), check the JDBC URL and the driver generated for each one, and that the scripts will find them by type, without opening any connection.

#1. One private secret for the read-only ERP account;
parameter add "tuto_p4i_erp_pwd" "Erp-Read-2026!" "Password of the ERP read-only account" true;
-> "[pwd]" (parameter get value "tuto_p4i_erp_pwd");
#2. The four databases, declared only;
cm set "tuto_p4i_as400" {execute "db.as400.config.get" "[hostname]" "as400.erp.example.com" "[port]" "8471" "[database]" "ACMEPRD" "[user]" "MENTDB" "[password]" [pwd];};
cm set "tuto_p4i_db2" {execute "db.db2.config.get" "[hostname]" "db2.erp.example.com" "[port]" "50000" "[database]" "SALES" "[user]" "db2read" "[password]" [pwd];};
cm set "tuto_p4i_oracle" {execute "db.oracle.config.get" "[hostname]" "ora.erp.example.com" "[port]" "1521" "[database]" "FINPRD" "[user]" "fin_read" "[password]" [pwd];};
cm set "tuto_p4i_firebird" {execute "db.firebird.config.get" "[hostname]" "fb.shop.example.com" "[port]" "3050" "[database]" "/data/shop.fdb" "[user]" "SYSDBA" "[password]" [pwd];};
-> "[pwd]" "";
#3. What each config script produced, secrets left out;
-> "[rep]" "";
json load "rx_p4i_all" (cm show_obj "db");
json parse_obj "rx_p4i_all" "/" "[k]" "[v]" {
	if (string starts_with [k] "tuto_p4i_") {
		json load "rx_p4i_cm" (cm get [k]);
		-> "[rep]" (concat [rep] (string replace [k] "tuto_p4i_" "") ": " (json select "rx_p4i_cm" "/subType") " | " (json select "rx_p4i_cm" "/driver") " | " (json select "rx_p4i_cm" "/jdbc") [_n_]);
	};
};
#4. Cleanup;
-> "[rep]" (concat [rep] "Oracle as db: " (cm exist "tuto_p4i_oracle" "db") ", as sftp: " (cm exist "tuto_p4i_oracle" "sftp") ", password in report: " (string contains [rep] "Erp-Read"));
json parse_obj "rx_p4i_all" "/" "[k]" "[v]" {if (string starts_with [k] "tuto_p4i_") {cm remove [k];};};
json unload "rx_p4i_all";
json unload "rx_p4i_cm";
parameter remove "tuto_p4i_erp_pwd";
[rep];

oracle: Oracle | oracle.jdbc.driver.OracleDriver | jdbc:oracle:thin:@ora.erp.example.com:1521:FINPRD
db2: DB2 | com.ibm.db2.jcc.DB2Driver | jdbc:db2://db2.erp.example.com:50000/SALES
as400: AS400 | com.ibm.as400.access.AS400JDBCDriver | jdbc:as400://as400.erp.example.com:8471/ACMEPRD
firebird: Firebird | org.firebirdsql.jdbc.FBDriver | jdbc:firebirdsql://fb.shop.example.com:3050//data/shop.fdb
Oracle as db: 1, as sftp: 0, password in report: 0

Collect the orders sent by mail to a POP3 mailbox

Small customers still send their orders by mail to orders@. Download the new messages from the POP3 mailbox into a dated folder, delete them from the server once saved, and list the downloaded files for the order parser.

#1. The mailbox, SSL from the start on port 995;
cm set "tuto_p4i_pop3" {execute "mail.pop3.config.get"
	"[hostname]" "pop.mail.example.com"
	"[port]" "995"
	"[user]" "orders@acme.example.com"
	"[password]" (parameter get value "tuto_p4i_pop3_pwd")
	"[tls]" false
	"[ssl]" true
	"[connectTimeout]" "10000"
	"[sessionTimeout]" "60000"
;};
#2. Download 20 messages at most, deleted from the server after the download;
file mkdir "tmp/rx_p4i_pop3";
json load "rx_p4i_state" (mail download pop3 "tmp/rx_p4i_pop3"
	20 true
	{cm get "tuto_p4i_pop3";}
);
#3. Where are they;
-> "[n]" (json select "rx_p4i_state" "/NbReceived");
-> "[dir]" (json select "rx_p4i_state" "/Directory");
#4. The files of this run;
if (> [n] 0) {
	-> "[files]" (file dir_list [dir]);
} {
	-> "[files]" "[]";
};
concat [n] " order mail(s) of " (json select "rx_p4i_state" "/Total") " bytes saved in " [dir] [_n_] [files];

IMAP: supplier confirmations only, as JSON, moved to a folder

Read only the unread mails whose subject contains 'Order confirmation' from the purchasing mailbox, save them as JSON (body and attachments in base64), move them to a Processed folder and decode the PDF attachments for the archive.

#1. The purchasing mailbox;
cm set "tuto_p4i_imap" {execute "mail.imap.config.get"
	"[hostname]" "imap.mail.example.com"
	"[port]" "993"
	"[user]" "purchasing@acme.example.com"
	"[password]" (parameter get value "tuto_p4i_imap_pwd")
	"[auth]" false
	"[tls]" false
	"[ssl]" true
	"[connectTimeout]" "10000"
	"[sessionTimeout]" "60000"
;};
#2. 50 unread mails max, copied to Processed, marked as read, since the 1st of the month;
json load "rx_p4i_state" (mail download imap "tmp/rx_p4i_imap"
	50 "unread" "Processed" false true
	(date first_day_of_month (date sysdate)) null
	(mql (string ends_with [imap_from] "@ocean-pack.example.com"))
	(mql (string contains_ignore_case [imap_subject] "Order confirmation"))
	{cm get "tuto_p4i_imap";}
);
#3. Each mail is a JSON file;
-> "[saved]" 0;
if (> (json select "rx_p4i_state" "/NbReceived") 0) {
	-> "[dir]" (json select "rx_p4i_state" "/Directory");
	json load "rx_p4i_mails" (file dir_list [dir]);
	json parse_array "rx_p4i_mails" "/" "[m]" {
		if (string ends_with [m] ".json") {
			json load "rx_p4i_mail" (file load (concat [dir] "/" [m]));
			#4. Decode the PDF attachments: parts of type file;
			json parse_array "rx_p4i_mail" "/Parts" "[part]" {
				json load "rx_p4i_part" [part];
				if (and (equal (json select "rx_p4i_part" "/type") "file") (string ends_with (string lower (json select "rx_p4i_part" "/filename")) ".pdf")) {
					file b64_write (json select "rx_p4i_part" "/content_b64") (concat "tmp/rx_p4i_imap_pdf/" (json select "rx_p4i_part" "/filename"));
					++ "[saved]";
				};
			};
		};
	};
};
concat (json select "rx_p4i_state" "/NbReceived") " confirmation(s), " [saved] " PDF saved, still unread in the box: " (json select "rx_p4i_state" "/Unread");

Notify an internal HTTP webhook with a JSON event

When a stock level drops below the reorder point, push a JSON event to the warehouse system's internal webhook (plain HTTP inside the LAN) with an API key header, and log the answer for the support team.

#1. The event;
json load "rx_p4i_evt" "{}";
json iobject "rx_p4i_evt" / "type" "stock.below_reorder_point" STR;
json iobject "rx_p4i_evt" / "sku" "B-310" STR;
json iobject "rx_p4i_evt" / "on_hand" 35 INT;
json iobject "rx_p4i_evt" / "reorder_point" 81 INT;
json iobject "rx_p4i_evt" / "at" (date systimestamp) STR;
#2. Headers, the key comes from a private parameter;
json load "rx_p4i_hdr" "{}";
json iobject "rx_p4i_hdr" / "Content-Type" "application/json" STR;
json iobject "rx_p4i_hdr" / "X-Api-Key" (parameter get value "tuto_p4i_wms_key") STR;
#3. POST to the webhook of the warehouse system;
json load "rx_p4i_ans" (rest http_json_post "http://wms.lan.example.com:8085/hooks/stock" (json doc "rx_p4i_hdr") "[]" (json doc "rx_p4i_evt"));
#4. Keep a trace;
log write "rx_p4i_wms" (concat "stock event " (json select "rx_p4i_evt" "/sku") " -> " (json doc "rx_p4i_ans"));
json doc "rx_p4i_ans";