Set custom field from external SQL database

[dstephens]

Brief: I would like to query an external SQL database using variables from the current issue so i can return relevant information to our support agents.

Detail: Currently we used elements connect to solve this issue. This allows us to run SQL against the database with jira variables. I have tried looking at the documentation and it looks like I need to make a listener for this as I want these values to always be correct.

I have successfully made the SQL resource but i am struggling to find an example or tutorial that I can follow / learn from. I believe i need to import some classes, define my variables, use those to run the SQL and return the SQL output to a custom field.

In elements connect (formerly nFeed) you can make a field that runs script to populate its value. The database picker option in script runner is almost what i need, I want to return some values from our CRM system so our support agents can see that information on the JIRA ticket without having to dig into CRM

I also fully understand I have no provided any code and it wont be possible to provide code based on the information i have provided - I wanted to confirm my approach was correct

[Julie d_Antin _Elements_]

Hi @dstephens , is there something that's not working with Elements Connect or an extra functionality you need?

[Christophe Promé]

Hello @dstephens 

From my understanding, you'd like to to list all issues created by the reporter of the an issue in a custom field.

Is that correct?

If yes, with Elements Connect you can do that easily and you don't have to write any script.

Here is how:

In this example, I use the "Jira JQL" datasource which is already configured in Elements Connect.

You can retrieve any issue custom field and display issue in your custom field.

An alternative is to use the "Jira SQL" datasource (also preconfigured in Elements Connect) but I think that JQL is the easiest way

Let me know if you have further question

Christophe
(I am the Elements Connect Product Manager)

[Ravi Sagar _Sparxsys_]

Hi @Christophe Promé How are you doing these days? :)

[Christophe Promé]

Oh sorry, I had a second look at your question and apparently I misunderstood your need.

So, here is the right configuration for your use case:

Regards,

Christophe

[Christophe Promé]

Hello @Ravi Sagar _Sparxsys_ 

Glad to see you!
I am doing good thanks :)

What about you?

Cheers,
Christophe

[Ravi Sagar _Sparxsys_]

Hi @dstephens 

Since you have successfully connected to the database then do take a look at these pages for more examples.

• Resource documentation
• Some scripts to interact with database

In case you want to learn it further I also made a video explain how to fetch and use data returned from a database in your script.

I hope it helps.

Ravi

[PD Sheehan]

Looks like your getting some good help on HOW to get data from your sql connection.

But since you've mentioned custom field, I'll touch on that a bit. 

Let's assume that all you care about is this new_buildnumber based on the reporter

Small tangent, here is a better/safer way to pass your reporter to your sql query:

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil

def issue = ComponentAccessor.issueManager.getIssueByCurrentKey("PROJECT-128796")
def reporter = issue.reporter

def sqlst = "select new_buildnumber from crm__account where id = (select parentcustomerid from crm__contact where emailaddress1 = :reporterInput)"

def newBuildNum = DatabaseUtil.withSql('CRM') { sql ->
   sql.firstRow(sqlst, [reporterInput:reporter.name])
}.new_buildnumber

Some questions you may need to ask yourself that you will determine the direction.

1. Do you need the build number when an agent is creating the issue (live in the form) on behalf of your customer?
2. Once you have the build number, how will it be used?
   • Should you be able to search for it using JQL?
   • Will you need to frequently refer to that build number in the future from the issue?

If you say yes on the first and no on the second, maybe you don't need a custom field at all. you just need a way to display this information once. 

But if you do need a custom field.

1. When should that field get populated?
2. Could it change in the source DB after the issue is created?

Some options for custom fields are:

• db picker scriptrunner field
  • In my opinion probably not all that useful for you since there is no selection involved
• custom script field
  • not a great choice here since these don't normally appear on edit/create screen, only on the view screen after creation
  • Consideration for this type of field is that they are indexed (if so configured) only when the issue is created or updated. Not when the data source changes. So that might impacts searches
• regular custom field (text or number)
  • You will need a script to set or update the value. When? On the create screen? During create post-function? When should it be updated?
  • Index and search will be built-in
  • You might need to think about using behaviour or some other configuration to prevent users from changing it after it is set from the remote db

So if you want to view the build number from the create screen, then a behaviour script would be the way to go. And it doesn't even have to be stored in a field.

For example, you can display the build number right under the reporter field (and it will change/refresh when you change the reporter):

import com.onresolve.scriptrunner.db.DatabaseUtil
def reporterField = getFieldById('reporter')
def sqlst = "select new_buildnumber from crm__account where id = (select parentcustomerid from crm__contact where emailaddress1 = :reporterInput)"

def buildNum = DatabaseUtil.withSql('CRM') { sql ->
    sql.firstRow(sqlst, [reporterInput:reporterField.value])
}.new_buildnumber
reporterField.setHelpText("<span style='color:green'>New Build Number: $buildNum</span>")

But if you want to store it in a field, use this instead of the last line 

getFieldByName("New Build Number").setFormValue(buildNum).setReadOnly(true)

[dstephens]

Thanks guys - off to the races. Im using the script console to test currently.

What I have so far is 

import com.atlassian.jira.component.ComponentAccessor

// get an issue that has the Request Type that you want to find the id for
def issue = ComponentAccessor.issueManager.getIssueByCurrentKey("PROJECT-128796")
def reporter = issue.getReporter().getName()

import com.onresolve.scriptrunner.db.DatabaseUtil
def sqlst = "select new_buildnumber from crm__account where id = (select parentcustomerid from crm__contact where emailaddress1 = " + "'" + reporter +"'" + ")"

DatabaseUtil.withSql('CRM') { sql ->
sql.rows(sqlst)
}

Which gets me the SQL output of [[new_buildnumber:9.2.0.42]]
Is there a simple way for me to convert the [[new_buildnumber:9.2.0.42]] to 9.2.0.42

then i think i know enough to do this for one more SQL field and to populate two fields in JIRA

[Ravi Sagar _Sparxsys_]

Hi @dstephens 

I am extremely glad that you found your way with those links :)

If you query is returning just one value you can do this to fetch the buildname.

sql.rows(sqlst).new_buildnumber

If you sql return multiple value then the above line will give you a list like this

[2342,23432,23423]

I hope it helps.

Ravi

[Nic Brough -Adaptavist-]

To do the SQL in SR, try https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html

And for the field, have you run through https://scriptrunner.adaptavist.com/latest/jira/script-fields/database-picker.html ?