Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Scriptrunner macro to produce table from External mysql database

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

2 answers

1 accepted

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

Just from curiosity. Where do you display the information?

Thanks

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. 

Like Alexander Eck likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
6.15.9
TAGS
Community showcase
Posted in Confluence

Lessons and Learnings: Six Months of Working Remote [Discussion]

Hey there, folks! For most of us, the past six months- yes, you read that right- have been a journey. More people than ever before have pivoted to working remotely, and navigating being on-scre...

8,591 views 7 7
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you