Query an external database using groovy and script runner

[Jo-Anne MacLeod]

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.

[Sameera Shaakunthala [inactive]]

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.

[Bogdan Aioanei]

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

[Bogdan Aioanei]

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-]

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]

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

[Bogdan Aioanei]

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]

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

[Bogdan Aioanei]

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-]

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

[JamieA]

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