Script runner Resources. Retrieve, update database

[Sysad]

We are testing people's data database by connecting it to JIRA Servicedesk via script runner resources.

We would like to display the following people data into custom fields like:

DB column(s)	JIRA field
first_name & last_name(in different columns)	Full name
personrole	Role
securitypin	Security Pin
address,city,county,zip(in different columns)	Address
phone	Phone #
email	Email

I would like to retrieve the information as well as update the information if needed or delete it if needed.

I have checked the documentation for resources and found very limited to null information on the update and delete

For retrieving, I can add select personrole FROM userdeatils;  to scripted field called Role. but how to update/delete the record using script runner resources? Not just the role but all records including address

Also, would like to search for data from field and populate it. 

Like search for the name and populate in the name field

Any help/advice is much appreciated.

Thanks

[Peter-Dave Sheehan]

The scriptrunner resource accessed using DatabaseUtil...

import com.onresolve.scriptrunner.db.DatabaseUtil
DatabaseUtil.withSql('local_jira') { sql ->
   //some sql action here
}

... generates a groovy slq object. You can read the full reference for using that here: https://groovy-lang.org/databases.html

But when you see update/delete records ... I hope you don't mean to try to make any database-level changes in the Jira database. This is very bad practice and will break your jira environment. 

If you intend to manipulate db records in another database that you own and no other applications will break by those changes, then that page should show you how to do all that.

[Sysad]

@Peter-Dave Sheehan Thank you for the knowledge share.

I am not handling JIRA DB, I am trying to handle a DB with user data in it and user data is not connected to any application hence not breaking any functionality.

Usecase 1:

out of 1000 employees, we only have 50(security monitors). If a user needs to change their password after a lockout, the user should reach out to the security monitor(SM).

SM will then contact the service desk agent on behalf of the user for an AD password reset.

The agent will ask a few questions/confirms details like SM's full name, security pin, work address, email, ph, etc

if the information matches the agent will reset password or if there is any change in SM's details, then the service desk agent should update SM records.

Keeping this in mind, I am trying to utilize resources.

[Peter-Dave Sheehan]

If the resource definition was configured with a user with write permissions, the reference I gave you should include everything you need.

But specifically, some code like this should work:

import com.onresolve.scriptrunner.db.DatabaseUtil
DatabaseUtil.withSql('your_resource_name') { sql ->
   sql.execute('update mytable set field=value')
}

[Sysad]

Thanks again, I can retrieve and display DB data into fields. by using a behavior & a listener

Am trying to use field behavior & listener to update column called 'person location' but couldn't figure how to get value from an input

Eg:

Name: Lili

Location: street1

New Location: street15

By inputting name in the JIRA field, I can retrieve location in a JIRA field called 'Location'.

I can't figure out how to update the location record.

Usecase:

I want to update Lili's location from st1 to st15. If a new location is entered in Jira field called 'New Location, then I would like to update the personlocation record of Lili in db.

Here is the listener I'm using:

def valueCF = customFieldObjects.findByName("Full Name")
def staffDetails= customFieldObjects.findByName("Staff Details")
def pin = customFieldObjects.findByName("Security Pin")
def location = customFieldObjects.findByName("Person Location")
def newLocationCF =customFieldObjects.findByName("New Location")
def currentLocationCF = customFieldObjects.findByName("Person Location")

def value = issue.getCustomFieldValue(valueCF)
DatabaseUtil.withSql('Dummy People Data') {
sql ->
def row = sql.firstRow('SELECT * from dummypeopledata WHERE first_name = ?', value)
if(row){

staffDetails.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(staffDetails),row .'personrole'), new DefaultIssueChangeHolder())
pin.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(pin),row .'securitypin'), new DefaultIssueChangeHolder())
location.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(pin),row .'personlocation'), new DefaultIssueChangeHolder())



}

}

--------------------------------

above code helps to retrieve and display, below is what am trying to write to update record. Both cases in single listiner

----------------------------------------------------
DatabaseUtil.withSql('Dummy People Data') {
sql ->
def row =sql.execute ('UPDATE dummypeopledata SET personlocation where first_name=?',newLocationCF)
if(row)
{
def currentLocation = row.'personlocation' as String
def currentLocationValue = {location!=newLocationCF}
location.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(pin),row .'personlocation'), new DefaultIssueChangeHolder())
}
}

Attached is a code screenshot.

Please take a look if time permits and suggest.

Thank you

[Peter-Dave Sheehan]

The problem might be that def row = sql.firstRow is inside the first withSql block.

Try it like this:

def row
DatabaseUtil.withSql('Dummy People Data') {sql ->
  row = sql.firstRow('SELECT * from dummypeopledata WHERE first_name = ?', value)
  if(row){
   /*...*/
  }
}

row.'personlocation' //this is now available ourside the sql block

This way, when you access row.'personlocation' in the next block the row object will be available.

Also, I see in the update block that you are using the customfield object newLocationCF instead of getting the actual value for the custom field: issue.getCustomFieldValuda(newLocationCF)

