PQ_128x128

Using Confluence in your daily business emerges the essential need to connect wiki pages with well structured data from different sources. Regarding this requirement from the administration side of a confluence instance the maintenance should be kept clean. That’s why we’ve invented PocketQuery.

PocketQuery authorizes Administrators to connect any database via JDBC and to display arbitrary structured data. Therefore we developed an own maintenance area for PocketQuery within the admin interface of Confluence. Here’s the place where all the magic happens. Databases, queries and appropriate templates are managed at one place. This gives you the secureness, that only permitted queries can be used in Confluence. You can assign those queries which are already defined to one or several, even all wiki areas - just fitting right your needs.

PQ_admin-overview

Queries can be embedded into pages via Confluence macro, where you can define a query-name and pass appropriate parameters. Users are not allowed to enter any SQL statements.

PQ_contact_query_management

You can choose any available JDBC connection. Drivers should be accessible in the Java application for sure. To connect a database with Confluence declaring the driver and database as well as user and password is enough.

PQ_database

After a database has been successfully added to the PocketQuery admin interface you can use it for all your Queries. A Query describes an arbitrary SQL Statement, which can be used with named-parameters. Each parameter can be passed while a macro is being executed. e.g.

SELECT * FROM contact WHERE company = :company

PQ_contact-query

Now you can fill in the parameter company=scandio when executing the macro. It is also possible to use wildcards for parameters within PocketQuery, e.g. @page. This one passes the current page-name and the query.

After a query has been created it can instantly be used. The output of query results will be shown as a sortable table.

PQ_contact_table

If you wish to customize the output you can easily replace the standard table with own templates. The common Templating Lanugage in Confluence is Velocity. Following example fits to the SQL statement which was mentioned above:

PQ_contact_template

We integrated GoogleCharts API, so you can visualize all query results as diagrams. We also implemented a JavaScript API to simplify the usage. This Query

SELECT year, sum(revenue) totalRevenue
FROM billing GROUP BY year ORDER BY year

could be displayed like the following chart:

PQ_barchart

Scandio PocketQuery represents an indispensable plugin for Atlassian’s Team-Collaboration Tool Confluence. It will be refined continuously. At the moment PocketQuery is available for free - try it out now.