SQL Pass-thru

SQL Pass-thru enables users to execute custom-defined queries. It is accessible to all users from the Run Queries link on the main menu.

Queries can be executed in the browser, or using the REST API.

Info

To create and manage queries, see SQL Pass-thru configuration.

Important

Any user can access the query list on the Run Queries page, but the list will only display queries for which they have have execute privileges. Additionally, they will need a database mapping to execute queries.

List of Queries

This page provides a list of all the queries in a SlashDB instance that may be executed. It is accessed from the main Menu (Run Queries) or from the URL /query.

The list can be searched using the Search field above the list or sorted by clicking on column headers. The Select Database ID dropdown filters queries by the database they are associated with.

The Query ID is a unique identifier for the query which is used when making HTTP requests, e.g. these endpoints:

/query/songs-by-genre/genre/Rock - executes the songs-by-genre query with parameter genre = Rock
/querydef/songs-by-genre - open the songs-by-genre query for editing

Each query in the list also has:

a user-defined Description
the name of the Database on which the query runs
any Parameters required by the query
allowed HTTP Methods for executing the query

Info

The list can also be viewed in JSON or XML format by clicking the buttons in the top right corner.

Actions

To view or execute a query, click the Play icon in the Action column.

Executing Queries in the Browser

From the Run Queries list, click on the Play icon or the query ID of the query you wish to execute. Results will appear in the output pane.

Query Execution Actions

There are several buttons in the top right corner of the screen.

The Close button will return to the query list without saving.

Users with privileges to edit queries will have the Edit button available. This will take you to the Query Studio.

The format buttons let you execute the query in JSON, XML, or CSV format.

Run a Query

To execute a query, fill in the parameters if needed, then:

click on the Play button to see results in the output pane:
or, hover on the Play button, and click on the JSON, XML, or CSV format:

or, press the Enter key when focused on a parameter field to run the query

Modifiers

Modifiers let you set options which will affect the query output. The tooltip icons provide information about some of the options.

For example, you can set a limit or offset, transpose the output, or get a count of all rows.

Details about each of these options are available here.

Parameters

Some queries may accept parameters, which allow you to execute queries with parametrized SQL. When a parameter is defined in the query, a new tab will appear in the pane on the left hand side labeled Parameters.

You can set parameter values by entering values into the fields. Values may be set to NULL by toggling the NULL button on the parameter field. On execution, the query will be executed with the values you provided.

Info

For queries using the GET or DELETE methods, parameters will be passed to the query directly in the URL as parameter/value segments. For queries that use PUT or POST methods, parameters may also be passed in request bodies.

Learn more about creating parameters for queries.

Errors

Any errors encountered while executing the statement error are shown in the output pane.

Executing Queries with REST API

Each SQL Pass-thru query has an endpoint, which can be used to run it using an HTTP request.

The request must be sent using the same HTTP method as the query is configured to accept.

Info

Queries can return data in JSON, XML, or CSV formats.

Parameters that accept parameters may be configured to accept them in the URL as parameter/value segments, or they may be sent in the request body for PUT or POST methods. A query's parameters can be seen in the Run Queries list.

GET or DELETE

Queries can be executed using GET or DELETE HTTP methods. When running queries that accept parameters, they are provided in URL segments as parameter/value segments.

Info

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 URL query string parameter (modifier).

Examples

GET request for query sales-by-year:

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

GET request for query customer-in-city with parameter city = "Paris":

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

GET request for query customer-in-city with parameter city = NULL:

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

PUT or POST

Queries can be executed using PUT or POST HTTP methods. When running queries that accept parameters, they are provided either in URL segments or in the request body in the same format as the request format (JSON, XML, CSV).

Examples

POST request for query add-new-customer with parameters given 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 a PUT or POST request body. This will execute the query multiple times, each time with a different set parameters. All SQL statements are executed in a single transaction and changes are rolled back if any errors occur.

Format

JSON request body:

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

XML request body:

Info

XML parameter sets must be wrapped in <parameters> and <param_set> tags. These tags can be changed in the INI settings.

<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>

CSV request body:

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

Examples

POST request for query add-new-customer 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 for query add-new-customer 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

Query Results

Query results can be returned in JSON, XML or CSV formats.

Use the appropriate extension at the end of the query URL, or send the appropriate HTTP Accept header.

Info

You can send a request body in one format and receive the response in another by using the Content-Type and Accept HTTP headers instead of the format extension.

Examples

results for customers-in-city query in JSON:

/query/customers-in-city/city/Paris.json

Info

If you need only certain columns from the query result you can add an extra segment to the URL with a comma separated list of columns:

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

results for customers-in-city query with only FirstName and LastName columns:

/query/customers-in-city/city/Paris/FirstName,LastName.json

Errors

If execution of a query fails, the error message will be returned in the HTTP response Warning header and in the response body in the same format as the request was sent.

Examples

Try to run customers-in-city query with an invalid column name:

/query/customers-in-city/city/Paris/FirstName,BadColumn.json

HTTP 500 response returned with Warning header:

Warning: (sqlite3.OperationalError) no such column: BadColumn

JSON body:

{
    "url_template": "/query/customers-in-city/city/{city}.xml",
    "http_code": 500,
    "description": "(sqlite3.OperationalError) no such column: BadColumn"
}

XML body:

<?xml version="1.0" encoding="utf-8"?>
<SlashDB>
    <http_code>500</http_code>
    <description>(sqlite3.OperationalError) no such column: BadColumn</description>
    <url_template>/query/customers-in-city/city/{city}.xml</url_template>
</SlashDB>

CSV body:

url_template,http_code,description
/query/customers-in-city/city/{city}.xml,500,(sqlite3.OperationalError) no such column: BadColumn