A small warning, using customFieldObject.updateValue() is not generally recommended. It's usually better to use issueService.validateUpdate() and issueService.update().
This ensures that permissions, audit trail and events are all correctly generated and managed.

[Sysad]

Thank you for the heads up on issueService.validateUpdate(). Will use it when i use the listener in prod.

I have updated script per your suggestion, and I'm stuck with def row being 

The script could not be compiled:

org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Script111.groovy: 34: The current scope already contains a variable of the name row
 @ line 34, column 5.
   def row
       ^

1 error

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil
import com.atlassian.jira.event.issue.IssueEvent
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder

def event = event as IssueEvent
def issue = event.issue
def customFieldObjects = ComponentAccessor.customFieldManager.getCustomFieldObjects(issue)

def valueCF = customFieldObjects.findByName("Full Name")
def staffDetails= customFieldObjects.findByName("Staff Details")
def pin = customFieldObjects.findByName("Security Pin")
def location = customFieldObjects.findByName("Person Location")
def newLocationCF =customFieldObjects.findByName("New Location")
def currentLocationCF = customFieldObjects.findByName("Person Location")

def value = issue.getCustomFieldValue(valueCF)
def row
DatabaseUtil.withSql('Dummy People Data') {sql ->
row = sql.firstRow('SELECT * from dummypeopledata WHERE first_name = ?', value)
if(row){

staffDetails. updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(staffDetails),row .'personrole'), new DefaultIssueChangeHolder())
pin. updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(pin),row .'securitypin'), new DefaultIssueChangeHolder())
location.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(pin),row.'personlocation'), new DefaultIssueChangeHolder())

}
}
row.'personlocation'

//--------Code above helps to retrieve and display, below is to write/update record.//

def row
DatabaseUtil.withSql('Dummy People Data') { sql ->
row =sql.execute ('UPDATE dummypeopledata SET personlocation where first_name=?',newLocationCF)
if(row)
{
def currentLocation = row.'personlocation' as String
def currentLocationValue = {location!=issue.getCustomFieldValue(newLocationCF)}
location. updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(pin),row.'personlocation'), new DefaultIssueChangeHolder())
}
}

Am trying to get a workaround, but ran out of ideas.  Please try to care to suggest.

Thanks

[Peter-Dave Sheehan]

Just as the error implies, you can't have "def row " twice.

Here is your full script, corrected as best as I could:

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil
import com.atlassian.jira.event.issue.IssueEvent
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder

def event = event as IssueEvent
def issue = event.issue
def customFieldObjects = ComponentAccessor.customFieldManager.getCustomFieldObjects(issue)

def fullNameCf = customFieldObjects.findByName("Full Name")
def staffDetailsCf= customFieldObjects.findByName("Staff Details")
def pinCf = customFieldObjects.findByName("Security Pin")
def locationCf = customFieldObjects.findByName("Person Location")
def newLocationCf =customFieldObjects.findByName("New Location")

def fullName = issue.getCustomFieldValue(fullNameCf)
def oldStaffDetailsValue = issue.getCustomFieldValue(staffDetailsCf)
def oldPinValue = issue.getCustomFieldValue(pinCf)
def oldLocationValue = issue.getCustomFieldValue(locationCf)
def newLocationValue = issue.getCustomFieldValue(newLocationCf)

def personRow
DatabaseUtil.withSql('Dummy People Data') {sql ->
   personRow = sql.firstRow('SELECT * from dummypeopledata WHERE first_name = ?', fullName)
}
if(personRow){
   staffDetailsCf.updateValue(null, issue, new ModifiedValue(oldStaffDetailsValue, personRow.'personrole'), new DefaultIssueChangeHolder())
   pinCf.updateValue(null, issue, new ModifiedValue(oldPinValue, personRow.'securitypin'), new DefaultIssueChangeHolder())
   locationCf.updateValue(null, issue, new ModifiedValue(oldLocationValue, personRow.'personlocation'), new DefaultIssueChangeHolder())
}

//--------Code above helps to retrieve and display, below is to write/update record.//
def updateCount
DatabaseUtil.withSql('Dummy People Data') { sql ->
   updateCount = sql.executeUpdate ('UPDATE dummypeopledata SET personlocation where first_name=?', newLocationValue)
}
if(updateCount) {
   //update was successful, update the locationCf with the value of newLocationCf
   locationCf.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(locationCf), newLocationValue),  new DefaultIssueChangeHolder())
   //and clear out the newLocationCf
   newLocationCf.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(newLocationCf), ""), new DefaultIssueChangeHolder())
}

I've adjusted several of the variables to make it clear what they were. You seemed to have some mismatched between the fields you were trying to update and the value used in the modifiedValue object.

Also, I like to keep a minimal number of statements in the withSql block so that the sql connection can be closed as soon as possible.

And finally, execute doesn't return rows (just true/false). So I changed it to executeUpdate which returns a rowCount.

[Sysad]

Thanks for your time & patience.

Updated code and found a few things:

In if(personRow)

I see an error as follows and as attached

No such property: personrole : class java.lang.object

No such property: securitypin :class java.lang.object

No such property: personlocation :class java.lang.object

