| Div | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
On this page
|
...
Overview
SQL Macros macros such as SQL, SQL-query, and SQL-file use Data Sources use data sources to connect and access your databases. Creating one or more Data Source Profiles more data source profiles is the fastest and most convenient method of establishing a connection. You can also create Data Source Profiles create data source profiles that extend Data Sources data sources configured within your application server.
| Info |
|---|
Note: you You must have at least one Data Source in order one data source to begin using this application within Confluence. |
...
Add data source profiles
To add new or extend an existing Data Source Profileexisting data source profile:
- Log in as a user with the Confluence Administrators the Confluence administrators Global Permission.
- Select Add-onsManage apps from the Administration menu (cog icon:
) at the top right of your screen. Then scroll down to Bob Swift Configuration on the left-hand nav bar and choose sidebar and select SQL Configuration (see: 
). - Select View and Modify Data Source Profiles (see:
) from the top navigation. - Click on the
buttonthe button.
Setup Options
The Add Profile screen will provide 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 'll be are prompted for the following information.:
| Setup type | Field | Description | |||||
|---|---|---|---|---|---|---|---|
| Simple | Database type | They The type of database you are connecting to. | |||||
| Simple | Data source name | You have the option to create a new Data Source Profile new data source profile by extending an existing Data Sourcedata 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 our CMSP App 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 that you will use 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.
| |||||
| By connection string | Connection string | The database URL is entered in this format ( SQLServer SQL Server example): jdbc:sqlserver://<hostname>:<port>;database=<database>For example: jdbc:sqlserver://yourserver:1433;database=confluence
| |||||
| 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:
| Tip |
|---|
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 | dbUrl=jdbc:postgresql://localhost:5432/test | dbUser=confluence | dbPassword=confluence | dbDriver=org.postgresql.Driver | dbJar=https://jdbc.postgresql.org/download/postgresql-9.3-1102.jdbc41.jar |
Extended Parameters
...
| 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-9.3-1102.jdbc41.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.34/mysql-connector-java-5.1.34.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/sqljdbc4.jar |
Extended parameters
Data source profiles allow for the configuration of extended parameter options. These profile-wide settings will be are used by all SQL Macros, if not overridden at the Macro-macro level.
Table: extended parameter options explainedoptions explained
| Parameter | Macro Parameter | Default | Description | |||||
|---|---|---|---|---|---|---|---|---|
| Limit rows processed | limit | No limit | The maximum number of rows that will be 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 the limit parameter parameter to override this value. The following options are available for selection:
| |||||
| Limit query time | queryTimeout | None | The number The number of seconds that 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 the queryTimeout parameter parameter to override this value.
| |||||
| Limit max active | maxActive | NooneNone | 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 a an sql macro macro using the specific data source will return 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 of code or code-pro (Code Pro Macro) parameters used when when Show SQL is 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 as name=value, multiple properties are separated by semicolons (;). See See Apache Tomcat JNDI resources. | |||||
| 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:
No results are kept and any errors will generate error generates a macro exception. Using beforeSql is recommended for Postgres and other database that support multiple sql SQL statements as it is more efficient than multiple separated actions. | |||||
| Before SQL | beforeSql | None | SQL that is added before Macro macro defined SQL. | |||||
| After SQL | afterSql | None | SQL that is added after Macro macro defined SQL. |
...
View data source profiles
To view , add, or modify a Data Source Profilea data source profile:
- Log in as a user with the Confluence Administrators the Confluence administrators Global Permission.
- Select Add-onsManage apps from the Administration menu (cog icon: ) at the top right of your screen. Then scroll down to Bob Swift Configuration on the left-hand nav bar and choose sidebar and select SQL Configuration (see: ).
- Select View and Modify Data Source Profiles (see: ) from the top navigation.You will see a A list of Data Source Profilesdata source profiles and their connection status is shown.
...
Table: Status indicators explained
| Indicator | Definition |
|---|---|
 | Indicates that the connection information supplied for |
...
| the data source profile allows 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 |
...
Modify Data Source Profiles
...
| . | |
 | Indicates that your connection-specific information is incorrect. See Troubleshooting data source profile connections. |
Modify data source profiles
To modify a data source profile:
- Log in as a user with the Confluence Administrators the Confluence administrators Global Permission.
- Select Add-onsManage apps from the Administration menu menu (cog icon: ) at the top right of your screen. Then scroll down to Bob Swift Configuration on the left-hand nav bar and choose sidebar and select SQL Configuration (see: ).
- Select View and Modify Data Source Profiles (see: ) from the top navigation.You will see a A list of Data Source Profilesdata source profiles and their connection status is shown.
- Click on the edit link to the right of the Data Source Profile the data source profile you would like to modify.
- The edit dialog will appear and will default appears and defaults to the "By connection string" view view. You can edit in place or toggle to the "Simple" setup type and make necessary changes.
- To save changes, click on the Next button (see:
), and then the , Save Profile (see: 
) button.
Remove data source profiles
Security
Data sources are shared resources. Some of these resources may need to be secured for use only by specific users or groups. This can be accomplished by using CMSP and configuring access to the macro and perhaps to individual profiles.
Copy and paste the configuration into your data source profile configurationQuick Configurations
...
| Warning |
|---|
Removing data source profiles extended by SQL Macros renders the macro and section of the page broken. |
To remove a 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. You see a list of data source profiles and their connection status.
- Click the Remove link to the right of the data source profile you would like to remove.
- When prompted, confirm or exit.
Test a data source profile
To test one or more data source profiles:
| Tip | ||
|---|---|---|
| ||
All 5 db parameters are required when defining db access directly. Otherwise you will likely get a dataSource not found message. |
...
| title | Using the lib location |
|---|
...
- Create a new page or edit an existing.
- Select Insert Markup from menu (icon:
) on the top nav. Within the dialog window, add the following wiki markup and make sure to change the dataSource to a data source profile that you have created:
select 1No Format {sql-query:dataSource=example}select "This is working" {sql-query}
...
dbUrl=jdbc:postgresql://localhost:5432/test | dbUser=confluence | dbPassword=confluence | dbDriver=org.postgresql.Driver | dbJar=https://jdbc.postgresql.org/download/postgresql-9.3-1102.jdbc41.jar
If you need to specify a particular PostgreSQL schema, then include "?currentSchema=<schema_name>", such as:
dbUrl=jdbc:postgresql://localhost:5432/test?currentSchema=jiraschema | dbUser=confluence | dbPassword=confluence | dbDriver=org.postgresql.Driver | dbJar=https://jdbc.postgresql.org/download/postgresql-9.3-1102.jdbc41.jar
...
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.34/mysql-connector-java-5.1.34.jar
...
dbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver | dbUrl=jdbc:sqlserver://localhost:2433;database=test;integratedSecurity=false | dbUser=confluence | dbPassword=confluence | dbJar=../lib/sqljdbc4.jar
The configuration for other databases is similar to the information found in the examples section on: Data source configuration - application server.
Click Insert and then save the page.
If the test is successful, the following is rendered on the page: 
Next steps: get to work 
You've configured the product, you understand the differences between data sources and data source profiles, and you likely have a few configured. You are now ready to start using SQL macros within Confluence pages.
