Display SQL results in a table using a scriptrunner macro

[Filip Labarque]

Hi,

I'm trying to display the results from an SQL on an external dabase in a table using a scriptrunner macro. I've added the external DB as a resource in scriptrunner. When running a custom script macro like this:

import com.onresolve.scriptrunner.db.DatabaseUtil
def SqlList = DatabaseUtil.withSql('iTrackDB') { sql ->
sql.rows('SELECT TOP 5 [ID],[user_name] FROM <tablename>')}

I get this result when using that macro on a page:

[{ID=<ID1>, user_name=<user1>}, {ID=<ID2>, user_name=<user2}, {ID=<ID3>, user_name=<user3>}, {ID=<ID4>, user_name=<user4>}, {ID=<ID5>, user_name=<user5>}, , ]

So the connection works fine, but how can I present this result as a table?

[PD Sheehan · Accepted solution]

I do something like this:

import com.onresolve.scriptrunner.db.DatabaseUtil

def rows = DatabaseUtil.withSql('iTrackDB') { sql ->
   sql.rows('SELECT TOP 5 [ID],[user_name] FROM <tablename>')
}

def sqlTable = new StringBuffer()
//make a header row from the set of keys
sqlTable << """<table class="aui"><tr>"""
rows .first().keySet().each{
   sqlTable << """<th>$it</th>"""
}
sqlTable << "</tr>"
 
//now get each sql row and create a table row
rows.each{ columns ->
  sqlTable << "<tr>"
  //look in each columna dn create a td element
  columns.each{ cell ->
    sqlTable << "<td>$cell.value</td>"
  }
  sqlTable << "</tr>"

}
return sqlTable

[Filip Labarque]

Fantastic, just what I wanted.

[Markus Pöhler]

Hi Peter

I get errors trying exactly your code. Do you know why?

import com.onresolve.scriptrunner.db.DatabaseUtil

def rows = DatabaseUtil.withSql('test_cn') { sql ->
sql.rows('select COD_ACCESS, NAME From myTable')}


def sqlTable = new StringBuffer()
//make a header row from the set of keys
sqlTable << """<table class="aui"><tr>"""
rows .first().keySet().each{
sqlTable << """<th>$it</th>"""
}
sqlTable << "</tr>"

//now get each sql row and create a table row
rows.each{ columns ->
sqlTable << "<tr>"
//look in each columna dn create a td element
columns.each{ cell ->
sqlTable << "<td>$cell.value</td>"
}
sqlTable << "</tr>"

}
return sqlTable

TY

[PD Sheehan]

Static Type checking errors are informational only. The code will still work.

This is just because the code editor is unable to guess the data type of the rows variable.

[Tiffany Wortham]

Hey Filip!

Peter's answer is great, and just in case you're interested, I've created a script based off of it that uses MarkupBuilder, which is helpful to make sure your HTML is correct, and to prevent XSS attacks:

import com.onresolve.scriptrunner.canned.util.OutputFormatter
import com.onresolve.scriptrunner.db.DatabaseUtil

def rows = DatabaseUtil.withSql('petstore') { sql ->
    sql.rows('select NAME, SPECIES, ID From petstore')}

def sqlTable = OutputFormatter.markupBuilder {
    table('class':'aui') {
        tr {
            rows.first().keySet().each { key ->
                th {
                    mkp.yield(key)
                }
            }
        }
        rows.each{ columns ->
            tr {
                columns.each { cell ->
                    td {
                        mkp.yield(cell.value)
                    }
                }
            }
        }
    }
}

return sqlTable 

[Markus Pöhler]

Hi Tiffany 

what is the „MarkupBuilder“ ? I can‘t find it in the MP.

BR 

[Jonny Carter]

MarkupBuilder is just a utility class in the Groovy programming language that ScriptRunner for Confluence uses. You don't need an additional add-on.

If you'd like to see this script in action, Tiffany demonstrated it during our last Champion Hour Webinar, which you can watch at https://www.adaptavist.com/webinars/champion-hour-scriptrunner-september.