---
title: "SQL Query macro - cloud"
canonical: "https://support.appfire.com/space/SQL/74320881/SQL%20Query%20macro%20-%20cloud"
format: markdown
---
> Macro (aura-html)

<span style="color: #000000">This page provides all the relevant information required to use and </span><span style="color: #000000">customize </span><span style="color: #000000">the macro in an efficient and optimal manner. </span><span style="color: #000000">The </span><span style="color: #000000">*SQL Query*</span><span style="color: #000000"> macro supports read-only SQL statements and displays values from the given SQL statements or scripts attached to Confluence pages. </span>

<span style="color: #000000">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 </span><span style="color: #000000">[in this article](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74323863)</span><span style="color: #000000"> about profile configuration.</span>

> ℹ️ ### How a profile affects macro behavior:
> ℹ️ 
> ℹ️ <span style="color: #000000">The </span><span style="color: #000000">*SQL Query*</span><span style="color: #000000"> macro behaves as follows, depending on the chosen database and the database privileges the user (configured in the profile) holds.</span>

| <span style="color: #000000">Database</span> | Points to note |
| --- | --- |
| <span style="color: #000000">PostgreSQL</span><br><span style="color: #000000">MySQL</span> | <span style="color: #000000">Macro creates a read-only session for the database connection established through the specified profile. Given SQL statement(s) or the script file is executed, and results are displayed in a table in the Preview panel of the macro editor.</span><br>- <span style="color: #000000">Allows SQL statements or scripts in read-only mode only.</span>
- <span style="color: #000000">Error messages are displayed if Create, Insert, Update, or Delete SQL statements are given in editor or in a script attached to a page. This is because the database itself does not allow. execution of such statements as the logged in user has read-only privileges.</span> |
| <span style="color: #000000">Microsoft SQL Server</span> | <span style="color: #000000">Given SQL statement(s) or the script file is executed, and results are displayed in a table in the Preview panel of the macro editor. Once the statements are run: </span><br>- <span style="color: #000000">the database reflects the operations performed, and, </span>
- <span style="color: #000000">the macro executes a rollback that reverts the database to its previous state. </span><br><span style="color: #000000">Thus, Confluence administrators must make sure to provide credentials of a </span><span style="color: #000000">*user with read-only privilege*</span><span style="color: #000000">s configured in the database.</span> |

> Macro (anchor)



## <span style="color: #000000">Macro editor components</span>

<span style="color: #000000">Use the macro editor to provide SQL statements or use SQL scripts attached to Confluence pages to retrieve data using the configured profile. </span><span style="color: #000000">The main components of the editor are:</span>

- <u><span style="color: #000000">Data source panel</span></u><span style="color: #000000">: Specify the data source and view any messages returned by the database. There are two tabs in this panel as follows:</span>
  - <span style="color: #000000">Data source tab: </span><span style="color: #000000">The</span><span style="color: #000000"> parameters on this tab are mostly mandatory </span><span style="color: #000000">to</span><span style="color: #000000"> connect to your database and run the given query/script. Select a </span><span style="color: #000000">*Data source profile*</span><span style="color: #000000">, and then, either enter the relevant SQL statement(s) in the SQL statement section, or enable the </span><span style="color: #000000">*Select SQL as an attachment*</span><span style="color: #000000"> option to specify the SQL script attached to a Confluence page. Contact your administrator to know about the profiles configured in your instance.</span>
  - <span style="color: #000000">Message tab: This tab displays the information about successful query/script execution or any error messages. The text </span><span style="color: #000000">on</span><span style="color: #000000"> this tab changes to red if any errors were encountered, or turns green if the query execution was successful.</span>
- <span style="color: #000000">After providing the data source, click </span><span style="color: #000000">**Run**</span><span style="color: #000000"> to </span><span style="color: #000000">generate</span><span style="color: #000000"> a table with the required data retrieved through the specified SQL statement or script. Click the cog icon (</span>> Macro (inline-media-image)

<span style="color: #000000">) to view the Settings panel for other parameters to further define SQL query/script execution and format the generated table.</span>
- <u><span style="color: #000000">Settings panel</span></u><span style="color: #000000">: 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.</span>
- <u><span style="color: #000000">Preview panel</span></u><span style="color: #000000">: See the data retrieved from the database and apply styles to the generated table here. </span>

## <span style="color: #000000">Parameters</span>

<span style="color: #000000">The following parameters are available in the macro editor:</span>

