Use SQL File Macro with Markup - 11.x

Scenario

Estimated Time: 10 min

In this scenario, we need to add the SQL File macro to a Confluence page by inserting Markup from the menu. The SQL File macro references a file located within the <confluence-home>/script (default) directory on the Confluence server.

Background

In this scenario, use a data source profile, BookWarehouse, to query a database table, BOOKS. The table contains the following columns and rows:

SQL > select * from books;
+----+----------------------------+---------------------+-------+
| id | title                      | author              | price |
+----+----------------------------+---------------------+-------+
|  1 | The Grapes of Wrath        | John Steinbeck      | 12.99 |
|  2 | Nineteen Eighty-Four       | George Orwell       |  8.99 |
|  3 | The Wind-Up Bird Chronicle | Haruki Murakami     |  7.99 |
|  4 | Don Quixote                | Miguel De Cervantes | 29.99 |
|  5 | Pilgrims Progress          | John Bunyan         | 17.99 |
|  6 | Robinson Crusoe            | Daniel Defoe        | 29.05 |
|  7 | Gullivers Travels          | Jonathan Swift      | 15.98 |
|  8 | Tom Jones                  | Henry Fielding      | 13.59 |
|  9 | Clarissa                   | Samuel Richardson   | 13.59 |
| 10 | Tristram Shandy            | Laurence Sterne     |  7.59 |
+----+----------------------------+---------------------+-------+

The statement is present in the books.sql file and is located within the <confluence-home>/script (default) directory on the Confluence server. The statement is as follows:

SELECT title as "Title", author as "Author", price as "Retail Price" FROM books WHERE price < 30 ORDER BY title;

Steps

The following steps demonstrate the steps involved in inserting a SQL File macro on a Confluence page:

Create a Confluence page

Create a Confluence page that contains a SQL File macro.

Create a page named Classic Books in a space accessible to the desired audience.
Click Save to save the new page.

Insert SQL File macro using the Insert markup menu

Edit the Classic Books page and:

Select Insert markup from the Insert content list at the top.
Type the following into the editor window. Note the dataSource name and file name referenced.

{sql-file:dataSource=BookWarehouse|file=books.sql}

Click Insert to include your Markup within the page.
Click Save to save your page and view your results.

How to Insert markup from the menu

Alternative method

Edit the Classic Books page and:

On the page, begin typing { (open parenthesis) and then type sql-.
Select SQL File from the available list of macros.
The macro editor dialog appears.
Select BookWarehouse as the data source name.
Type books.sql into the File name field.
Click Save settings to insert your Markup within the page.
Click Save to save your page and view your results.