Type/to search
Built-in Functions
Global
Log
Market
Trade
Account
Futures
Exchange
IO
Network
Storage
_G
DBExec
SetChannelData
GetChannelData
Threads
TA
Talib
OS
Structures
Built-in Variables and Constants
Conventions
Exchanges

Executes SQL statements against the live trading database.


    
    
    
DBExec(sql)

    
    
    
DBExec(sql, ...args)

Examples

  • Supports in-memory databases: when calling the DBExec function, if the sql statement starts with :, the operation runs in the in-memory database. Because nothing is written to a file, execution is faster. This suits database operations that don't need persistent storage, for example:

    
    javascript
    
    
    function main() {
    var strSql = [
        ":CREATE TABLE TEST_TABLE(",
        "TS INT PRIMARY KEY NOT NULL,",
        "HIGH REAL NOT NULL,",
        "OPEN REAL NOT NULL,",
        "LOW REAL NOT NULL,",
        "CLOSE REAL NOT NULL,",
        "VOLUME REAL NOT NULL)"
    ].join("")
    var ret = DBExec(strSql)
    Log(ret)

    // Insert a record
    Log(DBExec(":INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"))

    // Query the data
    Log(DBExec(":SELECT * FROM TEST_TABLE;"))
}
    
    python
    
    
    def main():
    arr = [
        ":CREATE TABLE TEST_TABLE(",
        "TS INT PRIMARY KEY NOT NULL,",
        "HIGH REAL NOT NULL,",
        "OPEN REAL NOT NULL,",
        "LOW REAL NOT NULL,",
        "CLOSE REAL NOT NULL,",
        "VOLUME REAL NOT NULL)"
    ]
    strSql = ""
    for i in range(len(arr)):
        strSql += arr[i]
    ret = DBExec(strSql)
    Log(ret)

    # Insert a record
    Log(DBExec(":INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"))

    # Query the data
    Log(DBExec(":SELECT * FROM TEST_TABLE;"))
    
    rust
    
    
    fn main() {
    let arr = [
        ":CREATE TABLE TEST_TABLE(",
        "TS INT PRIMARY KEY NOT NULL,",
        "HIGH REAL NOT NULL,",
        "OPEN REAL NOT NULL,",
        "LOW REAL NOT NULL,",
        "CLOSE REAL NOT NULL,",
        "VOLUME REAL NOT NULL)",
    ];
    let strSql = arr.join("");
    let ret = DBExec(&strSql);
    Log!(format!("{:?}", ret));

    // Insert a record
    Log!(format!("{:?}", DBExec(":INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);")));

    // Query the data
    Log!(format!("{:?}", DBExec(":SELECT * FROM TEST_TABLE;")));
}

  • Use the DBExec() function to create a data table in the database.

    
    javascript
    
    
    function main() {
    var strSql = [
        "CREATE TABLE TEST_TABLE(",
        "TS INT PRIMARY KEY NOT NULL,",
        "HIGH REAL NOT NULL,",
        "OPEN REAL NOT NULL,",
        "LOW REAL NOT NULL,",
        "CLOSE REAL NOT NULL,",
        "VOLUME REAL NOT NULL)"
    ].join("")
    var ret = DBExec(strSql)
    Log(ret)
}
    
    python
    
    
    def main():
    arr = [
        "CREATE TABLE TEST_TABLE(",
        "TS INT PRIMARY KEY NOT NULL,",
        "HIGH REAL NOT NULL,",
        "OPEN REAL NOT NULL,",
        "LOW REAL NOT NULL,",
        "CLOSE REAL NOT NULL,",
        "VOLUME REAL NOT NULL)"
    ]
    strSql = ""
    for i in range(len(arr)):
        strSql += arr[i]
    ret = DBExec(strSql)
    Log(ret)
    
    rust
    
    
    fn main() {
    let arr = [
        "CREATE TABLE TEST_TABLE(",
        "TS INT PRIMARY KEY NOT NULL,",
        "HIGH REAL NOT NULL,",
        "OPEN REAL NOT NULL,",
        "LOW REAL NOT NULL,",
        "CLOSE REAL NOT NULL,",
        "VOLUME REAL NOT NULL)",
    ];
    let strSql = arr.join("");
    let ret = DBExec(&strSql);
    Log!(format!("{:?}", ret));
}

  • Perform insert, delete, query and update operations on records in a table.

    
    javascript
    
    
    function main() {
    var strSql = [
        "CREATE TABLE TEST_TABLE(",
        "TS INT PRIMARY KEY NOT NULL,",
        "HIGH REAL NOT NULL,",
        "OPEN REAL NOT NULL,",
        "LOW REAL NOT NULL,",
        "CLOSE REAL NOT NULL,",
        "VOLUME REAL NOT NULL)"
    ].join("")
    Log(DBExec(strSql))

    // Insert a record
    Log(DBExec("INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"))

    // Query records
    Log(DBExec("SELECT * FROM TEST_TABLE;"))

    // Update a record
    Log(DBExec("UPDATE TEST_TABLE SET HIGH=? WHERE TS=?", 110, 1518970320000))

    // Delete records
    Log(DBExec("DELETE FROM TEST_TABLE WHERE HIGH=?", 110))
}
    
    python
    
    
    def main():
    arr = [
        "CREATE TABLE TEST_TABLE(",
        "TS INT PRIMARY KEY NOT NULL,",
        "HIGH REAL NOT NULL,",
        "OPEN REAL NOT NULL,",
        "LOW REAL NOT NULL,",
        "CLOSE REAL NOT NULL,",
        "VOLUME REAL NOT NULL)"
    ]
    strSql = ""
    for i in range(len(arr)):
        strSql += arr[i]
    Log(DBExec(strSql))

    # Insert a record
    Log(DBExec("INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"))

    # Query records
    Log(DBExec("SELECT * FROM TEST_TABLE;"))

    # Update a record
    Log(DBExec("UPDATE TEST_TABLE SET HIGH=? WHERE TS=?", 110, 1518970320000))

    # Delete records
    Log(DBExec("DELETE FROM TEST_TABLE WHERE HIGH=?", 110))
    
    rust
    
    
    fn main() {
    let arr = [
        "CREATE TABLE TEST_TABLE(",
        "TS INT PRIMARY KEY NOT NULL,",
        "HIGH REAL NOT NULL,",
        "OPEN REAL NOT NULL,",
        "LOW REAL NOT NULL,",
        "CLOSE REAL NOT NULL,",
        "VOLUME REAL NOT NULL)",
    ];
    let strSql = arr.join("");
    Log!(format!("{:?}", DBExec(&strSql)));

    // Insert a record
    Log!(format!("{:?}", DBExec("INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);")));

    // Query records
    Log!(format!("{:?}", DBExec("SELECT * FROM TEST_TABLE;")));

    // Update a record: the Rust DBExec() function accepts only a single SQL statement string and does not support ? placeholders for parameters, so parameter values must be written directly into the statement
    Log!(format!("{:?}", DBExec("UPDATE TEST_TABLE SET HIGH=110 WHERE TS=1518970320000;")));

    // Delete records
    Log!(format!("{:?}", DBExec("DELETE FROM TEST_TABLE WHERE HIGH=110;")));
}

