| Div | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
On this page
|
Overview
SQL, SQL File, and SQL Query macros use data sources to connect to and access your databases. Creating one or more data source profiles is the fastest and most convenient method of establishing a connection. You can also create data source profiles that extend data sources configured within your application server.
| Info |
|---|
You must provide at least one data source to use this application within Confluence. |
...
| Note | ||
|---|---|---|
| ||
All profile sensitive information (such as passwords) are now encrypted to provide enhanced security. This encryption-decryption of profile passwords is not available if the app is downgraded from version 10.3 to a lower release version. If you must downgrade the app version, passwords of all the profiles must be re-entered to ensure proper working of profiles. |
...
To add a new or extend an existing data source or global data source (defined in server.xml) profile:
- Log in as a Confluence administrator with Global Permission.
- Select Manage apps from the CONFLUENCE ADMINISTRATION menu (cog icon:
) at the top right of your screen. - Scroll down to BOB SWIFT CONFIGURATION in the navigation pane to the left and click SQL (Pro Edition) (see:
). - Click View and modify data source profiles (see:
) tab at the top. - Click the
button.
The Add profile screen provides you with two setup type options:
- Simple - this is the most straightforward way to connect to your database.
- By connection string - use this option if you want to specify additional parameters and you are comfortable with constructing a database URL.
...
| Database | Example |
|---|---|
| PostgreSQL | dbUrl=jdbc:postgresql://localhost:5432/test | dbUser=confluence | dbPassword=confluence | dbDriver=org.postgresql.Driver | dbJar=https://jdbc.postgresql.org/download/postgresql-42.2.5.jar |
| PostgreSQL (using specific Schema) | dbUrl=jdbc:postgresql://localhost:5432/test?currentSchema=jiraschema | dbUser=confluence | dbPassword=confluence | dbDriver=org.postgresql.Driver | dbJar=https://jdbc.postgresql.org/download/postgresql-42.2.5.jar |
| MySQL | 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.47/mysql-connector-java-5.1.47.jar |
Microsoft SQL Server | dbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver | dbUrl=jdbc:sqlserver://localhost:2433;database=test;integratedSecurity=false | dbUser=confluence | dbPassword=confluence | dbJar=../lib/mssql-jdbc-7.0.0.jre8.jar |
Do we need to add more database examples in the above table?
| Tip |
|---|
|
Extended parameters
Data source profiles allow for the configuration of extended parameter options. These profile-wide settings are used by all SQL macros if not overridden at the macro-level.
...
- Log in as a user with the Confluence administrators Global Permission.
- Select Manage apps from the CONFLUENCE ADMINISTRATION menu (cog icon:
) at the top right of your screen. - Scroll down to BOB SWIFT CONFIGURATION in the navigation pane to the left and click SQL (Pro Edition) (see:).
- Click View and modify data source profiles (see:) tab at the top.
- You will see a list of data source profiles (changed the screenshot, but can be changed again with the error message) and their connection status.
...
| Indicator | Definition |
|---|---|
 | Indicates that the connection information supplied for the data source Profile will allow connections from Confluence to the targeted database. This does not indicate that the data source profile is in use by any macros on Confluence pages. |
 | Indicates that your connection-specific information is incorrect. See troubleshooting data source profile connections. |
Modify data source profiles
...
- Create a new page or edit an existing one.
- Enter {SQL to get macro suggestions for the SQL macros. Select the SQL macro.
- The SQL settings navigation pane pops up.
- Enter the Data source profile under the SQL statement tab.
Enter the following query in the SQL statement text area.
- Click Preview (See:
) to see the results.