if(personRow){
   staffDetailsCf.updateValue(null, issue, new ModifiedValue(oldStaffDetailsValue, personRow.'personrole'), new DefaultIssueChangeHolder())
   pinCf.updateValue(null, issue, new ModifiedValue(oldPinValue, personRow.'securitypin'), new DefaultIssueChangeHolder())
   locationCf.updateValue(null, issue, new ModifiedValue(oldLocationValue, personRow.'personlocation'), new DefaultIssueChangeHolder())
}

2020-01-23 16:34:00,537 WARN [sql.Sql]: Failed to execute: UPDATE dummypeopledata SET personlocation where first_name=? because: ERROR: syntax error at or near "where"
  Position: 43
2020-01-23 16:34:00,542 ERROR [runner.AbstractScriptListener]: *************************************************************************************
2020-01-23 16:34:00,542 ERROR [runner.AbstractScriptListener]: Script function failed on event: com.atlassian.jira.event.issue.IssueEvent, file: null
org.postgresql.util.PSQLException: ERROR: syntax error at or near "where"
  Position: 43
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
 at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
 at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
 at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
 at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:138)
 at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
 at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
 at Script28$_run_closure2.doCall(Script28.groovy:36)
 at com.onresolve.scriptrunner.db.AbstractDbConnectionManager.withSql(AbstractDbConnectionManager.groovy:63)
 at com.onresolve.scriptrunner.db.AbstractDbConnectionManager$withSql$0.callCurrent(Unknown Source)
 at com.onresolve.scriptrunner.db.AbstractDbConnectionManager$withSql$0.callCurrent(Unknown Source)
 at com.onresolve.scriptrunner.db.AbstractDbConnectionManager.withSqlTracked(AbstractDbConnectionManager.groovy:92)
 at com.onresolve.scriptrunner.db.DbConnectionManager$withSqlTracked.call(Unknown Source)
 at com.onresolve.scriptrunner.db.DbConnectionManager$withSqlTracked.call(Unknown Source)
 at com.onresolve.scriptrunner.db.DatabaseUtil.withSql(DatabaseUtil.groovy:22)
 at com.onresolve.scriptrunner.db.DatabaseUtil$withSql.call(Unknown Source)
 at Script28.run(Script28.groovy:35)

 However, even after those red errors, the listener gets updated  & saved. data retrieve works as expected, but listener logs error at sqlexecuteupdate. Error as above.

Says syntax but looks ok.

[Peter-Dave Sheehan]

That is normal and expected.

The syntax checker is just a warning that something "might" be wrong. But it can't know what are the possible columns from the GroovyRowResult.

A different, but functionally equivalent syntax to get a column value is to use personRow['column name'] and that will pass the static type checker.

staffDetailsCf.updateValue(null, issue, new ModifiedValue(oldStaffDetailsValue, personRow['personrole']), new DefaultIssueChangeHolder())
pinCf.updateValue(null, issue, new ModifiedValue(oldPinValue, personRow['securitypin']), new DefaultIssueChangeHolder())
locationCf.updateValue(null, issue, new ModifiedValue(oldLocationValue, personRow['personlocation']), new DefaultIssueChangeHolder())

[Sysad]

Yup, all looks good except with the syntax error.

Data is not getting updated, but retrieval works as expected.  

I am not sure what could be done next but will keep on looking.

Thanks for your time and for your positiveness towards contributing to our community.

[Peter-Dave Sheehan]

Well I think the problem is with your sql statement itself.

 updateCount = sql.executeUpdate ('UPDATE dummypeopledata SET personlocation where first_name=?', newLocationValue)

You won't find a row with first_name=newLocationValue. ANd you are not actually specifying anywhere what value to set on the personlocation field (missing personlocation=xxx)

Do you have a full name column? I see you are matching first_name db column to fullName value from jira.  

But if you want to make an update you have to specify a value. And to send more than one value to a sql statement, the following syntax is better (using named parameters instead of ordinal parameters)

 updateCount = sql.executeUpdate ('UPDATE dummypeopledata SET personlocation=:jiraNewLocation where first_name=:jiraFullName', [jiraNewLocation:newLocationValue, jiraFullname: fullName])

[Sysad]

So, what am I currently trying  vs what I get 

1. I have a Jira custom field - Full Name
2. In Full Name- I'm inputting first_name value
3. Based on first_name value, the listener is populating following fields
   1. personrole to Person Role(JIRA field)
   2. security pin to Security Pin(JIRA field)
   3. personlocation to Person Location(JIRA field)
4. This data retrieval is working fine
5. Now when Person Location is populated with personlocation(db value), I will check if first_name is still at personlocation or not.
6. if not, then I want to populate New Location(JIRA field) with new personlocation, and update it in db 
7. similarly with securitypin, if a new pin is generated, I want to update db or let db create a new 9 digit and populate in New Security Pin(Jira field). This is something fancy
8. I also have a field behavior on Full Name, whenever I input a first_name in Full Name, the behavior will check if first_name exists or not in db, if not an error message is displayed.

FYI- I'm using Full Name jira field to input just first_name.

I will try your suggested method and update shortly.