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
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
PTC_DATASOURCE_KEYenvironment 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
Below are Examples for PostgreSQL, Microsoft SQL Server, Oracle, MySQL JDBC Driver and Connection String.
Microsoft SQL Server
Microsoft JDBC Driver for SQL Server
jTDS 1.3.1 driver
Test the connection
To make sure that the JDBC Driver and the Connection String can connect to your DBMS click on test.
Configure a Query
The configured queries can be selected in the PTC Placeholder Macro.
Identification for the query. The name will be shown in the Placeholder Macro.
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.
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.