Datasource Settings (PRO)

The PTC Datasource Settings can be found in the General Configuration of your Confluence Instance in the Page Tree Creator Section.

Security Tip: Create a special user in your DBMS with

No update, create or delete permission
With view permission on the desired tables

Use Cases

The basic use case is to have a Placeholder on a page with the type Datapicker that will use the values of an external datasource configured in the administration settings: External datasource as placeholder options
For advanced use, one single Datapicker can replace multiple Placeholders when the SQL query consists of more than two select statements: Multiple replacements for a single datapicker
A Datapicker Placeholder can also be combined with a Create Issue Automation Macro (PRO)

Configure a Datasource

Both a JDBC driver and a connection string are needed to connect an external database. The correct JDBC Driver needs to be copied into the classpath of the running Confluence server. Have a look at Atlassian Documentation - Database JDBC Drivers for more information .

Confluence already comes with some drivers bundled. If compatible with your DBMS, you don’t need to copy your own drivers.

Extra Security with Password Encryption

The password field for your DBMS is stored in the Confluence DB. To mitigate some risks, the password is encrypted in this process. Per default, we use our own encryption key. As additional security measurement, you can (and probably should) supply your own encryption key. For this, you need to provide the PTC_DATASOURCE_KEY environment variable when starting your Confluence instance.

Your Confluence instance needs to be restarted for the changes to take effect
Set the PTC_DATASOURCE_KEY environment variable to provide your custom encryption key
The encryption key needs to be 16, 24 or 32 bytes long
Ask your system administrator or solution partner for more information on this topic

Connection Examples

Below are Examples for PostgreSQL, Microsoft SQL Server, Oracle, MySQL JDBC Driver and Connection String.

PostgreSQL

JDBC Driver	`org.postgresql.Driver`
Connection String	`jdbc:postgresql://[host]:[port]/[database_name]`

Microsoft SQL Server

Microsoft JDBC Driver for SQL Server

JDBC Driver	`com.microsoft.sqlserver.jdbc.SQLServerDriver`
Connection String	`jdbc:sqlserver://[host]:[port];database=[database_name]`

jTDS 1.3.1 driver

JDBC Driver	`net.sourceforge.jtds.jdbc.Driver`
Connection String	`jdbc:jtds:sqlserver://[host]:[port]/[database_name]`

Oracle

JDBC Driver	`oracle.jdbc.driver.OracleDriver`
Connection String	`jdbc:oracle:thin:@//[host][:port]/[SID]`

MySQL

JDBC Driver	`com.mysql.jdbc.Driver`
Connection String	`jdbc:mysql://[host]:[port]/[database_name]`

Test the connection

To make sure that the JDBC Driver and the Connection String can connect to your DBMS click on test.

JDBC Driver and Connection String can connect - Test button | Image

Configure a Query

The configured queries can be selected in the PTC Placeholder Macro.

Parameter

Example

Name

Identification for the query. The name will be shown in the Placeholder Macro.

Project ID

Query

SQL Select query to be used. A query needs to have two columns in the result. The first column will be the displayed text in the replace dialog and the second column will be the replacement.

CODE

SELECT CONCAT(project_name, ' ', project_id) AS Text, 
  project_id
  FROM Projects

Test a query

Use the run button to check if a query is valid. If the query is valid the first ten results are returned.

Test a query | Image