Working with SQL parameters

Common SQL parameters

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

Parameter	Required	Default	Macro Browser Label	Description

Parameter	Default	Macro Browser Label	Description
dataSource 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.
output	html	Output 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 might contain unintended wiki markup characters, set the escape parameter to 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 body	Location of SQL statements	Additional SQL statements can be added after the macro body sql 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. This parameter is not available for the SQL-file macro.
heading	1	Number of heading rows	Specify heading=0 to not show any heading lines. Heading rows do not participate in sorting.
border	normal	Table border width	Border width in pixels.
width	100%	Table width	Width in pixels or %.
rowOrientation	vertical	Display rows vertically or horizontally	Note that most of the styling, formatting, sorting, auto, and similar parameters only apply to the vertical orientation setting.
table	true	Show results as a table	For example, false can be used to produce single values that you want to include in text.
showSql	false	Show SQL	Displays SQL in a code macro.
showUpdateCount	false	Show number of rows updated	For update SQL, determines whether or not to show the number of rows updated as a result of the operation.
macros	false	Evaluate wiki markup macros	If requested, the body will be 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	true	Expand array fields	For vertical row orientation, array fields can be expanded to one entry per line.
autoCommit	true	Auto commit SQL statements	Wikipedia:Autocommit or see your database documentation.
columnLabel	false	Use database column labels	Choose whether to use column name or column label for a row header.
showWiki	false	Show generated wiki markup	When 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	true	Convert null fields to blank	Otherwise null will appear in the column.
noDataError	false	Show error if there are no rows	A 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 rows	Use @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_COMMITTED	Transaction isolation level	Wikipedia: Isolation. See your database documentation. READ_COMMITTED READ_UNCOMMITTED REPEATABLE_READ SERIALIZABLE NONE
showSqlOptions		Options for showing SQL code	Since 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.
limit	no limit	Maximum number of rows to display	System administrator may have configured a limit.
queryTimeout	no limit	Maximum number of seconds for query to run	System administrator may have configured a limit. Since 5.1. Requires database/JDBC enablement.
disableAntiXss	false	Stop encoding HTML characters	If the SQL is producing HTML, this parameter needs to be selected (true). User must be authorized (via global add-on configuration or by more fine grained control using Macro Security for Confluence) to use this option due to security considerations.
encoding	system default	File encoding	Encoding for an external file if different from the system default handling. Example: UTF8.