Versions Compared

Old Version 3

changes.mady.by.user Smita Nair

Saved on

compared with

New Version Current

changes.mady.by.user Nisha Shantilal

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

This page provides all the relevant information required to customize and use and customize the app 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. Statements can range from data manipulation language (DML), data definition language (DDL), data control language (DCL), and transaction control language (TCL) statements.

Some databases support read-only SQL statements. The SQL Query macro uses this support, if applicable, with such databases. 

...

attached 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
titleHow a profile affects macro behavior:

The SQL Query macro behaves as follows depending on the chosen database and the database privileges the user (configured in the profile) holds:

DatabasePoints to note

PostgreSQL

MySQL

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.

  • Allows SQL statements or scripts in read-only mode only.
  • 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.

Microsoft SQL Server

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: 

  • the database reflects the operations performed, and, 
  • the macro executes a rollback that reverts the database to its previous state.

Thus, Confluence administrators must make sure to provide credentials of a user with read-only privileges configured in the database.

Anchor
sqlCld_macroEditorComponents
sqlCld_macroEditorComponents

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 and view any messages returned by the database. There are two tabs in this panel as follows:
    • Data source tab: The parameters on this tab are mostly mandatory 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 attached 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 on this tab changes 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 retrieved through the specified SQL statement or script. Click the cog icon (Image Added) 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 tabMacro editor labelDefault valueDescriptionMacro parameter
Main panelData 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 attachmentOff

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:

  • Space: Specify the space from which the SQL script is to be accessed. This field is mandatory.
  • Page: Specify the page in the specified space that contains the required SQL script file as an attachment. This field is mandatory.
  • Attachment: Select the SQL script file to be executed. This field is mandatory.
  • File encoding: Select the encoding of the SQL script file if different from the system default handling. By default, UTF-8 is selected. This parameter is optional.
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 > RuntimeTransaction 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:

  • Read Committed 
  • Read Uncommitted
  • Repeatable read
  • Serializable
  • None

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. 

Note
  • The administrator sets the default transaction isolation level in Global configuration. If a value is provided in Global configuration, the parameter displays Default and the value in round brackets as the default value for the macro. 
  • Select a value from the list to override the default. 
  • The selected database must support the selected transaction isolation level.


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:

  • 250
  • 500
  • 1000
  • 2500
  • 5000
  • 10000
  • 25000
Note
  • The target database driver must support the remote query timeout server configuration option (Maximum number of seconds for query to run).
  • 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 Default and the value in round brackets as the default value for the macro. 
  • Select a value from the list to override the default.


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:

  • 1
  • 5
  • 10
  • 30
  • 60
  • 120


Note
  • The administrator sets the maximum query time in Global configuration. If a value is provided in Global configuration, the parameter displays Default and the value in round brackets as the default value for the macro. 
  • Select a value from the list to override the default. 


queryTimeout
Stop encoding of HTML charactersoff

If the SQL is producing HTML, this parameter needs to be enabled (On). 

Note

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 Macro security in SQL Configuration. The restrictions can be configured for the parameter disableAntiXss to trusted users and groups in trusted spaces. It may be necessary for queries that produce HTML constructs.


disableAntiXss
Settings > RenderingTable ID
Enter an ID for the table to be used in chart macros, JavaScript, and custom styles.id
Display data filterOffEnable 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 columnOffEnable this option to display numbers beside each row in a separate column that is shown as the first column of the table.autoNumber
Enable paginationOn

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. 

Note
  • The administrator sets the maximum number of rows to be processed in Global configuration.
  • Select a value from the list to override the default. 


rowsPerPage
Show error if there are no rowsOffEnable 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 SQLOffEnable this option to display the SQL statement or script used in a box below the table.showSQL

Helpful resources