Scriptrunner macro to produce table from External mysql database

Moritz Wagner

Hello,

 

I'm just new to scriptrunner and trying to produge a macro that can output a table with data gathered by various MySQL queries. However I didn't get the output logic of custom scriptrunner macros yet. So displaying simple text is just putting a plain text into the code console, but as soon as I add my Mysql qiery it outputs the result object. Further more when trying to print the result object nothing changes. What am I missing here?

So this outputs the result object: 

import com.onresolve.scriptrunner.db.DatabaseUtil
'Hello <b>world</b>!'
def authors = DatabaseUtil.withSql('Jira') { 
sql -> sql.rows('select * from jiraissue WHERE ID < 10500')
}

and this results in outputting "null":

import com.onresolve.scriptrunner.db.DatabaseUtil

def authors = DatabaseUtil.withSql('Jira') { 
sql -> sql.rows('select * from jiraissue WHERE ID < 10500')
}
print('Hello <b>world</b>!')

Would be very nice, if somebody could give me a hint here, on how to output my result to form a table on the confluence page.

 

Thanks very much,

Moritz

Moritz Wagner

Okay I now understood, that the page output equals the macro script return value/output

So to produce a table one can simply use the following:

def xhtmlContent = ComponentLocator.getComponent(XhtmlContent)
def writer = new StringWriter()
def builder = new MarkupBuilder(writer)
def entity = context.getEntity()

def issues= DatabaseUtil.withSql('Jira') { 
  sql -> sql.rows('select * from jiraissue LIMIT 1000')
}
def output = "<table><tbody><tr><th>ID</th><th>Summary</th><th>Author</th></tr>"
def counter = 0

for(iss in issues){
  output = output + "<tr><td>"+iss.ID+"</td><td>"+xhtmlContent.convertWikiToView(iss.Description,context,new ArrayList<>())+"</td><td>"+iss.creator+"</td></tr>"

  counter=counter+1
}

xhtmlContent.convertStorageToView(output + "</tbody></table>Counted: "+counter, context)

 Maybe this simple example helps other newbies getting a jumpstart.

Now I just have to learn how to invoke jiras wiki parser for the jira wiki content instead of confluence's but maybe I'll add that later.

Thanks and have a great day,

Moritz