Queries

A query fetches data from one of your datasources. Its properties largely depend on the type of datasource being used.

Properties

All queries consist of the following properties:

• Name: The name identifying the query. Names must start with a letter and only contain letters [A-Za-z_-], numbers, hyphens and underscores.

• Datasource: The datasource on which the query is to be run (referencing a datasource entity). You can filter the list of queries by their datasources using the dropdown on the top left of the query list.
  

• Template: A template used to display the result of the query in the Jira issue containing the PocketQuery add-on. References a template entity. If the Default template is selected, a default PocketQuery template is used.

• Converter: Converter used to transform the response of the datasource before it is handed on to the template. See the “Converters” section below.

Depending on the query type there are some more properties. Please refer to the sections below.

SQL Queries on JDBC datasources

Additional Properties

For JDBC datasources the following additional properties are available:

• Statement: The query statement. It consists of arbitrary SQL code, that forms exactly one single SQL statement. (If you need multiple statements, please use multiple queries or create a stored procedure in your database.) Optionally, the code can be enhanced by parameters prefixed by a colon in the form :parameterName. These parameter placeholders (“named parameters”) are filled in during runtime of the PocketQuery add-on (see the example statements below for further explanation). It is possible to use wildcards within theses parameters (see section "Using Wildcards for Parameters" below for more information)
  

• Parameter Types: By default they are all strings. Currently, the types Integer, ListOfStrings, ListOfIntegers, Boolean and Constant are also supported. These have to be specified explicitly. For each parameter, add its name on the left and from the select box on the right choose the correct type. Examples where this is required is the LIMIT clause (e.g. “LIMIT :number“) or the IN clause (e.g. “:name IN (:myListOfStrings)“). See the “Parameter Types” section below.

Parameter Types 

By default, all parameters in a query will become strings. For example:

SELECT Name, Population
FROM Country
WHERE Continent = :continent

This will become something like the following after the replacement took place:

SELECT Name, Population
FROM Country
WHERE Continent = 'Europe'

Now, in certain cases this behaviour is not intended. Examples are:

• Numbers

• Boolean values

• Lists for IN-clauses

• Constant values in the SELECT clause

That is why we implemented the Parameter Types configuration option for queries. You can configure types for all your query parameters in the section "Parameter Types" by inserting their names and selecting their respective types from the dropdown. If parameters are not specified the default value will be “String”.

Currently supported parameter types:

• String: This is the default parameter type that does not need to be specified. Values provided for query parameters will be wrapped in single quotes.

• Number: Your numeric value will be inserted in the query with no quotes.

• ListOfIntegers: Your integer list will be inserted as a comma-separated list of values. This is useful for IN-clauses with numbers, e.g.... IN(1,2,3,4,5) ...

• ListOfStrings: Your list of strings will be inserted as a comma-separated list of values with single quotes. This is useful for IN-clauses with strings, e.g. ... IN('one','two','three') ...

• Boolean: Your true/false value will be inserted without quotes.

• Constant: This is useful for parameters in the SELECT clause like SELECT :mycolumn ... where you simply want the value to be inserted as a constant.

Query statement examples:

SELECT Name, GovernmentForm, Region, HeadOfState
FROM Country
WHERE Continent = :continent;
 
SELECT Name
FROM Country
INNER JOIN CountryLanguage ON Country.Code = CountryLanguage.CountryCode
WHERE Language = :language;
 
SELECT *
FROM City
WHERE Population > :population;
 
SELECT Name, GovernmentForm, Region, HeadOfState
FROM Country
WHERE Population > :minPopulation AND Population < :maxPopulation;
 
SELECT Name
FROM Country
LIMIT :max;  -- "max" needs the parameter type "Integer"
 
SELECT Name, GovernmentForm
FROM Country
WHERE Name IN (:names);  -- "names" needs the parameter type "ListOfStrings"

Queries on REST Datasources

Additional Properties

For REST datasources the following additional properties are available:

• REST URL: The URL path that is appended to the root URL of this datasource when calling it. It may contain parameters prefixed by a colon in the form :parameterName. These parameter placeholders (“named parameters”) are filled in during runtime of the PocketQuery add-on. It is possible to use wildcards within theses parameters (see section "Using Wildcards for Parameters" below for more information). If special characters occur, they need to be url encoded (e.g. the percentage sign "%" is encoded as "%25", "ä" is encoded as "%C3%A4"). The structure of the REST URL depends entirely on the REST API used - please refer to its documentation.

Using Parameters in Queries

As mentioned above, both SQL queries and REST calls may include PocketQuery parameters. These parameters can be set when adding the PocketQuery add-on to an issue which makes the query much more versatile.

PocketQuery parameters are marked with a colon in front of their name (:parameterName). For SQL queries, it is possible to set the type of the parameter to influence how the final values will be included in the SQL query. See the section Parameter Types above for more details. For REST calls there are no different parameter types - all parameter values will be URL encoded and then included in the REST call URL.

Using Wildcards for Parameters

Instead of using fixed values, it is possible to use wildcards to set a PocketQuery parameter. These wildcards will be replaced by the actual content before the query is executed. The wildcards can be set by the user when adding the PocketQuery add-on to an issue. Additionally, it is also possible to set wildcards directly within the query statement or REST URL itself. This way, the add-on user herself has no opportunity to adjust the values manually which can be important for security.

The following wildcards can be set as parameters values:

• @username - the username of the current Jira user

• @userfullname - the full name of the current Jira user

• @useremail - the email address of the current Jira user

• @issue.id - the ID of the current Jira issue

• @issue.summary - the summary of the current Jira issue

• @issue.description - the description of the current Jira issue

• @issue.status.id - the ID of the current Jira issue status

• @issue.status.name - the name of the current Jira issue status

• @project.key - the key of the current Jira project

• @project.name - the name of the current Jira project

• @issue.assignee - the username of the current Jira issue assignee

• @issue.assignee.name - the username of the current Jira issue assignee

• @issue.assignee.email - the email address of the current Jira issue assignee

• @issue.reporter - the username of the current Jira issue reporter

• @issue.reporter.name - the username of the current Jira issue reporter

• @issue.reporter.email - the email address of the current Jira issue reporter

• @issue.duedate - the due date of the current Jira issue

• @issue.resolutiondate - the resolution date of the current Jira issue

• @issue.cf_{CUSTOM FIELD ID} - the value of a custom field. You need to specify the ID of your custom field. (e.g. "@issue.cf_12345")

For users that are not logged into Jira @username, @userfullname and @usermail will return "anonymous".

Note: When using a wildcard within the query statement or REST URL, it has to be prefixed with a colon to mark it as a parameter. So the resulting syntax is something like "SELECT * FROM mytable WHERE id LIKE :@issue.id".

Continue with Templates