This page provides all the relevant information required to customize and use and customize the macro in an efficient and optimal manner. The SQL Query macro supports read-only SQL statements and displays values from the given SQL statements or scripts (or both) on Confluence pages. Some databases support read-only SQL statements. The SQL Query macro uses this support, if applicable, with such databases.
| Note |
|---|
Read-only support means that the database can optimize for read-only statements. For some databases (PostgreSQL, for instance) the read-only condition is enforced while in others, it is a suggestion for improving performance. |
Macro editor components
The macro editor has a new look and feel compared to the server version and aims to provide as much information at a glance for better usabilityattached to Confluence pages.
The macro uses a profile to connect to the required database and retrieve the relevant information. Administrators configure profiles that contain sensitive information such as database and user connection details. Contact your administrator to learn about the profiles available in your instance. Read more in this article about profile configuration.
| Info | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||
The SQL Query macro behaves as follows depending on the chosen database and the database privileges the user (configured in the profile) holds:
|
Macro editor components
Use the macro editor to provide SQL statements or use SQL scripts attached to Confluence pages to retrieve data using the configured profile. The main components of the editor are:
- Data source panel: Specify the data source of the relevant database and view any messages generated due to errors or after successful query execution herereturned by the database. There are two tabs in this panel as follows:
- Data source tab: This parameters The parameters on this tab are mostly mandatory that to connect to your database and run the given query/script. Select a Data source profile, and then, either enter the relevant SQL statement(s) in the SQL statement section, or enable the Select SQL as an attachment option to specify the SQL script available on either the same or different space/pageattached to a Confluence page. Contact your administrator to know about the profiles configured in your instance.
- Message tab: This tab displays the information about successful query/script execution or any error messages. The text of on this tab changes color to red if any errors were encountered, or turns green if the query execution was successful.
- After providing the data source, click Run to generate a table with the required data generated retrieved through the specified SQL statement or script. Click the cog icon (
) to view the Settings panel for other parameters to further define SQL query/script execution and format the generated table. - Settings panel: Specify other parameters here to further define how the macro must handle the incoming data. The parameters available here are defined in the Parameters section below.
Preview panel: See the data retrieved from the database and apply styles to the generated table here.
Parameters
The following parameters are available in the macro editor:
| Macro editor tab | Macro editor label | Default value | Description | Macro parameter |
|---|---|---|---|---|
| Main panel | Data source profile | Select a profile from the list of pre-configured profiles. This field is mandatory. A profile contains the required information to connect to the |
preferred database with |
given user credentials. This ensures that sensitive information |
is secure and is not visible to all. Contact your administrator to know about the profiles available for your use. Refer to the Configuration documentation to |
learn more about profile configuration. | dataSource |
| Select SQL |
| script attachment | Off | Enable this option to provide an SQL script |
attached to the same or different space/page. If enabled, the following drop-down lists are displayed:
|
|
| useAttachment | |
| SQL statement | Enter the required SQL statement to be executed to retrieve the relevant information. This field is mandatory. If Select SQL as an attachment is enabled and the SQL script file details are provided, this parameter displays the file contents |
, but doesn't permit editing the attached file contents. | |||||
| Settings > Runtime | Transaction isolation level | None Or, Default (<value specified either in Global configuration or profile configuration>) | Specify the default level to be used for transactions. The following options are available for selection:
Most Database Management Systems (DBMS) offer a number of transaction isolation levels to control the degree of locking that occurs when selecting data. To know more about what you can do with each type, refer to Wikipedia: Isolation or see your database documentation.
|
|
|
| transactionIsolation | ||
| Maximum number of rows to retrieve and display | 250 Or, Default (<value specified either in Global configuration or profile configuration>) | Select the maximum number of rows to be processed and displayed. This setting prevents queries from using excessive resources that can result in a large number of rows. The following options are available from a selection list:
|
|
| limit | ||||
| Maximum number of seconds for query to run | 120 Or, Default (<value specified either in Global configuration or profile configuration>) | Select the time in seconds that a query can take before a forced timeout. This prevents queries that take too long, from impacting other users. The following options are available from a selection list:
| queryTimeout | ||
| Stop encoding of HTML characters | off | If the SQL is producing HTML, this parameter needs to be enabled (On).
| disableAntiXss | ||
| Settings > Rendering | Table ID | Enter an ID for the table to be used in chart macros, JavaScript, and custom styles. | id | ||
| Display data filter | Off | Enable this option to view drop-down lists with column values below each column header. Either, select or enter the value to filter the specific column. | displayDataFilter | ||
| Insert auto number column | Off | Enable this option to display numbers beside each row in a separate column that is shown as the first column of the table. | autoNumber | ||
| Enable pagination | On | Disable this option to display the |
retrieved data in a continuous, single table. If enabled, this option displays a pagination panel beneath the table based on the values provided in Global configuration or in the macro's Number of rows to be displayed on one page parameter. | pagination | ||||
| Number of rows to be displayed on one page | Select the maximum number of rows to be displayed on a page.
| rowsPerPage | |||
| Show error if there are no rows | Off | Enable this option to display an error message if the query or script does not retrieve any from the database. | noDataError | ||
| Text to display when there are no rows | Enter the text to be displayed if the query or script does not retrieve any from the database. | noDataMessage | |||
| Show SQL | Off | Enable this option to display the SQL statement or script used in a box below the table. | showSQL |
Helpful resources
- Administrator guide to know more about app configuration
- Release notes to understand app features per version