Query external database and display results on confluence page

Deleted user December 4, 2013

I'm trying to query external databases and display the results on a confluence page. What would be some options for doing this.

I'd like to do something with AJAX and a php script on the Confluence server but I'm not exactly sure how to get this to work.

Thanks

2 answers

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

2 votes
Adrien Ragot 2
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 5, 2013

Hi Richard,

It's quite simple to add the CORS headers in PHP so you'll be able to show your panel even if it's on a different domain - so I think you should just do that.

Apart from this, we'd recommend to use public add-ons for SQL, so long as they are published for free, you'll have more features available. Play SQL Base offers caching and built-in parameters (the page name, etc), and integrates with the Charts macro of Confluence. PocketQuery has Google Charts integration and SQL Plugin has many features and has been proven for 6-7 years on the Marketplace... One of them should fit your need!

Cheers

2 votes
Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 5, 2013

There are a few options. There are three plugins that I know of in the marketplace that can work with SQL directly (SQL Plugin for Confluence, Play SQL Base, and PocketQuery). You could also enable the html macro(it comes disabled by default, I believe) and then you can put in a script tag inside the HTML macro referecing an external js, or even inline the js. Then you can just do the AJAX call to a webservice to get the data and display it on the page. Confluence has jQuery bundled so setting up the AJAX call is pretty simple.

Bob Swift OSS (Bob Swift Atlassian Apps)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 5, 2013

Adding to Davin's answer, the Future macro does the AJAX for you and Cache Macro is often used in situations where performance is a concern. Both work with the SQL Macro or other scripting. If you really want to program yourself, then the Script Plugin for Confluence macro has various language options, but php isn't one of them.

Like Dave Collins likes this
Deleted user December 5, 2013

Davin,

I think I have the AJAX call part, but the webservice is what I'm getting stuck on. I've used the code below in the HTML macro, I have the following line calling a PHP file that does the query. I think this isn't working because PHP+Apache are not installed, would installing them solve this? What are some other options I have for doing a webservice?

<script>
  AJS.toInit(function(){
    AJS.$(document).ready(function(){
      AJS.$("button").click(function(){
        AJS.$("#div1").load('test.php');
      });
    });
  });
</script>

<div id="div1"></div>
<button id="button">Do Query</button>

Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 5, 2013

Can you browse to the test.php directly? What happens? Where did you place the php file? Because the way you have it the AJAX call would try to use the path to your current URL. So, it would assume it is a sibling of your current page, which doesn't make much sense as Confluence doesn't use PHP. If you want to use PHP you are going to have to have PHP installed on another webserver. It could still be the same machine but the web server would have to run on a different port than you Confluence instance ... they both couldn't run on port 80.

Deleted user December 5, 2013

I have updated the url to go to mysite.

AJS.$("#div1").load('http://mysite/test.php');

This is different than my confluence site. I’m having issues with single origin, can’t access a page on a different domain. If I browse directly to this php page it displays the text output of the query, so I’m pretty sure that part is working.

What are other options for web services I could call on the Confluence machine that would be able to do the queries to external databases?

Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 5, 2013

You need to use something called JSONP if you are going to be doing a cross domain AJAX request. Look into JSONP ... jQuery supports it natively too.

TAGS
AUG Leaders

Atlassian Community Events