.png?version=1&modificationDate=1662627367996&cacheVersion=1&api=v2&width=226&height=72)
On this page
Summary
Data Sources describes two ways to configure data sources for the macros provided by SQL for Confluence. This page focuses on application server based data source configurations.
The data source is refenced using the dataSource (Data source name) parameter. The exact configuration of a data source is application server and database specific. The configuration must be done by a Conflunece administrator and the Confluence application server must be restarted for changes to be active. The configuration must be done very carefully as any errors will result the data source being unusable 
. Each database you need to access in Confluence needs to a have a data source configuration.
These are the major elements of the configuration
- Application server data source configuration - consult your application server documentation on how this is done and problem determination mechanisms. Note that the configuration may be specific to the application server version. For example, older Tomcat versions have some different configurations.
- JDBC driver specific for the database to be accessed - consult your database documentation and other sources for the best JDBC driver to use.
- Installation of JDBC driver on the application server
- Database access - consult your database documentation on how to configure your database for access
Various users have provided some examples that covers many standard situations. This page can be used to share that type of information as well as problem determination information. This is the best place to start especially if you are not an expert in this area.
Just Upgraded Confluence?
A common problem is errors after upgrading Confluence. Ensure that your upgrade procedures include updating your server.xml and copying all the JDBC drivers you use into the appropriate location. Also, check out this page for isolating these or application server version differences that cause problems. If you get errors immediately after an upgrade, this is the most likely cause.
Updated documentation
This page is updated from a previous version that has been archived as SQL for Confluence datasource configuration. The former page has been kept as a reference for links and comments. Some of the comments are still useful as they represent user questions and user contributed configurations and solutions. However, some of the information is also out dated.
Confluence standalone installation (Tomcat)
Many users install the standard Confluence standalone distribution. This section will discuss configuration specific to this application server environment. The most recent versions of Confluence use newer versions of the Tomcat application server where the following configuration examples apply:
JDBC driver location
Put the driver jar in <install-dir>/lib - this is the directory that has other tomcat jars. If you have a much older version of Confluence that does not have this directory, look for <install-dir>/common/lib instead.
Tomcat configuration
Modify <install-dir>/conf/server.xml to add one or more data source resource elements. server.xml is an example of adding a few data sources for various local and non-local databases.
The essential element is something like the following where the data source name is myDS:
<Resource name="jdbc/myDS"
auth="Container"
type="javax.sql.DataSource"
username="confluence"
password="confluence"
maxActive="100"
maxIdle="10"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/mydatabase"
validationQuery="Select 1"
/>
Resource must be within the confluence context tag
... <Context path="" docBase="../confluence" debug="0" reloadable="false"> ... <Resource ... /> ... </Context>
Consider using a validation query
Database examples
The sql macro markup would be: {sql:dataSource=myDS| ... }
Database |
Information |
JDBC driver example |
Resource configuration |
|---|---|---|---|
PostgreSQL |
postgresql-8.2-507.jdbc3.jar |
<Resource name="jdbc/myDS"
auth="Container"
type="javax.sql.DataSource"
username="confluence"
password="confluence"
maxActive="100"
maxIdle="10"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/confluence"
validationQuery="Select 1"
/>
|
|
MySQL |
mysql-connector-java-3.1.14-bin.jar |
<Resource name="jdbc/myDS"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/mydatabase?autoReconnect=true"
username="username"
password="password"
maxActive="25"
maxIdle="5"
maxWait="10000"
validationQuery="Select 1"
/>
|
|
Oracle |
Thin Driver |
ojdbc6.jar |
<Resource
name="jdbc/myDS"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@www.some_server.com:1521:mysid"
username="username"
password="password"
connectionProperties="SetBigStringTryClob=true"
maxActive="25"
maxIdle="5"
maxWait="10000"
validationQuery="Select 1 from DUAL"
/>
|
Oracle Advanced |
Thin Driver |
ojdbc6.jar |
|
Microsoft SQL Server |
jtds-1.2.2.jar |
<Resource
name="jdbc/myDS"
auth="Container"
type="javax.sql.DataSource"
driverClassName="net.sourceforge.jtds.jdbc.Driver"
url="jdbc:jtds:sqlserver://mysqlserver:1433/mydatabase"
username="username"
password="password"
maxActive="20"
maxIdle="10"
maxWait="-1"
validationQuery="Select 1"
/>
|
|
Microsoft SQL Server |
Using Microsoft's jdbc driver |
sqljdbc4.jar |
Similar to the other examples here with parameters like the MS SQL Server example on Data Source Profiles |
DB2 for IBM i |
jt400.jar |
<Resource
name="jdbc/myDS"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.ibm.as400.access.AS400JDBCDriver"
url="jdbc:as400://ibmi;prompt=false;translate binary=true; extended metadata=true"
username="username"
password="password"
maxActive="20"
maxIdle="10"
maxWait="-1"
validationQuery="Select 1"
/>
|
|
Derby |
derbyclient.jar |
<Resource
name="jdbc/myDS"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.apache.derby.jdbc.ClientDriver"
url="jdbc:derby://localhost:1527/dbname"
username="username"
password="password"
maxActive="20"
maxIdle="10"
validationQuery="Select 1"
/>
|
Other databases
Support for other databases depends on the capabilities provided by the JDBC driver. SQL for Confluence expects certain JDBC features to be available. In some cases, parameter choices can affect which capabilities are needed. User testing and community support is about the only help that is available for less popular databases/JDBC drivers. If you have had success with other databases, please add information to this page.
WebLogic 9.1 Application Server
- In the WebLogic console, under Services > JDBC, create a data source (see WebLogic's documentation on how to Configure JDBC data sources for more information).
- Take note of the JNDI name you used to create the data source.
- Point the SQL macro to that JNDI name (dataSource parameter).
