convertToSingleSelect behaviour

[Kyrylo Filipenkov]

Hello,

I use benaviour and rest endpoint to query the database.
There are two fields. One for input data. Other for output data.
I want to query the database by value from the first field and output the result into the second field. The result in the second field is a drop-down list.

But I do not know how to transfer data from the first field to the rest endpoint script to execute the query in the database 

Help me please with the solution of the problem.

[Kyrylo Filipenkov]

@Steven Behnke This is due to the fact that the value of the first field in the database corresponds to several values. For example, it is my creation form:

Behaviour:

def personnelNumber = getFieldById("customfield_10604")
def gpid = getFieldById("customfield_10228").value
personnelNumber.convertToMultiSelect([ 
 ajaxOptions: [
 url : getBaseUrl() + "/rest/scriptrunner/latest/custom/getValue", 
 query: true,
 minQueryLength: 4, 
 keyInputPeriod: 500, 
 formatResponse: "general", 
 ]
])

REST Endpoint:

import com.atlassian.jira.config.database.DatabaseConfigurationManager
import com.atlassian.jira.config.database.JdbcDatasource
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import groovy.transform.BaseScript
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
import java.sql.Driver

@BaseScript CustomEndpointDelegate delegate
getValue(httpMethod: "GET") { MultivaluedMap queryParams ->
 def query = queryParams.getFirst("query") as String
 def rt = [:]
 def datasource = ComponentAccessor.getComponent(DatabaseConfigurationManager).getDatabaseConfiguration().getDatasource() as JdbcDatasource
 def driver = Class.forName('com.mysql.jdbc.Driver').newInstance() as Driver
 def props = new Properties()
 props.setProperty("user", "user")
 props.setProperty("password", "password")
 def conn = driver.connect("jdbc:mysql://db_bus", props)
 def sql = new Sql(conn) 
 def gpid = (getFieldById("customfield_10228").value).toInteger()
 try {
 sql
 def rows = sql.rows("select personnel_number from table where gpid=$gpid")
 rt = [
 items : rows.collect { GroovyRowResult row ->
 [
 value: row.get("personnel_number"),
 html: row.get("personnel_number"),
 label: row.get("personnel_number"),
 ]
 },
 total: rows.size(),
 footer: "Choose a personnel_number"
 ]
 } finally {
 sql.close()
 conn.close()
 }
 return Response.ok(new JsonBuilder(rt).toString()).build()
}

 But it doesn't work? i have an error message:

2018-03-03 11:18:25,071 ERROR [common.UserCustomScriptEndpoint]: *********************************************************************************
2018-03-03 11:18:25,072 ERROR [common.UserCustomScriptEndpoint]: Script endpoint failed on method: GET getValue
org.codehaus.groovy.runtime.typehandling.GroovyCastException: Cannot cast object 'customfield_10228' with class 'java.lang.String' to class 'groovy.lang.Closure'
 at com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate.methodMissing(CustomEndpointDelegate.groovy:22)
 at Script153$_run_closure1.doCall(Script153.groovy:24)
 at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint.doEndpoint(UserCustomScriptEndpoint.groovy:304)
 at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint.getUserEndpoint(UserCustomScriptEndpoint.groovy:195)

[Steven Behnke]

This use-case is not clear. What is the point of two fields? If the second field is dependent on the first field, why is it a drop-down list? Wouldn't it be read only?

[Kyrylo Filipenkov]

This is due to the fact that the value of the first field in the database has several corresponding values. For example, it is my creation form. I want to make a request to the database by the value of the field "GPID_"

I use a default behaviour example:

def personnelNumber = getFieldById("customfield_10604")
def gpid = getFieldById("customfield_10228").value
personnelNumber.convertToMultiSelect([ 
 ajaxOptions: [
 url : getBaseUrl() + "/rest/scriptrunner/latest/custom/getValue", 
 query: true,
 minQueryLength: 4, 
 keyInputPeriod: 500, 
 formatResponse: "general", 
 ]
])

 And my REST Endpoint :

import com.atlassian.jira.config.database.DatabaseConfigurationManager
import com.atlassian.jira.config.database.JdbcDatasource
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import groovy.transform.BaseScript
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
import java.sql.Driver

