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

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