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 (/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 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.

Parameter

Default

Macro Browser Label

Description

heading

1

Heading rows

Number of rows to be considered as heading rows. Heading rows do not participate in column sorting.

footing

0

Footing rows

Number of rows to be considered as footing rows. Footing rows do not participate in column sorting.

enableSorting

true

Enable column sorting

Set to false to disable sorting.

enableHighlighting

true

Enable row highlighting on mouse over

As the mouse moves over a table row, the row will be highlighted by default. Set to false to stop this behavior.

sortColumn

Auto sort column

The table can be auto sorted before it is displayed by any valid column name or number provided by this parameter. No auto sorting will be done if this value is not provided or is invalid. A column number is a 1-based count of columns (excluding auto number column).

sortDescending

false

Sort descending

If sortColumn is used, this determines the order of the sort that is automatically done before display.

sortTip

Sort tip on mouse over

Text displayed when mouse is over a sortable column. Normally, this value is Click to sort or equivalent translation.

sortIcon

false

Show sort icon

Use true to include a sort icon in the first heading row for sortable columns. An icon will show for the last column sorted indicating the direction the column was sorted.

highlightColor

lightgoldenrodyellow

Highlight color

Color of row when mouse is over a row element. See Web Colors for instructions on how to specify this.

autoNumber

false

Auto number on each row

Use true to show an additional column that show row number for each data row.

autoNumberSort

false

Auto number sort

Use true to enable the auto number column to be sortable. This will retain the original data row count even after row sorting.

autoTotal

false

Auto total row

Use true to append a row to the end of the table that will contain totals of all numeric columns.

allowExport

false

Allow download and export

Enable user to download or export the current view of the table as a CSV file. Download is to the local file system. Export is to an attachment to the current page and is only available to users that have permission to add attachments to the page. When enabled, download and export icons will be available to the right of the table. This is a new option starting with Advanced Tables 7.2, SQL 8.3, and Excel 6.3.

columnTypes

Column types

Column types determine how column data is sorted and displayed. By default, column data is treated as a text string sorted alphabetically. Some macros, like the SQL and CSV macros, may automatically generate more precise types. When the default behavior is less than optimal for your situation, the columnTypes parameter can be set specifically. This describes the behavior of the various types. In addition, numeric types are right aligned and can result in a total row entry.

Type	Description	Sorting
S	Text string. This is a series of one or more alphanumeric characters, possibly including a letter, number, space, punctuation mark or other special characters.	Alphabetic
I	Integer. This is a numeric field without a fractional component.	Numeric
F	Numeric or float value and may contain numeric separators including a decimal point and a comma for thousands separator.	Numeric
FF	Numeric or float value and may contain numeric separators including a decimal point and a comma for thousands separator. Forced formatting of data columns.	Numeric
FC	Numeric or float value and may contain numeric separators including a comma for decimal separator and a period for thousands separator.	Numeric
C	Similar to F with more leniency on leading and trailing characters to allow currency and similar symbols to be present and ignored.	Numeric
CC	Similar to FC with more leniency on leading and trailing characters to allow currency and similar symbols to be present and ignored.	Numeric
M	Date format in many popular formats. See Advanced Date Sorting for more information. This is the preferred date sorting type.	Date
D	Date in the browser date format. See Advanced Date Sorting for more information. The D type is available for compatibility with older releases - the M type is recommended.	Date
E	Emoticons or other HTML elements.	Alphabetic on the inner HTML value
X	Indicates to exclude this column from user selectable sorting.	Disabled
.	Separated numbers, like phone numbers or TCP addresses. Valid values are multiple integer numbers separated by '.'.	Numeric on each separated value
:	Separated numbers, like phone numbers or TCP addresses. Valid values are multiple integer numbers separated by ':'.	Numeric on each separated value
-	Separated numbers, like phone numbers or TCP addresses. Valid values are multiple integer numbers separated by '-'.	Numeric on each separated value
/	Separated numbers, like phone numbers or TCP addresses. Valid values are multiple integer numbers separated by '/'.	Numeric on each separated value
H	Indicates to hide the column.

Example Use

Column types: S,I,F"0,0",F"$0,0.00"

columnCalculations

Column calculations

When an auto total parameter is selected, this parameter can be used to customize the calculation or text that is shown on the total row. By default, all numeric fields, as specified by column types, are totaled. By specifying a comma separated list of calculation requests, the behavior can be modified using special values for the corresponding column.

Type	Result
sum	Column entries are totaled. The column type must be numeric.
avg	Column entries are totaled and then divided by the number of data rows. The column type must be numeric.
avgIgnoreZero	Column entries are totaled and then divided by the number of data rows that are non-zero and non-blank. The column type must be numeric. Requires Table Library 3.5 and above.
max	The largest data value among the column entries is returned. The Column type must be numeric.
min	The smallest data value among the column entries is returned. The Column type must be numeric.
<other text>	Any other text including a blank field will be copied to the corresponding total row field. No calculations are done.

The Max and Min Total type is available only for Advanced Tables for Confluence: Server and Data Center.

rowStyles

Table row styles

A comma separated list of styles. Each style is made up of one or more properties. The first is applied to all heading rows determined by the heading parameter. The remaining styles are applied to the remaining rows in order with repetition as necessary. A style can be reused for subsequent rows by referring to it using a 1-based numeric reference. Row styles are applied to the table row and participate with other CSS properties to determine the look of an element. In particular, some properties may be overridden by table or element styles or classes.

Example: , background:lightyellow, background:lightblue

retainRowStyleOrder

true

Retain row style order after sorting

When selected, the row styles correspond to the order the rows are displayed on the screen. When not selected, the original style given to a row is retained not matter where the row lands after sorting.

columnStyles

Column styles

Comma separated list of styles. Each style is made up of one or more CSS properties (semi-colon separated). The styles are applied to columns in sequence. If a column is skipped, supply a comma before adding the next columns style. A style can be reused for subsequent columns by referring to it using a 1-based numeric reference. Column styles are applied to the table column and participate with other CSS properties to determine the look of an element. In particular, some properties may be overridden by table, row, or element styles or classes.

Example: background:lightyellow, background:lightblue,1,2,1,2

columnAttributes

Column attributes

A comma separated list of values used to modify cell attributes for all cells in a column. The position in the comma separated list corresponds to the column that the values apply to. Each value is a double semi-colon list of attributeName=value pairs that will be applied to the column cells.

enableHeadingAttributes

true

Enable heading attributes

Any column attributes provided will be applied to the all column rows including heading rows. Use false to have the column attributes apply only to data rows.

id

auto generated

Table id

Table id can be referenced for use in macros (like the chart macro) or javascript.

class

@default

Table class

Sets the class of the table. Normally, it will be confluenceTable. Use a blank (one or more spaces) value to not have any table class.

style

Table style

Style attributes can be set. For example: font-style:italic; background:lightblue;.

width

Table width

In pixels or %. Recommend using style settings instead.

border

Table border width

In pixels. Set class to blank also. Recommend using style settings instead.

SQL Macro - 8.x

On this page

Parameters

Examples

Using parameter markers