Running SQL passthrough queries through REST
You can run any SQL statement and retrieve results back using SQL passthrough queries with the REST listener.
Before you begin
You must enable SQL operations by setting security.sql.passthrough=true in the wire listener properties file.
Procedure
Use GET method and system.sql as the collection name and
$sql as the query operator, followed by the SQL statement.
GET /databaseName/system.sql?query={"$sql": "sql_statement"}
To use host variables, include question marks in the SQL statement, and include the $bindings operator with an array that contains a value for each host variable in order of appearance.
GET /databaseName/system.sql?query={"$sql": "sql_statement", "$bindings": [values]}
Examples
- Create an table
-
GET /mydb/system.sql?query={"$sql": "create table foo (c1 int)"}
- Run an SQL query
Sample response:GET /mydb/system.sql?query={"$sql": "select count(*) as count from foo"}
[ { "count": 10 } ]
- Delete rows based on a host variable
Sample response:GET /mydb/system.sql?query={"$sql": "delete from foo where c1 < ?", "$bindings" : [100] })
[ { "n": 2 } ]