@BaseScript CustomEndpointDelegate delegate
getValue(httpMethod: "GET") { MultivaluedMap queryParams ->
 def query = queryParams.getFirst("query") as String
 def rt = [:]
 def datasource = ComponentAccessor.getComponent(DatabaseConfigurationManager).getDatabaseConfiguration().getDatasource() as JdbcDatasource
 def driver = Class.forName('com.mysql.jdbc.Driver').newInstance() as Driver
 def props = new Properties()
 props.setProperty("user", "user")
 props.setProperty("password", "password")
 def conn = driver.connect("jdbc:mysql://db_bus", props)
 def sql = new Sql(conn) 
 def gpid = (getFieldById("customfield_10228").value).toInteger()
 try {
 sql
 def rows = sql.rows("select personnel_number from table where gpid=$gpid")
 rt = [
 items : rows.collect { GroovyRowResult row ->
 [
 value: row.get("personnel_number"),
 html: row.get("personnel_number"),
 label: row.get("personnel_number"),
 ]
 },
 total: rows.size(),
 footer: "Choose a personnel number"
 ]
 } finally {
 sql.close()
 conn.close()
 }
 return Response.ok(new JsonBuilder(rt).toString()).build()
}

But it dosn't work, i have en error message:

2018-03-03 11:18:25,071 ERROR [common.UserCustomScriptEndpoint]: *********************************************************************************
2018-03-03 11:18:25,072 ERROR [common.UserCustomScriptEndpoint]: Script endpoint failed on method: GET getValue
org.codehaus.groovy.runtime.typehandling.GroovyCastException: Cannot cast object 'customfield_10228' with class 'java.lang.String' to class 'groovy.lang.Closure'
 at com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate.methodMissing(CustomEndpointDelegate.groovy:22)
 at Script153$_run_closure1.doCall(Script153.groovy:24)
 at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint.doEndpoint(UserCustomScriptEndpoint.groovy:304)
 at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint.getUserEndpoint(UserCustomScriptEndpoint.groovy:195)

[Kyrylo Filipenkov]

@Steven Behnke This is due to the fact that the value of the first field in the database corresponds to several values.

Behaviour:

def personnelNumber = getFieldById("customfield_10604")
def gpid = getFieldById("customfield_10228").value
personnelNumber.convertToMultiSelect([ 
 ajaxOptions: [
 url : getBaseUrl() + "/rest/scriptrunner/latest/custom/getValue", 
 query: true,
 minQueryLength: 4, 
 keyInputPeriod: 500, 
 formatResponse: "general", 
 ]
])

REST Endpoint:

import com.atlassian.jira.config.database.DatabaseConfigurationManager
import com.atlassian.jira.config.database.JdbcDatasource
import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import groovy.transform.BaseScript
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
import java.sql.Driver

@BaseScript CustomEndpointDelegate delegate
getValue(httpMethod: "GET") { MultivaluedMap queryParams ->
 def query = queryParams.getFirst("query") as String
 def rt = [:]
 def datasource = ComponentAccessor.getComponent(DatabaseConfigurationManager).getDatabaseConfiguration().getDatasource() as JdbcDatasource
 def driver = Class.forName('com.mysql.jdbc.Driver').newInstance() as Driver
 def props = new Properties()
 props.setProperty("user", "user")
 props.setProperty("password", "password")
 def conn = driver.connect("jdbc:mysql://db_bus", props)
 def sql = new Sql(conn) 
 def gpid = (getFieldById("customfield_10228").value).toInteger()
 try {
 sql
 def rows = sql.rows("select personnel_number from table where gpid=$gpid")
 rt = [
 items : rows.collect { GroovyRowResult row ->
 [
 value: row.get("personnel_number"),
 html: row.get("personnel_number"),
 label: row.get("personnel_number"),
 ]
 },
 total: rows.size(),
 footer: "Choose a personnel_number"
 ]
 } finally {
 sql.close()
 conn.close()
 }
 return Response.ok(new JsonBuilder(rt).toString()).build()
}

 Error message:

2018-03-03 11:18:25,071 ERROR [common.UserCustomScriptEndpoint]: *********************************************************************************
2018-03-03 11:18:25,072 ERROR [common.UserCustomScriptEndpoint]: Script endpoint failed on method: GET getValue
org.codehaus.groovy.runtime.typehandling.GroovyCastException: Cannot cast object 'customfield_10228' with class 'java.lang.String' to class 'groovy.lang.Closure'
 at com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate.methodMissing(CustomEndpointDelegate.groovy:22)
 at Script153$_run_closure1.doCall(Script153.groovy:24)
 at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint.doEndpoint(UserCustomScriptEndpoint.groovy:304)
 at com.onresolve.scriptrunner.runner.rest.common.UserCustomScriptEndpoint.getUserEndpoint(UserCustomScriptEndpoint.groovy:195)

[Steven F Behnke]

I suggest you return to the documentation. There's a lot going on here and not a lot of it is correct.

First, it's important to know the distinction between Behaviors and REST Endpoints - Behaviors uses groove to run JAVASCRIPT on the clients browser - This allows you to dynamically update the UI on fields. REST Endpoints registers a Groovy Closure at runtime as if it was a Java Jira Plugin Module. The abilities between these two systems STOP at sharing the same syntax. 

Second, you may not call 'getFieldById' in a REST Endpoint. That method call is for the Behaviors Module, not the REST Endpoint module. Instead, you must obtain the CustomFieldManager and the Issue object and obtain the field/value that way instead. 'getFieldById' is literally checking the id attribute of the HTML on screen. 'getFieldByName' literally checks the name attribute of the HTML on screen. This makes zero sense in the REST Endpoint context!

Third, since 'convertToSingleSelect' is just running 'AJS.SingleSelect()' on fields, there's no DOCUMENTED way to hook into the behavior and update another field when data is received. In javascript, this would typically be another function bound on the 'done' or 'always' callback. As far as I know, this is not exposed by Behaviors.

I am working on a similar project right now and I'm storing the user's input in the first field, and then using a listener to listen for an update and make the call to update the ticket asynchronously. I suggest you take the same route.

[Kyrylo Filipenkov]

Thanks for the answer.
Yes, in principle, I understand the difference between behaviour and restendpoint.
I thought it was possible to pass dynamic values from the field to the restendpoint.
Could you talk more about the listener? Or tell where I can read in that direction.

[Steven F Behnke]

I think I can detail the problem - 

• You have a REST Endpoint and a Behavior configured
• You have this Endpoint linked up to a field via a Behavior Select-List Conversion set in the Initializer Function, essentially via the documented example for Select-List Conversions.
• You'd like to affect a second field based on the selection chosen in the first field (which is itself returned dynamically)
• This is a problem because the Select-List Conversion is attached to a Field 1 and the 'convertToSingleSelect()' is not smart enough to affect a second field on it's own somehow, nor can you write a function in the 'convertToSingleSelect()' to solve this AFAIK.
• Additionally, the REST Endpoint has no knowledge of the field/screen/whatever, it's simply returning an HttpResponse to a HttpRequest, this data is interpreted automatically by the converted field.

I will make some more suggestions -

• Try adjusting your Behavior to have an additional script set to Field 1. This should trigger when Field 1 is changed. You can use this information to make another call and set the field.
  • https://scriptrunner.adaptavist.com/5.0.0/jira/behaviours-overview.html#_using_a_server_side_validator_to_set_the_fix_versions_required Attaching a script to a field will call when it changes.
• Use a custom listener to listen for ISSUE_CREATED, ISSUE_UPDATED, write a condition so that it only affects the issues you want to affect, and then write a function to make another call and set the field. https://scriptrunner.adaptavist.com/5.0.0/jira/listeners.html#_custom_listener_example
  • You would likely use IssueService to set the field in this example.

[Kyrylo Filipenkov]

As far as I understand, the listener is triggered when there issue is already a created or when you click create button.
In my case, it is necessary that the processing is triggered dynamically on the create  form.

[Kyrylo Filipenkov]

@Steven F Behnke sank you for answers. I find a solution.

First, in behaviour it need to transer a value of field, add "?query=$gpid" to url:

 url : getBaseUrl() + "/rest/scriptrunner/latest/custom/getValue?query=$gpid",

And in REST Endpoint get this value

def query = queryParams.getFirst("query") as Integer

And than, use a variable "query" in sql query

def rows = sql.rows("select personnel_number from table where gpid=$query") 

 and it works's.

[Steven F Behnke]

That is very dangerous. That leaves you vulnerable to sql injection

At least make sure $query is a number before passing it to sql