MentDB, The digital brain.  About us Home
Back / All functions / SQL

Here you can manage SQL actions.

sql connectsql showsql auto_commitsql set_timeoutsql valuesql rowsql col_distinctsql 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 show tablessql selectsql show datasql show descsql 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 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> <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 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

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




© 2012 - 2019 - Innov-AI.