Skip to main content
Skip table of contents

Datasources

A datasource can be a JDBC Connection to a SQL database or a REST API with different authentication options.

JDBC Datasources

A JDBC datasource consists of the following properties:

  • Name: The name identifying the database.

  • URL: The JDBC-formatted URL of the database. See the list below.

  • Driver: The JDBC database driver, see the table below. If you have already filled in a valid URL before, PocketQuery will recognize and filled this field automatically.

  • User: The database username. (Note: As PocketQuery is designed to be a read-only tool, we recommend to use a user with only reading permissions for a clean setup.)

  • Password: The database user password.

You can click the Test Connection button below the form to test your database connection.

Note: Confluence already comes with a bundle of JDBC drivers, but depending on the type of SQL database you want to query, you might need to add another one. Check the list of bundled JDBC drivers and if necessary download the driver, add it to your confluence-install/confluence/WEB-INF/lib directory and restart Confluence. For more information, see Prerequisites and Installation.

List of drivers and their respective URL syntax

Type

Driver

URL Syntax

MySQL

com.mysql.jdbc.Driver

jdbc:mysql://hostname:port/databaseName

PostgreSQL

org.postgresql.Driver

jdbc:postgresql://hostname:port/database

Microsoft SQL Server

com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc:sqlserver://hostname:port;database=databaseName

Microsoft SQL Server (jTDS)

net.sourceforge.jtds.jdbc.Driver

jdbc:jtds:sqlserver://hostname:port/databaseName

ORACLE

oracle.jdbc.driver.OracleDriver

jdbc:oracle:thin:@hostname:port Number:databaseName

DB2

com.ibm.db2.jdbc.net.DB2Driver

jdbc:db2://hostname:port/databaseName

Sybase

com.sybase.jdbc.SybDriver

jdbc:jtds:sybase://hostname:port/databaseName

SAP HANA

com.sap.db.jdbc.Driver

jdbc:sap://hostname:port/databaseName

HSQLDB

org.hsqldb.jdbc.JDBCDriver

jdbc:hsqldb:hsql://hostname:port/databaseName

AWS Redshift

com.amazon.redshift.jdbc42.Driver

jdbc:redshift://hostname:port/databaseName

MariaDB

org.mariadb.jdbc.Driver

jdbc:mariadb://hostname:port/databaseName

Snowflake

net.snowflake.client.jdbc.SnowflakeDriver

jdbc:snowflake://myorganization-myaccount.snowflakecomputing.com/?user=username&warehouse=mywarehouse&db=mydb&schema=myschema

If you are using Microsoft's Active Directory which uses a MS SQL database and you want to connect to that database via PocketQuery you might need to specify parameters like a domain name in your JDBC URL. For example, if you are using the jTDS JDBC driver your URL could look like this:

CODE
jdbc:jtds:sqlserver://[servername]:1433;domain=[domainname];sendStringParametersAsUnicode=false;selectMethod=cursor;databaseName=[databasename]

These parameters might be different for each driver (e.g. with the MSSQL driver you might need the IntegratedSecurity=true instead of the domain parameter). For this reason you should look up the documentation of your JDBC driver (e.g. jTDS documentation) and see what parameters you can use with your JDBC driver.

JNDI Datasources

A JNDI datasource consists of only two properties:

  • Name: The name identifying the database.

  • Resource name: Name of the JNDI resource that should be used. It needs to be set up beforehand. Make sure to include the context as prefix to the actual given name - in most cases this will be "java:comp/env/".

You can click the Test Connection button below the form to test your database connection.

A JNDI datasource requires configuration beforehand, as the connection details must be stored somewhere in the environment (e.g. your Tomacat server). If you do not know of any JNDI resources that already exist, we would recommend to use a standard JDBC datasource.

If you think JNDI could still be useful, talk to your server administrator and your database administrator about setting one up. You could start by reading Atlassian's Guide on how to configure a JNDI connection (especially steps 1 to 3 are relevant).

REST Datasources

You can configure arbitrary REST APIs as datasources. If you want a quick walkthrough, refer to Connecting to the First REST Datasource. As the settings of the datasource and further processing can largely differ depending on the REST API used, we also provide a list of REST examples for various services.

There are five types of REST datasources available in PocketQuery. All of them share the fields:

  • Type: Type of the REST datasource.

  • Name: The name identifying the datasource.

  • URL: Base URL that indicates where the REST endpoint is located. The URLs you set for each query will then be appended to this base URL.

  • Test URL: The test URL is optional and only needed to verify the connection settings when clicking on “Test connection”.

Additional fields differ for each type of REST datasource:

  • REST Basic: Uses basic authentication requiring username and password.

  • REST Application Link: Only works with Application Links, that have to be configured in the Confluence administration. Does not require further authentication information, as the connection via the link is already established with OAuth.

    Please make sure the application link uses the authorization type OAuth with impersonation. (Please refer to the Atlassian documentation to learn about the differences between OAuth with or without impersonation.)

  • REST OAuth: Uses OAuth 1 authentication requiring Consumer Key, Consumer Secret, Token, Token Secret and offers to change the signature method.

  • REST OAuth2: Uses OAuth2 authentication which means handling expiring access tokens. When the given access token expires, a new one is requested using the refresh process as defined in the standards RFC6749 and RFC6750. If a new refresh token is provided in response to the Refresh grant, the refresh token is updated on the datasource. If no initial access token is provided, a Client Credentials grant is sent to obtain an access token.

  • REST Custom: Allows to set arbitrary request parameters and headers.

You can click the Test Connection button below the form to test your REST connection, if you included a test url.


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.