Returns

TypeDescription

object

The result of the SQL statement: columns is an array of column names, and values is a two-dimensional array of row data, for example {"columns":["TS","CLOSE"],"values":[[1518970320000,100]]}. For the full structure, see DBExec-return.

Arguments

NameTypeRequiredDescription

sql

string

Yes

The sql statement string to execute.

arg

string / number / bool / null value

No

Parameter values for the ? placeholders in the sql statement, passed in order. Multiple arg parameters can be passed. Only JavaScript and Python support this parameter. In Rust, DBExec() takes only one parameter, the sql statement.

See Also

_G

Remarks

  • The DBExec() function operates on the live trading database (an SQLite database) using the parameters passed to it.
  • It supports adding, deleting, querying and updating data in the live trading database, using SQLite syntax.
  • Multiple non-query statements can be written in a single call, separated by semicolons (for example, to create several tables and their indexes at once). They are executed one by one, in order. Parameters for the ? placeholders are assigned to the statements in order, and the function returns the result of the last statement. If a statement fails, the function throws an error, but the statements before it have already been executed.
  • The system-reserved tables in the live trading database are kvdb, cfg, log, profit and chart. Never operate on these tables.
  • Transactions are not currently supported. Do not run transaction-related operations, because they will cause system conflicts.
  • The DBExec() function is only supported in live trading.