Although Data Discovery covers most use cases there are situations in which we need more flexibility of pure SQL. Using SlashDB we can define a required SQL and make it conveniently available in REST API. We call this feature the SQL Pass-Thru.
Let's have a look how to create and use a defined query.
First, we log in with administrative privileges which allows us to add a new query.
The admin creates a new query called salary-history. The SQL has a placeholder :surname which allows to parameterize the query.
Next, we learn that only privileged uses are allowed to execute the query.
Query Definitions list
Administrators manage query Query Definitions and permissions via Configure > Queries from the main menu.
The Query Definitions page provides a list all of queries setup in SlashDB.
The button allows to run a query.
The button allows users to edit query details.
This column lists the name of the queries.
This column provides a quick description of the query.
This column contains a database in which the query will execute.
XML and JSON buttons
The and icons will return JSON and XML representation of the query definition respectively.
Use these endpoints for 3rd party application to manage query definitions programmatically.
The Search field allows users to quickly find queries already setup in SlashDB. To conduct a search, input the search terms in the field and the list will automatically filter down to the matching results. The search engine will search the Query ID, Description and Database columns.
Adding a new query
1. To add a new query, click the button. The screen below will appear.
2. Fill in the Query ID field to give the query name, and select database against which the query will run in in the Database drop-down menu.
3. Fill in the remaining fields on the screen.
Hover your mouse cursor over the icon to learn more about the field's purpose and configuration tips.
4. Fields Columns and SQL Statement are the two most important fields in Query definitions. Columns contains a comma separated a list of columns to be selected in the query. The SQL Statement is the literal SQL statement with some SlashDB specific syntax. Hover over the icon for more info.
Executing a query
1. Click the play button next to desired query to launch it.
A panel will appear to the right of the query list.
2. Fill-in any paramter fields.
3. Click Execute to test the query.
Results will appear at the bottom of the screen.
If the resulting query returns more than the default amount of rows, you can load more row by pressing Load more.
4. Alternative representations.
Links to the corresponding resource types (i.e. HTML, JSON, CVS, XML and XSD).
Click on those to view results in separate window.
When the query are empty or an error has occurred, a notification explaining what went wrong will appear below the query definition list.