.png?version=1&modificationDate=1662627367996&cacheVersion=1&api=v2&width=226&height=72)
SQL-query Macro
- Bob Swift
- Jeff Pierce
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 | Required | Default | Macro Browser Label | Description |
|---|---|---|---|---|
dataSource
|  | 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:
| |
| script | macro body | Location of SQL statements | Additional SQL statements can be added after the SQL in macro body by specifying another location:
| |
| 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
| ||
| transactionIsolation | READ_COMMITTED | Transaction isolation level | Wikipedia: Isolation. See your database documentation.
| |
| 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}
Log a request with our support team.
Confluence®, Jira®, Atlassian Bamboo®, Bitbucket®, Fisheye®, and Atlassian Crucible® are registered trademarks of Atlassian®
Copyright © 2005 - 2024 Appfire | All rights reserved. Appfire™, the 'Apps for makers™' slogan and Bob Swift Atlassian Apps™ are all trademarks of Appfire Technologies, LLC.