Problems with sql plugin

Hi folks,

I am running into problems running the sql plugin. My problem may be a limitation I dont know about but perhaps you know a way. What I am trying to do is build a list containing page title, author, creationdate, number of likes, labels etc.

To solve this I want to use temp tables. This works fine if I run the query directly on my database. If I put this query inside the plugin it just wont work. I found out that I can only put 1 query inside the plugin.

What does not work is something like this (a simple example):

CREATE TEMPORARY TABLE tmp_test
(c1 BIGINT NOT NULL,
c2 VARCHAR (255),
PRIMARY KEY (c1));

INSERT INTO tmp_test (c1, c2) VALUES (1,'A'),(2,'B');

SELECT * FROM tmp_test;

As soon as I split it up into three separate macro steps it works (If I dont use temp tables).

Is this how it should work or am I missing something? We are running Confluence 4.3.2 against MySQL.

Thanks, Bruce

3 answers

1 accepted

0 votes
Accepted answer

I found a workaround that solves my initial problem. With MySQL it is possible to do a "group_concat()" so that I can group the lables belonging to a page into one column. So there is no need to work with temporary tables.

Hello Bruce,

I believe you should be able to create a temp table from the sql plugin. I would check that the user that you are using to query your database has the appropriate privilidges.

Bob Swiff, the creator of the plugin is a very active member on this forum. I might wait to see what he has to say about this.

If you want to contact him directly I would use the contact page from our marketplace.

https://marketplace.atlassian.com/plugins/org.swift.confluence.sql

I believe I have all priviliges needed as I did the test with root. I am able to create a table if I split the lines into three separate macros. But with this the temporary tables are not visible. It looks like that each macro stands for a single connection and therefore the temp table is not visible for the next statement. :-(

Your example works for me. Make sure you are on a later version of MySQL and the connector since I think earlier versions did not support multiple results sets (ie. multiple lines of sql separated by semi-colon). Postgres has always had that, but I am a bit fussy on when MySQL support came in.

well we are running the latest version of the plugin 5.4.1 gainst a MySQL jdbc 5.1.11. So I do believe we are quite up to date. Did you run this script against MySQL or Postgres? I saw an example in the user macros with loads of SQL statements but this looks like postgres also. So there may be a trick to get it running against MySQL. I will investigate a little further.

Yes, 5.5.28 with connector 5.1.22. I do use postgres mostly for examples :).

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Monday in Confluence

Organizing your space just got easier - Page Tree Drag & Drop is here

Hi Community! I’m Elaine, Confluence Product Manager. You may have read my earlier post about page tree in space navigation sidebar. I'm excited to share another improvement that helps you organize ...

72 views 3 3
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you