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
For example it’s my create 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 i have an error:
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)
Help me please to find solution.