Rest Endpoint Error

Shah Baloch

Hi,
I'm trying to create a Rest Endpoint from ScriptRunner, however, I'm getting errors. I copied the code from the ScriptRunner's page. Actually, I'm trying to make a text field to a dropdown. The field full the customer name from the database. Currently, its text field user has to write the correct name in order to create a ticket. I would like to have it as a drop-down user can select or start typing it should display matches. I found this code with a Behaviour script from the documentation. But's not working. I try to run the code from the console too and it is displaying the same error. Attaching screenshot. How I can fix it?

import com.atlassian.jira.component.ComponentAccessor
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

eventTypes(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(datasource.getDriverClassName()).newInstance() as Driver

def props = new Properties()
props.setProperty("testuser", datasource.getUsername())
props.setProperty("fakepassword", datasource.getPassword())

def conn = driver.connect("jdbc:mysql://myDatabase:3306", props)
def sql = new Sql(conn)

try {
sql
def rows = sql.rows("SELECT FirstName, LastName from Person WHERE FirstName like ?", ["%${query}%".toString()])

rt = [
items : rows.collect { GroovyRowResult row ->
[
value: row.get("FirstName"),
html: row.get("FirstName").replaceAll(/(?i)$query/) { "<b>${it}</b>" },
label: row.get("FirstName"),
]
},
total: rows.size(),
footer: "Choose event type... "
]

} finally {
sql.close()
conn.close()
}

return Response.ok(new JsonBuilder(rt).toString()).build();
}

My second question is that is it possible to create a multi-select dropdown with help of a groovy script? Currently, there isn't a multi-select option in Jira Server. I'm looking to create a multi-select drop with 4,5 options.

Thank you for your help.

PD Sheehan

A few pointers

 

1) these static error checking don't necessarily the code is bad. Just to review because the checker is unable to be sure.

The real test is what happens when you deploy and call the new API

2) You will have a cleaner implementation of the sql query if you use the Resources functionality of scriptrunner 

3) SQL Statement (passing query condition via parameter) expects objects as parameters, not strings. I think your code would work, but either of these two will also work but without showing the error:

sql.rows('SELECT FirstName, LastName from Person WHERE FirstName like :nameInput', [nameInput:"%${query}%"])
sql.rows('SELECT FirstName, LastName from Person WHERE FirstName like ?', ["%${query}%" as Object])

To make the second error go away, include the "Matcher" class in your import and declare it explicitly in your replaceAll closure.

All that together, assuming you've created a db resource (where passwords are more secure,) with the label: client_db

You can convert your full rest api script to this:

import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.sql.GroovyRowResult
import groovy.transform.BaseScript
import com.onresolve.scriptrunner.db.DatabaseUtil
import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response
import java.util.regex.Matcher
@BaseScript CustomEndpointDelegate delegate

eventTypes(httpMethod: "GET") { MultivaluedMap queryParams ->

   def query = queryParams.getFirst("query") as String
   def rt = [footer: 'Choose event type']

   rt.items = DatabaseUtil.withSql('client_db'){ sql->
      sql.rows('SELECT FirstName, LastName from Person WHERE FirstName like :nameInput', [nameInput:"%${query}%"])
   }.collect{GroovyRowResult row ->
      [
          value: row.get("FirstName"),
          html: (row.get("FirstName") as String).replaceAll(/(?i)$query/) { Matcher it->"<b>${it}</b>" },
          label: row.get("FirstName"),
      ]
   }
   rt.total = rt.items.size()

   return Response.ok(new JsonBuilder(rt).toString()).build();
}

 And finally, yes you can convert your text field to a multiselect with the convertToMultiSelect() method. More details here: https://docs.adaptavist.com/sr4js/6.24.0/features/behaviours/behaviours-examples/select-list-conversions