How do I split SQL output into separate lines via Scriptrunner?

[Michael Krüger]

Hi all,

I'm trying to run sql query to get all users who have never been logged in to JIRA via Scriptrunner console.

import com.atlassian.jira.component.ComponentAccessor

import com.onresolve.scriptrunner.db.DatabaseUtil

import groovy.sql.Sql

def issueManager = ComponentAccessor.issueManager

def output = []

DatabaseUtil.withSql('local') { sql ->

 output = sql.rows("SELECT cwd_user.id, cwd_user.user_name, cwd_user.created_date FROM jiradb.cwd_user WHERE not exists (select cwd_user_attributes.user_id from jiradb.cwd_user_attributes where cwd_user_attributes.attribute_name = 'login.count' and cwd_user_attributes.user_id = cwd_user.id) and cwd_user.active = true order by cwd_user.created_date")

}

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

log.warn "${output}"

output

I got a result, but it's one large string separated by comma.

I want to have the output line by line (1 line per user id), but everything I try fails.

Thx,

Michael

[Vikrant Yadav · Accepted solution]

Hi @Michael Krüger Welcome to Atlassian Community!
Try to modify your result part like below script, to get the Result in table format :- 

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")

//Update SQL query as per the your requirement

def sqlStmt = """

SELECT cwd_user.id, cwd_user.user_name, cwd_user.created_date FROM cwd_user

"""

Connection conn = ConnectionFactory.getConnection(helperName)

Sql sql = new Sql(conn)

//Create table

String result = """

<table>

  <tr>

    <th>UserId</th>

    <th>User Name</th>

    <th>Create Date</th>

  </tr>

"""

sql.eachRow(sqlStmt){ it ->

    result += """

        <tr>

          <td>${it.getAt("id")}</td>

          <td>${it.getAt("user_name")}</td>

          <td>${it.getAt("created_date")}</td> 

        </tr>

"""

    }

return result+"</table>"

sql.close()

[Vikrant Yadav]

@Michael Krüger  If SQL query doesn't works for you then try to run script mentioned in below link in script console :- 

https://gist.github.com/vikrantyadav11/61a04c6f05a8fb719e773ab42b1b5b67

[Michael Krüger]

Hi @Vikrant Yadav

thanks a lot for your quick response.

Sounds interesting with the table creation (I'll try that also), but I simply want to have the result line by line without any further formatting.

This is how the result looks like now:

After each closing square bracket a line break should be done.

Thx,

Michael

[Vikrant Yadav]

@Michael Krüger  

Try below script for this , use :- sb.append('\n')

But it give only result upto 300 lines only.

Update SQL query as well and column name cfname.

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.warn sb.toString()
}
finally {
sql.close()
}

Thanks

V.Y

[Michael Krüger]

Hi @Vikrant Yadav

that works for me. Thanks a lot.

Best,

Michael