Example: 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.
For this example, we are going to use data from an external pet store database. To display a table with this store data, follow these steps:
Create a resource to configure an external database connection.
See External Database Connection for more information.Select the Create Macro button on the Macro page.
- Select Custom Script Macro.
- Configure your custom script macro by filling out the following fields:
- Key: pet-store-table
- Name: Pet store database table
- Description: Table displaying SQL results from an external pet store database
- Body Type: None
- Output Type: Block
- Skip the Parameter section.
Enter the following code for Macro Code:
import com.onresolve.scriptrunner.db.DatabaseUtil import groovy.xml.MarkupBuilder /* You will replace 'petstore' with whatever you named your pool when configuring your Resource. */ def rows = DatabaseUtil.withSql('petstore') { sql -> sql.rows('select NAME, SPECIES, ID From petstore') } def writer = new StringWriter() /* 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. */ 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()
For more information on MarkupBuilder and safe HTML, check out Security and Best Practices.
- Skip the Macro Javascript Code, Macro CSS Style, and Lazy Loaded fields.
- Click Add.
- View your new macro when the Macro page loads.
Result
When the macro is used on a page and the results load, it looks like the following image: