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

Here you can manage SQL actions.

sql connectsql showsql auto_commitsql valuesql rowsql to jsonsql to xmlsql to htmlsql to excelsql to excelxsql to pdfsql to csvsql 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 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 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 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 - 2018 - Innov-AI.