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

PD 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.