Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

SQL query on Script Console not giving result nor any error

Hi Guys,

I am trying to Run Select query on Database using Script Console. Script is fine but not sure why it's not giving result. Not giving any error when running script. Please suggest

@Nic Brough _Adaptavist_ @Martin Bayer _MoroSystems_ s_r_o__ @Kevin Johnson @Elifcan Cakmak 

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default")

def sqlStmt = """
SELECT cfname
FROM customfield
WHERE customfieldtypekey = 'com.atlassian.jira.plugin.system.customfieldtypes:select';
"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)

try {
StringBuffer sb = new StringBuffer()
sql.eachRow(sqlStmt) { it ->

sb.append( it.getAt("cfname") )
sb.append('\n')
}
log.debug sb.toString()
}
finally {
sql.close()
}

Below Screenshot when running query on Local Database connection.

SQL on Resources.PNG

1 answer

1 accepted

Hi @Vikrant Yadav 

Instead of using the ConnectionFactory etc., to establish the connection to the Database to execute your query, please try and use ScriptRunner's DatabaseUtil, i.e. com.onresolve.scriptrunner.db.DatabaseUtil.

Below is a sample working code for your reference:-

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


def issueManager = ComponentAccessor.issueManager
def issue = issueManager.getIssueByCurrentKey("SAM-1")

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select * from stored_value where issue_key = ?", issue.key)
}

output.each {
result = it as Map<String,String>
}

result.each {
log.warn it
}

Please note, this sample code is not 100% exact to your environment. Hence, you will need to modify it accordingly.

The local_db used in the code above refers to the Local Database connection shown below:-

local_db_config.png

Below is a print screen of the output:-

console_output.png

I hope this helps to answer your question. :)

 

Thank you and Kind Regards,

Ram

Hi @Ram Kumar Aravindakshan _Adaptavist_  Thanks for the response. But still it's not working. Do i need to create Local Database connection using Resouces then below scriot works ? :- 

 

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface
import com.onresolve.scriptrunner.db.DatabaseUtil

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default")

def sqlStmt = """
SELECT cfname
FROM customfield
WHERE customfieldtypekey = 'com.atlassian.jira.plugin.system.customfieldtypes:textarea'
"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)

try {
StringBuffer sb = new StringBuffer()
sql.eachRow(sqlStmt) { it ->

sb.append( it.getAt("cfname") )
sb.append('\n')
}
log.debug sb.toString()
}
finally {
sql.close()
}

@Ram Kumar Aravindakshan _Adaptavist_  without creating DB connection i can't run SQL query in script console ?

Hi @Vikrant Yadav

To answer your question, yes,  you will first need to set up a connection to your Local Database, i.e. the database your Jira instance is connected to using ScriptRunner's Resources.

Looking at the configuration print screen that you have shared in your previous comment, it appears that you have already configured it. And, have you saved the Local Database configuration?

First, go to ScriptRunner's Resources and setup and Local Database connection as shown below:-

create_resource1.png

Once you have selected Create Resources, select Local Database connection as shown below:-

create_resource2.png

Next, enter the name you want to use for the Local Database connection and save it. 

create_resource3.png

 

In the print screen above, the Local Database name set is local_db. This is the same name that is used in the DatabaseUtil.withSql shown in the sample code below.

Using the DatabaseUtil, you do not need to use the ConnectionFactory as shown in the Sample Code below:-

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


def issueManager = ComponentAccessor.issueManager
def issue = issueManager.getIssueByCurrentKey("SAM-1")

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select * from stored_value where issue_key = ?", issue.key)
}

output.each {
result = it as Map<String,String>
}

result.each {
log.warn it
}

I hope this helps to answer your question. :)

Thank you and Kind Regards,

Ram

Like Vikrant Yadav likes this

@Ram Kumar Aravindakshan _Adaptavist_  Intially i was trying without creating connection with the local database. I was simply trying to run script in script console. 

Now i have created local database connection having name local_db and SQL box is empty. Now i am trying to run below query :- 

it's giving me only single value, i need complete list of fields. I have tried to change sql.rows to sql.eachRow but not working. Kindly suggest.

 

What wrong with my intial script ?

 

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface


def issueManager = ComponentAccessor.issueManager


def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("""select cfname from customfield where customfieldtypekey = 'com.atlassian.jira.plugin.system.customfieldtypes:select'""")
}
output.each {
result = it as Map<String,String>
}

result.each {
log.warn it
}

@Ram Kumar Aravindakshan _Adaptavist_  sql.close() is not required ?

I think i must be closed after processing ? Please suggest

Hi @Vikrant Yadav

Try to log the output of the query like below:-

import com.atlassian.jira.component.ComponentAccessor
import com.onresolve.scriptrunner.db.DatabaseUtil
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface


def issueManager = ComponentAccessor.issueManager

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select cfname from customfield where customfieldtypekey ='com.atlassian.jira.plugin.system.customfieldtypes:select'")
}

log.warn "${output.size()}"

log.warn "${output}"

output.each {
result = it as Map<String,String>
}

result.each {
log.warn it
}

and see what do you get. 

Like Vikrant Yadav likes this

@Ram Kumar Aravindakshan _Adaptavist_  Great, Result is correct now. 

But i want every value in next line. Currently complete result in same line. I want to append /n also, so that i can output come in next line. Where do i need to put /n in output ?

Can i use this in our PROD server as well or it's risky touch JIRA DB ?

Hi @Vikrant Yadav,

In order to get the result in new lines, you can modify the code slightly like below:-

import com.onresolve.scriptrunner.db.DatabaseUtil

def output = []
def result = [:]

DatabaseUtil.withSql('local_db') { sql ->
output = sql.rows("select field_value from stored_value")
}

output.each {
result = it as Map<String,String>

result.values().findAll { value ->
log.warn value
}
}

So in this, it should print all the values. Below is the test result:-

result.pngIf you notice in the image above, the results are now displayed in new lines.

I hope this helps to answer your question. :)

Thank you and Kind regards,

Ram 

Like Vikrant Yadav likes this

Hi @Ram Kumar Aravindakshan _Adaptavist_  just to let u know , that we can run SQL query without creating Local DB resource. Initial script provided by me was correct only mistake I was doing is using “ log.debug” I have changed to log.warn and it worked. Script console script make connection with database for this we no need to make local db resource. 

thanks

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

JSM June Challenge #2: Share how your business teams became ITSM rockstars

For JSM June Challenge #2, share how your non-technical teams like HR, legal, marketing, finance, and beyond started using Jira Service Management! Tell us: Did they ask to start using it or...

168 views 6 7
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you