Executing Query

GUI

When certain query is selected from the list a view for edit/execute is opened.

Users with execute and view/edit permission on the query will see full Query Studio editor with execute form in lower part of the window.

Users with only execute permission will see different layout that doesn't allow to view details of the query.

To execute the query, fill the parameters (use the NULL button to set a parameter value as NULL), then

• click on round play button to see output in results panel,
• click on buttons for specific format that slide out when hovering over play button,
• press enter when focused on parameter form to see output in results panel.

Errors

Any problems executing the statement like SQL syntax error are shown in Results tab of the GUI

REST

Each SQL Pass-thru query has its own endpoint to run it with HTTP request. Right HTTP method must be used on the URL constructed of query id, parameters and output format. All that information is available either on list of queries or query definition.

GET or DELETE

For HTTP methods GET or DELETE, query parameters are given in URL segments.

To pass NULL values as parameters, use the placeholder keyword <null> in the URL segment. If you want to use a custom placeholder, you can use the nullStr query string parameter.

Examples:

GET request to SlashDB Demo server for query customer-in-city with single query parameter city equals Paris.

curl -X GET "https://demo.slashdb.com/query/customers-in-city/city/Paris.json"

GET request to SlashDB Demo server for query without any parameters - sales-by-year.

curl -X GET "https://demo.slashdb.com/query/sales-by-year.json"

GET request to SlashDB Demo server for query customer-in-city with single query parameter city equals NULL.

curl -X GET "https://demo.slashdb.com/query/customers-in-city/city/<null>.json"

PUT or POST

For HTTP methods PUT or POST parameters can be sent either in URL segments or in the request body in the data format matching the request format (json, xml, csv).

POST with parameters passed in URL

curl -X POST "https://demo.slashdb.com/query/add-new-customer/FirstName/John/LastName/Tutorial/Phone/123 456 789/City/New York/State/NY/Email/john.tutorial@slashdb.com.json"

Bulk execute

Multiple parameter sets can be passed in body of PUT or POST request which results in executing the query multiple times, each time with different parameters. All SQL statements are executed in a single transaction and changes are rolled back if any errors occur.

Usage:
Body in CSV

ParamName1,ParamName2
Param Value 1, Param Value 2
Param Value 3, Param Value 4

Body in JSON

[
  {
    "ParamName1": "Param Value 1",
    "ParamName2": "Param Value 2"
  },
  {
    "ParamName1": "Param Value 1",
    "ParamName2": "Param Value 2"
  }
]

Body in XML
For XML parameter sets must be wrapped in tags parameters and param_set.

<parameters>
  <param_set>
    <ParamName1>Param Value 1</ParamName1>
    <ParamName2>Param Value 2</ParamName2>
    ...
  </param_set>
  <param_set>
    <ParamName1>Param Value 11</ParamName1>
    <ParamName2>Param Value 22</ParamName2>
  </param_set>
  ...
</parameters>

If needed the tags can be changed in INI settings

xml_sdb.sqlpassthru_params.root_tag = parameters
xml_sdb.sqlpassthru_params.set_tag = param_set

Examples:

POST request with two parameter sets in JSON body

curl -X POST "https://demo.slashdb.com:443/query/add-new-customer.json" \
-d @- << EOF
[
  {
    "FirstName":"John",
    "LastName":"Tutorial",
    "Phone":"123 456 789",
    "City":"New York",
    "State":"NY",
    "Email":"john.tutorial@slashdb.com"
  },
  {
    "FirstName":"Monica",
    "LastName":"Tutorial",
    "Phone":"123 456 789",
    "City":"New York",
    "State":"NY",
    "Email":"monica.tutorial@slashdb.com"
  }
]
EOF

POST request with two parameter sets in XML body

curl -v -X POST "https://demo.slashdb.com/query/add-new-customer.xml" \
-d @- << EOF
<parameters>
  <param_set>
    <FirstName>John</FirstName>
    <LastName>Tutorial</LastName>
    <Phone>123 456 789</Phone>
    <City>New York</City>
    <State>NY</State>
    <Email>john.tutorial@slashdb.com</Email>
  </param_set>
  <param_set>
    <FirstName>Monica</FirstName>
    <LastName>Tutorial</LastName>
    <Phone>222 222 222</Phone>
    <City>New York</City>
    <State>NY</State>
    <Email>john.tutorial@slashdb.com</Email>
  </param_set>
</parameters>
EOF

Errors

When execution of the query fails the explanation will be returned in the header warning and response body in format matching to the request.

Examples:

Header "warning"

warning: (sqlite3.OperationalError) no such column: ar.NotAName

JSON error output

{
    "url_template": "/query/songs-by-genre/genre/{genre}.json",
    "http_code": 500,
    "description": "(sqlite3.OperationalError) no such column: ar.NotAName"
}

CSV error output

url_template,http_code,description
/query/songs-by-genre/genre/{genre}.csv,500,(sqlite3.OperationalError) no such column: ar.NotAName

XML error output

<?xml version="1.0" encoding="utf-8"?>
<SDBTest>
    <http_code>500</http_code>
    <description>(sqlite3.OperationalError) no such column: ar.NotAName</description>
    <url_template>/query/songs-by-genre/genre/{genre}.xml</url_template>
</SDBTest>

---

Query Result

Result rows can be presented in JSON, XML and CSV.

Examples:

/query/customers-in-city/city/Paris.json - SQL Pass-Thru query result in JSON.

In cases when you need only certain columns from your query result you can add a segment to the URL with comma separated names of columns.

Usage:

/query/[query_id]/[columnA],[columnB],[columnC]

Value Type: comma separated list of valid column names

Examples:

/query/customers-in-city/city/Paris/FirstName,LastName.json - the output will contain only columns FirstName and LastName (State and Phone were excluded).

---