| Macro editor tab | <span style="color: #000000">Macro editor label</span> | <span style="color: #000000">Default value</span> | <span style="color: #000000">Description</span> | <span style="color: #000000">Macro parameter</span> |
| --- | --- | --- | --- | --- |
| <span style="color: #000000">Main panel</span> | <span style="color: #000000">Data source profile</span> |  | <span style="color: #000000">Select a profile from the list of pre-configured profiles. </span><span style="color: #000000">*This field is mandatory*</span><span style="color: #000000">.</span><br><span style="color: #000000">A profile contains the required information to connect to the preferred database with given user credentials. This ensures that sensitive information </span><span style="color: #000000">is secure </span><span style="color: #000000">and is not visible to all. Contact your administrator to know about the profiles available for your use. Refer to the </span><span style="color: #000000">[Configuration documentation](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74323863)</span><span style="color: #000000"> to </span><span style="color: #000000">learn</span><span style="color: #000000"> more about profile configuration.</span> | <span style="color: #000000">dataSource</span> |
| <span style="color: #000000">Select SQL script attachment</span> | <span style="color: #000000">Off</span> | <span style="color: #000000">Enable this option to provide an SQL script attached to the same or different space/page.</span><br><span style="color: #000000">If enabled, the following drop-down lists are displayed:</span><br>- <span style="color: #000000">*Space*</span><span style="color: #000000">: Specify the space from which the SQL script is to be accessed. </span><span style="color: #000000">*This field is mandatory*</span><span style="color: #000000">.</span>
- <span style="color: #000000">*Page*</span><span style="color: #000000">: Specify the page in the specified space that contains the required SQL script file as an attachment. </span><span style="color: #000000">*This field is mandatory*</span><span style="color: #000000">.</span>
- <span style="color: #000000">*Attachment*</span><span style="color: #000000">: Select the SQL script file to be executed. </span><span style="color: #000000">*This field is mandatory*</span><span style="color: #000000">.</span>
- <span style="color: #000000">*File encoding*</span><span style="color: #000000">: Select the encoding of the SQL script file if different from the system default handling. By default, </span><span style="color: #000000">*UTF-8*</span><span style="color: #000000"> is selected. This parameter is optional.</span> | <span style="color: #000000">useAttachment</span> |
| <span style="color: #000000">SQL statement</span> |  | <span style="color: #000000">Enter the required SQL statement to be executed to retrieve the relevant information. </span><span style="color: #000000">*This field is mandatory*</span><span style="color: #000000">.</span><br><span style="color: #000000">If </span><span style="color: #000000">*Select SQL as an attachment*</span><span style="color: #000000"> 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.</span> |  |
| ***Settings > Runtime*** | <span style="color: #000000">Transaction isolation level</span> | <span style="color: #000000">*None*</span><span style="color: #000000"> </span><br><span style="color: #000000">Or,</span><br><span style="color: #000000">*Default *</span><span style="color: #000000">(<value specified either in Global configuration or profile configuration>)</span> | <span style="color: #000000">Specify the default level to be used for transactions. The following options are available for selection:</span><br>- <span style="color: #000000">*Read Committed *</span>
- <span style="color: #000000">*Read Uncommitted*</span>
- <span style="color: #000000">*Repeatable read*</span>
- <span style="color: #000000">*Serializable*</span>
- <span style="color: #000000">*None*</span><br><span style="color: #000000">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 </span><span style="color: #222222">[Wikipedia: Isolation](http://en.wikipedia.org/wiki/Isolation_(database_systems))</span><span style="color: #091e42"> </span><span style="color: #000000">or see your database documentation. </span><br>> ⚠️ - <span style="color: #000000">The administrator sets the default transaction isolation level in Global configuration. If a value is provided in Global configuration, the parameter displays </span><span style="color: #000000">*Default*</span><span style="color: #000000"> and the value in round brackets as the default value for the macro. </span>
> ⚠️ - <span style="color: #000000">Select a value from the list to override the default. </span>
> ⚠️ - <span style="color: #000000">The selected database must support the selected transaction isolation level.</span> | <span style="color: #000000">transactionIsolation</span> |
| <span style="color: #000000">Maximum number of rows to retrieve and display</span> | <span style="color: #000000">*250*</span><br><span style="color: #000000">Or,</span><br><span style="color: #000000">*Default *</span><span style="color: #000000">(<value specified either in Global configuration or profile configuration>)</span> | <span style="color: #000000">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. </span><br><span style="color: #000000">The following options are available from a selection list:</span><br>- <span style="color: #000000">*250*</span>
- <span style="color: #000000">*500*</span>
- <span style="color: #000000">*1000*</span>
- <span style="color: #000000">*2500*</span>
- <span style="color: #000000">*5000*</span>
- <span style="color: #000000">*10000*</span>
- <span style="color: #000000">*25000*</span><br>> ⚠️ - <span style="color: #000000">The target database driver must support the remote query timeout server configuration option (</span><span style="color: #000000">*Maximum number of seconds for query to run*</span><span style="color: #000000">).</span>
> ⚠️ - <span style="color: #000000">The administrator sets the maximum number of rows to be processed in Global configuration. If a value is provided in Global configuration, the parameter displays </span><span style="color: #000000">*Default*</span><span style="color: #000000"> and the value in round brackets as the default value for the macro. </span>
> ⚠️ - <span style="color: #000000">Select a value from the list to override the default.</span> | <span style="color: #000000">limit</span> |
| <span style="color: #000000">Maximum number of seconds for query to run</span> | <span style="color: #000000">*120*</span><br><span style="color: #000000">Or,</span><br><span style="color: #000000">*Default *</span><span style="color: #000000">(<value specified either in Global configuration or profile configuration>)</span> | <span style="color: #000000">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.</span><br><span style="color: #000000">The following options are available from a selection list:</span><br>- <span style="color: #000000">*1*</span>
- <span style="color: #000000">*5*</span>
- <span style="color: #000000">*10*</span>
- <span style="color: #000000">*30*</span>
- <span style="color: #000000">*60*</span>
- <span style="color: #000000">*120*</span><br>> ⚠️ - <span style="color: #000000">The administrator sets the maximum query time in Global configuration. If a value is provided in Global configuration, the parameter displays </span><span style="color: #000000">*Default*</span><span style="color: #000000"> and the value in round brackets as the default value for the macro. </span>
> ⚠️ - <span style="color: #000000">Select a value from the list to override the default. </span> | <span style="color: #000000">queryTimeout</span> |
| <span style="color: #000000">Stop encoding of HTML characters</span> | off | <span style="color: #000000">If the SQL is producing HTML, this parameter needs to be enabled (On). </span><br>> ⚠️ <span style="color: #000000">The administrator can control access to the parameter due to security considerations. The restriction is set via global app configuration or by more fine-grained control using </span><span style="color: #000000">*Macro security*</span><span style="color: #000000"> in SQL Configuration. The restrictions can be configured for the parameter </span><span style="color: #000000">*disableAntiXss*</span><span style="color: #000000"> to trusted users and groups in trusted spaces. It may be necessary for queries that produce HTML constructs.</span> | <span style="color: #000000">disableAntiXss</span> |
| ***Settings > Rendering*** | <span style="color: #000000">Table ID</span> |  | <span style="color: #000000">Enter an ID for the table to be used in chart macros, JavaScript, and custom styles.</span> | id |
| <span style="color: #000000">Display data filter</span> | <span style="color: #000000">Off</span> | <span style="color: #000000">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.</span> | <span style="color: #000000">displayDataFilter</span> |
| <span style="color: #000000">Insert auto number column</span> | <span style="color: #000000">Off</span> | <span style="color: #000000">Enable this option to display numbers beside each row in a separate column that is shown as the first column of the table.</span> | <span style="color: #000000">autoNumber</span> |
| <span style="color: #000000">Enable pagination</span> | <span style="color: #000000">On</span> | <span style="color: #000000">Disable this option to display the </span><span style="color: #000000">retrieved</span><span style="color: #000000"> data in a continuous, single table. </span><br><span style="color: #000000">If enabled, this option displays a pagination panel beneath the table based on the values provided in Global configuration or in the macro's </span><span style="color: #000000">*Number of rows to be displayed on one page*</span><span style="color: #000000"> parameter. </span> | <span style="color: #000000">pagination</span> |
| <span style="color: #000000">Number of rows to be displayed on one page</span> |  | <span style="color: #000000">Select the maximum number of rows to be displayed on a page. </span><br>> ⚠️ - <span style="color: #000000">The administrator sets the maximum number of rows to be processed in Global configuration.</span>
> ⚠️ - <span style="color: #000000">Select a value from the list to override the default. </span> | <span style="color: #000000">rowsPerPage</span> |
| <span style="color: #000000">Show error if there are no rows</span> | <span style="color: #000000">Off</span> | <span style="color: #000000">Enable this option to display an error message if the query or script does not retrieve any from the database.</span> | <span style="color: #000000">noDataError</span> |
| <span style="color: #000000">Text to display when there are no rows</span> |  | <span style="color: #000000">Enter the text to be displayed if the query or script does not retrieve any from the database.</span> | <span style="color: #000000">noDataMessage</span> |
| <span style="color: #000000">Show SQL</span> | <span style="color: #000000">Off</span> | <span style="color: #000000">Enable this option to display the SQL statement or script used in a box below the table.</span> | <span style="color: #000000">showSQL</span> |

# <span style="color: #000000">Helpful resources</span>

- <span style="color: #000000">[Administrator guide](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74324707)</span><span style="color: #000000"> to know more about app configuration</span>
- <span style="color: #000000">[Release notes](https://appfire.atlassian.net/wiki/spaces/SQL/pages/74320266)</span><span style="color: #000000"> to understand app features per version</span>