Displays the result table or values from the SQL query on a Confluence page. Data from single or multiple results sets are formatted, usually as a table, for display. Multiple SQL statements (semi-colon separated) can be specified within a single sql macro body (subject to support provided by specific databases).
Download statistics
Date
2009-Jan
2009-Feb
2009-Mar
2009-Apr
2009-May
2009-Jun
2009-Jul
Hits
463
347
342
307
267
323
29
Security
This macro and the datasource parameter can be restricted to trusted user by configuring security using the Macro Security Plugin.
Security migration from 3.3.0 and earlier Macro security users upgrading to 3.4.0 or above from 3.3.0 or earlier must install and config the Macro Security Plugin to retain your security.
Macros
sql - supports most SQL statements
sql-query - support read-only SQL statements
sql1 - duplicate of sql for recursive use
sql2 - duplicate of sql for recursive use
sql3 - duplicate of sql for recursive use
sql-query1 - duplicate of sql-query for recursive use
sql-query2 - duplicate of sql-query for recursive use
sql-query3 - duplicate of sql-query for recursive use
dataSource - Required parameter. Specify the datasource name defined in the application server. Do not use the standard prefix (java:comp/env/jdbc/) as it is added automatically. Formerly called the jndi parameter which will remain valid for upward compatibility.
output - Determines how the output is formated:
html - Data is output as a HTML table (default).
wiki - Data is output as a Confluence wiki table. Use this option if you want data within the table to be formated by the Confluence wiki renderer.
script - Location of sql statement. Default is the macro body only.
#filename - Data is read from the file located in confluence home directory/script/filename. Subdirectories can be specified.
global page template name - Data is read from a global page template.
space:page template name - Data is read from a space template.
^attachment - Data is read from an attachment to the current page.
page^attachment - Data is read from an attachment to the page name provided.
space:page^attachment - Data is read from an attachment to the page name provided in the space indicated.
heading - Number of rows to be considered heading rows (default is 1 row). Specify heading=false or heading=0 to not show any heading lines. Heading rows do not participate in sorting.
border - The border width in pixels. Defaults to normal table border width.
width - The table width in pixels. Default is 100%.
rowOrientation - By default, data is oriented with rows appearing vertically (in rows) and columns in columns. Specify rowOrientation=horizontal to have rows appear horizontally (as columns) and columns appear as rows.
columnLabel - By default, database column names are used as column labels. Specify columnLabel=true to have database column labels used for column labels. Note that most databases default the column label to be the column name when no explicit column label is provided. Fly-over text for a column label will be the column name when columnLabel=true. Fly-over text for a column label will be the database column label when columnLabel=false or by default.
escape - When wiki output is requested (output=wiki), some special characters (like '|', '[', ']', '{', '}') in data may cause undesirable formatting of the table. Set escape=true to allow these special characters to be escaped so that it will not affect the formatting. The default is false so that data that has wiki markup will be handled correctly.
convertNull - Default is true. Converts SQL NULL values to blank. Set to false to have NULL values show up as 'null'.
table - Default is true. Set to false to not format the data in a table. This option can be used to run queries that produce single values that you want to include in text.
noDataError - Default is false. Set to true to show an macro error when no data is returned from the query.
expandArray - Default is true to have array fields expanded one entry per line for vertical row orientation.
showSql - Default is false. Set to true to show a panel with the sql statement.
macros - Default is false. Set to true to resolve any macros in the sql statement.
SQL parameter markers
SQL supports parameter markers in statements - these are identified by ?'s in the SQL statement. Parameters p1, p2, ... are substititued for the parameter markers. This can be used to parameterize complex scripts stored as attachments or files.
p1 - first parameter marker
p2 - second parameter marker
... - ...
Data source setup
Your application server must have a datasource configured for each database you access, so if you wish to access Confluence data, you must add the appropriate datasource.
Confluence Standalone And Tomcat Application Server
Please follow this guide on adding the Confluence datasource. If you are using Confluence standalone, you can check which data sources you have configured by opening your_install_directory/conf/server.xml. If the guide was insufficient for you to successfully setup your datasource, please check out the example datasources below, which can be modified and pasted into your{{your_install/conf/server.xml}}:
The following examples use the Tomcat 5.5 resource specification scheme which is different from Tomcat 5.0. See the Tomcat 5.0 JNDI datasource documentation for an example of configuring a resource using the older scheme.
MySQL DataSource Example - Local Confluence Database
You can also view the attached server.xml for a further example, though you should modify your own server.xml rather than using this file as a template.
WebLogic 9.1 Application Server
In the WebLogic console, under Services > JDBC, create a data source (see WebLogic's documentation on how to Configure JDBC data sources for more information).
Take note of the JNDI name you used to create the data source.
Point the SQL macro to that JNDI name.
Usage
{sql-query:dataSource=TestDS|output=wiki}
select * from test
{sql-query}
There are *{sql-query:dataSource=TestDS|table=false} select count(*) from test01 {sql-query}* rows in table test01
{sql-query:dataSource=TestDS|p1=%test%|showsql=true}
select * from test01 where a1 like ?
{sql-query}
The JIRA server does not support trust requests. Issues have been retrieved anonymously. You can set the macro to always use an anonymous request by setting the anonymous parameter to true
The JIRA server does not support trust requests. Issues have been retrieved anonymously. You can set the macro to always use an anonymous request by setting the anonymous parameter to true
The JIRA server does not support trust requests. Issues have been retrieved anonymously. You can set the macro to always use an anonymous request by setting the anonymous parameter to true
The JIRA server does not support trust requests. Issues have been retrieved anonymously. You can set the macro to always use an anonymous request by setting the anonymous parameter to true
The JIRA server does not support trust requests. Issues have been retrieved anonymously. You can set the macro to always use an anonymous request by setting the anonymous parameter to true
The JIRA server does not support trust requests. Issues have been retrieved anonymously. You can set the macro to always use an anonymous request by setting the anonymous parameter to true
The JIRA server does not support trust requests. Issues have been retrieved anonymously. You can set the macro to always use an anonymous request by setting the anonymous parameter to true
The JIRA server does not support trust requests. Issues have been retrieved anonymously. You can set the macro to always use an anonymous request by setting the anonymous parameter to true
Wiki markup would be a great thing to have in this macro, I hope it makes it!
A...
Wiki markup would be a great thing to have in this macro, I hope it makes it!
Another feature I'd like to see is the ability to handle multiple result sets, as when you use a COMPUTE statement. That would make it the one SQL macro that does everything!
Hello~~ Is this feature implemented?Why do I always get error notificatoins...
Hello~~ Is this feature implemented?Why do I always get error notificatoins when I try to use multiple SQL statements (semi-colon separated).
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; select * from school' at line 1
Actually, there are not any syntax error. Just two select statement, like "select * from student; select * from school";
Yes, multiple results sets are implemented. You need a sql statement that is va...
Yes, multiple results sets are implemented. You need a sql statement that is valid for your database and your database is saying your sql is not valid. I know earlier versions of mySQL did not allow multiple statements on a single request for instance. Postgres allows for semi-colon separated statements. Whatever sql statement you use, make sure it works when submitted through your database admin tool or favorite sql tool.
The following error is prompted when trying to use the sql macro. Any suggestion...
The following error is prompted when trying to use the sql macro. Any suggestions?
"sql: javax.naming.NameNotFoundException: Name jdbc is not bound in this Context"
I have two instances of Confluence running: one with the default database and the other is using Oracle. I have tried to implement the sql macro with both instances without succeeding. Thanks in advance.
How are you connecting confluence to the database? ie: are you using a datasourc...
How are you connecting confluence to the database? ie: are you using a datasource bound to the application containers JNDI or did you select the direct connection option? In the case of the direct connection option, the internal connection pool and database connections are currently not made available via jndi.
If you have set up confluence via the datasource option, I would suggest double checking the jndi name.
That still doesn't work for me though. I got this error:
sql: org.apache.common...
That still doesn't work for me though. I got this error:
sql: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory, cause: Io exception: The Network Adapter could not establish the connection
Do you know why Confluence is connected with our Oracle but the sql macro failed to? Thanks!
In my conf/server.xml, I added <Resource name="jdbc/confluence" auth="Contain...
In my conf/server.xml, I added <Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"/> and it's an oracle DS. Confluence was running fine. But using {sql:jndi=confluence} gave me the error I listed above.
And is confluence the same resource being used by Confluence or did you add this...
And is confluence the same resource being used by Confluence or did you add this only for the sql macro? I suggest you clip out all your data source specifications in server.xml and attach it here so we know exactly what it looks like.
I also searched and made sure under my confluence installation directory this file is the only one that has my oracle data source definitions. I've restarted server a couple times since moving to Oracle and Confluence was running perfectly on it.
OK. I was mostly looking to use your suggestion that retrieves some stats ...
OK. I was mostly looking to use your suggestion that retrieves some stats about confluence usage, but I guess I'll just directly use a script to do it if I need to. One thing missing from your stats sqls is the page access stats, but I guess that's not stored in the DB, so we could only analyze server logs for those stats, right?
All looks okay...
Just to verify that Confluence is using this exact same datas...
All looks okay...
Just to verify that Confluence is using this exact same datasource, can you please have a look at the CONFLUENCE_HOME/confluence.cfg.xml file, and tell me the value of the "hibernate.connection.datasource" property.
hmm, that might be the problem. In the cfg.xml, there are lots of hibernat...
hmm, that might be the problem. In the cfg.xml, there are lots of hibernate parameters, but none called hibernate.connection.datasource. Instead, there are hibernate.connection.username/password/url. So somehow my installation must be using direct connection then. Should I deleted these lines and replace with the datasource line?
However, I would strongly recommend that you get the SQL macro working first. If the SQL macro is not able to use the datasource that you have configured within your app server, then Confluence will not be able to do so either.
You cannot currently setup Confluence against aMS Access database. Since this ma...
You cannot currently setup Confluence against aMS Access database. Since this macro uses Confluence's database connection, you will not be able to use this macro with a MS Access db.
Just to make sure there is no confusion, the macro can use any data source that ...
Just to make sure there is no confusion, the macro can use any data source that is configured in the app server, not just the confluence data source. For instance, I have about 10 data sources configurated from multiple systems. That said, I don't know if MS Access can be configured as a data source.
Certainly a rookie's question but I cannot figure out how to configure the syste...
Certainly a rookie's question but I cannot figure out how to configure the system to use a datasource connection:
I installed a standalone 2.1.1 connected to a MySql database
During the installation process I selected a direct JDBC Connection with database URL= "jdbc:mysql://localhost:3306/confluence?autoReconnect=true"
I understand that I should have rather selected a datasource connection in order to be able to use this SQL plugin - right?
If this is the case what has to be done? I tried to install again but the system never accepted any of the datasource name I provided: java:comp/env/jdbc/confluence or jdbc/confluence.
It does not matter how you installed Confluence. However, for any database that...
It does not matter how you installed Confluence. However, for any database that you want to access, there needs to be a data source defined in your application server. So for instance, I have about 20 data sources defined accessing different databases. If you want access to the database Confluence uses, then, you will have to define a data source to that database and if you do that it is natural to also use that data source for Confluence. I will add some further data source configuration information on this page.
Yes, good warning. The server.xml is for the Tomcat that ships with the standal...
Yes, good warning. The server.xml is for the Tomcat that ships with the standalone version of Confluence - Tomcat 4. I have examples for Tomcat 5.5 as well if someone needs that.
How do you configure multiple data sources in the servers.xml? Is that don...
How do you configure multiple data sources in the servers.xml? Is that done by providing multiple <ResourceParam> elements? I'd like use this macro to access our JIRA database for displaying in Confluence pages. Is this possible?
Yes, I have over 20 defined in one of my installations. You need the
<Resou...
Yes, I have over 20 defined in one of my installations. You need the
<Resource name=... and ResourceParams name=... as many times as you have different data sources.
Ok, you will have to be more specific. What application server/version are you ...
Ok, you will have to be more specific. What application server/version are you using? Or are you using standalone version of Confluence or Jira? If so, what version. Attach your server.xml.
Jira 3.5 standalon Tomcat is a higher release level (5.5) and so excepts better ...
Jira 3.5 standalon Tomcat is a higher release level (5.5) and so excepts better stuff. I wish Confluence and Jira supported the same level, it would make things less confusing.
Question regarding the use of parameters...
I would like to use the SQL Macro o...
Question regarding the use of parameters...
I would like to use the SQL Macro on one wiki page to generate links to another wiki page, and pass parameters to that second wiki page for expansion in a second SQL Macro. For example:
EmployeeID
First Name
Last Name
Personnel Record
1
John
Doe
(link with param 1)
2
Jane
Doe
(link with param 2)
3
Sara
Dane
(link with param 3)
The fourth column would then link to another wiki page, where the employeeID is automagically expanded into one of the parameter fields in the sql macro definition of that page:
{sql:dataSource=TestDS|p1=(employeeID here)|showsql=true}
select * from personnelrecord where employeeID = ?
{sql}
Which in turn would show something like
EmployeeID
First Name
Last Name
Birthdate
Unladen Airspeed Velocity
1
John
Doe
1/1/76
43mph
Sort of a summary and subsequent drill-down, if you will. I've accomplished the creation of a link in the first table by mangling the query to return wiki markup, but I haven't figured out the passing of parameters.
Is this remotely feasible or am I trying to use this in an unintended fashion? If this isn't possible, is there an alternative wiki plugin I could consider? I've briefly looked at the Jasper Report Macro and while it looks more complicated, perhaps that's the way to go.
Derek, I have similar problems and I have another plugin in the works that will ...
Derek, I have similar problems and I have another plugin in the works that will help with this type of problem. Unfortunately, it was not quite ready for the 2.3 release. Can you please capture your request and open a issue so it can be tracked and discussed. Thanks. The technique of using the query to create wiki markup for more complex things is valid - we use this in a number of places.
Derek, you can experiment with the plugin attached to http://developer.atlassian...
Derek, you can experiment with the plugin attached to http://developer.atlassian.com/jira/browse/SCRP-70. It is work in progress and you will need to figure it out from the documentation in the notation guide. I would appreciate any feedback and requirements.
Derek, thanks for the issue. Yes, wanted both issues as your issue captures the...
Derek, thanks for the issue. Yes, wanted both issues as your issue captures the requirement you have. The notation guide can be consulted once the macro is installed (I saw your other problem). Yes, the source HTML is in the jar, but much better to look at as part of the Advanced section of the notation guide. I see from your other note, you have got the idea. If you are not familar with the notation guide, edit any page on your site and on the bottom right in the help area you will see a link to Confluence notation guide,
Hi,
Could someone please help me complete the installation of this plugin....
Hi,
Could someone please help me complete the installation of this plugin. So far I have uploaded the sql macro onto the server via the plugin uploader and placed the JDBC Sybase driver (jConnect) in the /common/lib/ directory. I am currently trying to edit the server.xml file but am having trouble what values to change. We are using a Tomcat server, and plan to use a Sybase Datasource.
I've found documentation on Configuring a MySQL Datasource in Tomcat, is there an equivalent documentation for a Sybase Datasource in Tomcat?
In particular I am not sure of the values that are highlighted in red text.
The macro seems not to work under 2.2.1a (mySQL). After upgrading from 2.1.4 wit...
The macro seems not to work under 2.2.1a (mySQL). After upgrading from 2.1.4 with an unchanged server.xml (Logger removed) I get:
sql: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
Are there any hints to get it right ?
I suspect you are using the standalone version of Confluence? If so, 2.2.x uses...
I suspect you are using the standalone version of Confluence? If so, 2.2.x uses Tomcat 5.5 which has changes in data source configuration from previous versions. Check your data source configuration. Here is an example:
from liu_app_log partition (LIU_APP_LOG_20060529)
where
log_timestamp > to_date($fromDate, 'mmddyyyy') and message_level_name = 'ERROR'
order by
log_timestamp DESC
A great macro! However, is there a way to get the output as comma separated ...
A great macro! However, is there a way to get the output as comma separated values or any delimiter for that matter. Was experimenting with creation of a user macro to use the data returned from this but manipulation of the data from tables seems cumbersome.
It could be changed to support that I suppose, but I would like to understand ho...
It could be changed to support that I suppose, but I would like to understand how it would be used. Write up an issue with some details. You can specify table=false and just get the raw data that could included constants (like commas) separating fields.
I am attempting to create [sparklines|http://www.edwardtufte.com/bboard/q-a...
I am attempting to create [sparklines|http://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=0001OR&topic_id=1] using an user macro. For this I need to use the data returned from the SQL query. I tried using table=false but the fields are continuous with nothing to separate them. Is there anything else I need to specify. If the functionality does not exist / cannot be achieved in anyway, I shall raise an issue.
I'm not sure if this helps, but we do a lot of links from SQL queries and found ...
I'm not sure if this helps, but we do a lot of links from SQL queries and found that it is easier to output in HTML format and code the links as HTML. Something like this.
You can separate the fileds with whatever constants you want using silimar SQL t...
You can separate the fileds with whatever constants you want using silimar SQL techniques like in Jeff's comment. If you want more detailed control, you can use a beanshell (java) script that runs SQL and then formats the output - but this is a lot more programming. See the Beanshell Macro.
Thanks. I did use SQL techniques as suggested by Jeff to get the desired result....
Thanks. I did use SQL techniques as suggested by Jeff to get the desired result. Although modifying it for any changes seems very cumbersome. Shall try and experiment with beanshell too. Would there be any SQL example in beanshell?
Thanks again!!
Curious if there is any reason behind why when using the SQL macro within a user...
Curious if there is any reason behind why when using the SQL macro within a user marco to produce an internal link (as in, within confluence) always shows in error in the table. Am I doing something wrong?
I've got a basic query that returns a field for each row using MySQL's concat function to build a wiki markup style link, this works great if the sql macro is used in a normal page context. But when I create a user macro that uses the sql macro within it, the rendering for some reason get's messed up.
This is my user macro, I use this because you cannot use the MAX() function without grouping in a MySQL select statement, and I'm not using new enough MySQL to allow nested selects, so the only way to get SQL run in a reliable serial method is to create the requests into a user macro as they are performed in turn. i.e. run a query to store a variable in mysql session, then use that in the next query. It works well, I just can't get around the linking issue.
#set($globalHelper=$action.getGlobalHelper())
#set( $lastRun = "{sql:dataSource=MyDB|table=false}SELECT @MAXrunDate:= MAX(runDate) FROM tbl_Status{sql}" )
#set($renderedContent=$globalHelper.renderConfluenceMacro($lastRun))
#set( $query = "{sql:dataSource=MyDB|showSql=$paramshowSql|table=true|output=wiki}SELECT host Hostname, CONCAT('[Status+-+',host,']') Details FROM tbl_Status WHERE tbl_Stauts.runDate=@MAXrunDate{sql}" )
#set ($rContent=$globalHelper.renderConfluenceMacro($query))
${rContent}
The hope is to obviously have these link to pages within confluence. Doing it this way they error (the link shows in RED), however if I do anything else, such as make the link a full URL e.g.
[http://sitename/display/spacename/pagename]
they render just fine.
I'm unsure if this is the User Macro getting in the way, or something in the SQL macro not coming through correctly. Anyone have any thoughts on this?
We've just started to make use of the SQL and Charting macros and finding them b...
We've just started to make use of the SQL and Charting macros and finding them both excellent additions to confluence, however we're finding that some of the queries we're running often take some time to run and was wondering if there's anyway of adding cached resultset support to the macro.
For example,
Unknown macro: {sql}
select * from ......
This would greatly speed up the display of subsequent page views.
Another idea I had was, in addition to adhoc queries in the body of the macro, or as an attachment, the use of 'named queries' which could be defined in some form of plugin configuration area, and are executed/refreshed every so often, so that the sql macro could just return pre-cached results which would make things even faster.
Mark, please look at the Cache Macro and see if that meets some of your needs. ...
Mark, please look at the Cache Macro and see if that meets some of your needs. Some significant upgrades are in progress for these macros, so please create new ideas and requests in the issue tracker for the appropriate macro (see the link in the header of the macro page). Specifically, I am interested in your ideas regarding named queries. Perhaps it is a similar concept to a Chart enhancement this is being implemented now: Chart plugin - saving chart image as attachment
Yes, you can write a beanshell macro that does its own SQL and processes the res...
Yes, you can write a beanshell macro that does its own SQL and processes the result set in whatever way you like. We do this occasionally when we need more than a table output. An example is: [Script - beanshell sql example]
Comments (315)
Aug 22, 2005
Jeff Hatfield says:
Wiki markup would be a great thing to have in this macro, I hope it makes it! A...Wiki markup would be a great thing to have in this macro, I hope it makes it!
Another feature I'd like to see is the ability to handle multiple result sets, as when you use a COMPUTE statement. That would make it the one SQL macro that does everything!
Feb 06, 2006
Rickard Hansson says:
Yea!Yea!
Mar 18, 2006
Bob Swift says:
Multiple results sets is tracked by SCRP-63.Multiple results sets is tracked by SCRP-63.
Feb 03, 2008
xia yongjie says:
Hello~~ Is this feature implemented?Why do I always get error notificatoins...Hello~~ Is this feature implemented?Why do I always get error notificatoins when I try to use multiple SQL statements (semi-colon separated).
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; select * from school' at line 1Actually, there are not any syntax error. Just two select statement, like "select * from student; select * from school";
Could you explain any reason?
Feb 03, 2008
Bob Swift says:
Yes, multiple results sets are implemented. You need a sql statement that is va...Yes, multiple results sets are implemented. You need a sql statement that is valid for your database and your database is saying your sql is not valid. I know earlier versions of mySQL did not allow multiple statements on a single request for instance. Postgres allows for semi-colon separated statements. Whatever sql statement you use, make sure it works when submitted through your database admin tool or favorite sql tool.
Aug 30, 2005
Orion says:
The following error is prompted when trying to use the sql macro. Any suggestion...The following error is prompted when trying to use the sql macro. Any suggestions?
"sql: javax.naming.NameNotFoundException: Name jdbc is not bound in this Context"
I have two instances of Confluence running: one with the default database and the other is using Oracle. I have tried to implement the sql macro with both instances without succeeding. Thanks in advance.
Aug 30, 2005
Daniel Ostermeier says:
How are you connecting confluence to the database? ie: are you using a datasourc...How are you connecting confluence to the database? ie: are you using a datasource bound to the application containers JNDI or did you select the direct connection option? In the case of the direct connection option, the internal connection pool and database connections are currently not made available via jndi.
If you have set up confluence via the datasource option, I would suggest double checking the jndi name.
Aug 30, 2005
Bob Swift says:
For instance, if you configured your Confluence data source in server.xml (or el...For instance, if you configured your Confluence data source in server.xml (or elsewhere depending on your server and release)
Then the following works
{sql:jndi=ConfluenceDS} select count(distinct CONTENT.title) as "Confluence page count" from CONTENT {sql}Nov 01, 2005
Mingyi Liu says:
That still doesn't work for me though. I got this error: sql: org.apache.common...That still doesn't work for me though. I got this error:
sql: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory, cause: Io exception: The Network Adapter could not establish the connection
Do you know why Confluence is connected with our Oracle but the sql macro failed to? Thanks!
Nov 02, 2005
Daniel Ostermeier says:
Are you using the exact same resource being used by Confluence? If you are usin...Are you using the exact same resource being used by Confluence?
If you are using the same resources, then there should be no reason that Confluence has access and the SQLMacro does not.
Nov 03, 2005
Mingyi Liu says:
In my conf/server.xml, I added <Resource name="jdbc/confluence" auth="Contain...In my conf/server.xml, I added <Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"/> and it's an oracle DS. Confluence was running fine. But using {sql:jndi=confluence} gave me the error I listed above.
Nov 03, 2005
Bob Swift says:
And is confluence the same resource being used by Confluence or did you add this...And is confluence the same resource being used by Confluence or did you add this only for the sql macro? I suggest you clip out all your data source specifications in server.xml and attach it here so we know exactly what it looks like.
Nov 03, 2005
Mingyi Liu says:
Here's the data source definition <Resource name="jdbc/confluence" auth="Co...Here's the data source definition
<Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"/> <ResourceParams name="jdbc/confluence"> <parameter> <name>factory</name> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value> </parameter> <parameter> <name>maxActive</name> <value>20</value> </parameter> <parameter> <name>maxIdle</name> <value>10</value> </parameter> <parameter> <name>maxWait</name> <value>10000</value> </parameter> <parameter> <name>username</name> <value>myname</value> </parameter> <parameter> <name>password</name> <value>mypass</value> </parameter> <parameter> <name>driverClassName</name> <value>oracle.jdbc.driver.OracleDriver</value> </parameter> <parameter> <name>url</name> <value>jdbc:oracle:thin:@myserver:1521:mydb</value> </parameter> <parameter> <name>connectionProperties</name> <value>SetBigStringTryClob=true</value> </parameter> </ResourceParams> </Context>I also searched and made sure under my confluence installation directory this file is the only one that has my oracle data source definitions. I've restarted server a couple times since moving to Oracle and Confluence was running perfectly on it.
Nov 03, 2005
Bob Swift says:
Nothing obviously wrong but I am not familar with Oracle. Perhaps Daniel c...Nothing obviously wrong but I am not familar with Oracle. Perhaps Daniel can help some more.
Nov 03, 2005
Mingyi Liu says:
OK. I was mostly looking to use your suggestion that retrieves some stats ...OK. I was mostly looking to use your suggestion that retrieves some stats about confluence usage, but I guess I'll just directly use a script to do it if I need to. One thing missing from your stats sqls is the page access stats, but I guess that's not stored in the DB, so we could only analyze server logs for those stats, right?
Nov 03, 2005
Bob Swift says:
See Tracking Plugin for page access tracking.See Tracking Plugin for page access tracking.
Nov 03, 2005
Daniel Ostermeier says:
All looks okay... Just to verify that Confluence is using this exact same datas...All looks okay...
Just to verify that Confluence is using this exact same datasource, can you please have a look at the CONFLUENCE_HOME/confluence.cfg.xml file, and tell me the value of the "hibernate.connection.datasource" property.
Nov 04, 2005
Mingyi Liu says:
hmm, that might be the problem. In the cfg.xml, there are lots of hibernat...hmm, that might be the problem. In the cfg.xml, there are lots of hibernate parameters, but none called hibernate.connection.datasource. Instead, there are hibernate.connection.username/password/url. So somehow my installation must be using direct connection then. Should I deleted these lines and replace with the datasource line?
Nov 06, 2005
Daniel Ostermeier says:
If you want confluence to work with a datasource, then you will need to change t...If you want confluence to work with a datasource, then you will need to change the cfg to contain the following (Oracle 9 example):
<property name="hibernate.connection.datasource">java:comp/env/jdbc/confluence</property>
<property name="hibernate.dialect">net.sf.hibernate.dialect.Oracle9Dialect</property>
<property name="hibernate.setup">true</property>
However, I would strongly recommend that you get the SQL macro working first. If the SQL macro is not able to use the datasource that you have configured within your app server, then Confluence will not be able to do so either.
Cheers,
-Daniel
Nov 06, 2005
prash b says:
Can I use this Macro to connect to MS Access DB?? If not, can you please direct ...Can I use this Macro to connect to MS Access DB?? If not, can you please direct me to some documentation that helps me do that....
thanks
Nov 06, 2005
David Loeng says:
You cannot currently setup Confluence against aMS Access database. Since this ma...You cannot currently setup Confluence against aMS Access database. Since this macro uses Confluence's database connection, you will not be able to use this macro with a MS Access db.
Cheers,
Dave
Nov 06, 2005
Bob Swift says:
Just to make sure there is no confusion, the macro can use any data source that ...Just to make sure there is no confusion, the macro can use any data source that is configured in the app server, not just the confluence data source. For instance, I have about 10 data sources configurated from multiple systems. That said, I don't know if MS Access can be configured as a data source.
Jan 18, 2006
Christian Barthelemy says:
Certainly a rookie's question but I cannot figure out how to configure the syste...Certainly a rookie's question but I cannot figure out how to configure the system to use a datasource connection:
Thanks for any help
Jan 18, 2006
Bob Swift says:
It does not matter how you installed Confluence. However, for any database that...It does not matter how you installed Confluence. However, for any database that you want to access, there needs to be a data source defined in your application server. So for instance, I have about 20 data sources defined accessing different databases. If you want access to the database Confluence uses, then, you will have to define a data source to that database and if you do that it is natural to also use that data source for Confluence. I will add some further data source configuration information on this page.
Jan 19, 2006
Christian Barthelemy says:
Thank you Bob, I am definitely interested to learn how to define a data source. ...Thank you Bob, I am definitely interested to learn how to define a data source.
I tried to google "mysql define data source": do you think that this URL provides relevant information?
Thank you
Jan 19, 2006
Bob Swift says:
Yes, the server.xml looks like a good example if you are using Tomcat. See also...Yes, the server.xml looks like a good example if you are using Tomcat. See also the attachment and paragraph I added to this page.
Jan 19, 2006
Tom Davies says:
Be aware that there are differences in the way datasources are defined between T...Be aware that there are differences in the way datasources are defined between Tomcat 4, 5.0 and 5.5.
Jan 19, 2006
Bob Swift says:
Yes, good warning. The server.xml is for the Tomcat that ships with the standal...Yes, good warning. The server.xml is for the Tomcat that ships with the standalone version of Confluence - Tomcat 4. I have examples for Tomcat 5.5 as well if someone needs that.
Jan 20, 2006
Christian Barthelemy says:
Thank you Bob and Tom, I have a standalone 2.1.1, so I added the lines in serve...Thank you Bob and Tom,
I have a standalone 2.1.1, so I added the lines in server.xml as indicated above.
After restarting my server I get the following error message when trying to sql the confluence database:
sql: Unexpected program error: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.mysql.jdbc.Driver', cause: com.mysql.jdbc.Driver select count(distinct CONTENT.title) as "Confluence page count" from CONTENTI am using the driver mysql-connector-java-3.0.17-ga-bin.jar as recommended.
Any idea what could be wrong?
Jan 20, 2006
Christian Barthelemy says:
(on)Ooops! It is working now! Sorry for the previous post: my driver wa...(on)Ooops!
It is working now! Sorry for the previous post: my driver wasn't located in the common/lib but in confluence\WEB-INF\lib.
Thank you again for your help
Mar 03, 2006
Kevin James says:
How do you configure multiple data sources in the servers.xml? Is that don...How do you configure multiple data sources in the servers.xml? Is that done by providing multiple <ResourceParam> elements? I'd like use this macro to access our JIRA database for displaying in Confluence pages. Is this possible?
Mar 03, 2006
Bob Swift says:
Yes, I have over 20 defined in one of my installations. You need the <Resou...Yes, I have over 20 defined in one of my installations. You need the
<Resource name=... and ResourceParams name=... as many times as you have different data sources.
Feb 19, 2006
Dan Hardiker says:
Have you set your MySQL server to operate on port 8080? The standard port for My...Have you set your MySQL server to operate on port 8080? The standard port for MySQL is 3306.
Change 8080 in the resource url to 3306 and give that a go.
Feb 19, 2006
Bob Swift says:
And you are missing the factory parameter compared to the example I use. <pa...And you are missing the factory parameter compared to the example I use.
Feb 19, 2006
Bob Swift says:
Ok, you will have to be more specific. What application server/version are you ...Ok, you will have to be more specific. What application server/version are you using? Or are you using standalone version of Confluence or Jira? If so, what version. Attach your server.xml.
Feb 19, 2006
Bob Swift says:
I get the same error if I try to use your method to configure the datasource: &...I get the same error if I try to use your method to configure the datasource:
I don't think this is valid format for this version of Tomcat. Use the format in the example server.xml and it should work:
<ResourceParams name="jdbc/TestDB"> <parameter> <name>factory</name> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value> </parameter> ... </ResourceParams>Feb 19, 2006
Bob Swift says:
Of course you need this part first: <Resource name="jdbc/TestDB" auth="Conta...Of course you need this part first:
Feb 20, 2006
Bob Swift says:
Jira 3.5 standalon Tomcat is a higher release level (5.5) and so excepts better ...Jira 3.5 standalon Tomcat is a higher release level (5.5) and so excepts better stuff. I wish Confluence and Jira supported the same level, it would make things less confusing.
Feb 20, 2006
Tom Davies says:
Confluence 2.2 will use Tomcat 5.5Confluence 2.2 will use Tomcat 5.5
Feb 20, 2006
Bob Swift says:
Apr 11, 2006
Derek Stevenson says:
Question regarding the use of parameters... I would like to use the SQL Macro o...Question regarding the use of parameters...
I would like to use the SQL Macro on one wiki page to generate links to another wiki page, and pass parameters to that second wiki page for expansion in a second SQL Macro. For example:
The fourth column would then link to another wiki page, where the employeeID is automagically expanded into one of the parameter fields in the sql macro definition of that page:
{sql:dataSource=TestDS|p1=(employeeID here)|showsql=true} select * from personnelrecord where employeeID = ? {sql}Which in turn would show something like
Sort of a summary and subsequent drill-down, if you will. I've accomplished the creation of a link in the first table by mangling the query to return wiki markup, but I haven't figured out the passing of parameters.
Is this remotely feasible or am I trying to use this in an unintended fashion? If this isn't possible, is there an alternative wiki plugin I could consider? I've briefly looked at the Jasper Report Macro and while it looks more complicated, perhaps that's the way to go.
Thanks,
Derek
Apr 11, 2006
Bob Swift says:
Derek, I have similar problems and I have another plugin in the works that will ...Derek, I have similar problems and I have another plugin in the works that will help with this type of problem. Unfortunately, it was not quite ready for the 2.3 release. Can you please capture your request and open a issue so it can be tracked and discussed. Thanks. The technique of using the query to create wiki markup for more complex things is valid - we use this in a number of places.
Apr 12, 2006
Bob Swift says:
Derek, you can experiment with the plugin attached to http://developer.atlassian...Derek, you can experiment with the plugin attached to http://developer.atlassian.com/jira/browse/SCRP-70. It is work in progress and you will need to figure it out from the documentation in the notation guide. I would appreciate any feedback and requirements.
Apr 12, 2006
Derek Stevenson says:
Per your request I opened a ticket to track the feature -- http://develo...Per your request I opened a ticket to track the feature -- http://developer.atlassian.com/jira/browse/SCRP-71
Don't know if this duplicates http://developer.atlassian.com/jira/browse/SCRP-70 or the two should be linked, I'll let you decide.
I'll work with our wiki admin to eval the run macro and will let you know when I've had a chance to look at it.
Apr 12, 2006
Derek Stevenson says:
Is the notation guide in the jar itself? runmacro-help.vm perhaps?Is the notation guide in the jar itself? runmacro-help.vm perhaps?
Apr 12, 2006
Bob Swift says:
Derek, thanks for the issue. Yes, wanted both issues as your issue captures the...Derek, thanks for the issue. Yes, wanted both issues as your issue captures the requirement you have. The notation guide can be consulted once the macro is installed (I saw your other problem). Yes, the source HTML is in the jar, but much better to look at as part of the Advanced section of the notation guide. I see from your other note, you have got the idea. If you are not familar with the notation guide, edit any page on your site and on the bottom right in the help area you will see a link to Confluence notation guide,
Apr 17, 2006
Jerome Jr Villalon says:
Hi, Could someone please help me complete the installation of this plugin....Hi,
Could someone please help me complete the installation of this plugin. So far I have uploaded the sql macro onto the server via the plugin uploader and placed the JDBC Sybase driver (jConnect) in the /common/lib/ directory. I am currently trying to edit the server.xml file but am having trouble what values to change. We are using a Tomcat server, and plan to use a Sybase Datasource.
I've found documentation on Configuring a MySQL Datasource in Tomcat, is there an equivalent documentation for a Sybase Datasource in Tomcat?
In particular I am not sure of the values that are highlighted in red text.
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://-------:8808/test?autoReconnect=true</value>
</parameter>
Also, is there any additional information I should know?
Apr 17, 2006
Jerome Jr Villalon says:
Sorry, forgot to highlight another part of the script. <parameter> <n...Sorry, forgot to highlight another part of the script.
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://-------:8808/test?autoReconnect=true</value>
</parameter>
Apr 17, 2006
Bob Swift says:
Maybe http://www.mail-archive.com/commons-user@jakarta.apache.org/msg14957.html ...Maybe http://www.mail-archive.com/commons-user@jakarta.apache.org/msg14957.html will help? Otherwise try google tomcat sybase datasource.
Apr 20, 2006
Jerome Jr Villalon says:
Thanks got it working! That message was useful. Heres what I did, it might help ...Thanks got it working! That message was useful. Heres what I did, it might help others.
<name>driverClassName</name>
<value>com.sybase.jdbc3.jdbc.SybDriver</value>
...
<name>url</name>
<value>jdbc:sybase:Tds:servername:port?autoReconnect=true</value>
Apr 25, 2006
Bob Swift says:
Open an issue including the html that was generated for the page around where it...Open an issue including the html that was generated for the page around where it was messed up. Thanks.
May 05, 2006
Jeff Hatfield says:
Has anyone figured out a way to prompt a user for the parameter to feed to the S...Has anyone figured out a way to prompt a user for the parameter to feed to the SQL macro?
May 05, 2006
Bob Swift says:
Yes, there are some early efforts in this area. See the earlier posts.Yes, there are some early efforts in this area. See the earlier posts.
May 20, 2006
Rainer Bugow says:
The macro seems not to work under 2.2.1a (mySQL). After upgrading from 2.1.4 wit...The macro seems not to work under 2.2.1a (mySQL). After upgrading from 2.1.4 with an unchanged server.xml (Logger removed) I get:
sql: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
Are there any hints to get it right ?
May 20, 2006
Bob Swift says:
I suspect you are using the standalone version of Confluence? If so, 2.2.x uses...I suspect you are using the standalone version of Confluence? If so, 2.2.x uses Tomcat 5.5 which has changes in data source configuration from previous versions. Check your data source configuration. Here is an example:
<Resource name="jdbc/ConfluenceDS" auth="Container" type="javax.sql.DataSource" username="confluence" password="xxxxxxx" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://myserver:3306/mydatabase?autoReconnect=true" />May 21, 2006
Rainer Bugow says:
Thanks, got it working and attached my server.xml.Thanks, got it working and attached my server.xml.
Jun 08, 2006
patrice saint-louis says:
If I have a query (in Oracle) such as the where clause is like below: where &nb...If I have a query (in Oracle) such as the where clause is like below:
where log_timestamp > to_date('5/23/2006', 'mm/dd/yyyy')
I tried to replace the Date ('5/23/2006') with a parameter $fromdate
so that it looks like where log_timestamp > to_date($fromdate, 'mm/dd/yyyy')
It doesn't seem to work. I get the following error:
====
sql: java.sql.SQLException: ORA-00911: invalid character
select exception_classname, exception_message, logger_name, message_level_name, user_session_id, log_timestamp, user_agent
from liu_app_log partition (LIU_APP_LOG_20060529)
where
log_timestamp > to_date($fromDate, 'mmddyyyy') and message_level_name = 'ERROR'
order by
log_timestamp DESC
=====
Jun 08, 2006
Derek Stevenson says:
Make sure whatever you're providingin $fromDate matches the date mask you're p...Hope one or both of these helps.
Jun 24, 2006
Vaishnavi says:
A great macro! However, is there a way to get the output as comma separated ...A great macro! However, is there a way to get the output as comma separated values or any delimiter for that matter. Was experimenting with creation of a user macro to use the data returned from this but manipulation of the data from tables seems cumbersome.
Thanks in advance!
Jun 24, 2006
Bob Swift says:
It could be changed to support that I suppose, but I would like to understand ho...It could be changed to support that I suppose, but I would like to understand how it would be used. Write up an issue with some details. You can specify table=false and just get the raw data that could included constants (like commas) separating fields.
Jun 26, 2006
Vaishnavi says:
I am attempting to create [sparklines|http://www.edwardtufte.com/bboard/q-a...I am attempting to create [sparklines|http://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=0001OR&topic_id=1] using an user macro. For this I need to use the data returned from the SQL query. I tried using table=false but the fields are continuous with nothing to separate them. Is there anything else I need to specify. If the functionality does not exist / cannot be achieved in anyway, I shall raise an issue.
Thanks again!
Jun 26, 2006
Jeff Hatfield says:
I'm not sure if this helps, but we do a lot of links from SQL queries and found ...I'm not sure if this helps, but we do a lot of links from SQL queries and found that it is easier to output in HTML format and code the links as HTML. Something like this.
... '<a href="' + 'https://somedomain.com/CAisd/pdmweb1.exe?OP=SEARCH+FACTORY=cr+SKIPLIST=1+QBE.EQ.id=' + rtrim(Convert(Char,id)) + '" target="new">' + rtrim(Convert(Char,ref_num))+ '</a>'Jun 26, 2006
Bob Swift says:
You can separate the fileds with whatever constants you want using silimar SQL t...You can separate the fileds with whatever constants you want using silimar SQL techniques like in Jeff's comment. If you want more detailed control, you can use a beanshell (java) script that runs SQL and then formats the output - but this is a lot more programming. See the Beanshell Macro.
Jun 27, 2006
Vaishnavi says:
Thanks. I did use SQL techniques as suggested by Jeff to get the desired result....Thanks. I did use SQL techniques as suggested by Jeff to get the desired result. Although modifying it for any changes seems very cumbersome. Shall try and experiment with beanshell too. Would there be any SQL example in beanshell?
Thanks again!!
Nov 14, 2006
Andrew Whyte says:
Curious if there is any reason behind why when using the SQL macro within a user...Curious if there is any reason behind why when using the SQL macro within a user marco to produce an internal link (as in, within confluence) always shows in error in the table. Am I doing something wrong?
I've got a basic query that returns a field for each row using MySQL's concat function to build a wiki markup style link, this works great if the sql macro is used in a normal page context. But when I create a user macro that uses the sql macro within it, the rendering for some reason get's messed up.
This is my user macro, I use this because you cannot use the MAX() function without grouping in a MySQL select statement, and I'm not using new enough MySQL to allow nested selects, so the only way to get SQL run in a reliable serial method is to create the requests into a user macro as they are performed in turn. i.e. run a query to store a variable in mysql session, then use that in the next query. It works well, I just can't get around the linking issue.
#set($globalHelper=$action.getGlobalHelper()) #set( $lastRun = "{sql:dataSource=MyDB|table=false}SELECT @MAXrunDate:= MAX(runDate) FROM tbl_Status{sql}" ) #set($renderedContent=$globalHelper.renderConfluenceMacro($lastRun)) #set( $query = "{sql:dataSource=MyDB|showSql=$paramshowSql|table=true|output=wiki}SELECT host Hostname, CONCAT('[Status+-+',host,']') Details FROM tbl_Status WHERE tbl_Stauts.runDate=@MAXrunDate{sql}" ) #set ($rContent=$globalHelper.renderConfluenceMacro($query)) ${rContent}The hope is to obviously have these link to pages within confluence. Doing it this way they error (the link shows in RED), however if I do anything else, such as make the link a full URL e.g.
they render just fine.
I'm unsure if this is the User Macro getting in the way, or something in the SQL macro not coming through correctly. Anyone have any thoughts on this?
Nov 29, 2006
Mark Derricutt says:
We've just started to make use of the SQL and Charting macros and finding them b...We've just started to make use of the SQL and Charting macros and finding them both excellent additions to confluence, however we're finding that some of the queries we're running often take some time to run and was wondering if there's anyway of adding cached resultset support to the macro.
For example,
select * from ......
This would greatly speed up the display of subsequent page views.
Another idea I had was, in addition to adhoc queries in the body of the macro, or as an attachment, the use of 'named queries' which could be defined in some form of plugin configuration area, and are executed/refreshed every so often, so that the sql macro could just return pre-cached results which would make things even faster.
Thanks for the great macros...
Nov 29, 2006
Bob Swift says:
Mark, please look at the Cache Macro and see if that meets some of your needs. ...Mark, please look at the Cache Macro and see if that meets some of your needs. Some significant upgrades are in progress for these macros, so please create new ideas and requests in the issue tracker for the appropriate macro (see the link in the header of the macro page). Specifically, I am interested in your ideas regarding named queries. Perhaps it is a similar concept to a Chart enhancement this is being implemented now: Chart plugin - saving chart image as attachment
Nov 30, 2006
apolo says:
can I process result of sql query by using beanshell macro?can I process result of sql query by using beanshell macro?
Nov 30, 2006
Bob Swift says:
Yes, you can write a beanshell macro that does its own SQL and processes the res...Yes, you can write a beanshell macro that does its own SQL and processes the result set in whatever way you like. We do this occasionally when we need more than a table output. An example is: [Script - beanshell sql example]
Dec 14, 2006
Jeff Guthrie says:
Can someone add a sample of connecting to a normal SQL database above? (where t...Can someone add a sample of