Scenario
| Div | ||
|---|---|---|
| ||
|
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:
| Code Block | ||
|---|---|---|
| ||
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:
| Code Block | ||
|---|---|---|
| ||
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.
...
| border | 0 |
|---|---|
| heading | 0 |
| multiple | false |
| enableHeadingAttributes | false |
| columnAttributes | style="border:0;width=5%;",style="border:0;width=65%;",style="border:0;width=30%;max-width=30% !important;" |
| id | steps_table |
| enableSorting | false |
| enableHighlighting | false |
...
Create a 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:
...
Type the following into the editor window. Note the dataSource name and file name referenced.
| Code Block |
|---|
{sql-file:dataSource=BookWarehouse|file=books.sql} |
...
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.
| Include Page | ||||
|---|---|---|---|---|
|