SQL
Connect to databases, run queries and DML, manage transactions, and get results as values, rows, JSON, XML, HTML, CSV, Excel or PDF.
sql bulk_insert and sql bulk_upsert load JSON rows into a table. Typical uses: build a report from a database, load an import file into a table.sql connectsql showsql auto_commitsql set_timeoutsql valuesql rowsql col_distinctsql block opensql block closesql block to_jsonsql to jsonsql to xmlsql to htmlsql to excelsql to excelxsql to pdfsql to csvsql to csv_filesql dmlsql parsesql commitsql rollbacksql disconnectsql disconnect allsql encodesql encode_stdsql show tablessql selectsql show datasql show descsql show activitysql bulk_insertsql bulk_insertsql bulk_upsertsql bulk_upsertsql create_tablesql create_tablesql infer_schemasql infer_schema
sql connect <sqlId> <configJson>
Description
Connect to a database
Connect to a database
Parameters
sqlId: The SQL id - string - required
configJson: The JSON connection configuration - string - required
sqlId: The SQL id - string - required
configJson: The JSON connection configuration - string - required
admin
sql connect "session1" {cm get "demo_cm_mysql";};
mentdb
1
Used in use cases (38 recipes)
Script developer › Know which scripts a change will affect before touching a library script
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Register a local H2 database connection
CRUD & data API developer › Install the generated update and delete on the key
Industrial quality engineer › Gauge linearity and bias study from the lab database
Statistician & biostatistician › Mincer wage equation from a survey table in SQL
and 32 more in the Use cases
Script developer › Know which scripts a change will affect before touching a library script
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Register a local H2 database connection
CRUD & data API developer › Install the generated update and delete on the key
Industrial quality engineer › Gauge linearity and bias study from the lab database
Statistician & biostatistician › Mincer wage equation from a survey table in SQL
and 32 more in the Use cases
Often used with
sql disconnect · cm get · sql dml · execute · sql value · file delete · sql encode · cm remove
sql disconnect · cm get · sql dml · execute · sql value · file delete · sql encode · cm remove
sql show
Description
Show all open connections
Show all open connections
admin
sql show
mentdb
["session1"]
Used in use cases (4 recipes)
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Register a local H2 database connection
CRUD & data API developer › Tear down the tutorial database
API & database integrator › Transactional price import, then reports in every format
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Register a local H2 database connection
CRUD & data API developer › Tear down the tutorial database
API & database integrator › Transactional price import, then reports in every format
Often used with
sql disconnect · cm get · execute · sql connect · sql value · cm exist · cm remove · file delete
sql disconnect · cm get · execute · sql connect · sql value · cm exist · cm remove · file delete
sql auto_commit <sqlId> <bool>
Description
Set the connection to auto-commit
Set the connection to auto-commit
Parameters
sqlId: The SQL id - string - required
bool: The boolean - number - required
sqlId: The SQL id - string - required
bool: The boolean - number - required
admin
sql auto_commit "session1" true;
mentdb
1
Used in use cases (5 recipes)
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Transactions: commit, rollback, all or nothing
CRUD & data API developer › A write API with business rules in one transaction
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly copy of an Oracle table into MySQL, committed by batches
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Transactions: commit, rollback, all or nothing
CRUD & data API developer › A write API with business rules in one transaction
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly copy of an Oracle table into MySQL, committed by batches
Often used with
cm get · sql commit · sql connect · sql disconnect · sql dml · execute · sql encode · sql rollback
cm get · sql commit · sql connect · sql disconnect · sql dml · execute · sql encode · sql rollback
sql set_timeout <timeout>
Description
Set the query timeout
Set the query timeout
Parameters
timeout: The timeout in seconds (integer >= 0, 0 = no limit) - number - required
timeout: The timeout in seconds (integer >= 0, 0 = no limit) - number - required
admin
sql set_timeout 0;
mentdb
1
Used in use cases (4 recipes)
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly copy of an Oracle table into MySQL, committed by batches
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly copy of an Oracle table into MySQL, committed by batches
Often used with
cm get · sql connect · sql disconnect · execute · sql auto_commit · sql commit · sql dml · sql parse
cm get · sql connect · sql disconnect · execute · sql auto_commit · sql commit · sql dml · sql parse
sql value <sqlId> <selectQuery>
Description
Get a value from the database
Get a value from the database
Parameters
sqlId: The SQL id - string - required
selectQuery: The SELECT query - string - required
sqlId: The SQL id - string - required
selectQuery: The SELECT query - string - required
admin
sql value "session1" (concat "select name from products where id=1")
mentdb
car
Used in use cases (19 recipes)
Script developer › Know which scripts a change will affect before touching a library script
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Register a local H2 database connection
CRUD & data API developer › Install the generated update and delete on the key
Data quality analyst › Outlier readings in a sensor table by density clustering
Excel reporting analyst › Sales ledger workbook queried with SQL
and 13 more in the Use cases
Script developer › Know which scripts a change will affect before touching a library script
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Register a local H2 database connection
CRUD & data API developer › Install the generated update and delete on the key
Data quality analyst › Outlier readings in a sensor table by density clustering
Excel reporting analyst › Sales ledger workbook queried with SQL
and 13 more in the Use cases
Often used with
sql connect · sql disconnect · cm get · sql dml · execute · sql encode · file delete · cm remove
sql connect · sql disconnect · cm get · sql dml · execute · sql encode · file delete · cm remove
sql row <sqlId> <selectQuery>
Description
Get a row from the database
Get a row from the database
Parameters
sqlId: The SQL id - string - required
selectQuery: The SELECT query - string - required
sqlId: The SQL id - string - required
selectQuery: The SELECT query - string - required
admin
sql row "session1" (concat "select * from products where id=" (sql encode 1))
mentdb
{ "dtcreate": "2018-02-15 10:00:00.0", "sale": "1", "quantity": "5", "subtype": "T", "price": "7.50", "cat": null, "name": "car", "weight": "23.4567", "id": "1", "type": "A", "desc": "a car ...." }
Used in use cases (4 recipes)
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › One value, one row, distinct values
CRUD & data API developer › Install the generated update and delete on the key
API & database integrator › Transactional price import, then reports in every format
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › One value, one row, distinct values
CRUD & data API developer › Install the generated update and delete on the key
API & database integrator › Transactional price import, then reports in every format
Often used with
cm get · sql connect · sql disconnect · sql value · execute · sql encode · string regexp_replace · sql col_distinct
cm get · sql connect · sql disconnect · sql value · execute · sql encode · string regexp_replace · sql col_distinct
sql col_distinct <sqlId> <selectQuery>
Description
Get the distinct values of a column from the database
Get the distinct values of a column from the database
Parameters
sqlId: The SQL id - string - required
selectQuery: The SELECT query - string - required
sqlId: The SQL id - string - required
selectQuery: The SELECT query - string - required
admin
sql col_distinct "session1" (concat "select * from products where id=" (sql encode 1))
mentdb
Used in use cases (3 recipes)
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › One value, one row, distinct values
API & database integrator › Transactional price import, then reports in every format
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › One value, one row, distinct values
API & database integrator › Transactional price import, then reports in every format
Often used with
cm get · sql connect · sql disconnect · sql value · execute · sql auto_commit · sql commit · sql dml
cm get · sql connect · sql disconnect · sql value · execute · sql auto_commit · sql commit · sql dml
sql block open <sqlId> <tableName> <selectQuery>
Description
Open the result set to get data blocks
Open the result set to get data blocks
Parameters
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
admin
sql block open "session1" tablename (concat "select * from products where id=" (sql encode 1));
mentdb
1
Used in use cases (3 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Read a large result set by blocks
API & database integrator › Transactional price import, then reports in every format
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Read a large result set by blocks
API & database integrator › Transactional price import, then reports in every format
Often used with
cm get · sql block close · sql block to_json · sql connect · sql disconnect · execute · sql dml · sql encode_std
cm get · sql block close · sql block to_json · sql connect · sql disconnect · execute · sql dml · sql encode_std
sql block close
Description
Close the result set
Close the result set
admin
sql block close;
mentdb
1
Used in use cases (3 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Read a large result set by blocks
API & database integrator › Transactional price import, then reports in every format
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Read a large result set by blocks
API & database integrator › Transactional price import, then reports in every format
Often used with
cm get · sql block open · sql block to_json · sql connect · sql disconnect · execute · sql dml · sql encode_std
cm get · sql block open · sql block to_json · sql connect · sql disconnect · execute · sql dml · sql encode_std
sql block to_json <nbLine>
Description
Get a data block from the result set
Get a data block from the result set
Parameters
nbLine: The number of lines to return - number - required
nbLine: The number of lines to return - number - required
admin
sql block to_json 1
mentdb
{ "data": [ { "dtcreate": "2018-02-15 10:00:00.0", "sale": "1", "quantity": "5", "subtype": "T", "price": "7.50", "cat": null, "name": "car", "weight": "23.4567", "id": "1", "type": "A", "desc": "a car ...." } ], "columns": [ "id", "name", "quantity", "cat", "desc", "dtcreate", "type", "subtype", "price", "sale", "weight" ], "table": "products" }
Used in use cases (3 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Read a large result set by blocks
API & database integrator › Transactional price import, then reports in every format
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Read a large result set by blocks
API & database integrator › Transactional price import, then reports in every format
Often used with
cm get · sql block close · sql block open · sql connect · sql disconnect · execute · sql dml · sql encode_std
cm get · sql block close · sql block open · sql connect · sql disconnect · execute · sql dml · sql encode_std
sql to json <sqlId> <tableName> <selectQuery>
Description
Get data from the database as JSON
Get data from the database as JSON
Parameters
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
admin
sql to json "session1" "products" (concat "select * from products")
mentdb
{ "data": [ { "dtcreate": "2018-02-15 10:00:00.0", "sale": "1", "quantity": "5", "subtype": "T", "price": "7.50", "cat": null, "name": "car", "weight": "23.4567", "id": "1", "type": "A", "desc": "a car ...." }, { "dtcreate": "2018-02-16 12:00:00.0", "sale": "0", "quantity": "2", "subtype": "R", "price": "9.80", "cat": "", "name": "pen", "weight": "29.987", "id": "2", "type": "A", "desc": "a pen ...." }, { "dtcreate": "2018-02-17 13:00:00.0", "sale": "0", "quantity": "200", "subtype": "1", "price": "14.70", "cat": "money", "name": "yen", "weight": "89.987", "id": "3", "type": "Z", "desc": "a yen ...." }, { "dtcreate": "2018-02-18 13:00:00.0", "sale": "0", "quantity": "1", "subtype": "1", "price": "14.70", "cat": "human", "name": "bob", "weight": "99.098", "id": "4", "type": "Z", "desc": "a human ...." }, { "dtcreate": "2018-02-19 15:00:00.0", "sale": "1", "quantity": "19", "subtype": "T", "price": "19.40", "cat": "animal", "name": "spider", "weight": "123.08", "id": "5", "type": "E", "desc": "an animal ...." } ], "columns": [ "id", "name", "quantity", "cat", "desc", "dtcreate", "type", "subtype", "price", "sale", "weight" ], "table": "products" }
Used in use cases (8 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Query to JSON, then enrich the document
CRUD & data API developer › A read API with typed parameters
XML, HTML & Word integrator › Stage XML orders in an HSQL database and export them as XML
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Publish a SQL table as a JSON web service
and 2 more in the Use cases
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Query to JSON, then enrich the document
CRUD & data API developer › A read API with typed parameters
XML, HTML & Word integrator › Stage XML orders in an HSQL database and export them as XML
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Publish a SQL table as a JSON web service
and 2 more in the Use cases
Often used with
sql connect · sql disconnect · cm get · sql encode · execute · sql dml · cm remove · file delete
sql connect · sql disconnect · cm get · sql encode · execute · sql dml · cm remove · file delete
sql to xml <sqlId> <tableName> <selectQuery>
Description
Get data from the database as XML
Get data from the database as XML
Parameters
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
admin
sql to xml "session1" "products" (concat "select * from products")
mentdb
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <table> <table>products</table> <columns> <item>id</item> <item>name</item> <item>quantity</item> <item>cat</item> <item>desc</item> <item>dtcreate</item> <item>type</item> <item>subtype</item> <item>price</item> <item>sale</item> <item>weight</item> </columns> <data> <item> <id>1</id> <name>car</name> <quantity>5</quantity> <cat nil="true"/> <desc>a car ....</desc> <dtcreate>2018-02-15 10:00:00.0</dtcreate> <type>A</type> <subtype>T</subtype> <price>7.50</price> <sale>1</sale> <weight>23.4567</weight> </item> <item> <id>2</id> <name>pen</name> <quantity>2</quantity> <cat/> <desc>a pen ....</desc> <dtcreate>2018-02-16 12:00:00.0</dtcreate> <type>A</type> <subtype>R</subtype> <price>9.80</price> <sale>0</sale> <weight>29.987</weight> </item> <item> <id>3</id> <name>yen</name> <quantity>200</quantity> <cat>money</cat> <desc>a yen ....</desc> <dtcreate>2018-02-17 13:00:00.0</dtcreate> <type>Z</type> <subtype>1</subtype> <price>14.70</price> <sale>0</sale> <weight>89.987</weight> </item> <item> <id>4</id> <name>bob</name> <quantity>1</quantity> <cat>human</cat> <desc>a human ....</desc> <dtcreate>2018-02-18 13:00:00.0</dtcreate> <type>Z</type> <subtype>1</subtype> <price>14.70</price> <sale>0</sale> <weight>99.098</weight> </item> <item> <id>5</id> <name>spider</name> <quantity>19</quantity> <cat>animal</cat> <desc>an animal ....</desc> <dtcreate>2018-02-19 15:00:00.0</dtcreate> <type>E</type> <subtype>T</subtype> <price>19.40</price> <sale>1</sale> <weight>123.08</weight> </item> </data> </table>
Used in use cases (4 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › CSV, XML and HTML for three partners
XML, HTML & Word integrator › Stage XML orders in an HSQL database and export them as XML
API & database integrator › Transactional price import, then reports in every format
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › CSV, XML and HTML for three partners
XML, HTML & Word integrator › Stage XML orders in an HSQL database and export them as XML
API & database integrator › Transactional price import, then reports in every format
Often used with
cm get · sql connect · sql disconnect · execute · file delete · sql dml · sql to html · sql to json
cm get · sql connect · sql disconnect · execute · file delete · sql dml · sql to html · sql to json
sql to html <sqlId> <tableName> <selectQuery>
Description
Get data from the database as HTML
Get data from the database as HTML
Parameters
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
admin
sql to html "session1" "products" (concat "select * from products")
mentdb
<html><head> <style> table, td, th { border: 1px solid black; padding: 8px; } #table1 { border-collapse: collapse; } </style> </head><body><h3>Table: <b>products</b></h3><br/> <table id='table1'><tr><th>id</th><th>name</th><th>quantity</th><th>cat</th><th>desc</th><th>dtcreate</th><th>type</th><th>subtype</th><th>price</th><th>sale</th><th>weight</th></tr><tr><td>1</td><td>car</td><td>5</td><td style='color:#FF0000'>[NULL]</td><td>a car ....</td><td>2018-02-15 10:00:00.0</td><td>A</td><td>T</td><td>7.50</td><td>1</td><td>23.4567</td></tr><tr><td>2</td><td>pen</td><td>2</td><td></td><td>a pen ....</td><td>2018-02-16 12:00:00.0</td><td>A</td><td>R</td><td>9.80</td><td>0</td><td>29.987</td></tr><tr><td>3</td><td>yen</td><td>200</td><td>money</td><td>a yen ....</td><td>2018-02-17 13:00:00.0</td><td>Z</td><td>1</td><td>14.70</td><td>0</td><td>89.987</td></tr><tr><td>4</td><td>bob</td><td>1</td><td>human</td><td>a human ....</td><td>2018-02-18 13:00:00.0</td><td>Z</td><td>1</td><td>14.70</td><td>0</td><td>99.098</td></tr><tr><td>5</td><td>spider</td><td>19</td><td>animal</td><td>an animal ....</td><td>2018-02-19 15:00:00.0</td><td>E</td><td>T</td><td>19.40</td><td>1</td><td>123.08</td></tr></table></body></html>
Used in use cases (4 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › CSV, XML and HTML for three partners
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Mail a PDF report built from live data
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › CSV, XML and HTML for three partners
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Mail a PDF report built from live data
Often used with
cm get · sql connect · sql disconnect · sql to xml · csv parse · execute · file delete · json doc
cm get · sql connect · sql disconnect · sql to xml · csv parse · execute · file delete · json doc
sql to excel <sqlId> <tableName> <selectQuery> <filePath>
Description
Get data from the database as an Excel document
Get data from the database as an Excel document
Parameters
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
filePath: The Excel file path - string - required
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
filePath: The Excel file path - string - required
admin
sql to excel "session1" "products" (concat "select * from products") "/Users/jimmitry/Desktop/test.xls";
mentdb
1
Used in use cases (2 recipes)
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
Often used with
cm get · sql connect · sql disconnect · sql set_timeout · sql to csv_file · sql to excelx · sql to pdf · csv parse
cm get · sql connect · sql disconnect · sql set_timeout · sql to csv_file · sql to excelx · sql to pdf · csv parse
sql to excelx <sqlId> <tableName> <selectQuery> <filePath>
Description
Get data from the database as an ExcelX document
Get data from the database as an ExcelX document
Parameters
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
filePath: The Excel file path - string - required
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
filePath: The Excel file path - string - required
admin
sql to excelx "session1" "products" (concat "select * from products") "/Users/jimmitry/Desktop/test.xlsx";
mentdb
1
Used in use cases (4 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly export pack: CSV, Excel and PDF in an outbox connection
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly export pack: CSV, Excel and PDF in an outbox connection
Often used with
cm get · sql connect · sql disconnect · sql to csv_file · sql to pdf · execute · file count_lines · file delete
cm get · sql connect · sql disconnect · sql to csv_file · sql to pdf · execute · file count_lines · file delete
sql to pdf <sqlId> <tableName> <selectQuery> <filePath>
Description
Get data from the database as a PDF document
Get data from the database as a PDF document
Parameters
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
filePath: The Excel file path - string - required
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
filePath: The Excel file path - string - required
admin
sql to pdf "session1" "products" (concat "select * from products") "/Users/jimmitry/Desktop/test.pdf";
mentdb
1
Used in use cases (4 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly export pack: CSV, Excel and PDF in an outbox connection
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly export pack: CSV, Excel and PDF in an outbox connection
Often used with
cm get · sql connect · sql disconnect · sql to csv_file · sql to excelx · execute · file count_lines · file delete
cm get · sql connect · sql disconnect · sql to csv_file · sql to excelx · execute · file count_lines · file delete
sql to csv <sqlId> <tableName> <selectQuery> <columnSeparator> <quoteChar>
Description
Get data from the database as CSV
Get data from the database as CSV
Parameters
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
columnSeparator: The column separator - String - required
quoteChar: The quote character - String - required
sqlId: The SQL id - string - required
tableName: The table name - string - required
selectQuery: The SELECT query - string - required
columnSeparator: The column separator - String - required
quoteChar: The quote character - String - required
admin
sql to csv "session1" "products" (concat "select * from products") "," "'"
mentdb
id,name,quantity,cat,desc,dtcreate,type,subtype,price,sale,weight 1,car,5,null,a car ....,2018-02-15 10:00:00.0,A,T,7.50,1,23.4567 2,pen,2,,a pen ....,2018-02-16 12:00:00.0,A,R,9.80,0,29.987 3,yen,200,money,a yen ....,2018-02-17 13:00:00.0,Z,1,14.70,0,89.987 4,bob,1,human,a human ....,2018-02-18 13:00:00.0,Z,1,14.70,0,99.098 5,spider,19,animal,an animal ....,2018-02-19 15:00:00.0,E,T,19.40,1,123.08
Used in use cases (5 recipes)
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › CSV, XML and HTML for three partners
Excel reporting analyst › Sales ledger workbook queried with SQL
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Incremental sync between two databases with a watermark
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › CSV, XML and HTML for three partners
Excel reporting analyst › Sales ledger workbook queried with SQL
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Incremental sync between two databases with a watermark
Often used with
cm get · sql connect · sql disconnect · execute · file delete · sql dml · sql encode · cm remove
cm get · sql connect · sql disconnect · execute · file delete · sql dml · sql encode · cm remove
sql to csv_file <sqlId> <filePath> <selectQuery> <columnSeparator> <quoteChar>
Description
Get data from the database as CSV
Get data from the database as CSV
Parameters
sqlId: The SQL id - string - required
filePath: The file path - string - required
selectQuery: The SELECT query - string - required
columnSeparator: The column separator - String - required
quoteChar: The quote character - String - required
sqlId: The SQL id - string - required
filePath: The file path - string - required
selectQuery: The SELECT query - string - required
columnSeparator: The column separator - String - required
quoteChar: The quote character - String - required
admin
sql to csv_file "session1" "/Users/jimmitry/Desktop/data.csv" (concat "select * from products") "," "'";
mentdb
1
Used in use cases (4 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly export pack: CSV, Excel and PDF in an outbox connection
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › Export a query to CSV, Excel and PDF files
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Nightly export pack: CSV, Excel and PDF in an outbox connection
Often used with
cm get · sql connect · sql disconnect · sql to excelx · sql to pdf · execute · file count_lines · file delete
cm get · sql connect · sql disconnect · sql to excelx · sql to pdf · execute · file count_lines · file delete
sql dml <sqlId> <dmlQuery>
Description
Execute a DML request
Execute a DML request
Parameters
sqlId: The SQL id - string - required
dmlQuery: The DML query - string - required
sqlId: The SQL id - string - required
dmlQuery: The DML query - string - required
admin
sql dml "session1" (concat "insert into products (id, name, quantity) values (4, 'other', '4567');");
mentdb
1
Used in use cases (25 recipes)
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Create a table and load rows safely
CRUD & data API developer › A write API with business rules in one transaction
Industrial quality engineer › Gauge linearity and bias study from the lab database
Statistician & biostatistician › Mincer wage equation from a survey table in SQL
Data quality analyst › Outlier readings in a sensor table by density clustering
and 19 more in the Use cases
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Create a table and load rows safely
CRUD & data API developer › A write API with business rules in one transaction
Industrial quality engineer › Gauge linearity and bias study from the lab database
Statistician & biostatistician › Mincer wage equation from a survey table in SQL
Data quality analyst › Outlier readings in a sensor table by density clustering
and 19 more in the Use cases
Often used with
sql connect · sql disconnect · cm get · execute · file delete · cm remove · sql value · sql encode
sql connect · sql disconnect · cm get · execute · file delete · cm remove · sql value · sql encode
sql parse <sqlId> <namespace> <selectQuery> <mqlAction>
Description
Parse data
Parse data
Parameters
sqlId: The SQL id - string - required
namespace: The namespace - string - required
selectQuery: The SELECT query - string - required
mqlAction: The MQL action to execute on each line - string - required
sqlId: The SQL id - string - required
namespace: The namespace - string - required
selectQuery: The SELECT query - string - required
mqlAction: The MQL action to execute on each line - string - required
admin
sql parse "session1" "T" (concat "select name from products") { log trace [T_name]; };
mentdb
Used in use cases (6 recipes)
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Run MQL on each row with sql parse
Excel reporting analyst › Sales ledger workbook queried with SQL
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Incremental sync between two databases with a watermark
Integrator & ETL developer › Nightly copy of an Oracle table into MySQL, committed by batches
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Run MQL on each row with sql parse
Excel reporting analyst › Sales ledger workbook queried with SQL
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Incremental sync between two databases with a watermark
Integrator & ETL developer › Nightly copy of an Oracle table into MySQL, committed by batches
Often used with
cm get · sql connect · sql disconnect · execute · sql dml · sql encode · cm remove · file delete
cm get · sql connect · sql disconnect · execute · sql dml · sql encode · cm remove · file delete
sql commit <sqlId>
Description
Commit a connection
Commit a connection
Parameters
sqlId: The SQL id - string - required
sqlId: The SQL id - string - required
admin
sql commit "session1";
mentdb
1
Used in use cases (6 recipes)
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Transactions: commit, rollback, all or nothing
CRUD & data API developer › A write API with business rules in one transaction
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Import a CSV from SFTP into a database, every morning
Integrator & ETL developer › Nightly copy of an Oracle table into MySQL, committed by batches
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Transactions: commit, rollback, all or nothing
CRUD & data API developer › A write API with business rules in one transaction
API & database integrator › Transactional price import, then reports in every format
Integrator & ETL developer › Import a CSV from SFTP into a database, every morning
Integrator & ETL developer › Nightly copy of an Oracle table into MySQL, committed by batches
Often used with
cm get · sql connect · sql disconnect · sql dml · sql auto_commit · sql encode · execute · sql rollback
cm get · sql connect · sql disconnect · sql dml · sql auto_commit · sql encode · execute · sql rollback
sql rollback <sqlId>
Description
Rollback a connection
Rollback a connection
Parameters
sqlId: The SQL id - string - required
sqlId: The SQL id - string - required
admin
sql rollback "session1";
mentdb
1
Used in use cases (4 recipes)
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Transactions: commit, rollback, all or nothing
CRUD & data API developer › A write API with business rules in one transaction
API & database integrator › Transactional price import, then reports in every format
Configuration & connections › Transactions: auto_commit off, rollback, commit, parse
SQL developer in MQL › Transactions: commit, rollback, all or nothing
CRUD & data API developer › A write API with business rules in one transaction
API & database integrator › Transactional price import, then reports in every format
Often used with
cm get · sql auto_commit · sql commit · sql connect · sql disconnect · sql dml · sql value · execute
cm get · sql auto_commit · sql commit · sql connect · sql disconnect · sql dml · sql value · execute
sql disconnect <sqlId>
Description
Disconnect from a database
Disconnect from a database
Parameters
sqlId: The SQL id - string - required
sqlId: The SQL id - string - required
admin
sql disconnect "session1";
mentdb
1
Used in use cases (39 recipes)
Script developer › Know which scripts a change will affect before touching a library script
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Register a local H2 database connection
CRUD & data API developer › Install the generated update and delete on the key
Industrial quality engineer › Gauge linearity and bias study from the lab database
Statistician & biostatistician › Mincer wage equation from a survey table in SQL
and 33 more in the Use cases
Script developer › Know which scripts a change will affect before touching a library script
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Register a local H2 database connection
CRUD & data API developer › Install the generated update and delete on the key
Industrial quality engineer › Gauge linearity and bias study from the lab database
Statistician & biostatistician › Mincer wage equation from a survey table in SQL
and 33 more in the Use cases
Often used with
sql connect · cm get · sql dml · execute · sql value · file delete · sql encode · cm remove
sql connect · cm get · sql dml · execute · sql value · file delete · sql encode · cm remove
sql disconnect all
Description
Disconnect all connections
Disconnect all connections
admin
sql disconnect all;
mentdb
1
sql encode <data>
Description
Encode a valid value
Encode a valid value
Parameters
data: The data - string - required
data: The data - string - required
admin
sql encode "data"
mentdb
'data'
admin
sql encode null
mentdb
null
Used in use cases (17 recipes)
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Create a table and load rows safely
CRUD & data API developer › A read API with typed parameters
Excel reporting analyst › Sales ledger workbook queried with SQL
XML, HTML & Word integrator › Stage XML orders in an HSQL database and export them as XML
API & database integrator › Transactional price import, then reports in every format
and 11 more in the Use cases
Configuration & connections › A local H2 database through a connection: create, insert, query, export
SQL developer in MQL › Create a table and load rows safely
CRUD & data API developer › A read API with typed parameters
Excel reporting analyst › Sales ledger workbook queried with SQL
XML, HTML & Word integrator › Stage XML orders in an HSQL database and export them as XML
API & database integrator › Transactional price import, then reports in every format
and 11 more in the Use cases
Often used with
sql connect · sql disconnect · cm get · sql dml · sql value · execute · sql to json · cm remove
sql connect · sql disconnect · cm get · sql dml · sql value · execute · sql to json · cm remove
sql encode_std <data>
Description
Encode a valid value in standard SQL (PostgreSQL, Oracle, SQL Server, H2, Derby, DB2...): only the quotes are doubled, a backslash stays as it is. 'sql encode' also doubles the backslashes, which is only right for MySQL.
Encode a valid value in standard SQL (PostgreSQL, Oracle, SQL Server, H2, Derby, DB2...): only the quotes are doubled, a backslash stays as it is. 'sql encode' also doubles the backslashes, which is only right for MySQL.
Parameters
data: The data - string - required
data: The data - string - required
admin
sql encode_std "C:\tmp\l'eau"
mentdb
'C:\tmp\l''eau'
admin
sql encode_std null
mentdb
null
Used in use cases (3 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › sql encode or sql encode_std: the backslash trap
API & database integrator › Transactional price import, then reports in every format
Configuration & connections › Export a query in every format and stream big results by blocks
SQL developer in MQL › sql encode or sql encode_std: the backslash trap
API & database integrator › Transactional price import, then reports in every format
Often used with
cm get · sql connect · sql disconnect · sql dml · execute · sql block close · sql block open · sql block to_json
cm get · sql connect · sql disconnect · sql dml · execute · sql block close · sql block open · sql block to_json
sql show tables <cmId>
Description
Show the tables of a database
Show the tables of a database
Parameters
cmId: The connection id - string - required
cmId: The connection id - string - required
admin
sql show tables "demo_cm_mysql";
mentdb
In editor ...
Used in use cases (2 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
CRUD & data API developer › Inspect the table before writing the API
Configuration & connections › Export a query in every format and stream big results by blocks
CRUD & data API developer › Inspect the table before writing the API
Often used with
sql show desc · string regexp_replace · atom get · cm get · cm remove · execute · file count_lines · file delete
sql show desc · string regexp_replace · atom get · cm get · cm remove · execute · file count_lines · file delete
sql select <cmId> <query> <sizeCols> <title>
Description
Show the data of a table
Show the data of a table
Parameters
cmId: The connection id - string - required
query: The select query - string - required
sizeCols: The column sizes separated by commas (,) - string - not required
title: The editor title - string - required
cmId: The connection id - string - required
query: The select query - string - required
sizeCols: The column sizes separated by commas (,) - string - not required
title: The editor title - string - required
admin
sql select "demo_cm_mysql" "select * from table limit 0, 100" "table";
mentdb
In editor ...
Often used with
atom get · scrud create · sql show data · sql show desc · sql show tables · string instr · string regexp_replace · string substr
atom get · scrud create · sql show data · sql show desc · sql show tables · string instr · string regexp_replace · string substr
sql show data <cmId> <query> <title>
Description
Show the data of a table
Show the data of a table
Parameters
cmId: The connection id - string - required
query: The select query - string - required
title: The editor title - string - required
cmId: The connection id - string - required
query: The select query - string - required
title: The editor title - string - required
admin
sql show data "demo_cm_mysql" "select * from products limit 0, 100" "products";
mentdb
In editor ...
Often used with
atom get · scrud create · sql select · sql show desc · sql show tables · string instr · string regexp_replace · string substr
atom get · scrud create · sql select · sql show desc · sql show tables · string instr · string regexp_replace · string substr
sql show desc <cmId> <tablename>
Description
Show the description of a table
Show the description of a table
Parameters
cmId: The connection id - string - required
tablename: The table name - string - required
cmId: The connection id - string - required
tablename: The table name - string - required
admin
sql show desc "demo_cm_mysql" "products";
mentdb
In editor ...
Used in use cases (2 recipes)
Configuration & connections › Export a query in every format and stream big results by blocks
CRUD & data API developer › Inspect the table before writing the API
Configuration & connections › Export a query in every format and stream big results by blocks
CRUD & data API developer › Inspect the table before writing the API
Often used with
sql show tables · string regexp_replace · atom get · cm get · cm remove · execute · file count_lines · file delete
sql show tables · string regexp_replace · atom get · cm get · cm remove · execute · file count_lines · file delete
sql show activity <groupType> <dtMin> <dtMax>
Description
Show the activity of scripts
Show the activity of scripts
Parameters
groupType: The group type (SEC|MIN|HOUR|DAY|MONTH|YEAR) - string - required
dtMin: The min date - string - required
dtMax: The max date - string - required
groupType: The group type (SEC|MIN|HOUR|DAY|MONTH|YEAR) - string - required
dtMin: The min date - string - required
dtMax: The max date - string - required
admin
sql show activity DAY (date datedifft (concat (date sysdate) " 00:00:00") "DAY" 100) (concat (date sysdate) " 23:59:59")
mentdb
In editor ...
Often used with
cm exist · cm remove · date sysdate · file delete · file exist · file size · sql disconnect · sql show
cm exist · cm remove · date sysdate · file delete · file exist · file size · sql disconnect · sql show
sql bulk_insert <sqlId> <table> <json_rows>
Description
Insert JSON rows into a table with JDBC batches. The keys of the rows are matched to the columns without case, the values are converted by column type. Result: rows, inserted, rejected, errors (row index from 0 and message, first 100), batches, seconds. In auto-commit mode each batch is committed; in a transaction (sql auto_commit 0) nothing is committed: call sql commit or sql rollback. By default the first error stops the load with the row number and the number of rows committed before
Insert JSON rows into a table with JDBC batches. The keys of the rows are matched to the columns without case, the values are converted by column type. Result: rows, inserted, rejected, errors (row index from 0 and message, first 100), batches, seconds. In auto-commit mode each batch is committed; in a transaction (sql auto_commit 0) nothing is committed: call sql commit or sql rollback. By default the first error stops the load with the row number and the number of rows committed before
Parameters
sqlId: The SQL id of an open connection (sql connect) - String - required
table: The table (letters, digits, _ $ #, optional schema.) - String - required
json_rows: The rows: JSON array of objects - String - required
sqlId: The SQL id of an open connection (sql connect) - String - required
table: The table (letters, digits, _ $ #, optional schema.) - String - required
json_rows: The rows: JSON array of objects - String - required
admin
sql connect "tuto_bulk" "{\"driver\": \"org.h2.Driver\", \"jdbc\": \"jdbc:h2:mem:tuto_bulk\", \"loginTimeout\": \"10\", \"properties\": {\"user\": \"sa\", \"password\": \"\"}}"; sql dml "tuto_bulk" "CREATE TABLE customer (id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(50), balance DECIMAL(10,2), since DATE)"; -> "[rows]" "[{\"id\": 1, \"name\": \"Dupont\", \"city\": \"Paris\", \"balance\": 120.5, \"since\": \"2024-03-12\"}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Denis\", \"balance\": \"\", \"since\": \"2025-01-07\"}, {\"id\": 3, \"name\": \"Payet\", \"city\": \"Saint-Pierre\", \"balance\": 87, \"since\": \"2025-06-30T09:15:00\"}]"; -> "[r]" (sql bulk_insert "tuto_bulk" "customer" [rows]); -> "[n]" (sql value "tuto_bulk" "SELECT COUNT(*) FROM customer WHERE balance IS NOT NULL"); sql disconnect "tuto_bulk"; concat [r] " / rows with a balance: " [n]
mentdb
{"rows": 3, "inserted": 3, "rejected": 0, "errors": [], "batches": 1, "seconds": 0} / rows with a balance: 2
sql bulk_insert <sqlId> <table> <json_rows> <json_params>
Description
Insert with parameters: batch_size (500, 1 to 100000), on_error (stop or skip: the failed batch is replayed row by row with savepoints, the bad rows are rejected and listed), max_errors (1000: then the load stops), columns (the JSON keys to load, in this order), ignore_unknown (false: a key that is not a column is an error), quote_names (false: true quotes the table and column names with the quote of the database)
Insert with parameters: batch_size (500, 1 to 100000), on_error (stop or skip: the failed batch is replayed row by row with savepoints, the bad rows are rejected and listed), max_errors (1000: then the load stops), columns (the JSON keys to load, in this order), ignore_unknown (false: a key that is not a column is an error), quote_names (false: true quotes the table and column names with the quote of the database)
Parameters
sqlId: The SQL id - String - required
table: The table - String - required
json_rows: The rows - String - required
json_params: The parameters - String - required
sqlId: The SQL id - String - required
table: The table - String - required
json_rows: The rows - String - required
json_params: The parameters - String - required
admin
sql connect "tuto_bulk" "{\"driver\": \"org.h2.Driver\", \"jdbc\": \"jdbc:h2:mem:tuto_bulk\", \"loginTimeout\": \"10\", \"properties\": {\"user\": \"sa\", \"password\": \"\"}}"; sql dml "tuto_bulk" "CREATE TABLE customer (id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(50), balance DECIMAL(10,2), since DATE)"; sql bulk_insert "tuto_bulk" "customer" "[{\"id\": 1, \"name\": \"Dupont\", \"city\": \"Paris\", \"balance\": 120.5, \"since\": \"2024-03-12\"}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Denis\", \"balance\": \"\", \"since\": \"2025-01-07\"}, {\"id\": 3, \"name\": \"Payet\", \"city\": \"Saint-Pierre\", \"balance\": 87, \"since\": \"2025-06-30T09:15:00\"}]"; -> "[r]" (sql bulk_insert "tuto_bulk" "customer" "[{\"id\": 3, \"name\": \"Payet\", \"city\": \"Le Tampon\", \"balance\": 95, \"since\": \"2025-06-30\"}, {\"id\": 4, \"name\": \"Grondin\", \"city\": \"Saint-Paul\", \"balance\": 12.3, \"since\": \"2026-10-02\"}, {\"id\": 5, \"name\": \"T\u00e9cher\", \"city\": \"Saint-Louis\", \"balance\": \"abc\", \"since\": \"2026-10-05\"}]" "{\"on_error\": \"skip\", \"batch_size\": 1000}"); sql disconnect "tuto_bulk"; [r]
mentdb
{"rows": 3, "inserted": 1, "rejected": 2, "errors": [{"row": 0, "error": "Violation d'index unique ou clé primaire: \"PRIMARY KEY ON PUBLIC.CUSTOMER(ID) ( /* key:3 */ 3, 'Payet', 'Saint-Pierre', 87.00, DATE '2025-06-30')\"\ Unique index or primary key violation: \"PRIMARY KEY ON PUBLIC.CUSTOMER(ID) ( /* key:3 */ 3, 'Payet', 'Saint-Pierre', 87.00, DATE '2025-06-30')\"; SQL statement:\ INSERT INTO customer (ID, NAME, CITY, BALANCE, SINCE) VALUES (?, ?, ?, ?, ?) [23505-214]"}, {"row": 2, "error": "column BALANCE: 'abc' is not a number"}], "batches": 1, "seconds": 0}
Used in use cases (2 recipes)
CRUD & data API developer › Fill a demo database for the screens of an application: customers and their orders
Integrator & ETL developer › Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
CRUD & data API developer › Fill a demo database for the screens of an application: customers and their orders
Integrator & ETL developer › Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
Often used with
sql connect · sql create_table · sql disconnect · sql value · fake rows · sql bulk_upsert · sql dml · sql encode
sql connect · sql create_table · sql disconnect · sql value · fake rows · sql bulk_upsert · sql dml · sql encode
sql bulk_upsert <sqlId> <table> <json_rows> <json_keys>
Description
Insert or update JSON rows by key, portable on every database: a batch of UPDATE ... WHERE keys, then a batch of INSERT for the rows that updated nothing. A key repeated in the rows is applied in order (the last row wins), keys compared as the database does (3 = 03 in a numeric column, no case in a text column). A NULL key is a row error. Result: rows, inserted, updated, rejected, errors, batches, seconds
Insert or update JSON rows by key, portable on every database: a batch of UPDATE ... WHERE keys, then a batch of INSERT for the rows that updated nothing. A key repeated in the rows is applied in order (the last row wins), keys compared as the database does (3 = 03 in a numeric column, no case in a text column). A NULL key is a row error. Result: rows, inserted, updated, rejected, errors, batches, seconds
Parameters
sqlId: The SQL id - String - required
table: The table - String - required
json_rows: The rows - String - required
json_keys: The key columns: JSON array, ex: ["id"] - String - required
sqlId: The SQL id - String - required
table: The table - String - required
json_rows: The rows - String - required
json_keys: The key columns: JSON array, ex: ["id"] - String - required
admin
sql connect "tuto_bulk" "{\"driver\": \"org.h2.Driver\", \"jdbc\": \"jdbc:h2:mem:tuto_bulk\", \"loginTimeout\": \"10\", \"properties\": {\"user\": \"sa\", \"password\": \"\"}}"; sql dml "tuto_bulk" "CREATE TABLE customer (id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(50), balance DECIMAL(10,2), since DATE)"; sql bulk_insert "tuto_bulk" "customer" "[{\"id\": 1, \"name\": \"Dupont\", \"city\": \"Paris\", \"balance\": 120.5, \"since\": \"2024-03-12\"}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Denis\", \"balance\": \"\", \"since\": \"2025-01-07\"}, {\"id\": 3, \"name\": \"Payet\", \"city\": \"Saint-Pierre\", \"balance\": 87, \"since\": \"2025-06-30T09:15:00\"}]"; -> "[r]" (sql bulk_upsert "tuto_bulk" "customer" "[{\"id\": 3, \"name\": \"Payet\", \"city\": \"Le Tampon\", \"balance\": 95, \"since\": \"2025-06-30\"}, {\"id\": 4, \"name\": \"Grondin\", \"city\": \"Saint-Paul\", \"balance\": 12.3, \"since\": \"2026-10-02\"}, {\"id\": 5, \"name\": \"T\u00e9cher\", \"city\": \"Saint-Louis\", \"balance\": \"abc\", \"since\": \"2026-10-05\"}]" "[\"id\"]" "{\"on_error\": \"skip\"}"); -> "[city]" (sql value "tuto_bulk" "SELECT city FROM customer WHERE id = 3"); sql disconnect "tuto_bulk"; concat [r] " / city of customer 3: " [city]
mentdb
{"rows": 3, "inserted": 1, "updated": 1, "rejected": 1, "errors": [{"row": 2, "error": "column BALANCE: 'abc' is not a number"}], "batches": 1, "seconds": 0} / city of customer 3: Le Tampon
sql bulk_upsert <sqlId> <table> <json_rows> <json_keys> <json_params>
Description
Upsert with parameters: the same as sql bulk_insert (batch_size, on_error, max_errors, columns, ignore_unknown, quote_names)
Upsert with parameters: the same as sql bulk_insert (batch_size, on_error, max_errors, columns, ignore_unknown, quote_names)
Parameters
sqlId: The SQL id - String - required
table: The table - String - required
json_rows: The rows - String - required
json_keys: The key columns - String - required
json_params: The parameters - String - required
sqlId: The SQL id - String - required
table: The table - String - required
json_rows: The rows - String - required
json_keys: The key columns - String - required
json_params: The parameters - String - required
admin
sql connect "tuto_bulk" "{\"driver\": \"org.h2.Driver\", \"jdbc\": \"jdbc:h2:mem:tuto_bulk\", \"loginTimeout\": \"10\", \"properties\": {\"user\": \"sa\", \"password\": \"\"}}"; sql dml "tuto_bulk" "CREATE TABLE customer (id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(50), balance DECIMAL(10,2), since DATE)"; sql auto_commit "tuto_bulk" 0; -> "[r]" (sql bulk_upsert "tuto_bulk" "customer" "[{\"id\": 1, \"name\": \"Dupont\", \"city\": \"Paris\", \"balance\": 120.5, \"since\": \"2024-03-12\"}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Denis\", \"balance\": \"\", \"since\": \"2025-01-07\"}, {\"id\": 3, \"name\": \"Payet\", \"city\": \"Saint-Pierre\", \"balance\": 87, \"since\": \"2025-06-30T09:15:00\"}]" "[\"id\"]" "{\"batch_size\": 100}"); -> "[before]" (sql value "tuto_bulk" "SELECT COUNT(*) FROM customer"); sql rollback "tuto_bulk"; -> "[after]" (sql value "tuto_bulk" "SELECT COUNT(*) FROM customer"); sql disconnect "tuto_bulk"; concat "in the transaction: " [before] " rows, after the rollback: " [after]
mentdb
in the transaction: 3 rows, after the rollback: 0
Used in use cases (2 recipes)
Integrator & ETL developer › Load a partner CSV file into a table: detection, typing, upsert and rejected rows
Integrator & ETL developer › Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
Integrator & ETL developer › Load a partner CSV file into a table: detection, typing, upsert and rejected rows
Integrator & ETL developer › Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
Often used with
sql connect · sql disconnect · sql dml · sql value · csv read · csv sniff · csv write · file delete
sql connect · sql disconnect · sql dml · sql value · csv read · csv sniff · csv write · file delete
sql create_table <sqlId> <table> <json_rows>
Description
Create a table from JSON rows: the type of each column is inferred from its values (INTEGER or BIGINT, DECIMAL(p,s), BOOLEAN, DATE, TIMESTAMP, VARCHAR(n) with a margin of 20 %, CLOB / TEXT beyond 4000 characters; codes with leading zeros stay texts) and written for the database of the connection. If the table exists nothing is done (created false) The table is looked for in the current schema of the connection (or the schema given as schema.table): a table of the same name in another schema does not count. Limits per database: DECIMAL of MySQL 65 digits and 30 decimals, DB2 VARCHAR sized in bytes (UTF-8), Oracle VARCHAR2 limited to 4000 bytes (CLOB beyond), MySQL row of 65535 bytes (the largest VARCHAR become TEXT, with a warning). A reserved word as table name or a # in a name needs quote_names.
Create a table from JSON rows: the type of each column is inferred from its values (INTEGER or BIGINT, DECIMAL(p,s), BOOLEAN, DATE, TIMESTAMP, VARCHAR(n) with a margin of 20 %, CLOB / TEXT beyond 4000 characters; codes with leading zeros stay texts) and written for the database of the connection. If the table exists nothing is done (created false) The table is looked for in the current schema of the connection (or the schema given as schema.table): a table of the same name in another schema does not count. Limits per database: DECIMAL of MySQL 65 digits and 30 decimals, DB2 VARCHAR sized in bytes (UTF-8), Oracle VARCHAR2 limited to 4000 bytes (CLOB beyond), MySQL row of 65535 bytes (the largest VARCHAR become TEXT, with a warning). A reserved word as table name or a # in a name needs quote_names.
Parameters
sqlId: The SQL connection id (sql connect) - String - required
table: The table (letters, digits, _ $ #, optional schema.) - String - required
json_rows: The rows (array of objects) - String - required
sqlId: The SQL connection id (sql connect) - String - required
table: The table (letters, digits, _ $ #, optional schema.) - String - required
json_rows: The rows (array of objects) - String - required
admin
sql connect "tuto_ddl" "{\"driver\": \"org.h2.Driver\", \"jdbc\": \"jdbc:h2:mem:tuto_ddl\", \"loginTimeout\": \"10\", \"properties\": {\"user\": \"sa\", \"password\": \"\"}}"; -> "[rows]" "[{\"id\": 1, \"code\": \"00042\", \"name\": \"Dupont SA\", \"balance\": 1250.5, \"since\": \"2024-03-12\", \"updated\": \"2026-11-03T10:15:00\", \"vip\": true}, {\"id\": 2, \"code\": \"00043\", \"name\": \"Martin\", \"balance\": -80, \"since\": \"2025-01-07\", \"updated\": \"2026-11-02T08:00:00\", \"vip\": false}]"; json load "tuto_c" (sql create_table "tuto_ddl" "customer" [rows] "{\"primary_key\": [\"id\"]}"); sql bulk_insert "tuto_ddl" "customer" [rows]; -> "[res]" (concat "created " (json select "tuto_c" "/created") ", balance " (json select "tuto_c" "/columns/[3]/type") ", rows " (sql value "tuto_ddl" "SELECT COUNT(*) FROM customer")); json unload "tuto_c"; sql disconnect "tuto_ddl"; [res]
mentdb
created true, balance DECIMAL(5,1), rows 2
sql create_table <sqlId> <table> <json_rows> <json_params>
Description
Create a table with parameters: primary_key ([columns]), not_null (true: NOT NULL for the columns without empty value), if_not_exists (true; false = error if the table exists), dry_run (true: only the DDL; with the sqlId "" give the dialect: h2, mysql, postgresql, oracle, sqlserver, derby, hsql, db2, firebird), decimal ("exact" or "double"), varchar_factor (1.2), quote_names (false). A column named with a reserved word (order, user, month for H2...) gives a warning if_not_exists checks then creates in two steps: two sessions creating the same table at the same time can still meet an error from the database.
Create a table with parameters: primary_key ([columns]), not_null (true: NOT NULL for the columns without empty value), if_not_exists (true; false = error if the table exists), dry_run (true: only the DDL; with the sqlId "" give the dialect: h2, mysql, postgresql, oracle, sqlserver, derby, hsql, db2, firebird), decimal ("exact" or "double"), varchar_factor (1.2), quote_names (false). A column named with a reserved word (order, user, month for H2...) gives a warning if_not_exists checks then creates in two steps: two sessions creating the same table at the same time can still meet an error from the database.
Parameters
sqlId: The SQL connection id, or "" with dry_run - String - required
table: The table - String - required
json_rows: The rows - String - required
json_params: The parameters - String - required
sqlId: The SQL connection id, or "" with dry_run - String - required
table: The table - String - required
json_rows: The rows - String - required
json_params: The parameters - String - required
admin
sql create_table "" "customer" "[{\"id\": 1, \"code\": \"00042\", \"name\": \"Dupont SA\", \"balance\": 1250.5, \"since\": \"2024-03-12\", \"updated\": \"2026-11-03T10:15:00\", \"vip\": true}, {\"id\": 2, \"code\": \"00043\", \"name\": \"Martin\", \"balance\": -80, \"since\": \"2025-01-07\", \"updated\": \"2026-11-02T08:00:00\", \"vip\": false}]" "{\"dry_run\": true, \"dialect\": \"sqlserver\", \"primary_key\": [\"id\"]}"
mentdb
{"dialect": "sqlserver", "ddl": "CREATE TABLE customer (\ id INT NOT NULL,\ code NVARCHAR(10) NOT NULL,\ name NVARCHAR(20) NOT NULL,\ balance DECIMAL(5,1) NOT NULL,\ since DATE NOT NULL,\ updated DATETIME2 NOT NULL,\ vip BIT NOT NULL,\ PRIMARY KEY (id)\ )", "created": false, "exists": false, "columns": [{"name": "id", "type": "INT", "nullable": false, "max_length": 1}, {"name": "code", "type": "NVARCHAR(10)", "nullable": false, "max_length": 5}, {"name": "name", "type": "NVARCHAR(20)", "nullable": false, "max_length": 9}, {"name": "balance", "type": "DECIMAL(5,1)", "nullable": false, "max_length": 6}, {"name": "since", "type": "DATE", "nullable": false, "max_length": 10}, {"name": "updated", "type": "DATETIME2", "nullable": false, "max_length": 19}, {"name": "vip", "type": "BIT", "nullable": false, "max_length": 5}]}
Used in use cases (2 recipes)
CRUD & data API developer › Fill a demo database for the screens of an application: customers and their orders
Integrator & ETL developer › Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
CRUD & data API developer › Fill a demo database for the screens of an application: customers and their orders
Integrator & ETL developer › Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
Often used with
sql bulk_insert · sql connect · sql disconnect · sql value · fake rows · sql bulk_upsert · sql dml · sql encode
sql bulk_insert · sql connect · sql disconnect · sql value · fake rows · sql bulk_upsert · sql dml · sql encode
sql infer_schema <json_rows>
Description
Describe the columns of JSON rows (type, nullable, max length) and give the CREATE TABLE for a database, without a connection
Describe the columns of JSON rows (type, nullable, max length) and give the CREATE TABLE for a database, without a connection
Parameters
json_rows: The rows - String - required
json_rows: The rows - String - required
admin
sql infer_schema "[{\"id\": 1, \"code\": \"00042\", \"name\": \"Dupont SA\", \"balance\": 1250.5, \"since\": \"2024-03-12\", \"updated\": \"2026-11-03T10:15:00\", \"vip\": true}, {\"id\": 2, \"code\": \"00043\", \"name\": \"Martin\", \"balance\": -80, \"since\": \"2025-01-07\", \"updated\": \"2026-11-02T08:00:00\", \"vip\": false}]"
mentdb
{"dialect": "h2", "ddl": "CREATE TABLE my_table (\ id INTEGER NOT NULL,\ code VARCHAR(10) NOT NULL,\ name VARCHAR(20) NOT NULL,\ balance DECIMAL(5,1) NOT NULL,\ since DATE NOT NULL,\ updated TIMESTAMP NOT NULL,\ vip BOOLEAN NOT NULL\ )", "columns": [{"name": "id", "type": "INTEGER", "nullable": false, "max_length": 1}, {"name": "code", "type": "VARCHAR(10)", "nullable": false, "max_length": 5}, {"name": "name", "type": "VARCHAR(20)", "nullable": false, "max_length": 9}, {"name": "balance", "type": "DECIMAL(5,1)", "nullable": false, "max_length": 6}, {"name": "since", "type": "DATE", "nullable": false, "max_length": 10}, {"name": "updated", "type": "TIMESTAMP", "nullable": false, "max_length": 19}, {"name": "vip", "type": "BOOLEAN", "nullable": false, "max_length": 5}]}
sql infer_schema <json_rows> <json_params>
Description
Describe the columns with parameters: dialect (h2), table (my_table), primary_key, not_null, decimal, varchar_factor, quote_names
Describe the columns with parameters: dialect (h2), table (my_table), primary_key, not_null, decimal, varchar_factor, quote_names
Parameters
json_rows: The rows - String - required
json_params: The parameters - String - required
json_rows: The rows - String - required
json_params: The parameters - String - required
admin
sql infer_schema "[{\"id\": 1, \"date\": \"2026-11-03\", \"amount\": 12.5, \"label\": \"Invoice 42\"}]" "{\"dialect\": \"oracle\", \"table\": \"payment\", \"primary_key\": [\"id\"]}"
mentdb
{"dialect": "oracle", "ddl": "CREATE TABLE payment (\ id NUMBER(10) NOT NULL,\ date DATE NOT NULL,\ amount NUMBER(3,1) NOT NULL,\ label VARCHAR2(20 CHAR) NOT NULL,\ PRIMARY KEY (id)\ )", "columns": [{"name": "id", "type": "NUMBER(10)", "nullable": false, "max_length": 1}, {"name": "date", "type": "DATE", "nullable": false, "max_length": 10}, {"name": "amount", "type": "NUMBER(3,1)", "nullable": false, "max_length": 4}, {"name": "label", "type": "VARCHAR2(20 CHAR)", "nullable": false, "max_length": 10}], "warnings": ["the column 'date' is a reserved word for oracle: rename it or use {\"quote_names\": true}"]}"
For PostgreSQL
admin
sql infer_schema "[{\"id\": 1, \"code\": \"00042\", \"name\": \"Dupont SA\", \"balance\": 1250.5, \"since\": \"2024-03-12\", \"updated\": \"2026-11-03T10:15:00\", \"vip\": true}, {\"id\": 2, \"code\": \"00043\", \"name\": \"Martin\", \"balance\": -80, \"since\": \"2025-01-07\", \"updated\": \"2026-11-02T08:00:00\", \"vip\": false}]" "{\"dialect\": \"postgresql\", \"table\": \"customer\"}"
mentdb
{"dialect": "postgresql", "ddl": "CREATE TABLE customer (\ id INTEGER NOT NULL,\ code VARCHAR(10) NOT NULL,\ name VARCHAR(20) NOT NULL,\ balance DECIMAL(5,1) NOT NULL,\ since DATE NOT NULL,\ updated TIMESTAMP NOT NULL,\ vip BOOLEAN NOT NULL\ )", "columns": [{"name": "id", "type": "INTEGER", "nullable": false, "max_length": 1}, {"name": "code", "type": "VARCHAR(10)", "nullable": false, "max_length": 5}, {"name": "name", "type": "VARCHAR(20)", "nullable": false, "max_length": 9}, {"name": "balance", "type": "DECIMAL(5,1)", "nullable": false, "max_length": 6}, {"name": "since", "type": "DATE", "nullable": false, "max_length": 10}, {"name": "updated", "type": "TIMESTAMP", "nullable": false, "max_length": 19}, {"name": "vip", "type": "BOOLEAN", "nullable": false, "max_length": 5}]}
