...

...

...

...

Description

Description

A number of macros that run SQL queries and display data in the form of a table share common capabilities as described below. A JavaScript enabled browser is required to enable most of these capabilities.

In the macro editor window, all these common capabilities are logically grouped into various tabs based on the functionality for easy access and styling.

• SQL settings:

  • SQL statement: Allows you to select the profile for a SQL data source and run SQL queries in the editor. Select the Data source profile for your SQL database and enter the query in the SQL statement editor. There is no limit to the number of statements you can run.
  • SQL script: Allows you to read and run SQL scripts from external sources like files, templates, or attachments on your Confluence instance. Advanced capabilities will enable you to combine the outputs from the script and macro body.
  • Parameters: Allows you to create a set of optional data parameters to refine your query. Parameters apply to your SQL statements during Runtime. Simply enter the number of parameters you want to create in the Start with parameter (s) field and click + Add a parameter (s) to define the parameters. You can define multiple parameters by clicking + Add value; there is no limit to the number of parameters you can add.
  • Runtime: Allows you to specify additional conditions that apply to a SQL query during execution.

• Display settings:

  • Data layout: Allows you to define how the output for a SQL query is presented. 
  • Advanced formatting: Allows you to define advance settings like displaying special characters, SQL query, etc. in the output.

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

Tabs

Required

Macro Editor Label	Default

Macro Browser Label

Description

dataSource

• may be restricted

Macro Parameter
SQL settings
SQL statement	Data source

name

profile

Specify the data source name. It represents an application server defined data source (Pro Edition) or data source profile defined by your administrator.

outputhtmlOutput 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 to 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.

scriptmacro bodyLocation of SQL statements

 Parameter will not work with our SQL-file Macro.

Additional SQL statements can be added after the macro body sql 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

This is a required field and some of the data sources may be restricted	dataSource
	SQL statement		Enter the SQL statements you want to execute. There is no limit to the number of statements you can run. Note Parameter will not work with our SQL-File Macro.	sql-editor-query-input
SQL script	Method of locating script		Specifies the list of options where CSV data is located. The included data will follow the body data based on the location selected. Attachment - Data is read from the list of options selected for the following parameters: Space - Lists all available spaces. Page - Lists all the pages available from the selected space. The following options indicate: @self - current space or page @home - homepage of current space @parent - parent page of current page or space Template - Data is read from a global page template specified in the 'Name of the template' field.  Special note: How to deal with templates on Confluence 4.3 and later. Note Above parameters will not work with our SQL-file Macro.  Filename - Data is read from the input specified in the 'File name' field. You can specify 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.

heading1Number of heading rowsSpecify heading=0 to not show any heading lines. Heading rows do not participate in sorting.bordernormalTable border widthBorder width in pixels.width100%Table widthWidth in pixels or %.rowOrientationverticalDisplay rows vertically or horizontallyNote that most of the styling, formatting, sorting, auto, and similar parameters are applicable to only the vertical orientation setting.tabletrueShow results as a tableFor example, false can be used to produce single values that you want to include in text.showSqlfalseShow SQLDisplays SQL in a code macro.showUpdateCountfalseShow number of rows updatedFor update SQL, determines whether or not to show the number of rows updated as a result of the operation.macrosfalseEvaluate  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 SCRP or similar that can produce SQL output.expandArraytrueExpand array fieldsFor vertical row orientation, array fields can be expanded to one entry per line.autoCommittrueAuto commit SQL statementsWikipedia:Autocommit or see your database documentation.columnLabelfalseUse database column labelsChoose whether to use column name or column label for a row header.showWikifalseShow generated wiki markupWhen output is wiki-based, choose this option to show the generated wiki markup.escapefalse

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.

convertNulltrueConvert null fields to blankUse this parameter to avoid displaying null values in the column.noDataErrorfalseShow error if there are no rowsA 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).noDataMessageText to display when there are no rowsUse @default to show a default error message.

p1 through p10

Parameter markerValues for SQL parameter markers identified by ? in SQL statements. On untrusted sites, prevent SQL injection attacks by using parameter markers. This in

, also subdirectories. File encoding - Encoding for an external file if different from the system default handling. Default file encoding is UTF8.	dataLocation
	p1 through pn		Click + Add a parameter (s) to create a set of optional data parameters to refine your SQL statements during Runtime. Each parameter field allows you to create a set of optional data parameters to refine your query. Parameters apply to your SQL statements during Runtime. Simply enter the number of parameters you want to create in the Start with parameter (s) field and click + Add a parameter (s) to define the parameters. You can define multiple parameters by clicking + Add value;there is no limit to the number of parameters you can add. For example, select * from example where name = ? or  department = ? Note Values for SQL parameter markers identified by ? in SQL statements. On untrusted sites, prevent SQL injection attacks by using parameter markers. This is only necessary when the SQL statements are partially constructed from user input.

See Wikipedia

See Wikipedia: SQL injection. Wiki markup

sql

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.

tip

row-number

titletransactionIsolationREAD_COMMITTEDTransaction isolation levelWikipedia: Isolation. See

Runtime

Example SQL with 2 parameter markers

select * from example where name = ? or  department = ?

limitDefault (~ no limit)

Auto commit SQL statements

On

Wikipedia:Autocommit or see your database documentation.

• READ_COMMITTED
• READ_UNCOMMITTED
• REPEATABLE_READ
• SERIALIZABLE
• NONE

showSqlOptionsOptions for showing SQL codeSince 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.

autoCommit
	Transaction isolation level		Most DBMSs offer a number of transaction isolation levels, which control the degree of locking that occurs when selecting data. You can select among a list of 5 kinds of transaction isolation levels: Read committed Read uncommitted Repeatable read Serializable: This is the highest isolation level. None: Skips adding any controls while your data is being selected.  To know more about what you can do with each type, refer to Wikipedia: Isolation or See your database documentation.	transactionIsolation
	Maximum number of rows to retrieve and display

System administrator Default (~ no

Specify the upper limit for the number of rows your SQL query returns. Administrators can configure the limit.

queryTimeout

limit

)

	Maximum number of seconds for query to

runSystem administrator can configure the limit. Since 5.1. Encoding for an external file if different from the system default handling. Example: UTF8.

run

Time in seconds that a query runs before a forced timeout. The default value is set by your Confluence administrator in the global configuration for SQL for Confluence. Since 5.1. Select a value from the list to override the default setting. Your database and JDBC driver must support the selected value to use this parameter. This parameter can be managed by Macro Security for Confluence App. Requires database/JDBC enablement.

disableAntiXssfalseStop encoding HTML charactersIf 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 CMSP) to use this option due to security considerations.encodingsystem defaultFile encoding

queryTimeout
	Render wiki markup macros in body	Off	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.	macros