Introduction

This Page contains all user information on the Plugin.

The current version is 1.0.2

User Information

General Information

This add-on allows JIRA to access an external database by a custom SQL Query that is settable for each custom field. However, the Query itself is identical for each issue.

To get rid of that drawback this plugin supports reading data from other fields into the SQL query in the same issue, identified by their id.

An example:

SELECT * FROM TESTTABLE WHERE type={customfield_10010} and owner={customfield_10026}

Supported Jira-versions and Databases

JIRA 7.0.0-7.3.0

MySQL
PostgreSQL
Oracle SQL
Microsoft SQL Server

 

Installing and getting started

Install this add-on from the Atlassian Marketplace or by getting the jar file and uploading it (All under JIRA Administration Panel / Add-ons).

After installing, create a new customfield:

https://confluence.atlassian.com/jira/adding-a-custom-field-185729521.html

You can find it by its Name: Live SQL Custom Fields.

The plugins customfield is only visible in the view screen of an issue, as you shouldn't be able to modify the content of it directly. It also prints some simple SQL error Messages if its not configured right, including important information as e.g. its ID.

 

Configuration

DataSource configuration

You can configure the field in the JIRA Administration Panel / Add-ons / Live SQL Custom Fields. However, you need to be logged in as JIRA Admin.

First you should create a DataSource:

Here you have to define the database connection parameters:

OptionDescription
NameThe name for the DataSource - be specific, don`t use the same name multiple times. You can change it later on aswell, as the DS is identified over an internal id.
Database TypeThe type of the database, single select field for all supported databases.
Driver

The driver class name, some databases might not need it (e.g. newer versions of mysql). Usually they are: oracle.jdbc.driver.OracleDriver, org.postgresql.Driver, com.mysql.jdbc.Driver, net.sourceforge.jtds.jdbc.Driver.

IMPORTANT. The drivers jar-file HAS to be included in jira, in the lib folder of the installation directory

UsernameThe username for the Database
PasswordThe password for the user
Connection URLThe Connection url to the database, ususally        jdbc:XXX//ipadress:port/db<;domain=>        XXX can be (for example)    mysql:   or   oracle:thin:@    or    postgresql: or jdts:sqlserver

 

After writing the configuration you should test it before saving (although you can also save a not working one).

The Test Connection button tries to establish a connection and gives you the result in a JIRA - popup flag. It also includes the sql error message (if there is one).

Now you need to configure the previously created custom field:

 

Custom Field configuration

First of all, you need to create a customfield of type "Live SQL CustomField" if you haven't yet.

To do this, go to the JIRA Administration Panel / Issues / Custom fields. Here click on "Add custom field" and choose "Advanced" or "All" in the left menu and search for "Live SQL CustomField".

You can configure the field in the JIRA Administration Panel / Add-ons / Live SQL Custom Fields / Live SQL Custom Field Configuration . However, you need to be logged in as JIRA Admin.

After opening the configuration screen you have to select the custom field to load from the single select and load it:

Here you can choose which DataSource the custom field should use, and define the sql query.

The sql query can include {customfield_xxxxx} to access the value of the named custom field. If you don`t know the custom fields id you want to use, you can search it by its name in the bottom form - Find the Id of a custom field.

Furthermore it supports

Additional Features:

Copying configuration from one field to another

You can "copy" the configuration easily:

Simply load the existing configuration you want to re-use, then select the other custom field (without pressing the load button), then use the save button.

The save button saves the config information always to the custom field that is chosen in the single select.

Apply the custom field configuration to existing issues

You can easily apply your configuration to your existing issues by selecting the projects in the update-section. A click on update will refresh the issues of the selected projects: