nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL query on Script Console not giving result nor any error

Vikrant Yadav

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

Accepted answers

Ram Kumar Aravindakshan _Adaptavist_

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

Below is a print screen of the output:-

I hope this helps to answer your question.

Thank you and Kind Regards,

Ram

All comments

Ram Kumar Aravindakshan _Adaptavist_

Hi @Vikrant Yadav

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

Below is a print screen of the output:-

I hope this helps to answer your question.

Thank you and Kind Regards,

Ram

Vikrant Yadav

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()
}

Vikrant Yadav

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

Ram Kumar Aravindakshan _Adaptavist_

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

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

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

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

Raja Suriyamoorthi

Do any one have similar code with Java ?? With obiz delegator

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

scriptrunner

sql

jira-server

jira-core

script-console