Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,359,759
Community Members
 
Community Events
168
Community Groups

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

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

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
Community showcase
Published in Confluence

Watch 4 Confluence apps compete for Best App Demo in September's Appy Hours

Calling all collaborators! Appy Hours on the Atlassian Community is a monthly event where 4 Partner and app vendor presenters go head-to-head with 5-minute demos for the title of Best App Demo. I...

475 views 0 12
Read article

Atlassian Community Events