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

Parameters

    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
Often used with

    sql disconnect  ·  cm get  ·  sql dml  ·  execute  ·  sql value  ·  file delete  ·  sql encode  ·  cm remove

sql show


Description

    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
Often used with

    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

Parameters

    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
Often used with

    cm get  ·  sql commit  ·  sql connect  ·  sql disconnect  ·  sql dml  ·  execute  ·  sql encode  ·  sql rollback

sql set_timeout <timeout>


Description

    Set the query timeout

Parameters

    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
Often used with

    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

Parameters

    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
Often used with

    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

Parameters

    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
Often used with

    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

Parameters

    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
Often used with

    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

Parameters

    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
Often used with

    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

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
Often used with

    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

Parameters

    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
Often used with

    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

Parameters

    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
Often used with

    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

Parameters

    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
Often used with

    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

Parameters

    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
Often used with

    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

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
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
Often used with

    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

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
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
Often used with

    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

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
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
Often used with

    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

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
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
Often used with

    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

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
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
Often used with

    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

Parameters

    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
Often used with

    sql connect  ·  sql disconnect  ·  cm get  ·  execute  ·  file delete  ·  cm remove  ·  sql value  ·  sql encode

sql parse <sqlId> <namespace> <selectQuery> <mqlAction>


Description

    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
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
Often used with

    cm get  ·  sql connect  ·  sql disconnect  ·  execute  ·  sql dml  ·  sql encode  ·  cm remove  ·  file delete

sql commit <sqlId>


Description

    Commit a connection

Parameters

    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
Often used with

    cm get  ·  sql connect  ·  sql disconnect  ·  sql dml  ·  sql auto_commit  ·  sql encode  ·  execute  ·  sql rollback

sql rollback <sqlId>


Description

    Rollback a connection

Parameters

    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
Often used with

    cm get  ·  sql auto_commit  ·  sql commit  ·  sql connect  ·  sql disconnect  ·  sql dml  ·  sql value  ·  execute

sql disconnect <sqlId>


Description

    Disconnect from a database

Parameters

    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
Often used with

    sql connect  ·  cm get  ·  sql dml  ·  execute  ·  sql value  ·  file delete  ·  sql encode  ·  cm remove

sql disconnect all


Description

    Disconnect all connections

admin
sql disconnect all;
mentdb
1

sql encode <data>


Description

    Encode a valid value

Parameters

    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
Often used with

    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.

Parameters

    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
Often used with

    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

Parameters

    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
Often used with

    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

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
admin
sql select "demo_cm_mysql" "select * from table limit 0, 100" "table";
mentdb
In editor ...
Used in use cases (1 recipe)

    CRUD & data API developer › Inspect the table before writing the API
Often used with

    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

Parameters

    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 ...
Used in use cases (1 recipe)

    CRUD & data API developer › Inspect the table before writing the API
Often used with

    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

Parameters

    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
Often used with

    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

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
admin
sql show activity DAY (date datedifft (concat (date sysdate) " 00:00:00") "DAY" 100) (concat (date sysdate) " 23:59:59")
mentdb
In editor ...
Used in use cases (1 recipe)

    CRUD & data API developer › Tear down the tutorial database
Often used with

    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

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
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)

Parameters

    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
Often used with

    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

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
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)

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
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
Often used with

    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.

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
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.

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
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
Often used with

    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

Parameters

    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

Parameters

    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}]}