On this page
Overview
SQL macros such as SQL, SQL-query, and SQL-file use data sources to connect 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.
You must have at least one data source to begin using this application within Confluence.
Add data source profiles
To add new or extend an existing data source profile:
Log in as a user with the Confluence administrators Global Permission.
Select Manage apps from the Administration menu (cog icon: ) at the top right of your screen. Then scroll down to Bob Swift Configuration on the sidebar and select SQL Configuration (see: ).
Select View and Modify Data Source Profiles (see: ) from the top navigation.
Click the button.
Setup Options
The provides you with two setup 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 are comfortable constructing a database URL.
Depending on the setup type, you are prompted for the following information:
Setup type | Field | Description |
|---|---|---|
Simple | Database type | The type of database you are connecting to. |
Simple | Data source name | You have the option to create a new data source profile by extending an existing data source. This may be useful if you'd like to tighten/alter the configuration parameter settings to be more/less restrictive for certain usage. You can of course then secure the usage using our Macro Security for Confluence app. |
Simple | Hostname | This is the hostname or IP address of your database server. |
Simple | Port | This is the port used to access your database on the server it is running against. |
Simple | Database | This is the name of your database. |
Both | Driver class | The class of JDBC driver used to connect to your database (e.g., com.mysql.jdbc.Driver, or, org.postgresql.Driver) |
Both | Driver JAR location | The path on your Confluence server where the JDBC driver is located. 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. |
By connection string | Connection string | The database URL is entered in this format (SQL Server example):
For example:
Once you select the By connection string option and start filling in the details of the Connection string field, the Simple (recommended) setup type is disabled (for you to switch to Simple). If you want to enable the Simple option, empty the details in the Connection string field. |
Both | Username | This is the username of your dedicated database user. |
Both | Password | This is the password for your dedicated database user. |
Quick connection strings
When using the By connection string setup option, the following examples can be quickly copied into the relevant sections and then modified:
The configuration for other databases (other than the ones listed in the table below) is similar to the information found in the examples section on: Data source configuration - application server.
Database | Example |
|---|---|
PostgreSQL |
|
PostgreSQL (using specific Schema) |
|
MySQL |
|
Microsoft SQL Server |
|
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.
Table: extended parameter options explained
Parameter | Macro Parameter | Default | Description |
|---|---|---|---|
Limit rows processed | limit | No limit | The maximum number of rows that is processed and displayed by SQL macros. This prevents queries that result in a large number of rows from using excessive resources. Individual queries can use the limit parameter to override this value. The following options are available for selection:
|
Limit query time | queryTimeout | None | The number of seconds that a query can take before we force a timeout. This prevents queries that take too long from impacting other users. Individual queries can use the queryTimeout parameter to override this value. Note, this parameter:
|
Limit max active | maxActive | None | Used to limit the number of actively executing SQL queries for a specific data source. Once the maximum active limit is reached, the next requested render of an sql macro using the specific data source returns an error message instead of trying to connect to the database. See this article for additional information. |
Show sql options | showSqlOptions | None | Since 6.4. A comma separated list of code or code-pro (Code Pro Macro) parameters used when Show SQL is selected. This allows for customization of how the SQL code is shown. See How to improve the display of SQL source. |
Connection properties | connectionProperties | None | A list of driver specific properties passed to the driver for creating connections. Each property is given as |
Initial SQLs | initalSql<n> | None | 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 use for Oracle: initialSql1=ALTER SESSION SET NLS_TERRITORY = GERMANY|initialSql2=ALTER SESSION SET NLS_LANGUAGE = GERMANNo results are kept and any error generates a macro exception. Using beforeSql is recommended for Postgres and other database that support multiple SQL statements as it is more efficient than multiple separated actions. |
Before SQL | beforeSql | None | SQL that is added before macro defined SQL. |
After SQL | afterSql | None | SQL that is added after macro defined SQL. |