Versions Compared

Old Version 2

changes.mady.by.user Randall Ward

Saved on

compared with

New Version 3

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
maxLevel1
excludeOn this page
typeflat
separatorpipe


Section


Column

Description

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

Data source profiles allow the administrator to provide additional configuration for use by SQL macros. See  See Release notes 5.2 for  for details on the new capabilities this provides. Data source profiles can be stand-ins for data sources referenced by macros using the dataSource parameter. They are treat the same. This includes how you configure CMSP. Think of data source profiles as just additional data sources for use.

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 This 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 and are case insensitive.

Screen

Shot

shot

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


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, to use for in 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 generates a macro exception. Using beforeSql is recommended for Postgres and other database databases that support multiple sql 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 urlURL. Required, unless an application data source is referenced by the dataSource parameter.
dbUser
Database userDatabase defined user id 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.



Quick configurations

  1. Copy and paste the configuration into your data source profile

...

  1. configuration.

  2. Change the name to something meaningful for your organization.
  3. Modify the url URL to your database server.
  4. Modify the database name (from test to the name of database you want to query).
  5. Modify the user and password to be match the user credentials defined for the the database server.

  6. To test: , create a page and use Insert -> Markup to add the following wiki markup : {sql-query:dataSource=example} select 1 {sql-query}.


Table plus


DatabaseProfile NameConfiguration
PostgreSQLexample

dbUrl=jdbc:postgresql://localhost:5432/test | dbUser=confluence | dbPassword=confluence | dbDriver=org.postgresql.Driver | dbJar=https://jdbc.postgresql.org/download/postgresql-9.3-1102.jdbc41.jar

Info
(info)

If you need to specify a particular PostgreSQL schema, then include "?currentSchema=<schema_name>", such as:
dbUrl=jdbc:postgresql://localhost:5432/test?currentSchema=jiraschema | dbUser=confluence | dbPassword=confluence | dbDriver=org.postgresql.Driver | dbJar=https://jdbc.postgresql.org/download/postgresql-9.3-1102.jdbc41.jar 


MySQLmyExample

dbUrl=jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf8&amp;allowMultiQueries=true | dbUser=confluence | dbPassword=confluence | dbDriver=com.mysql.jdbc.Driver | dbJar=http://central.maven.org/maven2/mysql/mysql-connector-java/5.1.34/mysql-connector-java-5.1.34.jar

MS SQL ServermsExample

dbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver | dbUrl=jdbc:sqlserver://localhost:2433;database=test;integratedSecurity=false | dbUser=confluence | dbPassword=confluence | dbJar=../lib/sqljdbc4.jar

The configuration for other databases is similar to the information found in the examples section on: Configure Application Server-based Data Sourcesof Configure application server based data sources.


Tip
titledataSource not found

All 5 db database parameters are required when defining db database access directly. Otherwise, you will likely get a dataSource not found message.


Tip
titleUsing the lib location

Often, using the lib location (for example: , dbJar=lib/ojdbc6.jar)is best, especially if you already have or later intend to define an application server based data source. The following is for a standalone installation.: