Versions Compared

Key

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


Div
idProductTOC

On this page

Table of Contents
maxLevel2
excludeOn this page
typeflat
separatorpipe

Overview

SQLSQL File, and SQL Query macros use data sources to connect to and access your databases. Creating one or more data source profiles is the fastest and most convenient method of establishing a connection. You can also create data source profiles that extend data sources configured within your application server.

Info

You must provide at least one data source to use this application within Confluence.


Note
titleFrom version 10.3:

All profile sensitive information (such as passwords) are now encrypted to provide enhanced security. This encryption-decryption of profile passwords is not available if the app is downgraded from version 10.3 to a lower release version. If you must downgrade the app version, passwords of all the profiles must be re-entered to ensure proper working of profiles.

Add data source profiles

...

Tip

The configuration for other databases (other than the ones listed in the table above) is similar to the information found in the examples section on: Configure Application Server Based Data Sources.

Extended parameters

Data source profiles allow for the configuration of extended parameter options. These profile-wide settings are used by all SQL macros if not overridden at the macro-level.

...

ParameterMacro ParameterDefaultDescription
Limit rows processedlimitNo limit

The maximum number of rows to be processed and displayed by SQL macros. This prevents queries that result in a large number of rows from using excessive resources. Individual queries can use the limit parameter to override this value. The following options are available from a selection list:

  • No limit
  • 250 (Recommended)
  • 500
  • 1,000
  • 2,500
  • 5,000
  • 10,000
  • 15,000
  • 20,000
  • 25,000
Limit query timequeryTimeoutNone

The number of seconds that a query can take before a forced timeout. This prevents queries that take too long from impacting other users. Individual queries can use the queryTimeout parameter to override this value. 

Info

Note, this parameter:

  • Requires that the target database and JDBC driver support the remote query timeout server configuration option (queryTimeout). 
  • Administrators can limit (or control) who can use this parameter through the use of our CMSP app.


Limit max activemaxActiveNoneLimit the number of actively executing SQL queries for a specific data source. Once the maximum active limit is reached, the next requested render of a SQL macro using the specific data source returns an error message instead of trying to connect to the database. See this article for additional information.
Show sql optionsshowSqlOptionsNoneA 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. Since 6.4
Connection propertiesconnectionPropertiesNoneA list of driver specific properties passed to the driver for creating connections. Each property is given as name=value, multiple properties are separated by semicolons (;). See Apache Tomcat JNDI resources.
Initial SQLsinitalSql<n>None

SQL that is run after the SQL connection is established where n is a number (1, 2, 3, ...). Multiple initial SQL statements are allowed to support databases that only allow single SQL statements. Example use for Oracle:

Code Block
languagetext
initialSql1=ALTER SESSION SET NLS_TERRITORY = GERMANY|initialSql2=ALTER SESSION SET NLS_LANGUAGE = GERMAN

No results are kept and any errors generates a macro exception. Using beforeSql is recommended for Postgres and other database that support multiple SQL statements as it is more efficient than multiple separated actions.

Before SQLbeforeSqlNoneSQL that is added before macro defined SQL.
After SQLafterSqlNoneSQL that is added after macro defined SQL.

...

IndicatorDefinition
Indicates that the connection information supplied for the data source Profile will allow connections from Confluence to the targeted database. This does not indicate that the data source profile is in use by any macros on Confluence pages.
Indicates that your connection-specific information is incorrect. See troubleshooting data source profile connections.

Modify data source profiles

...

  1. Create a new page or edit an existing one.
  2. Enter {SQL to get macro suggestions for the SQL macros. Select the SQL macro.
  3. The SQL settings navigation pane pops up.
  4. Enter the Data source profile under the SQL statement tab. 
  5. Enter the following query in the SQL statement text area. 

  6. Click Preview (See:) to see the results.

Image Modified