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:

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 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.
script	macro body	Location 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.
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 are applicable to only 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 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	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	Use this parameter to avoid displaying null values in the column.
noDataError	false	Show error if there are no rows	A 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).
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	Default (~ no limit)	Maximum number of rows to display	System administrator can configure the limit.
queryTimeout	Default (~ no limit)	Maximum number of seconds for query to run	System administrator can configure the limit. Since 5.1. Requires database/JDBC enablement.
disableAntiXss	false	Stop encoding HTML characters	If 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 default	File encoding	Encoding for an external file if different from the system default handling. Example: UTF8.