← Back Home

FX - SQL @ Protocol @ MentDB Weak

> Start the server
> (or start the server for the first time (embedded mode - H2 Database))
> (or start the server for the first time (remote mode - MySQL 8 Database))
> Open the MentDB Weak Editor

SQL

sql connect sql show sql auto_commit sql set_timeout sql value sql row sql col_distinct sql block open sql block close sql block to_json sql to json sql to xml sql to html sql to excel sql to excelx sql to pdf sql to csv sql to csv_file sql dml sql parse sql commit sql rollback sql disconnect sql disconnect all sql encode sql show tables sql select sql show data sql show desc sql show activity

sql connect <sqlId> <configJson>

Description

Connect to a database

Parameters

sqlId: The SQL id - string - required
configJson: The JSON connection config - string - required

admin
sql connect "session1" {cm get "demo_cm_mysql";};

mentdb
1

sql show

Description

Show all opened connections

admin
sql show

mentdb
["session1"]

sql auto_commit <sqlId> <bool>

Description

Set the connection as auto commit

Parameters

sqlId: The SQL id - string - required
bool: The boolean - number - required

admin
sql auto_commit "session1" true;

mentdb
1

sql set_timeout <timeout>

Description

Set the query timeout

Parameters

timeout: The timeout in second (integer>=0 - 0=no limit) - number - required

admin
sql set_timeout 0;

mentdb
1

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

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

sql col_distinct <sqlId> <selectQuery>

Description

Get a col distinct 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

sql block open <sqlId> <tableName> <selectQuery>

Description

Open the resultset to get a data block

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

sql block close

Description

Close the resultset

admin
sql block close;

mentdb
1

sql block to_json <nbLine>

Description

Get a data block from the resultset

Parameters

nbLine: The Snumber of line 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"
}

sql to json <sqlId> <tableName> <selectQuery>

Description

Get data from the database to 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"
}

sql to xml <sqlId> <tableName> <selectQuery>

Description

Get data from the database to 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>

sql to html <sqlId> <tableName> <selectQuery>

Description

Get data from the database to 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>

sql to excel <sqlId> <tableName> <selectQuery> <filePath>

Description

Get data from the database to 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

sql to excelx <sqlId> <tableName> <selectQuery> <filePath>

Description

Get data from the database to 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

sql to pdf <sqlId> <tableName> <selectQuery> <filePath>

Description

Get data from the database to 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

sql to csv <sqlId> <tableName> <selectQuery> <columnSeparator> <quoteChar>

Description

Get data from the database to 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: Quote char - 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

sql to csv_file <sqlId> <filePath> <selectQuery> <columnSeparator> <quoteChar>

Description

Get data from the database to 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: Quote char - String - required

admin
sql to csv_file "session1" "/Users/jimmitry/Desktop/data.csv" (concat "select * from products") "," "'";

mentdb
1

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

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 execut on each line - string - required

admin
sql parse "session1" "T" (concat "select name from products") {

	log trace [T_name];

};

mentdb

sql commit <sqlId>

Description

Commit a connection

Parameters

sqlId: The SQL id - string - required

admin
sql commit "session1";

mentdb
1

sql rollback <sqlId>

Description

Rollback a connection

Parameters

sqlId: The SQL id - string - required

admin
sql rollback "session1";

mentdb
1

sql disconnect <sqlId>

Description

Disconnect from a database

Parameters

sqlId: The SQL id - string - required

admin
sql disconnect "session1";

mentdb
1

sql disconnect all <sqlId>

Description

Disconnect all connections

Parameters

sqlId: The SQL id - string - required

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

sql show tables <cmId>

Description

Show tables from a database

Parameters

cmId: The connection id - string - required

admin
sql show tables "demo_cm_mysql";

mentdb
In editor ...

sql select <cmId> <query> <sizeCols> <title>

Description

Show data from a table

Parameters

cmId: The connection id - string - required
query: The select query - string - required
sizeCols: The size column separate by coma (,) - 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 ...

sql show data <cmId> <query> <title>

Description

Show data from 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 ...

sql show desc <cmId> <tablename>

Description

Show table description

Parameters

cmId: The connection id - string - required
tablename: The table name - string - required

admin
sql show desc "demo_cm_mysql" "products";

mentdb
In editor ...

sql show activity <groupType> <dtMin> <dtMax>

Description

Show activity of scripts

admin
sql show activity DAY
	(date datedifft (concat (date sysdate) " 00:00:00") "DAY" 100)
	(concat (date sysdate) " 23:59:59")

mentdb
In editor ...

start: 2026-02-02end: 2026-02-02v_start: 401v_end: