Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

convertToSingleSelect behaviour

Edited

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.

2 answers

@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:

image.pngBehaviour:

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)

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?

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_"

image.pngI 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)

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

image.pngBehaviour:

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)

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.

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.

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 -

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.

@Steven 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.

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

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Apps & Integrations

🍻🍂Apptoberfest Update: Upcoming Virtual Events 🎉

Hello Community! I hope you've been enjoying the 🍂Apptoberfestivities🍂 (I know I have!) The event is heating up next week with a series of virtual events that we're calling the 🍻🍂Partner App ...

326 views 3 14
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you