Description

This macro has all the same parameters as the SQL Macro and behaves similarly in most cases. There are two primary reasons for using sql-query:

Some databases/JDBC drivers support read-only SQL statements. The sql-query macro attempts to use this support, if available. Otherwise, it behaves exactly like the sql macro. Read-only support means the database can optimize for read only statements to improve performance. For some databases (PostgreSQL for instance), the read-only aspect is enforced while others it is only a performance suggestion.
Using Macro Security for Confluence, administrators can configure different restrictions for use of the sql and sql-query macros. This provides greater flexibility allowing more people access to query without having access for other SQL operations. For instance, administrators can create read-only data sources using database access controls and only grant access to those specific data sources.

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:

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 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 body	Location 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	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	Determines whether or not to show the number of rows updated as a result of the update SQL 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	Otherwise, null appears 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 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.

Common table parameters

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

Click a column heading to toggle the sorting of that column.

Examples

{sql-query:dataSource=readOnlyDS|output=wiki}
select * from test
{sql-query}

SQL for Confluence

SQL-query Macro

On this page

Description

Parameters

Common SQL parameters

Common table parameters

Examples