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

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

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

3 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

0 votes
Jessie Wang_ScriptRunner_The Adaptavist Group
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
Aug 24, 2022

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

0 votes
Alexander Eck [Tempo]
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.
Mar 19, 2020

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 [Tempo] likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
6.15.9
TAGS
AUG Leaders

Atlassian Community Events