Display SQL results in a table using a scriptrunner macro

Filip Labarque October 30, 2019

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?

2 answers

1 accepted

4 votes
Answer accepted
Peter-Dave Sheehan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 30, 2019

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 October 31, 2019

Fantastic, just what I wanted.

Markus Pöhler February 4, 2020

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

 

 

TYgrafik.png

Peter-Dave Sheehan
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 5, 2020

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.

Like Jonny Carter likes this
1 vote
Tiffany Wortham
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 24, 2020

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 September 27, 2020

Hi Tiffany 

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

 

BR 

Jonny Carter
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 28, 2020

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.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events