Versions Compared

Old Version 8

changes.mady.by.user Nisha Shantilal

Saved on

compared with

New Version Current

changes.mady.by.user Nisha Shantilal

Saved on

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

Include Page
_SQL for Confluence 11.x announcement
_SQL for Confluence 11.x announcement

Overview

SQL, SQL 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.

...

Setup TypeFieldDescription
SimpleDatabase typeThe type of database you are connecting to.
SimpleData source nameYou have the option to create a new data source profile by extending an existing data source.  This may be useful if you'd like to tighten/alter the configuration parameter settings to be more/less restrictive for certain usage. You can of course then secure the usage using our CMSP app.
SimpleHostnameThis is the hostname or IP address of your database server.  
SimplePortThis is the port used to access your database on the server it is running against. 
SimpleDatabase This is the name of your database. 
BothDriver classThe class of JDBC driver that you will use to connect to your database (e.g., com.mysql.jdbc.Driver, or org.postgresql.Driver).
BothDriver JAR location

The path on your Confluence server where the JDBC driver is located.

Tip
titleStart with an absolute file reference

Usually, it is desirable to start with an absolute reference to make sure it is working. Relative references are more maintainable but can be problematic especially on Windows. After it is working, you can experiment with relative references.


By connection string
Anchor
connection_string
connection_string
Connection string
The database URL is entered in this format (SQL Server example):
jdbc:sqlserver://<hostname>:<port>;database=<database>

For example:  
jdbc:sqlserver://yourserver:1433;database=confluence

  • SQL for Confluence version 11.0.7 and above supports Connection string URL of unlimited length.

  • SQL for Confluence version 11.0.6 and below supports Connection string URL of length less than or equal to 255 characters. 

    Note
    titleSQL for Confluence: Downgrading to v11.0.6 and below
    • If the Connection string URL length is more than 255 characters; Appfire does not recommend downgrading the app version below 11.0.7. 
    • If the Connection string URL length is more than 255 characters, and you still downgrade and face issues, contact support.


Note

Once you select the By connection string option and start filling in the details of the Connection string field, the Simple (recommended) Setup type is disabled (for you to switch to Simple). If you want to enable the Simple option, empty the details in the Connection string field.


BothUsernameThis is the username of the database you provided. 
Both

Password

This is the password of the database you provided. 

...

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
  • Administrators can enable the Restrict overriding global configuration limits flag on the Global configuration page to prevent overriding the Limit rows processed parameter and enforce the default value set on the Global configuration page.
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.
  • To know more about how the queryTimeout parameter value is evaluated, refer to the scenario page Limit query time - 11.x.
  • Administrators can enable the Restrict overriding global configuration limits flag on the Global configuration page to prevent overriding the Limit query time parameter and enforce the default value set on the Global configuration page.


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.

...