Configure Data Source Profiles - 10.x

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.

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

From 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

To add a new or extend an existing data source or global data source (defined in server.xml) profile:

Log in as a Confluence administrator with Global Permission.
Select Manage apps from the CONFLUENCE ADMINISTRATION menu (cog icon: ) at the top right of your screen.
Scroll down to BOB SWIFT CONFIGURATION in the navigation pane to the left and click SQL (Pro Edition) (see:).
Click View and modify data source profiles (see: ) tab at the top.
Click the button.
The Add profile screen provides you with two setup type options:
- Simple - this is the most straightforward way to connect to your database.
- By connection string - use this option if you want to specify additional parameters and you are comfortable with constructing a database URL.

Setup options

Depending on the setup type, you are prompted to enter the following information:

Setup Type	Field	Description
Simple	Database type	The type of database you are connecting to.
Simple	Data source name	You 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 Macro Security for Confluence app.
Simple	Hostname	This is the hostname or IP address of your database server.
Simple	Port	This is the port used to access your database on the server it is running against.
Simple	Database	This is the name of your database.
Both	Driver class	The class of JDBC driver that you will use to connect to your database (e.g., com.mysql.jdbc.Driver, or org.postgresql.Driver).
Both	Driver JAR location	The path on your Confluence server where the JDBC driver is located. Start 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	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` 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.
Both	Username	This is the username of the database you provided.
Both	Password	This is the password of the database you provided.

Quick connection strings

When using the By connection string setup option, the following examples can be quickly copied into the relevant sections and then modified:

Database	Example
PostgreSQL	dbUrl=jdbc:postgresql://localhost:5432/test \| dbUser=confluence \| dbPassword=confluence \| dbDriver=org.postgresql.Driver \| dbJar=https://jdbc.postgresql.org/download/postgresql-42.2.5.jar
PostgreSQL (using specific Schema)	dbUrl=jdbc:postgresql://localhost:5432/test?currentSchema=jiraschema \| dbUser=confluence \| dbPassword=confluence \| dbDriver=org.postgresql.Driver \| dbJar=https://jdbc.postgresql.org/download/postgresql-42.2.5.jar
MySQL	dbUrl=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true \| dbUser=confluence \| dbPassword=confluence \| dbDriver=com.mysql.jdbc.Driver \| dbJar=http://central.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar
Microsoft SQL Server	dbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver \| dbUrl=jdbc:sqlserver://localhost:2433;database=test;integratedSecurity=false \| dbUser=confluence \| dbPassword=confluence \| dbJar=../lib/mssql-jdbc-7.0.0.jre8.jar

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.

Table: extended parameter options explained

Parameter	Macro Parameter	Default	Description
Limit rows processed	limit	No 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 time	queryTimeout	None	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. 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 Macro Security for Confluence app.
Limit max active	maxActive	None	Limit 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 options	showSqlOptions	None	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. Since 6.4
Connection properties	connectionProperties	None	A 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 SQLs	initalSql<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: 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 SQL	beforeSql	None	SQL that is added before macro defined SQL.
After SQL	afterSql	None	SQL that is added after macro defined SQL.

View data source profiles

To view a data source Profile:

Log in as a user with the Confluence administrators Global Permission.
Select Manage apps from the CONFLUENCE ADMINISTRATION menu (cog icon: ) at the top right of your screen.
Scroll down to BOB SWIFT CONFIGURATION in the navigation pane to the left and click SQL (Pro Edition) (see:).
Click View and modify data source profiles (see:) tab at the top.
You will see a list of data source profiles and their connection status.

Table: Status indicators explained

Indicator	Definition
	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

To modify a data source profile:

Log in as a user with the Confluence administrators Global Permission.
Select Manage apps from the CONFLUENCE ADMINISTRATION menu (cog icon: ) at the top right of your screen.
Scroll down to BOB SWIFT CONFIGURATION in the navigation sidebar to the left and click SQL (Pro Edition) (see: ).
Click View and modify data source profiles (see:) tab at the top.
You will see a list of data source profiles and their connection status.
Click the Edit link to the right of the data source profile you would like to modify.
The edit dialog will appear and will default to the By connection string view. You can edit the fields under this option or toggle to the Simple setup type and make the necessary changes.

You will be able to toggle to the Simple setup type only after clearing the configuration information that appears under the By connection string.
To save changes, click the Next and then Save profile (see:).

Remove data source profiles

Removing data source profiles extended by SQL macros renders the macro and section of the page broken.

To remove a data source profile:

Log in as a user with the Confluence administrator's Global Permission.
Select Manage apps from the CONFLUENCE ADMINISTRATION menu (cog icon: ) at the top right of your screen.
Scroll down to BOB SWIFT CONFIGURATION in the navigation pane to the left and select SQL (Pro edition) (see:).
Click View and modify data source profiles (see: ) tab at the top. You see a list of data source profiles and their connection status.
Click the Remove link to the right of the data source profile that you want to remove.
Click Yes, delete it!.

Test a data source profile

To test one or more data source profiles:

Using Wiki markup

Create a new page or edit an existing one.
Select Markup from the Insert more content list (icon: ) from the toolbar menu at the top.
Enter the following wiki markup in the Insert pane. Ensure that you change the dataSource to a data source profile that you have created.
```
{sql-query:dataSource=example} select * from table_name {sql-query}
```
Click Insert and then Save the page.

Using SQL macro

Create a new page or edit an existing one.
Enter {SQL to get macro suggestions for the SQL macros. Select the SQL macro.
The SQL settings navigation pane pops up.
Enter the Data source profile under the SQL statement tab.
Enter the following query in the SQL statement text area.
Click Preview (See:) to see the results.

SQL for Confluence