Scenario

Estimated Time: 10 min

In this scenario, we will insert the SQL-file Macro on a Confluence page by inserting Markup from the menu. Our SQL-file Macro will reference a file containing SQL located within the /script (default) directory of your Confluence server.

Background

Our scenario will use a Data Source Profile called "BookWarehouse" to query a database table called "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 |
+----+----------------------------+---------------------+-------+

Our query will be stored within a file called "books.sql" and we will work with our Confluence administrator to place it within the /script (default) directory on our Confluence server. Our query looks like:

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

Steps

The following steps will demonstrate how to use Markup to insert the SQL-file Macro on a Confluence page.

Create Confluence page

Create a Confluence page that will eventually contain the SQL-file Macro needed to list out your books.

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

Insert the SQL-file Macro using the Insert Markup Menu

Edit the Classic Books page and:

Select Insert Markup from the top menu.
Type the follwing into the editor window. Note the dataSource name and file name referenced.
```
 {sql-file:dataSource=BookWarehouse|file=books.sql}
```
Click Insert to insert your Markup within the page.
Click Save to save your page and view your results.

Alternative:

Insert the SQL-file Macro using Markup on page

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 will appear.
Type "books.sql" into the required "File" field.
Select "BookWarehouse" as the Data source name
Click Insert to insert your Markup within the page.
Click Save to save your page and view your results.

How to insert Markup from the menu

Explore Tutorials

We've prepared a number of tutorials which demonstrate how to use our SQL Macros with the native Chart Macro.

Using SQL-file with Markup