On this page
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 Macro Security for Confluence. 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 for Confluence app in the list.
- Click Configure.
Parameters
Parameter | Default | Label | Description |
|---|---|---|---|
dataSource | profile name | Data source | Optionally, 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 setting | Maximum number of rows to display | Ability to override the global limit on a data source level. |
queryTimeout | global setting | Maximum number of seconds for query to run | Ability 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: 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 SQL | SQL that is added before macro defined SQL. | |
afterSql | After SQL | SQL that is added after macro defined SQL. | |
dbDriver | org.postgresql.Driver | Database Driver | JDBC driver name. Not used if an application data source is referenced by the dataSource parameter. |
dbUrl | Database URL | JDBC connection URL. Required, unless an application data source is referenced by the dataSource parameter. | |
dbUser | Database user | Database 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 password | Database 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.
Start 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
Copy and paste the configuration into your data source profile configuration.
Change the name to something meaningful for your organization.
Modify the URL to your database server.
Modify the database name (from test to the name of database you want to query).
Modify the user and password to match the user credentials defined for the the database server.
To test, create a page and use Insert -> Markup to add the following wiki markup : {sql-query:dataSource=example} select 1 {sql-query}.
Database | Profile Name | Configuration |
|---|---|---|
PostgreSQL | example | 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 If you need to specify a particular PostgreSQL schema, then include "?currentSchema=<schema_name>", such as: |
MySQL | myExample | 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.34/mysql-connector-java-5.1.34.jar |
MS SQL Server | msExample | 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 of Configure application server based data sources.
dataSource not found
All 5 database parameters are required when defining database access directly. Otherwise, you likely get a dataSource not found message.
Using 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:
Need support? Create a request with our support team.