Versions Compared

Old Version 1

changes.mady.by.user Randall Ward

Saved on

compared with

New Version 2

changes.mady.by.user Randall Ward

Saved on

Key

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

...

Div
idProductTOC

On this page

Table of Contents
maxLevel1
excludeOn this page
typeflat
separatorpipe

Section
Column

Description

This information is primarily for the Confluence or SQL for Confluence administrator.

...

Data Source Profiles allow the Confluence Administrator to extend Data Sources by providing additional configuration parameters for use by SQL macros.

...

How to implement role based security for SQL queries provides an example use case.

Definition

Each data source profile is identified by a unique name. Name is case specific, just like application server defined data sources. The value field is a | (bar) separated list of parameter expressions of the form name=value. This is just like wiki markup macro parameters. Parameters are case insensitive.

Screen Shot

  • Go to Manage Add-ons in Confluence administration
  • Find the SQL add-on in the list
  • Click on the Configure button

...

Like Data Sources, Data Source Profiles are referenced by the SQL, SQL-query, and SQL-file Macros using the dataSource parameter.

Info

Note: you must have at least one Data Source in order to begin using this application within Confluence.

Add Data Source Profiles

To add new or extend an existing Data Source Profile: 

  1. Log in as a user with the Confluence Administrators Global Permission.
  2. Select Add-ons from the Administration menu (cog icon: Image Added) at the top right of your screen. Then scroll down to Bob Swift Configuration on the left-hand nav bar and choose SQL Configuration (see: Image Added).
  3. Select View and Modify Data Source Profiles (see: Image Added ) from the top navigation.
  4. Click on the Image Added button.

The Add Profile screen will provide you with two setup 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 are comfortable constructing a database URL. 

Depending on the setup type, you'll be prompted for the following information.

Setup typeFieldDescription
SimpleDatabase typeThey type of database you are connecting to.
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 better 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 stringConnection stringThe database URL is entered in this format (SQLServer example):
jdbc:sqlserver://<hostname>:<port>;database=<database>

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

BothUsernameThis is the username of your dedicated database user. 
BothPasswordThis is the password for your dedicated database user.

View Data Source Profiles

To view, add, or modify a Data Source Profile:

  1. Log in as a user with the Confluence Administrators Global Permission.
  2. Select Add-ons from the Administration menu (cog icon: Image Added) at the top right of your screen. Then scroll down to Bob Swift Configuration on the left-hand nav bar and choose SQL Configuration (see: Image Added).
  3. Select View and Modify Data Source Profiles (see: Image Added ) from the top navigation.
  4. You will see a list of Data Source Profiles and their connection status.


Security

Data sources are shared resources. Some of these resources may need to be secured for use only by specific users or groups. This can be accomplished by using CMSP and configuring access to the macro and perhaps to individual profiles. 

Parameters


Table plus


ParameterDefaultLabelDescription
dataSourceprofile nameData sourceOptionally can reference a data source defined on the application server. This enables the ability to extend the data source with additional advanced features like those provided by the other parameters listed here!
limit
global settingMaximum number of rows to displayAbility to override the global limit on a data source level.
queryTimeoutglobal settingMaximum number of seconds for query to runAbility to override the global queryTimeout on a data source level. Requires database/JDBC support.
initialSql<n>
Initial SQL n

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 will generate 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.

beforeSql
Before SQLSQL that is added before macro defined SQL.
afterSql
After SQL

SQL that is added after macro defined SQL.

dbDriver

org.postgresql.Driver

Database DriverJDBC driver name. Not used if an application data source is referenced by the dataSource parameter.
dbUrl
Database URLJDBC connection url. Required unless an application data source is referenced by the dataSource parameter.
dbUser
Database userDatabase defined user id needed for accessing the database referenced by dbUrl. Not used if an application data source is referenced by the dataSource parameter.
dbPassword
Database user passwordDatabase defined password for the dbUser. Not used if an application data source is referenced by the dataSource parameter.
dbJar
JDBC jar

Location of the JDBC jar file. Required unless an application data source is referenced by the dataSource parameter.

  • Relative file reference in installation directory on Confluence server
    • Example: lib/postgresql-9.2-1000.jdbc4.jar
  • Absolute file reference on Confluence server
    • Example: /atlassian/confluence/lib/postgresql-9.2-1000.jdbc4.jar
  • URL reference (slow!)
  • Fully qualified jar reference (JAR URL reference)
    • Example: jar:file:lib/postgresql-9.2-1000.jdbc4.jar!/
Tip
titleStart with an absolute file reference

Usually better 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.



...