SQL Pass-thru enables users to execute custom defined queries. This screen is different from SQL Pass-thru configuration in that it is accessible to all users and only query execution is possible.
The Queries list provides a list of queries defined in SlashDB. Only queries for which a logged-in user has execute permission will be listed.
The button will execute the selected query.
This column contains the name of the query.
This column provides a quick description of the query.
This column shows in which database the query will execute.
The Search field allows users to search all queries setup in SlashDB. To conduct a search, input the search terms in the field and the queries list will automatically filter down to the matching results. Columns Query ID, Description and Database will be searched.
The JSON button located in the top right corner of the page works just like the buttons located in the Database list. The button allows users to view the query list in the JSON format. To return to HTML view, click the browser's back button.
Once the play button is clicked the query box will appear and allow users to execute the query. If you query accepts parameters, a text-box form will also display.
Click the Execute button to send the query for execution. Alternatively, you can click on a generated URL to get results in a new window in desired format.
SQL Pass-Thru queries accept parameters if SQL in query definition contains placeholders.
/query/customers-in-city/city/Paris.json - this query takes one parameter named "city" and in this example "city" has value "Paris".
Query results will appear at the bottom of the screen when a query is successfully executed.
Result rows can be presented in XML (with XSD), JSON and CSV.
/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.
Value Type: comma separated list of valid column names
/query/customers-in-city/city/Paris/FirstName,LastName.json - the output will contain only columns FirstName and LastName (State and Phone were excluded).
url string substitution
This feature allows to replace part of the url with string defined in query parameter. Visit this documentation page for detailed explanation.
limit and offset
This feature allows to limit and skip rows of returned data. Visit Visit this documentation page for detailed explanation.
When using this query string argument in request the response will contain also additional header SlashDB-All-Record-Count that tells what's the total count of rows returned by the query. Useful when using limit and offset with.
Value type: (no value)
Default: (not used)
/query/customers-in-city/city/Paris.json?count - response contains header with total number of rows returned by query (header SlashDB-All-Record-Count: 2)
/query/customers-in-city/city/Paris.json?count&limit=1 - like above example but with additional limit=1, header SlashDB-All-Record-Count still is 2, even though we're using limit.
This feature allows to force showing empty tags.
Value type: boolean (True or False)
/query/customers-in-city/city/Paris.xml - not showing empty tags by default
/query/customers-in-city/city/Paris.xml?nil_visible=False - not showing empty tags
/query/customers-in-city/city/Paris.xml?nil_visible=True - showint empty tags
This feature allows to adjust XML output.
Value type: string
- adPersistXML - MS XML Persistance Format contains schema and Recordsets in single document
Default: (not used)
/query/customers-in-city/city/Paris.xml?xmlType=adPersistXML - rendering XML in Persistance Format.