SQL request

SQL statements are executed by using HTTP methods and a URL that contains the SQL statement. Parameters are typically given by using http payload. All responses are JSON structures and contain meta, data, and status structures, see HTTP SQL requests and responses. Some structures might be empty depending on the request.

In the case of DDL and DML statements, the JSON structure that is returned contains empty meta and data structures. The status structure contains information about the DDL/DML statement execution result (response code, rows affected).

In the case of a query, the JSON structure that is returned contains result set metadata and the row data. For very large results, the result set that is returned might not be complete, in which case the HTTP response status code 206 is returned. You can then fetch more rows by sending the same request again until the complete result set and the HTTP response status code 200 are returned.

Note that '?' must be HTML URL encoded as '%3F' in URLs. If '?' binding is used, the actual parameter values are passed inside the message body. The parameters must be named by using keywords: param1=, param2= and so on. The number identifies the location of the '?' in the SQL statement. For example:

https://localhost:1313/sql/select+id+from+sys_users+where+id<%3F+and+id>%3F

The message body sent with the message contains actual parameter values, for example: "param1=4&param2=1".

The number of rows that are returned in one JSON response is limited to 100 rows. If more rows are available, the status response code 206 is returned, for example:

"status": [{
    "response code":"206 Partial Content",
    "rows returned":"100",
    "rows affected":"0",
    "autocommit":"true"
}]

The next call that uses the same URL returns the following 100 rows. When all rows are returned, the status code 200 is returned, for example:

"status":[{
   "response code":"200 OK",
   "rows returned":"12",
   "rows affected":"0",
   "autocommit":"true"
}]

URL: https://localhost:1313/sql/select+*+from+sys_users

[{
   "meta": [{
       "COLUMN_NAME": "1",
       "TYPE": "DECIMAL"
   }],
   "data": [{
       "1": "1"
   }],
   "status": [{
       "response code": "200 OK",
       "rows returned": "1",
       "rows affected": "0",
       "autocommit": "true"
   }]
}]

URL: https://localhost:1313/sql/create+table+kv+(key+integer,value+varchar)

[{
   "status": [{
       "response code": "404 Not Found",
       "error": "SOLID Table Error 13013: Table name KV conflicts with an existing entity",
       "native error": "13013",
       "sqlstate": "42S01"
   }]
}]

URL: https://localhost:1313/sql/delete+from+kv

[{
   "meta": [],
   "data": [],
   "status": [{
       "response code": "200 OK",
       "rows returned": "0",
       "rows affected": "3",
       "autocommit": "false"
   }]
}]

URL: https://localhost:1313/sql/select+from+kv

[{
   "status": [{
       "response code": "404 Not Found",
       "error": "SOLID SQL Error 1: syntax error (line 1 near 'FROM')",
       "native error": "1",
       "sqlstate": "HY000"
   }]
}]