The api/sql/exec Resource

The api/sql/exec resource is requested using an HTTP POST with a SQL statement or statements to be executed, and a response is returned that consists of a query result set or the number of rows affected depending on the SQL statement(s) executed.

For an overview see page eXtremeDB Web Service Resources

An example api/sql/exec request could be submitted using curl as follows:

 
    curl -X POST --data-raw '{ "sql": "SELECT * FROM Statistic" }' http://localhost:8083/api/sql/exec
            
     

Note that on Windows it might be necessary to escape the embedded double quote character as follows:

 
    curl -X POST --data-raw "{ \"sql\": \"SELECT * FROM Statistic\" }" http://localhost:8083/api/sql/exec
     

Request

The client submits SQL code to execute and execution parameters as JSON-encoded HTTP content. The /api/sql/exec request can also specify the optional max_records, max_array and max_sequence parameters. For example:

 
    {
        "sql": "SELECT * FROM Statistic",
        "max_records": 100,
        "max_array": 10,
        "max_sequence": 10
    }
     

These parameters specify the maximum sizes of the returned result set, arrays, and sequences respectively. Their default values are 100, 10, and 10.

The sql parameter contains the SQL statement(s) to execute. It may consist of multiple statements, separated by semicolons. If there are multiple statements, they are executed sequentially, and execution is aborted in case of a failure.

If the SQL text starts with select (case-insensitive), it is executed as a query, and a result set is returned. Otherwise it is executed as a non-query statement, and only the number of affected rows is returned.

Response

The result is returned as a JSON object containing a single array result. Each executed SQL statement has a corresponding record in this array. There are two types of records, stmt and query which is returned in the type field of the record.

Non-query Statement Result Records

These records have only two fields: type (which is set to stmt) and rows_affected, which specifies the number of affected rows, as returned by the SQL engine. For example:

 
    {
        "result":
        [
            ...
            {
                "type": "stmt",
                "rows_affected": 1
            }
            ...
        ]
    }
     

Query Result Records

These records are more complex than the statement results. They contain the type field (which is set to query) and the data object, which encapsulates the returned result set. The data object consists of a header and a resultset. The header contains the metadata for the resultset which consists of:

• resultset_size: the total size of the result set
• fields: an array consisting of the name and type of each columns in the result set

Note that the resultset_size value can be greater than the number of records returned, which is limited by the max_records parameter.

The resultset field is an array, each of whose elements is an array containing the values of the columns in the same order specified by the fields array in the header. For example:

     
    {
        "result":
        [
            ...
            {
                "type": "query",
                "data":
                {
                    "header":
                    {
                        "resultset_size": 4,
                        "fields":
                        [
                            { "name": "i", "type": "Int4" },
                            { "name": "s", "type": "String" }
                        ]
                    },
                    "resultset":
                    [
                        [ 1, "string1" ],
                        [ 2, "string2" ],
                        [ 3, "string3" ],
                        [ 4, "string4" ]
                    ]
                }
            }
            ...
        ]
    }
     

Datetime fields

For fields of type datetime, the precision is output in the resultset header. For example, consider a table DateTimeTable with a single datetime field dt. The table values could be extracted with a command like the following:

 
    curl -X POST http://localhost:8083/api/sql/exec
        {"sql":"SELECT dt FROM DateTimeTable;"}
         

The result will display the precision of dt in output like the following:

 
    {
        "result":
        [
            {
                "sql": "SELECT dt FROM TestDateTimeTable;",
                "type": "query",
                "data":
                {
                    "header":
                    {
                        "fields":
                        [
                            {
                                "name": "dt",
                                "type": "DateTime",
                                "precision": 1
            
                            }
                        ]
                    },
                    "resultset":
                    [
                        [100000000]
                    ]
                }
            }
        ]
    }
     

Error records

If an error occurs during the execution of a statement, an error record is returned, and further execution is aborted. In this case the error record will be the last in the result array, and it will be the only error record.

The error records contain the type field (query or stmt, corresponding to the failed SQL statement) and the error object. The error code and message are passed from the SQL engine verbatim. (Error codes are defined in the ErrorCode enumeration in the sql/exceptions.h header.) The following is a sample error record:

 
    {
        "result":
        [
            ...
            {
                "type": "stmt",
                "error":
                {
                    "code": 3,
                    "message": "Compiler error at position 20: Unknown table inexistent\nDROP TABLE inexistent;\n                 ^\n"
                }
            }
        ]
    }
     

Request and response sample

Suppose that the following SQL statements are to be executed:

 
    CREATE TABLE T(i INT, s STRING);
    INSERT INTO T(i, s) VALUES(1, 'string1'), (2, 'string2'), (3, 'string3'), (4, 'string4');
    SELECT * FROM T;
    DROP TABLE T;
     

The request body would be as follows:

     
    {
        "sql": "CREATE TABLE T(i INT, s STRING);\nINSERT INTO T(i, s) 
            VALUES(1, 'string1'), (2, 'string2'), (3, 'string3'), (4, 'string4');\n
            SELECT * FROM T;\nDROP TABLE T;\n"
    }
     

And the response body returned would be as follows:

 
    {
        "result":
        [
            {
                "type": "stmt",
                "rows_affected": -1
            },
            {
                "type": "stmt",
                "rows_affected": -1
            },
            {
                "type": "query",
                "data":
                {
                    "header":
                    {
                        "resultset_size": 4,
                        "fields":
                        [
                            {
                                "name": "i",
                                "type": "Int4"
                            },
                            {
                                "name": "s",
                                "type": "String"
                            }
                        ]
                    },
                    "resultset":
                    [
                        [
                            3,
                            "string3"
                        ],
                        [
                            2,
                            "string2"
                        ],
                        [
                            1,
                            "string1"
                        ],
                        [
                            4,
                            "string4"
                        ]
                    ]
                }
            },
            {
                "type": "stmt",
                "rows_affected": -1
            }
        ]
    }