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

[Mohit Jayesh Patel]

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.

[Jenna Davis · Accepted solution]

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

[Mohit Jayesh Patel]

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]

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

[Mohit Jayesh Patel]

The errors are as follows: 

[Mohit Jayesh Patel]

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

[Jenna Davis]

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. 

[Mohit Jayesh Patel]

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]

Great!

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

[Mohit Jayesh Patel]

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