Atlassian uses cookies to improve your browsing experience, perform analytics and research, and conduct advertising. Accept all cookies to indicate that you agree to our use of cookies on your device. Atlassian cookies and tracking notice, (opens new window)
Teams
, (opens new window)
SQL for Confluence
Results will update as you type.
  • SQL for Confluence
  • _InclusionsLibrary
  • _Archive
    • User's Guide
    • Administrator's Guide
    • SQL Macro
    • SQL-file Macro
    • SQL-query Macro
    You‘re viewing this with anonymous access, so some content might be blocked.
    /
    SQL Macro

    SQL Macro

    Jun 12, 2018

    On this page

    Parameters | Examples

    Parameters

    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

    dataSource

    • may be restricted
    		(tick)
    		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.

    output
    		htmlOutput format

    Determines how the output is formatted:

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

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

    • ^attachment - Data is read from an attachment to 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.
    heading
    		1Number of heading rowsSpecify heading=0 to not show any heading lines. Heading rows do not participate in sorting.
    border
    		normalTable border widthBorder width in pixels.
    width
    		100%Table widthWidth in pixels or %.
    rowOrientation
    		verticalDisplay rows vertically or horizontallyNote that most of the styling, formatting, sorting, auto, and similar parameters only apply to the vertical orientation setting.
    table
    		trueShow results as a tableFor example, false can be used to produce single values that you want to include in text.
    showSql
    		falseShow SQLDisplays SQL in a code macro.
    showUpdateCount
    		falseShow number of rows updatedDetermines whether or not to show the number of rows updated as a result of the update SQL operation.
    macros
    		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.
    expandArray
    		trueExpand array fieldsFor vertical row orientation, array fields can be expanded to one entry per line.
    autoCommit
    		trueAuto commit SQL statementsWikipedia:Autocommit or see your database documentation.
    columnLabel
    		falseUse database column labelsChoose whether to use column name or column label for a row header.
    showWiki
    		falseShow generated wiki markupWhen output is wiki based, choose this option to show the generated wiki markup.
    escape
    		false

    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.

    convertNull
    		trueConvert null fields to blankOtherwise, null appears in the column.
    noDataError
    		falseShow error if there are no rowsA result set 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).
    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 = ?

    transactionIsolation
    		READ_COMMITTEDTransaction isolation level

    Wikipedia: Isolation. See your database documentation.

    • READ_COMMITTED
    • READ_UNCOMMITTED
    • REPEATABLE_READ
    • SERIALIZABLE
    • NONE
    showSqlOptions

    		Options for showing SQL codeSince 6.4. A comma separated list of code or code-pro (/wiki/spaces/CODE/pages/67666007) 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.
    limit
    		no limitMaximum number of rows to displaySystem administrator may have configured a limit.
    queryTimeout
    		no limitMaximum number of seconds for query to runSystem administrator may have configured a limit. Since 5.1. Requires database/JDBC enablement.
    disableAntiXss
    		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.
    encoding
    		system defaultFile encoding

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

    Common table parameters

    The following parameters are part of our common table capabilities that are available to many macros that produce or modify tables.

    (info) Click a column heading to toggle the sorting of that column.

    Examples

    {sql:dataSource=datamartDS}
select * from sales
{sql}
    Using parameter markers
    {sql:dataSource=TestDS|p1=%test%|showsql=true}
select * from test01 where a1 like ?
{sql}

    , multiple selections available, Use left or right arrow keys to navigate selected items
    table_sorter_macro

    Need support? Create a request with our support team.

    Copyright © 2005 - 2025 Appfire | All rights reserved.

    {"serverDuration": 15, "requestCorrelationId": "7c5e87fbcbc04cd7b1939f5c3e294a26"}