Innov-AI     Services     Supporters     Contact     About    
SQL

Here you can manage SQL actions.

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 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 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") {<br><br>	log trace [T_name];<br><br>};
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

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