Query an external database using groovy and script runner

Jo-Anne MacLeod
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 24, 2013

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

0 votes
Sameera Shaakunthala [inactive]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 20, 2014

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.

0 votes
Bogdan Aioanei July 31, 2013

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 :))

0 votes
Bogdan Aioanei July 31, 2013

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 :)

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 31, 2013

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?

JamieA
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 31, 2013

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

Bogdan Aioanei July 31, 2013

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.

JamieA
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 4, 2013

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

0 votes
Bogdan Aioanei July 31, 2013

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?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 31, 2013

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

0 votes
JamieA
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 27, 2013

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")

Suggest an answer

Log in or Sign up to answer