Queries
Queries allows users to perform predefined searches throughout an entire Database.
Creating a query
See SQL Pass-thru Queries Configuration and Management.
Running a query
To perform a query go to SQL Pass-thru in SlashDB.
GET (SQL SELECT)
Locate customer-by-city, defined as:
And click the Run (
) button.
The query box will appear on the right-side of the screen. If the query allows it, input a search term in the search field and select Execute. Alternatively, to view the search results in the HTML, JSON, CSV, XML or XSD formats, select the desired link in the area above the Execute button.
3. If the Execute button is used, results will appear at the bottom of the screen. If the links were used, a new page will load in the desired format. You can also use curl or your languages of choice, request library, to perform this request:
curl https://demo.slashdb.com/query/customers-in-city/city/Paris.json?limit=29 -i
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Date: Thu, 01 Jun 2017 10:40:54 GMT
Content-Type: application/json
Content-Length: 329
Connection: keep-alive
[
{
"City": "Paris",
"LastName": "Bernard",
"State": null,
"FirstName": "Camille",
"Phone": "+33 01 49 70 65 65"
},
{
"City": "Paris",
"LastName": "Lefebvre",
"State": null,
"FirstName": "Dominique",
"Phone": "+33 01 47 42 71 71"
}
]
POST (SQL INSERT)
If the selected request was configured to be meant as a way to create new data entries, it will most likely have an POST option in the query run window.
For example the add-new-customer query definition is as following:
Instead of a GET option, a POST one will appear in the Run window.
Hitting the Execute will issue a Post request to the backend and create a new Customer. The new Customer can be viewed via Data Discovery, visiting:
https://demo.slashdb.com/db/Chinook/Customer.html?sort=-CustomerId&limit=5
The same can be achieved using curl:
# create the customer
curl https://demo.slashdb.com/query/add-new-customer/FirstName/Adam/LastName/Smith/Phone/555-555-5555/City/New%20York/State/NY/Email/adam.smith@gmail.com.json -i -XPOST
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Date: Thu, 01 Jun 2017 13:03:03 GMT
Content-Type: application/json
Content-Length: 2
Connection: keep-alive
# check if the new customer exists - here we limit he output to '1' but more might be needed
curl 'https://demo.slashdb.com/db/Chinook/Customer.json?sort=-CustomerId&limit=1' -i
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Date: Thu, 01 Jun 2017 13:07:41 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Connection: keep-alive
[{
"Phone": "555-555-5555",
"City": "New York",
"Fax": null,
"FirstName": "Adam",
"__href": "/db/Chinook/Customer/CustomerId/60.json",
"LastName": "Smith",
"Company": null,
"Country": null,
"Email": "adam.smith@gmail.com",
"PostalCode": null,
"State": "NY",
"Invoice": {
"__href": "/db/Chinook/Customer/CustomerId/60/Invoice.json"
},
"Address": null,
"Employee": {
"__href": "/db/Chinook/Customer/CustomerId/60/Employee.json"
},
"SupportRepId": null,
"CustomerId": 60
}]
PUT (SQL UPDATE)
If the selected request was configured to be meant as a way to update existing data entries, it will most likely have an PUT option in the query run window.
For example the update-customers-email query definition is as following:
Instead of a GET option, a PUT one will appear in the Run window.
Hitting the Execute will issue a Put request to the backend and update the selected Customer. The updated Customer can be viewed via Data Discovery, visiting:
https://demo.slashdb.com/db/Chinook/Customer/CustomerId/60.html
The same can be achieved using curl:
# update prevously created customers email
curl https://demo.slashdb.com/query/update-customers-email/Email/mrsmith@gmail.com/id/60.json -XPUT -i
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Date: Thu, 01 Jun 2017 13:46:57 GMT
Content-Type: application/json
Content-Length: 2
Connection: keep-alive
# check if the email has changed
curl https://demo.slashdb.com/db/Chinook/Customer/CustomerId/60.json -i
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Date: Thu, 01 Jun 2017 13:47:58 GMT
Content-Type: application/json
Content-Length: 426
Connection: keep-alive
{
"Phone": "555-555-5555",
"City": "New York",
"Fax": null,
"FirstName": "Adam",
"__href": "/db/Chinook/Customer/CustomerId/60.json",
"LastName": "Smith",
"Company": null,
"Country": null,
"Email": "mrsmith@gmail.com",
"PostalCode": null,
"State": "NY",
"Invoice": {
"__href": "/db/Chinook/Customer/CustomerId/60/Invoice.json"
},
"Address": null,
"Employee": {
"__href": "/db/Chinook/Customer/CustomerId/60/Employee.json"
},
"SupportRepId": null,
"CustomerId": 60
}
DELETE (SQL DELETE)
Lastly, if the selected request was configured to be meant as a way to delete existing data entries, it will most likely have an DELETE option in the query run window.
For example the delete-customer-by-id query definition is as following:
Instead of a GET option, a PUT one will appear in the Run window.
Hitting the Execute will issue a Delete request to the backend and delete the selected Customer. To check if the Customer has been deleted properly, Data Discovery can be used i.e. visiting:
https://demo.slashdb.com/db/Chinook/Customer/CustomerId/60.html
will produce a 404 error.
The same can be achieved using curl:
# lets delete the previously created customer
curl https://demo.slashdb.com/query/delete-customer-by-id/id/60.json -i -XDELETE
HTTP/1.1 200 OK
Server: nginx/1.4.6 (Ubuntu)
Date: Thu, 01 Jun 2017 13:57:23 GMT
Content-Type: application/json
Content-Length: 2
Connection: keep-alive
# check if the customer has been properly deleted
curl https://demo.slashdb.com/db/Chinook/Customer/CustomerId/60.json -i
HTTP/1.1 404 Not Found
Server: nginx/1.4.6 (Ubuntu)
Date: Thu, 01 Jun 2017 13:58:15 GMT
Content-Type: application/json; charset=utf-8
Transfer-Encoding: chunked
Connection: keep-alive
Warning: The resource could not be found.
{"http_code": 404, "description": "The resource could not be found."}
General query HTTP method notice
For example, if a query is defined as an DELETE method only, issuing a different method will result in an error 405 message:
curl https://demo.slashdb.com/query/delete-customer-by-id/id/60.json -i
HTTP/1.1 405 Method Not Allowed
Server: nginx/1.4.6 (Ubuntu)
Date: Thu, 01 Jun 2017 14:16:56 GMT
Content-Type: application/json; charset=utf-8
Transfer-Encoding: chunked
Connection: keep-alive
Warning: This method is not allowed for query: "delete-customer-by-id". Allowed methods: ["DELETE"].
{"url_template": "/query/delete-customer-by-id/id/{id}.json", "http_code": 405, "description": "This method is not allowed for query: \"delete-customer-by-id\". Allowed methods: [\"DELETE\"]."}
We encourage to use the proper HTTP methods for a given task, i.e. PUT for SQL UPDATE etc. At the same time, we leave room for interpretation, so if necessary, other methods might be allowed.