Query an external database using groovy and script runner

Here's hoping that someone here can help out with this.

I am using script runner to do a query of an external database. Here are my exact requirements. On an existing JIRA issue, use two custom fields, both of type text. Field names are: Customer and PIN. Then query an external database, and return the result to another variable. There is a bit more than that, but for the purposes of this question this is good enough.

Using groovy and an example provided by Jamie, I am able to do a query on the other sql database. I can get the information that I need, if I hard code (statically define) in the two custom fields in the groovy script. That is, Customer = "Acme", and PIN = "1234". So I know that my connection and syntax are correct. What I can't figure out is how do I get and use the contents of my two custom fields, and pass them to the query instead of using a static defined variable.

Ultimately I am looking to have a workflow transition that the user can click on. The transition would execute the groovy script, which would then go out to the other database and do the query to verify the two fields in question and return to a third field a "Verified", or "Not Verified". Or behaviour similar to that.

Has anyone tried to do this before, or have any other suggestions on how I could accomplish this? I have plenty of examples of how to do a general query and update the custom field in JIRA, just not of how to 'use' the contents of an existing field. I'm sure that the information exists somewhere, I just haven't been able to find it so far.

5 answers

Sounds like you've done the hard bit. You can retrieve and get custom field values using code like this. If you set this as the first post-function in your transition you shouldn't need to do much more than this.

package examples

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.MutableIssue
import org.apache.log4j.Logger

def log = Logger.getLogger("com.onresolve.jira.groovy.MyScript")

def MutableIssue issue = issue
def customFieldManager = ComponentAccessor.getCustomFieldManager()

def customerCf = customFieldManager.getCustomFieldObjectByName("Customer")
def customer = issue.getCustomFieldValue(customerCf)

// do database query...

// set some value retrieved from the db

def verifiedCf = customFieldManager.getCustomFieldObjectByName("Is Verified")
issue.setCustomFieldValue(verifiedCf, "Verified")

Hello. I have a similar problem. I want to get a custom field instance by name but don't know what the string should be. Where does the "Customer" string on line 12 come from? is it suppose to be configured in atlassian-plugin.xml?

No, it's the hard-coded name of the field. If you've got a custom field called "Mr Flibble", then you put "Mr Flibble" into the string in that line of code

I also assumed so, but it doesn't work for me. If I do it like this:

CustomFieldManager cfm = ComponentAccessor.getCustomFieldManager();
CustomField customText = cfm.getCustomFieldObjectByName("customText");

the customText reference is null. But if I do it like this:

CustomFieldManager cfm = ComponentAccessor.getCustomFieldManager();
CustomField customText = cfm.getCustomFieldObject("customfield_10000");

it works.

The problem is I don't want to do it like that because the id (customfield_10000) might be different when I deploy my plugin in production.

Any other advice? Thanks you :)

mmm, I'm stuck, because cfm.getCustomFieldObjectByName("Mr Flibble"); returns a field in my code. Only question is do you have a field called "customText" in your Jira?

Case is significant. Why don't you post the real name of the custom field, I doubt it is called customText.

Aah, it's so weird. I uploaded my plugin to a test environment an it works now. But in JIRA standalone version it doesn't seem to be working. So weird.

Thanks for your help anyway. Really apreaciate you taking time to answer.

The other issue is that the custom field name might have trailing white space...

When I created the custom field in the administration panel that was the actual name I used for it. I know it sounds dumb but I didn't have much inspiration :))

I didn't go through the description, but looking at the title I think you need to perform SQL operations in a database other than JIRA's.

I remember @Mizandeveloped something having this functionality.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,298 views 12 19
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