Page Comparison

The concept of script parameters is to allow the SQL or SIL scripts to get data from the UI of the field or even from another fields on the screen or issue. This will allow the query to retrieve different results based on the values of dependent fields.

There are two types of parameters that can be used inside SQL the configuration scripts:

Internal parameters

The syntax of an internal parameter internal parameters are needed when you want to use the UI values of the field’s columns. Here is a comparison of how the internal parameters are used in SQL scripts is & SIL scripts:

columnIndex is a number that represents the position of the column in the field configuration (Columns). The columnIndex of the first column is 1.

The internal parameters are used when you want to obtain the current values for the field’s columns.

Example

Let’s assume we have the following list of columns for a field configuration:

If you want to insert or update a record in the database, you click on the Insert/Update (https://appfire.atlassian.net/wiki/spaces/PSJ/pages/544113815/Using+the+Advanced+Database+Row+Field#Set-the-value-of-a-generic-field) option, you fill in the form values and then you submit it, triggering the run of the INSERT/UPDATE script.

Example of INSERT scriptscripts:

1. SQL

INSERT INTO countries(id, code, name, independence, gdp_capita, capital, is_eu) 
VALUES ({:1}, {:2}, {:3}, {:4}, {:5}, {:6}, {:7})

2. SIL

string sql = "INSERT INTO countries(id, code, name, independence, gdp_capita, capital, is_eu) VALUES (?::int, ?, ?, ?::date, ?::real, ?, ?::boolean)";
sql("postgresDB", sql, argv[0], argv[1], argv[2], argv[3], argv[4], argv[5], argv[6]);

Example of UPDATE scriptscripts:

1. SQL

UPDATE countries SET code = {:2}, name = {:3}, independence = {:4}, gdp_capita = {:5}, capital = {:6}, is_eu = {:7} 
WHERE id = {:1}

2. SIL

string sql = "UPDATE countries SET code = ?, name = ?, independence = ?::date, gdp_capita = ?::real, capital = ?, is_eu = ?::boolean WHERE id = ?::int";
sql("postgresDB", sql, argv[1], argv[2], argv[3], argv[4], argv[5], argv[6], argv[0]);

The internal parameters are used to refer the actual values that were filled in the Insert/Update form.

Similarly, let’s take a Search form with inputs that correspond to columns in the configuration:

In order to use their values when running the SEARCH script, they will have to be referred to using internal parameters. AnchorsearchSQLsearchSQL

Example of SEARCH scriptscripts:

1. SQL

SELECT id, code, name, independence, gdp_capita, capital, is_eu FROM countries 
WHERE code LIKE '%' || {:2} || '%'

2. SIL

string sql = "SELECT id, code, name, independence, gdp_capita, capital, is_eu FROM countries WHERE code LIKE ? || '%'";
string[] vals = sql("postgresDB", sql, argv[1]);
return vals;

External parameters

The syntax of an external parameter used in SQL scripts is {screen:fieldNameOrId} or {issue:fieldNameOrId}external parameters are needed when you want to use the values of another Jira fields.

There are two types of external parameters: issue and screen.

Use issue prefix for parameters if you want to employ their the field value that is stored in the Jira issue.

Use screen prefix for parameters if you want to employ their the field value that is displayed in the screen. This can happen be useful when someone is creating a new issue and has not yet submitted it, so the issue value doesn’t exist. Or simply when you want to correlate with the value of that field from the current screen as it might have changed from what is saved in the Jira issue.

Here is a comparison of how the external parameters are used in SQL & SIL scripts:

The fieldNameOrId can be chosen fromrefer to:

• standard variables - This is the list of fields that come out-of-the-box with Jira like assignee, description, summary, etc. For a full list of fields see Standard Variables

• custom fields - This is a complete list of custom fields from your Jira instance

Example of SEARCH script with external parameters:

1. SQL:

SELECT id, code, name, independence, gdp_capita, capital, is_eu FROM countries 
WHERE name LIKE {screen:summary} || '%'

Important: external parameters need to be explicitly declared in the Script parameters multi-select, otherwise their values will not be correctly calculated:

Image Removed

The previous script means the values that will be returned by the Search are those database records that have their name column starting with the screen value of the summary Jira standard field.

2. SIL

2. :

Example of SEARCH script:

string sql = "SELECT id, code, name, independence, gdp_capita, capital, is_eu FROM countries WHERE codename LIKE ? || '%'";
string[] vals = sql("postgresDB", sql, argv[1"summary"]);
return vals;

This above script will retrieve the same results that its SQL equivalent.

Image Added

The previous script means the values that will be returned by the Search are those database records that have their name column starting with the screen value of the summary Jira standard field. If the issue has ‘Sw’ value for summary, countries like Sweden and Switzerland will both show up in the search results.