# 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
>                 }
>             ]
>         }