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.

[Ram Kumar Aravindakshan _Adaptavist_ · Accepted solution]

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

[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

[Vikrant Yadav]

@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
}

[Vikrant Yadav]

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

I think i must be closed after processing ? Please suggest

[Ram Kumar Aravindakshan _Adaptavist_]

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. 

[Vikrant Yadav]

@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 ?

[Ram Kumar Aravindakshan _Adaptavist_]

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

If 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 

[Vikrant Yadav]

@Ram Kumar Aravindakshan _Adaptavist_  Thanks a lot, Mate!

[Vikrant Yadav]

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

[Raja Suriyamoorthi]

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

---