Working with SQL parameters - 9.x

Common SQL parameters

Some parameters may have values that are restricted by your administrator for security or operational reasons. The following parameters are available on all SQL macros:

ParameterRequiredDefaultMacro Browser LabelDescription


  • may be restricted
Data source name

Specify the data source name. It represents an application server defined data source (Pro Edition) or data source profile defined by your administrator.

htmlOutput format

Determines how the output is formatted:

  • HTML — Generates HTML from the ResultSet. Data is HTML encoded unless data encoding is disabled.
  • wiki — Generates and renders wiki markup from ResultSet. If the data contains unintended wiki markup characters, set the escape parameter as true.
  • XHTML (Pro Edition) — Generates and renders XHTML from ResultSet. Data is HTML encoded unless encoding has been disabled (disableAntiXss).
  • unrenderedWiki (Pro Edition) — Generates wiki markup from ResultSet, similar to wiki, without rendering. Wraps with a table-plus macro suitable for copying to another system.
macro bodyLocation of SQL statements

This parameter does not work with our SQL-file macro.

Additional SQL statements can be added after the SQL in macro body by specifying another location:

  • ^attachment — Data is read from an attachment into the current page.
  • page^attachment — Data is read from an attachment to the page name provided.
  • space:page^attachment — Data is read from an attachment to the page name provided in the space indicated.
  • #filename (Pro Edition) — Data is read from the file located in Confluence home directory/script/filename. Subdirectories can be specified.
  • global page template name (Pro Edition) — Data is read from a global page template.
  • space:page template name (Pro Edition) — Data is read from a space template.
1Number of heading rowsSpecify heading=0 to not show any heading lines. Heading rows do not participate in sorting.
normalTable border widthBorder width in pixels.
100%Table widthWidth in pixels or %.
verticalDisplay rows vertically or horizontallyNote that most of the styling, formatting, sorting, auto, and similar parameters are applicable to only the vertical orientation setting.
trueShow results as a tableFor example, false can be used to produce single values that you want to include in text.
falseShow SQLDisplays SQL in a code macro.
falseShow number of rows updatedFor update SQL, determines whether or not to show the number of rows updated as a result of the operation.
falseEvaluate  wiki markup macros If requested, the body is rendered to expand wiki markup macros. The rendered macros must produce valid SQL syntax. This is useful to run macros from Scripting for Confluence or similar that can produce SQL output.
trueExpand array fieldsFor vertical row orientation, array fields can be expanded to one entry per line.
trueAuto commit SQL statementsWikipedia:Autocommit or see your database documentation.
falseUse database column labelsChoose whether to use column name or column label for a row header.
falseShow generated wiki markupWhen output is wiki-based, choose this option to show the generated wiki markup.

Escape special wiki characters

When output is wiki-based, choose this option to escape special characters in wiki markup. Use this to prevent unintended wiki characters from interfering with table formatting.

trueConvert null fields to blankUse this parameter to avoid displaying null values in the column.
falseShow error if there are no rowsA ResultSet with no rows may indicate an error. Use this parameter to control what should happen in this case. A non-blank message must be available (noDataMessage).

Text to display when there are no rowsUse @default to show a default error message.

p1 through p10

Parameter marker

Values for SQL parameter markers identified by ? in SQL statements. On untrusted sites, prevent SQL injection attacks by using parameter markers. This in only necessary when the SQL statements are partially constructed from user input. See Wikipedia: SQL injection. Wiki markup SQL macros support an arbitrary number of parameter markers. An arbitrary number of parameter markers can be provided when using the wiki markup version of the SQL macro.

Example SQL with 2 parameter markers

select from example where name = ? or  department = ?

READ_COMMITTEDTransaction isolation level

Wikipedia: Isolation. See your database documentation.

  • NONE

Options for showing SQL codeSince 6.4. A comma-separated list of code or code-pro (Code Pro macro) parameters used when Show SQL is selected. This allows for customization of how the SQL code is shown. See How to improve the display of SQL source.
Default (~ no limit)Maximum number of rows to displaySystem administrator can configure the limit.
Default (~ no limit)Maximum number of seconds for query to runSystem administrator can configure the limit. Since 5.1. Requires database/JDBC enablement.
falseStop encoding HTML charactersIf the SQL is producing HTML, this parameter must be selected (true). User must be authorized (via global app configuration or with a more fine grained control using Macro Security for Confluence) to use this option due to security considerations.
system defaultFile encoding

Encoding for an external file if different from the system default handling. Example: UTF8.