Performing queries with the BearDB REST Server
The BearDB REST Server support SQL Queries and Inserts. A REST client can lookup data using a SELECT query or can insert one row of data into a specific table.
Using BearDB REST Server to SELECT data
BearDB support two forms of REST requests which represent a SELECT query.
The first form is known as a REST Query, where the parameters in the REST request are parsed to determine the table to be selected, and the WHERE clause for the selected records.
The second form allows a regular SQL SELECT statement to be included with the REST request. This form allows the use of any valid SELECT or DESCRIBE statement to be executed, including using Joins, Aggregate operations, and SQL Functions.
Creating a REST Query
To create a proper REST query for the BearDB REST Server, the following settings should be used for the REST request:
|HTTP Parameter(s)||column4=Some value|
The complete HTTP request is the following:
This is the equivalent SQL query:
- SELECT * FROM myTestTable WHERE column4 = 'Some value'
Notes on using a REST Query:
- You have to
SELECT *(select all columns in the table) with a REST query.
- Parameters in a REST Query are used as column values in a SQL WHERE clause. You can have up to 127 different parameters in a REST Query.
- If the REST Query has no parameters, then all records in the table will be selected without a WHERE clause.
- If the table name in the REST Query is not valid, or any of the column names in the http parameters are not valid then there will be a specific error in the response, and the query will not be performed.
Setting WHERE clause comparators
Parameters included with a REST Query represent the critieria for the selected data, and are utilized as the WHERE clause of a SQL SELECT operation.
It is not a requirement that a REST query have parameters, just as it is not required that a SQL SELECT operation must have a WHERE clause. The omission of any parameters for a REST Query will result in selecting all data in the specified table.
REST Queries are not limited to checking for equal values, and they can be utilized with any valid database comparator such as greater than, less than, and not equals.
To use comparators with a REST Query the parameter value must contain the comparator if it is not equal. Some examples of parameters in a REST Query are as follows:
|columnName = 12||WHERE columnName = 12||WHERE columnName equals 12.|
|columnName = <74||WHERE columnName < 74||WHERE columnName is less than 74.|
|columnName = <=92||WHERE columnName <= 92||WHERE columnName is less than or equal to 92.|
|columnName = >58||WHERE columnName > 58||WHERE columnName is greater than 58.|
|columnName = >=61||WHERE columnName >= 61||WHERE columnName is greater than or equal to 61.|
|columnName = <>88||WHERE columnName <> 88||WHERE columnName is not equal to 88.|