Database Direct is an advanced feature of the Hornbill Platform which allows you to query the database that underpins your Hornbill instance using Structured Query Language (SQL). Every Hornbill instance incorporates this feature however it is not exposed by default. This feature is intended for advanced users only.
Understanding the Risks when using Database Direct
While Database Direct offers a significant amount of flexibility to those skilled in SQL, there are risks when interacting directly with a database. The seasoned database administrators reading this will be well aware of those risks and appreciate that direct interaction circumvents much of the protection offered by the web services API's that would typically be selecting, inserting, and updating the data via the user interface.
It is important you understand the potential risks of making changes directly to the database and the disruption they may cause to your service if an inaccurate query was executed via database direct.
PLEASE NOTE: If any SQL executed via Database Direct effects the integrity of your data, the resulting data recovery exercise will not be covered by your Hornbill Success Plan and will be subject to an additional charge.
Enabling Database Direct
The visibility of Database Direct is governed by a system setting security.database.allowSqlQueryOperation which is found via Home > System > Settings > Advanced. Any user wishing to use database direct must be assigned the "Super User Role".
Once the application setting has been enabled, it may be necessary to refresh your browser to allow this change in system setting to be acknowledged. If the association of a role is also required, it will be necessary for the user to log out and then log in again to ensure the users session is aware of the recent association of the Super User role.
Using Database Direct
Once enabled, Database Direct can be found via Home > System > Data > Database Direct. The key features are outlined below.
- Query Editor - type your SQL query here
- Data Format menu - choose whether raw data values are displayed (e.g. boolean flags will appear as 0 or 1) or if data formatters are to be applied (e.g. No or Yes). Not all data is subject to data formating.
- Clear - Clears the current query in the query pane and result set.
- Execute - Execute the query that exists in the query pane
- Execute Selected Text - if more than one query exists in the query pane, highlighting the query and using this button will execute the highlighted query.
- Table filter - filter the list of Hornbill tables by typing in text.
- Table List - Shows all the tables available based on the applications you have installed on your instance.
- Manage Saved Queries - If you have saved any queries, you can manage them here.
- Show/Hide Query Editor - Toggle whether the query editor is displayed or not. This may be useful when examining larger result sets.
- Show/Hide Table List - Toggle whether the table list is displayed or not. This may be useful when examining larger result sets.
- Save Query - Saves the current content of the Query Editor
- Query Limit - all queries executed in database direct are subject to a limit, adjust the number of records returned here.
- Result Quick Filter - Filter the results set currently displayed.
- Column Display - When examining your result set, choose to display friendly column display names or raw column database names.
- Export - export your result set to a variety of formats including CSV.
- Result Set - The set of results returned by the last query you executed.