Problems with sql plugin

Bruce Schlueter April 28, 2013

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
Bruce Schlueter May 1, 2013

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.

4 votes
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.
April 28, 2013

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.

Bruce Schlueter April 29, 2013

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.

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.
April 29, 2013

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

4 votes
Daniel Borcherding
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.
April 28, 2013

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

Bruce Schlueter April 29, 2013

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. :-(

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events