Display SQL Results from an External Database Macro

If you want to display SQL results from an external database in a table, you can easily accomplish this with a custom script macro. In this example, we display results from a pet store database:

To display results from an external database of your choice, first, you’ll create a Resource to configure an external database connection. See External Database Connection for more information.

Once you’ve created a Resource for your database connection, you can configure your custom script macro. The following image shows a sample configuration for this custom script macro:

The file in the Macro Script field contains the following script:

groovy
import com.onresolve.scriptrunner.db.DatabaseUtil
import groovy.xml.MarkupBuilder

def rows = DatabaseUtil.withSql('petstore') { sql -> 
    sql.rows('select NAME, SPECIES, ID From petstore')
}

def writer = new StringWriter()
def builder = new MarkupBuilder(writer) 

builder.table('class': 'aui') {
    tr {
        rows.first().keySet().each { key ->
            th {
                mkp.yield(key)
            }
        }
    }
    rows.each { columns ->
        tr {
            columns.each { cell ->
                td {
                    mkp.yield(cell.value)
                }
            }
        }
    }
}

return writer.toString()

Line 4: You will replace 'petstore' with whatever you named your pool when configuring your Resource.

Line 9: Notice our use of Groovy’s MarkupBuilder here. This is a helper class for creating XML or HTML markup. It’s very important to use the MarkupBuilder here to ensure your HTML is safe.