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 · Accepted solution]

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

[Jessie Wang_ScriptRunner_The Adaptavist Group]

Hi there, ScriptRunner now has a video up that shows you how to do this.

It will talk you through how to establish a database connection then how to display the results of a SQL query on a Confluence page via a custom macro. https://www.youtube.com/watch?v=5UkGsOtkoTg

The script for the custom macro can be found here: https://library.adaptavist.com/entity/display-sql-results-from-an-external-database-macro

[Alexander Eck [Tempo]]

Just from curiosity. Where do you display the information?

Thanks

[Moritz Wagner]

I attached this code to a custom macro, so that I can display it on any Confluence page right at the position I add the custom Macro.