Page tree
Skip to end of metadata
Go to start of metadata

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. Names must start with a letter and only contain letters [A-Za-z_-], numbers, hyphens and underscores.
  • 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 in 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.

List of drivers and their respective URL syntax

TypeDriverURL syntax
MySQLcom.mysql.jdbc.Driverjdbc:mysql://hostname:port/databaseName
PostgreSQLorg.postgresql.Driverjdbc:postgresql://host:port/database
Microsoft SQL Servernet.sourceforge.jtds.jdbc.Driverjdbc:jtds:sqlserver://server:port/databaseName
ORACLEoracle.jdbc.driver.OracleDriverjdbc:oracle:thin:@hostname:port Number:databaseName
DB2COM.ibm.db2.jdbc.net.DB2Driverjdbc:db2:hostname:port Number/databaseName
Sybasecom.sybase.jdbc.SybDriverjdbc:sybase:Tds:hostname: port Number/databaseName

If you are using Microsofts 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:

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. Names must start with a letter and only contain letters [A-Za-z_-], numbers, hyphens and underscores.
  • 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 quite some 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 can e.g. start by reading Atlassian's Guide on how to configure a JNDI connection (expecially steps 1 to 3 are relevant).


REST Datasources

You can configure arbitrary REST APIs as datasources. If you want a quick walk through, refer to our Getting Started Part 2 - REST and Converters. 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. Names must start with a letter and only contain letters [A-Za-z_-], numbers, hyphens and underscores.
  • 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 Confluence 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, it is stored as well. This process requires the initial access token, the (initial) refresh token, client id, client secret and the auth URL (where the new access token can be requested).
  • 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.

 

  • No labels