How to create a table from a DB data

Overview

Assume you want to create a Confluence table that would be built on data, sourced from from some database. You can achieve this following the steps below.

Prerequisites

  1. Confluence (Server)

  2. SQL-Powered Table Transformer macro for Confluence installed

  3. Depending on the source DB used: Oracle, Postgres, Mysql or other jdbc driver installed on your Confluence server.

  4. Documentation: https://v-and-b.atlassian.net/wiki/spaces/PD/pages/763232257

Step-by-Step Instructions (Oracle)

  • Add SQL-Powered Table Transformer macro container to a Confluence page

  • Edit the macro properties as follows:

  • Put ; into "Query Splitter" field.

  • Put the following into the "SQL Query" field:

Hint: you can use a Helper Snippet to quickly add the below piece of code - just start typing the following in the SQL query editor: link_
You’ll see a drop down list with the matching Helper Snippets. Select the Oracle snippet.

1 2 3 4 5 6 7 8 9 10 CREATE LINKED TABLE     LINK(         'oracle.jdbc.OracleDriver',         'jdbc:oracle:thin:@<db01.mydb-host.com:1522>/<mydb_name>',         '<mydb_user_name>',         '<mydb_password>',         '<mydb_table_name>'     ) READONLY;   SELECT * FROM LINK LIMIT 20;
  • Replace the parameters within <> with your desired connection properties.

  • Save the macro configuration.

  • Now the macro will bring you some data of the linked table (in our example we limited it to 20 rows).

Step-by-Step Instructions (Postgres)

  • Add SQL-Powered Table Transformer macro container to a Confluence page

  • Edit the macro properties as follows:

  • Put ; into "Query Splitter" field.

  • Put the following into the "SQL Query" field:

Hint: you can use a Helper Snippet to quickly add the below piece of code - just start typing the following in the SQL query editor: link_
You’ll see a drop down list with the matching Helper Snippets. Select the Postgres snippet.

1 2 3 4 5 6 7 8 9 10 CREATE LINKED TABLE     LINK(         'org.postgresql.Driver',         'jdbc:postgresql://<db02.mydb-host.com:5432>/<mydb_name>',         '<mydb_user_name>',         '<mydb_password>',         '<mydb_table_name>'     ) READONLY;   SELECT * FROM LINK LIMIT 20;
  • Replace the parameters within <> with your desired connection properties.

  • Save the macro configuration.

  • Now the macro will bring you some data of the linked table (in our example we limited it to 20 rows).

Other DBMS

Linking tables from other databases (such as MySQL, H2, etc.) can be achieved in similar ways (see examples above). If you use H2 as the source DB, then you don’t need to install the JDBC driver (since it comes with the SQL-Powered Table Transformer macro).

Some Additional General Notes

If DB gets updated, the tables generated by the macro will be also updated.

You can link more than one table from the database and use those in your SQL Query. You can even link one table from one database and another table from another database (even other type DB) and execute an SQL on those linked tables as if they were the same database.