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

Michael Krüger_telpjb

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

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