Versions Compared

Old Version 5

changes.mady.by.user Smita Nair

Saved on

compared with

New Version Current

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 for Confluence administrator.

Data source profiles allow the administrator to provide additional configuration for use by SQL macros. See Release notes 5.2 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. 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 and are case insensitive.

Screen shot

  • Go to Manage Apps in Confluence administration.
  • Find the SQL app in the list.
  • Click Configure.


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 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 generates a macro exception. Using beforeSql is recommended for Postgres and other databases 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.



Quick configurations

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

  2. Change the name to something meaningful for your organization.
  3. Modify the 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 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}.

...