Versions Compared

Old Version 2

changes.mady.by.user Saumya Sunder (Inactive)

Saved on

compared with

New Version Current

changes.mady.by.user Smita Nair

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

...


Div
idProductTOC

On this page

Table of Contents
maxLevel2
minLevel2
excludeOn this page
typeflat
separatorpipe

Description

Macros that run SQL scripts and display results 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 data source profile for a database and run SQL statements 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 statements from external sources like attachments, files, or templates 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 statement. 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 script during execution.
Info

Selecting a value in the macro overrides the default value set by an Administrator. The order of overriding the default setting is:

Macro level parameter > Profile configuration > Global configuration.

  • Display settings:

    • Data layout: Allows you to define how the output for a SQL script is presented. 
    • Advanced formatting: Allows you to define advanced settings like expanding array fields, 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 LabelDefault
Macro Browser Label
Description

dataSource

  • may be restricted
(tick)
Parameter

SQL settings

SQL statementData 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

(warning) 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 the

     This is a required field and some of the data sources may be restricted 

    dataSource 


    SQL statement

    Use the available select lists to select the tables, views and respective columns, and add them to your SQL statement. You can:

    • Build simple queries by selecting the tables, views and/or columns, and clicking the add icon, or
    • Enter your own SQL statement and add tables, views, and columns from the select lists wherever required in the query, or
    • Add additional SQL scripts from the SQL script tab

    The tables, views, and columns in the select lists are loaded based on the Data source profile selected. There is no limit to the number of statements you can run.

    Note
    • This parameter is not available in SQL File macro.
    • The ability to add required tables and columns from the select lists to your SQL query, is added in the SQL 10.1 release.
    • At times, these select lists might take a while to load. This can be because of your network connectivity or a large number of tables in the selected data source. Note that this delay occurs only the first time you connect to a data source. Once retrieved, data is cached until one hour, which is the default cache expiry time.
    • When you add a new table or alter an existing table in any data source (using SQL macro), the changes might not be reflected in the select lists immediately, because the data before changes is cached for one hour. You can either:
      • contact your Administrator to flush the respective cache in the Cache Management page, to see the updates immediately, or
      • wait for the select lists to load once the cache is cleared and the latest updates are retrieved.



    SQL script


    		Method of locating script

    None








    Specifies the list of options where SQL scripts are located. The results will be based on the scripts present in the selected location.

    • Attachment - The script 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 - Scripts is read from a global page template specified in the 'Name of the template' field. 

    (info) Special note: How to deal with templates on Confluence 4.3 and later.

    Note

    Above parameters are not available for the SQL File macro. 

    • Filename - The script 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 
    • , also subdirectories.
    • File encoding - Encoding for an external file if different from the system default handling. Default file encoding is UTF8.
    		script

    Parameters

    		p1 through pn

    Click + Add 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 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,

    Code Block
    languagesql
    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. 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. See Wikipedia: SQL injection.



    Runtime





    		Auto commit SQL statements OnWikipedia: Autocommit or see your database documentation.autoCommit
    Transaction isolation level Default

    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.

    Info
    • The default transaction isolation level is set by the administrator via the Global configuration page.
    • Selecting a value from the list overrides the default. 
    • Database and JDBC drivers should support the selected transaction isolation level.


    		transactionIsolation
    Maximum number of rows to retrieve and displayDefault

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

    Info
    • The default value is set by the administrator via the Global configuration page or Profile configuration page.
    • Selecting a value from the list overrides the default. The order of overriding the default setting is:

    Macro level parameter > Profile configuration > Global configuration.


    		limit
    Maximum number of seconds for query to run Default

    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.

    Info
    • The default value is set by the administrator via the Global configuration page or Profile configuration page.
    • Selecting a value from the list overrides the default. The order of overriding the default setting is Macro level parameter > Profile configuration > Global configuration.
    • 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.


    		queryTimeout
    Render wiki markup macros in body OffIf requested, the body will be rendered to expand wiki markup macros. The rendered macros must produce valid SQL syntax.
     This
     This is useful to run macros
    from SCRP or autoCommittrueAuto commit SQL statementsWikipedia:Autocommit or see your database documentation.columnLabelfalseUse database column labelsChoose whether to use
    from Scripting for Confluence or similar that can produce SQL output.
    expandArraytrueExpand array fieldsFor vertical row orientation, array fields can be expanded to one entry per line.convertNulltrueConvert null fields to blankUse this parameter to avoid displaying null values in the column.noDataErrorfalse
    macros

    Display settings

    Data layout


    		Output formathtml

    Specifies how the output is formatted:

    • HTML - Generates HTML from the result set. Data is HTML encoded unless data encoding is disabled.
    • XHTML (Pro Edition) - Generates and renders XHTML from the result set. Data is HTML encoded unless encoding has been disabled (disableAntiXss).
    • Wiki - Generates and renders wiki markup from the result set. If the data contains unintended wiki markup characters, set the escape parameter On.
    • unrenderedWiki (Pro Edition) - Generates wiki markup from the result set, similar to wiki, without rendering. Wraps with a table-plus macro suitable for copying to another system.
    Note

    unrenderedWiki will not work with our SQL File macro. 


    		output
    Display rows vertically or horizontallyverticalNote that most of the styling, formatting, sorting, auto, and similar parameters are applicable to only the vertical orientation setting.rowOrientation
    Show results as a tableOnTurn this toggle on to produce single values that you want to include in the text.table
    Use database column labelsOffChoose whether to use a 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.

    columnLabel
    Show number of rows updatedOffFor update SQL, determines whether or not to show the number of rows updated as a result of the operation.showUpdateCount
    Show error if there are no rowsOffA 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
    noDataError
    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.

    Tip
    titleExample SQL with 2 parameter markers

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

    transactionIsolationREAD_COMMITTEDTransaction isolation level

    Wikipedia: Isolation. 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.limitDefault (~ no limit)Maximum number of rows to displaySystem administrator can configure the limit.queryTimeoutDefault (~ no limit)Maximum number of seconds for query to runSystem administrator can configure the limit. Since 5.1. Requires database/JDBC enablement.disableAntiXssfalseStop encoding HTML characters
    The message in this field is displayed only if Show results as a table is selected.noDataMessage
    Advanced formatting





    		Show generated wiki markupOffWhen the output is wiki-based, choose this option to show the generated wiki markup.showWiki
    Escape special wiki charactersOffWhen the 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.escape
    Convert null fields to blankOnUse this parameter to avoid displaying null values in the column.convertNull
    Stop encoding HTML charactersOffIf the SQL is producing HTML, this parameter needs to be selected (
    true
    On). User must be authorized (via global
    add-on
    app configuration or by more fine-grained control using
    CMSP
     Macro Security for Confluence) to use this option due to security considerations.disableAntiXss
    Expand array fieldsOnFor vertical row orientation, array fields can be expanded to one entry per line.
    encodingsystem defaultFile encodingEncoding for an external file if different from the system default handling. Example: UTF8.
    expandArray
    Show SQLOffDisplays SQL in a code macro.showSql
    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.showSqlOptions

    Image Added