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.

[Peter-Dave 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

[Shah Baloch]

Thank you @Peter-Dave Sheehan for your response. I'm having trouble making it works. I already created a DB connection from the source and add that connection to the code that you provided, but for some reason, it's not working. I'm not seeing an error. Maybe I don't have the correct behavior script. It is not pulling up the customer from the database.

Here is the Rest EndPoint code

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('Mysql_Customerdb'){ sql->
//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])
}.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();
}

Here is Behavirour code

import com.onresolve.jira.groovy.user.FieldBehaviours
import com.onresolve.scriptrunner.db.DatabaseUtil
import groovy.transform.BaseScript
@BaseScript FieldBehaviours fieldBehaviours

getFieldByName("Customer Name(s)").convertToMultiSelect([
ajaxOptions: [
url : getBaseUrl() + "/rest/scriptrunner/latest/custom/eventTypes",
query: true,
formatResponse: "general"
]
])

Thank you for your help.

[Peter-Dave Sheehan]

What happens when you point your browser to  this?<jiraBaseUrl>/rest/scriptrunner/latest/custom/eventTypes

Are you getting some data?

What about with this?

<jiraBaseUrl>/rest/scriptrunner/latest/custom/eventTypes?query=Victor 

Then you can try adding some logs to your restapi

Something like :

/* ...*/
def query = queryParams.getFirst("query") as String
log.info "queryparam = $query"
log.info "Atttempting to get data from db using criteria [ FirstName like '%${query}' ]"
/* ... */
rt.total = rt.items.size()
log.info "Returning: \n ${new JsonBuilder(rt).toPrettyString()}" 

[Shah Baloch]

Hi @Peter-Dave Sheehan  here are the logs, first, one from the URL, and the second one is from typing the user's first name in the text box.

2021-06-09 17:05:19,318 INFO [runner.ScriptBindingsManager]: queryparam = Victor 2021-06-09 17:05:19,319 INFO [runner.ScriptBindingsManager]: Atttempting to get data from db using criteria [ FirstName like '%Victor' ] 2021-06-09 17:05:19,441 INFO [runner.ScriptBindingsManager]: Returning: { "footer": "Choose event type", "items": [ ], "total": 0 }

2021-06-09 17:08:20,812 INFO [runner.ScriptBindingsManager]: queryparam = Victor 2021-06-09 17:08:20,813 INFO [runner.ScriptBindingsManager]: Atttempting to get data from db using criteria [ FirstName like '%Victor' ] 2021-06-09 17:08:20,878 INFO [runner.ScriptBindingsManager]: Returning: { "footer": "Choose event type", "items": [ ], "total": 0 }

Thank you,

[Peter-Dave Sheehan]

What do you get if you run the following in your console?

import com.onresolve.scriptrunner.db.DatabaseUtil
DatabaseUtil.withSql('Mysql_Customerdb'){ sql->
    sql.rows('SELECT FirstName, LastName from Person WHERE FirstName like ?', ["%Victor%" as Object])
}

[Shah Baloch]

Hi Peter, I'm getting results when I run that query in the console. here is a screenshot. Thanks

[Peter-Dave Sheehan]

So, we now know for sure that the issue is not with getting data from the DB.

Let's plug some more logs in the REST API to figure out why the DB results are not loaded in the rt.items

Try to update your restapi code with 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('Mysql_Customerdb'){ sql->
      def queryWildCard = "%${query}%"
      def slqStmt = "SELECT FirstName, LastName from Person WHERE FirstName like '$queryWildCard'"
      log.info "Running SQL Statment: $slqStmt"
      def rows = sql.rows('SELECT FirstName, LastName from Person WHERE FirstName like ?', queryWildCard )
      log.info "SQL Retreived ${rows.size()} rows"
      rows
   }.collect{GroovyRowResult row ->
      log.info "Preparing item for $row.FirstName"
      def singleItem = [
         value: row.get("FirstName"),
         html: (row.get("FirstName") as String).replaceAll(/(?i)$query/) { Matcher it->"<b>${it}</b>" },
         label: row.get("FirstName"),
      ]
      log.info "Item = $singleItem"
      singleItem
   }
   log.info "rt.items = $rt.items"
   rt.total = rt.items.size()
   log.info "Returning: \n ${new JsonBuilder(rt).toPrettyString()}"

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

[Shah Baloch]

Here is the log, thanks.

2021-06-10 10:44:27,011 INFO [runner.ScriptBindingsManager]: Running SQL Statment: SELECT FirstName, LastName from Person WHERE FirstName like '%Victor%' 2021-06-10 10:44:27,076 INFO [runner.ScriptBindingsManager]: SQL Retreived 0 rows 2021-06-10 10:44:27,077 INFO [runner.ScriptBindingsManager]: rt.items = [] 2021-06-10 10:44:27,077 INFO [runner.ScriptBindingsManager]: Returning: { "footer": "Choose event type", "items": [ ], "total": 0 }

[Peter-Dave Sheehan]

Ok, I had to doa bit of debugging on my end ... but ultimately... something I suggested that made the static type checking error go away was not correct and prevented the script from running.

I was able to get this to run in my environment:

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('Mysql_Customerdb'){ sql->
      if(query){
         String queryWildCard = "%$query%"
         sql.rows('SELECT FirstName, LastName from Person WHERE FirstName like :query', [query: queryWildCard] )
      } else {
         sql.rows('SELECT FirstName, LastName from Person' )
      }
   }.collect{GroovyRowResult row ->
      [
         value: row.FirstName,
         html: (row.FirstName as String).replaceAll(/(?i)$query/) {"<b>${it}</b>" },
         label: row.FirstName,
      ]
   }
   rt.total = rt.items.size()

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

There is one static type checking error on line 26, but the code runs fine. 

[Shah Baloch]

Thank you, the code works fine with the first name. Now I'm trying to update the SQL query part to display the first and last name but getting an error. Any idea how I'll display the first and last name? I mean instead of it show the first name it should display the full name.

Thank you for your help.

[Peter-Dave Sheehan]

I think it will be easier to combine those values in the groovy part of the script rather than in SQL.

Make your sql "Select FirstName, LastName FROM Person where FirstName like ?"

Then create your item object like this:

      def fullName = "$row.FirstName $row.LastName" as String
      [
         value: fullName,
         html:fullName.replaceAll(/(?i)$query/) {"<b>${it}</b>" },
         label: fullName,
      ]

[Shah Baloch]

It worked, thank you so much.

The link provided in the first reply regarding the drop-down field. I went through I didn't find any code about make a custom field to a dropdown. I mean I saw database picker field, issue picker, confluence Github/bitbucket, etc but I couldn't find the one about the drop-down field that I am looking for.

Jira doesn't have a multi-select drop-down field. I would like to have a field that the user can select multiple values from there. Suppose there is a field "Product" and it has option "Val1", Val2", "Val3", and "Val4". The user should be able to select more than one value.

Thank you for your help.

[Peter-Dave Sheehan]

In the behaiour code, you can use convertToSingleSelect or convertToMultiSelect 

The first one will convert a text custom field into a single select dropdwown.
While the second one will convert a text custom field into a multi select control (behaves a bit like a label field). 

In the multi select option, the user has to go back to the field and search/select a different value.
That's how most fields that support multiple values in jira function: component, version, labels.

There is no way to have this work by showing the full list and the user ctrl-click to select multiple values at once.