DBExec

Database interface functions.

An object containing the result of the execution of a sql statement, for example:

{"columns":["TS","HIGH","OPEN","LOW","CLOSE","VOLUME"],"values":[[1518970320000,100,99.1,90,100,12345.6]]}

object

DBExec(sql)

sql statement string. sql true string

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)
    
    // Add a piece of data
    Log(DBExec(":INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"))
    
    // Query data
    Log(DBExec(":SELECT * FROM TEST_TABLE;"))
}

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)
    
    # Add a piece of data
    Log(DBExec(":INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"))
    
    # Query data
    Log(DBExec(":SELECT * FROM TEST_TABLE;"))

void main() {
    string 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)";
    auto ret = DBExec(strSql);
    Log(ret);
    
    // Add a piece of data
    Log(DBExec(":INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"));
    
    // Query data
    Log(DBExec(":SELECT * FROM TEST_TABLE;"));
}

Support in-memory database, for DBExec function parameters, if sql statement starts with : then operate in in-memory database, it will be faster without writing files. It’s suitable for database operations that do not require persistent saving, for example:

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)
}

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)

void main() {
    string 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)";
    auto ret = DBExec(strSql);
    Log(ret);
}

Create a table.

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))
    
    // Add a piece of data
    Log(DBExec("INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"))
    
    // Query data
    Log(DBExec("SELECT * FROM TEST_TABLE;"))
    
    // Modify data
    Log(DBExec("UPDATE TEST_TABLE SET HIGH=? WHERE TS=?", 110, 1518970320000))    
    
    // Delete data
    Log(DBExec("DELETE FROM TEST_TABLE WHERE HIGH=?", 110))
}

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))
    
    # Add a piece of data
    Log(DBExec("INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"))
    
    # Query data
    Log(DBExec("SELECT * FROM TEST_TABLE;"))
    
    # Modify data
    Log(DBExec("UPDATE TEST_TABLE SET HIGH=? WHERE TS=?", 110, 1518970320000))
    
    # Delete data
    Log(DBExec("DELETE FROM TEST_TABLE WHERE HIGH=?", 110))

void main() {
    string 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)";
    Log(DBExec(strSql));            

    // Add a piece of data
    Log(DBExec("INSERT INTO TEST_TABLE (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) VALUES (1518970320000, 100, 99.1, 90, 100, 12345.6);"));
    
    // Query data
    Log(DBExec("SELECT * FROM TEST_TABLE;"));
    
    // Modify data
    Log(DBExec("UPDATE TEST_TABLE SET HIGH=? WHERE TS=?", 110, 1518970320000));
    
    // Delete data
    Log(DBExec("DELETE FROM TEST_TABLE WHERE HIGH=?", 110));
}

Add, delete, check and change the records in the table.

The function DBExec() can operate the live trading database (SQLite database) by passing in parameters. Realize the operation of adding, deleting, checking and changing data in the live trading database, supporting SQLite grammar. The system reserved tables in the live trading database: kvdb, cfg, log, profit, chart, do not operate on these tables. Currently Transactions are not supported and it is not recommended to perform such operations, which can cause conflicts in the system. The DBExec() function is only supported for live trading.

{@fun/Global/_G _G}

MD5 UUID