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
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.
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
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()
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Ram Kumar Aravindakshan _Adaptavist_ without creating DB connection i can't run SQL query in script console ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@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
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Ram Kumar Aravindakshan _Adaptavist_ sql.close() is not required ?
I think i must be closed after processing ? Please suggest
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@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 ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Ram Kumar Aravindakshan _Adaptavist_ Thanks a lot, Mate!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Do any one have similar code with Java ?? With obiz delegator
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.