I am trying to create a custom drop down select field using behaviors?

I have a project requirement that wants a custom field to be populated by a list of customers in a database table. I followed the instructions on the link: 

http://temp-jira-rca.pdsea.f5net.com:8080/rest/scriptrunner/latest/custom/eventTypes?query=work

and tried to check if the code mentioned therein will work. However, I am not able to modify the field behaviour to a drop down.

The REST Endpoint code:

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("user", datasource.getUsername())
props.setProperty("password", datasource.getPassword())

def conn = driver.connect(datasource.getJdbcUrl(), props)
def sql = new Sql(conn)

try {
sql
def rows = sql.rows("select name from jiraeventtype where name ilike ?.Q", [Q:"%${query}%".toString()])

rt = [
items : rows.collect { GroovyRowResult row ->
[
value: row.get("name"),
html: row.get("name"),
label: row.get("name"),
]
},
total: rows.size(),
footer: "DATA VISIBLE "
]

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

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

 

Behaviours Initialiser Script:

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

 

Please guide me where I am making the error.

1 answer

1 accepted

1 vote
Jenna Davis Community Champion Aug 08, 2017

Hello,

Have you read the behaviour overview page within the scriptrunner documentation? This pages children in particular may help you out. They contain several examples of select list conversions. There are other examples of behaviours here as well.

If none of these examples work for you, let me know and I will help you out with this further. :)

Jenna

Hi Jenna,

 

I have tried out the listed approach.. however when I try to use a custom database link, I am not able to get it to work. I have paseted the code below. Please let me know where I might be going wrong.

 

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 driver = Class.forName('com.mysql.jdbc.Driver').newInstance() as Driver

def props = new Properties()
props.setProperty("user", "reportsuser")
props.setProperty("password", "reportsuser")

def conn = driver.connect("jdbc:mysql://seabugzdb04.olympus.f5net.com:3306/bugs", props)
def sql = new Sql(conn)

try {
sql
def rows = sql.rows("select b.bug_id from bugs.bugs b where b.bug_id =?.Q ", [Q:"%${query}%".toString()])

rt = [
items : rows.collect { GroovyRowResult row ->
[
value: row.get("bug_id"),
html: row.get("bug_id"),  /There might be an issue on this line
label: row.get("bug_id"),
]
},
total: rows.size(),
footer: "DATA VISIBLE "
]

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

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

 

// If you can provide an example, it might help.

Jenna Davis Community Champion Aug 08, 2017

Do you recieve any errors or does the field just not show up/ not drop down? Please check the javascript console (using Chrome dev tools for example) and send me any errors you see.

Have you tested the REST endpoint on its own with a query? 

You'll need to do something like:

<jira-base-url>/rest/scriptrunner/latest/custom/eventTypes?query=yourqueryhere

If it fails to work, please send me any errors you recieve.

This might help you out on some parts of the code, though overall your code looks okay to me. If you haven't read it, this page goes over connecting to databases through scriptrunner. 

I think the problem might be stemming from how you're accessing the data (provided there aren't any connection errors). Are you sure you're accessing everything correctly? 

Jenna

The errors are as follows: 

Behaviours Error.PNG

The dropdown does not populate with the fields from the database table

Jenna Davis Community Champion Aug 08, 2017

Okay, when looking at this section:

try {
sql
def rows = sql.rows("select b.bug_id from bugs.bugs b where b.bug_id =?.Q ", [Q:"%${query}%".toString()])
rt = [
items : rows.collect { GroovyRowResult row ->
[
value: row.get("bug_id"),
html: row.get("bug_id").replaceAll(/(?i)$query/) { "<b>${it}</b>"},
label: row.get("bug_id"),
]
},
total: rows.size(),
footer: "DATA VISIBLE "
]

 Can you explain what you're accessing in your database? Are you certain that b.bug-id and bug_id are correct? Can you play around with these values some? The error you are recieving has to do with an array out of bounds problem which leads me to believe that code does not access the information you want properly.

I 'think' the replaceAll error you're recieving is due to static type checking and is harmless, seeing as the code compiles. It is just not accessing the data in your database. 

Hey Jenna, 

I resolved the error. I suspect it was due to the data type difference (bug_id is an integer, though the code converts it to string later). I tried it using another field in my database which was a char data type and it worked with that.

I am trying to have the field populate a list of customers which will be stored in a database table.

Code:

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 driver = Class.forName('com.mysql.jdbc.Driver').newInstance() as Driver

def props = new Properties()
props.setProperty("user", "reportsuser")
props.setProperty("password", "reportsuser")

def conn = driver.connect("jdbc:mysql://seabugzdb04.olympus.f5net.com:3306/bugs", props)
def sql = new Sql(conn)

try {
sql
def rows = sql.rows("select distinct bug_status from bugs.bugs where bug_status like ?.Q", [Q:"%${query}%".toString()])

rt = [
items : rows.collect { GroovyRowResult row ->
[
value: row.get("bug_status"),
html: row.get("bug_status"),
label: row.get("bug_status"),
]
},
total: rows.size(),
footer: "DATA VISIBLE "
]

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

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

Jenna Davis Community Champion Aug 08, 2017

Great!

Do you recieve data on your REST endpoint now? Are you getting any more errors (in the Chrome dev console or otherwise)? 

I can see the data on my REST endpoint, no errors on the console :)

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Bridget Sauer
Published Thursday in Marketplace Apps

Calling all developers––You're invited to Atlas Camp 2018

 Atlas Camp   is our developer event which will take place in Barcelona, Spain  from the 6th -7th of   September . This is a great opportunity to meet other developers and get n...

263 views 0 6
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you