/
Using SQL File with Markup - 9.x and earlier

Using SQL File with Markup - 9.x and earlier

Feb 24, 2020

Scenario

Estimated Time: 10 min

In this scenario, let's insert the SQL File macro on a Confluence page by inserting Markup from the menu. Our SQL File macro references a file containing SQL located within the /script (default) directory of your Confluence server.

Background

Our scenario uses 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 | +----+----------------------------+---------------------+-------+

For information about data source profiles, refer to:

Our query is stored within a file called books.sql and along 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 demonstrates how to use Markup to insert the SQL File macro on a Confluence page:

Create Confluence page

Create a Confluence page to add the SQL File macro needed to list out your books.

  1. Create a page named Classic Books in a space accessible to the desired audience.

  2. Click Save to save the new page.





Insert the SQL File macro using the Insert Markup Menu

Edit the Classic Books page and:

  1. Select Insert Markup from the top menu.

  2. Type the following into the editor window. Note the dataSource name and file name referenced: 

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

  3. Click Insert to insert your Markup within the page.

  4. 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:

  1. On the page, begin typing { (open parenthesis) and then type sql file.

  2. Select SQL File from the available list of macros.

  3. The macro editor dialog appears.

  4. Type books.sql into the required File field.

  5. Select BookWarehouse as the Data source name.

  6. Click Insert to insert your Markup within the page.

  7. 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 demonstrates how to use our SQL macros with the native Chart macro.