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
Answer accepted

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
Published Mar 12, 2019 in Confluence

Confluence Admin Certification now $150 for Community Members

More and more people are building their careers with Atlassian, and we want you to be at the front of this wave! Important Dates Start the Certification Prep Course by 2 April 2019 Take your e...

731 views 2 13
Read article

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