Versions Compared

Old Version 12

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

Macro behaviour w.r.t. Create, Insert, Update, or Delete (CRUD) statements given as SQL statements, or in scripts attached to a page
Info
titleHow 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:

Database
type
Read-only userRead-write user
Points to note

PostgreSQL

MySQL

(tick)(error)

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.

PostgreSQL(error)(tick)

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

  • Allows SQL statements or scripts in read-only mode only.
  • Error messages are displayed if
CRUD
  • Create, Insert, Update, or Delete SQL statements are given in editor or in a script attached to a page.
The
  • This is because the database itself does not allow
CRUD
  • . execution of such statements as the logged in user has read-only privileges.
MySQL(error)(tick)

Microsoft SQL Server

(tick)(error)

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.

(error)(tick)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.

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:

...

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

are

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.

 This field is mandatory.


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. 
Selecting
  • Select a value from the list
overrides
  • to override the default. 
Database should
  • 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
Requires that the
  • 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