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.
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.
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.
Badges are a great way to show off community activity, whether you’re a newbie or a Champion.Learn more
Hi Community, Jessica here from the Confluence Product Marketing team! July’s community challenge is all about sharing pictures — and as an extension of our first post on what ...
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!
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