Description | Security | Macros | Documentation | Data source setup | Usage | Release history | New issue tracking JIRA project
|
|
Additions and corrections to above
DescriptionDisplays 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
SecurityThis macro and the datasource parameter can be restricted to trusted user by configuring security using the Macro Security Plugin.
|
Macros
- sql - supports most SQL statements
- sql-query - support read-only SQL statements
- sql-query1 - duplicate of sql-query for recursive use
- sql-query2 - duplicate of sql-query for recursive use
Documentation
Parameters
In addition to the parameters listed below, this macro supports Common table capabilities.
- 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.
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
<Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/confluence?autoReconnect=true" username="username" password="password" maxActive="25" maxIdle="5" maxWait="10000" />
Oracle DataSource Example - Remote Confluence DB, Thin Drivers
<Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@www.some_server.com:1521:some_schema" username="username" password="password" connectionProperties="SetBigStringTryClob=true" maxActive="25" maxIdle="5" maxWait="10000" />
Generic Template
<Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource" driverClassName="com.some_driver_path" url="jdbc:some_database://some_server/some_path" username="username" password="password" maxActive="25" maxIdle="5" maxWait="10000" />
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}
Release history
| Version | Date | State | License | Price |
3.4.0
(#8)
|
25 Apr 2008 | Stable | Freeware / Open Source (BSD) | Free |
|
3.3.0
(#7)
|
22 Jun 2007 | Stable | Freeware / Open Source (BSD) | Free |
|
3.2.2
(#6)
|
13 May 2007 | Stable | Freeware / Open Source (BSD) | Free |
|
3.2.1
(#5)
|
25 Mar 2007 | Stable | Freeware / Open Source (BSD) | Free |
|
3.2
(#4)
|
04 Mar 2007 | Stable | Freeware / Open Source (BSD) | Free |
|
3.1
(#3)
|
08 Jan 2007 | Stable | Freeware / Open Source (BSD) | Free |
|
3.0.1
(#2)
|
26 Sep 2006 | Stable | Freeware / Open Source (BSD) | Free |
|
3.0
(#1)
|
24 Sep 2006 | Prerelease | Freeware / Open Source (BSD) | Free |
Closed
Resolved
- Previous versions were included in Scripting and External Content Macros
Open
In Progress
Comments (235)
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!