Page Comparison

This page provides all the relevant information required to 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 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

title	How database and user type in 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:

Macro behaviour w.r.t. Create, Insert, Update, or Delete (CRUD) statements given as SQL statements, or in scripts attached to a pagePostgreSQL

Macro creates a read-only session for the database connection established through the specified profile.

CRUD The CRUD

Database type

Read-only user

Read-write user

Points 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.

MySQL

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.

Macro executes the given SQL statement or attached script file and Once the statements are run:

the database reflects the operations performed

. Once the statements are run,

, and,
the macro

then executes

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.

...

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 are 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:

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. 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. This field is mandatory.

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:

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. Selecting a value from the list overrides the default. Database should 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

Requires that the target database driver 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

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.

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 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

...

Versions Compared

Old Version 12

New Version 13

Key