SQL Plugin

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!

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.

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

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.

Thanks for any help

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.

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

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.

Also, is there any additional information I should know?

Open an issue including the html that was generated for the page around where it was messed up. Thanks.

Has anyone figured out a way to prompt a user for the parameter to feed to the SQL macro?

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 ?

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

===== 

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!  

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 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,

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

can I process result of sql query by using beanshell macro?

Can someone add a sample of connecting to a normal SQL database above? (where the mysql and oracle datasources are documented) Thanks!

Jeff, hopefully someone else can post a simple example. In the meantime, you can look at Database Setup for SQL Server and specifically point 3 and the referenced faq to get the right URL parameters.

Has anyone managed to get this to work in JBoss?

We have confluence v2.3 running just fine in JBoss. We have set up our confluence datasource as follows:

<?xml version="1.0" encoding="UTF-8"?>

<!-- $Id$ -->
<!--  Datasource config for Pics 
-->

<datasources>
  <local-tx-datasource>
    <jndi-name>ConfluenceDS</jndi-name>
    <connection-url>jdbc:db2://auk.dolby.net:50000/CONF</connection-url>
    <driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
    <user-name>conf</user-name>
    <password>wel0veconf</password>

    <!-- sql to call when connection is created
    <new-connection-sql>some arbitrary sql</new-connection-sql>
    -->

    <!-- sql to call on an existing pooled connection when it is obtained from pool 
    <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
    -->

  </local-tx-datasource>
</datasources>

Every time we try and use the sql macro, the datasource cannot be found. I read above that this macro adds java:comp/env to the datasource name. Why is this.

Any help would be appreciated.

In the previous example, the solution is to prefix the jndi-name 'ConfluenceDS' with java: so the sql-plugin becomes:

It works fine in jboss405/confluence 2.3:- 

Just upgraded our installation from 2.1.5 to 2.3.1.  Installed the latest version of SQL Plugin through the repository.  I'm getting this error message:

Any ideas?

This is sort of a repeat question, but I couldn't see it clearly answered, or defined above. So in an effort to get more info I'll ask again.

It's currently possible to make use of the SQL macro within your own user macro, but simply using the content redering function, such as:

#set( $query = "{sql:dataSource=MyDB|showSql=false|table=true|output=wiki}SELECT * FROM tbl_Status WHERE tbl_Stauts.runDate=NOW(){sql}" )
#set ($rContent=$globalHelper.renderConfluenceMacro($query))

This lets you get at data in a table, but I'd like to know if the SQL macro provides a better method similar to the $content, $action, $req devices in the user macro space.

e.g. I'd love to be able to do something like this:

#set($user=$action.remoteUser)
#set($dbh = $sql.setConnector("MyDBConn"))
#set($DoB = $dbh.executeQuery("select date_of_birth from tbl_Users where userid='$user.name'"))

<div> Your Date of Birth is: $DoB </div>

This would really allow the power of the sql interface within confluence to be realised without going to the effort of a full blown macro. Something which is quite daunting for non-programmers I feel.

So, is this already possible and I just need to learn the syntax? Or is it something that would be possible to add?

Cheers.

I have a odbc Datasource, with MS Access. Confluence succeeds in connecting to this datasource, but never shows something in the resultset (there is no failure, just an empty resultset, ok this is a failure ).

I made a System-DSN which i registered with my MS Access-DB, the ressource-name i use in the server.xml:

First example:

<Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
				     username="test"
				     password="test"
				     driverClassName="sun.jdbc.odbc.JdbcOdbcDriver"
				     url="jdbc:odbc:myDSN"
			 validationQuery="Select 1" />

The following is the same result with the RmiJdbs-Connector from ObjectWeb

Second example:

<Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
				     username="test"
				     password="test"
				     driverClassName="org.objectweb.rmijdbc.Driver"
				     url="jdbc:rmi://localhost/jdbc:odbc:myDSN"
			 validationQuery="Select 1" />

When i use the SQL-Macro it gives me an error (like Table not found if i use a none existent Table, which i would say is a good indicator that the connection is there) if i make a wrong SQL-Query, but gaves me a empty-resultset when i make a select statement on a existing table like:

{sql:dataSource=myDB}select * from tab_test{sql}

I am also trying to use this in 2.3.1 and I would love to see this functionality added

 I hope we can get this issue resolved soon!

Would it be possible for the space admin to go through and add some of the knowledge in these comments to the content of the page and organize it a bit? There is lots of good stuff in here but it takes ages to read through all the comments. Just a thought...

Can someone post an example showing how to change the column labels with output=wiki?

Is it possible to create a table where  the 1st sql statement provides the values for the  rows in the first column, and and those values become a variable for the sql statement that produces the results for the 2nd column?

Thanks,

Shon

Hi

As part of my testing I ran simple select command to return huge set of row in access of 120000 , it did not work once it came back with nothing and next time it crashed my confluence,

Having browsed through I came across maxRows property with I set as maxRows="25"( can be anything ) in the data source configuration and re-ran the query but no luck

Does anyone have any idea as to why this is not working or otherwise.

Also

do we any examples on how to use this option - #filename - Data is read from the file located in confluence home directory/script/filename. Subdirectories can be specified

Hi Bob

What do i need to do to have this put in as a issue

I am trying to create a link to one of the columns in the resultset. But, it's returning an error. The column is mixed character. Any ideas?

The error is: sql-query: java.sql.SQLException: ORA-01722: invalid number

Part of the query is this...
select
techdate
, techname
, '<a href="www.companya.com&field=' + to_char(incident_number) + '">' + to_char(incident_number) + '</a>'
,...

When you look at the code again the query looks like this (the links are empty)...
select
techdate
, techname
, '' + to_char(incident_number) + ''
,...

Thanks for your help

Is it possible to add a CSV output type?  One of the guys in the office was asking about having support for that - not entirely sure what he was wanting it for thou..

Hi Bob,

I'm now looking at SQL scriptswhich I had running fine, but insecurely, Im trying to enable macro security but get a:

java.lang.NoSuchMethodError: com.atlassian.confluence.core.ContentEntityObject.getContentPermission

after setting  edit permissions on the page to be the group identified in the macro-security.properties file in confluence folder - and restarted appserver.  Is macro security working for SQL commands? is there an incompatibility with SQL and Script plugins?  Did I miss something?

(confluence =2.5.2, SQL plugin = 3.2.2, script plugin = 3.1.1-NG)

TTFN, Andy.

It appears the wrapping {table-plus} tags around this plugin with wiki output doesn't create a table-plus table. Is that a feature or a fault?

Thanks Bob - that's what I thought but with:

{sql:dataSource=blah|output=wiki|script=^attachment.sql|enableSorting=true}{sql}

...the table generated doesn't allow me to sort by different columns when I click on the headings.

Please update documentation on oracle, should say

url="jdbc:oracle:thin:@www.some_server.com:1521:ORACLE_SID"

not

jdbc:"oracle:thin:@www.some_server.com:1521:some_schema"

Also, we had to add

factory="org.objectweb.jndi.DataSourceFactory"

I have no idea what it does though, our tomcat 'expert' added it ,but w/o it the connection wouldn't work to Oracle

Is it possiable to assign the result of a query to a velocity variables for use in a page or user macro?

SQL like this:

select count(*)
from NOTIFICATIONS
where PAGEID = 1503

Variables:

$column1Name, $column1row1value, etc...

Which would give me access the number of users watching a page.

Has anyone got the Instructions above to work using Tomcat 5.0.19 with Confluence 2.5.3 and MySQL 4.1 ?

I spent nearly 2 hours trying to make it work, getting 'null' errors like mentioned in previous postings.   I tried adding factory="org.apache.commons.dbcp.BasicDataSourceFactory" which isn't mentioned in the instructions, but in one of the follow-ups to the first 'null'.  Still didn't work.

In desperation I copied the server.xml file  http://confluence.atlassian.com/download/attachments/128258/server.xml?version=1&nbsp; and copy & pasted my config into that different layout & it worked.

Here is my config in the hope of saving other people the frustration I went through

<Context path="/confluence" docBase="/usr/src/confluence-2.5.3/confluence" debug="0" reloadable="true">
<Resource name="jdbc/rt3" auth="Container" type="javax.sql.DataSource"/>
                    <ResourceParams name="jdbc/rt3">
                        <parameter>
                            <name>driverClassName</name>
                            <value>com.mysql.jdbc.Driver</value>
                        </parameter>
                        <parameter>
                            <name>url</name>
                            <value>jdbc:mysql://nyc1fdrt02.nyc1.freshdirect.com:3306/rt3?autoReconnect=true</value>
                        </parameter>
                        <parameter>
                            <name>username</name>
                            <value>my_login_here</value>
                        </parameter>
                        <parameter>
                            <name>password</name>
                            <value>xxxxx</value>
                        </parameter>
                        <parameter>
                            <name>factory</name>
                            <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
                        </parameter>
                   </ResourceParams>
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="atlassian-confluence." suffix=".log" timestamp="true"/>
</Context>
 

 

Dear Bob, we have tried this and have been able to sucessfully get it to work alongside the Chart plugin too.
Thanks for developing it.

I was wondering if there is a list of each SQL action that your plugin supports. Eg, does it support INSERT statements, or only read-only actions?

kind regards,
Paul
(edit: thanks for the info Bob)

Now have Oracle dbase and this is not working. Message displaying:

sql: javax.naming.NamingException: Could not create resource factory, ClassNotFoundException:org.objectweb.jndi.DataSourceFactory

I'm using the default settings for Oracle setup as outlined, with the addition of the datasourcefactory line. It has failed with the same message both with and without the datasourcefactory line.

Here's my code from server.xml:

<Resource
name="jdbc/confluencedb"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@serverIP:blah:blah"
username="theUsername"
password="thePassword"
factory="org.objectweb.jndi.DataSourceFactory"
connectionProperties="SetBigStringTryClob=true"
maxActive="25"
maxIdle="5"
maxWait="10000"
/>

My C:\confluence\confluence-2.5.4-std\confluence\WEB-INF\web.xml:

<resource-ref>
        <description>Connection Pool</description>
        <res-ref-name>jdbc/confluencedb</res-ref-name>
        <res-type>javax.sql.Datasource</res-type>
        <res-auth>Container</res-auth>
</resource-ref>

</web-app>

I'm using the same code for attempting to display content as I have referenced in my previous message when I was having trouble with MySQL.

Another user had the same error I had, but I don't understand the fix he did, which seemed rather vague.

Thanks,
Kirk

I'm getting a
  sql: Unexpected program error: java.sql.SQLException: Column '' not found.
whenever I use an aggregate or scalar function on a column (e.g. count, sum, length), like
  select count(id) from my_table
I'm using MySQL and have tried combinations of fully qualified names and aliases with no effect. Any ideas what could be going on?

I'm having a problem with a proprietary JDBC driver to a middle tier server. The datasource that I have defined seems to work OK from a beanshell query but not from the sql or sql-query macros. The following is the result from sql-query, sql, and beanshell run:

sql-query: java.sql.SQLException: The method Connection.setReadOnly is not implemented yet 
select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'

sql: java.sql.SQLException: The method Connection.setReadOnly is not implemented yet 
select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'

Isolation set: 0 
Columns in result set: 2 
  6081740  EA2 

The following is the code (beanshell code is modified copy from the scripts plugin page):

{sql-query:dataSource=PhsDev|output=wiki|showsql=true}
select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'
{sql-query}

{sql:dataSource=PhsDev|output=wiki|showsql=true|transactionIsolation=none}
select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'
{sql}


{beanshell}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.naming.NameNotFoundException;

    String dataSource = "PhsDev";
    String sql = "select Fld1, Fld2 FROM Table WHERE Fld1 like '608174%'";


    DataSource ds = null;
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet;

    try {
        ds = (DataSource) (new InitialContext()).lookup("java:comp/env/jdbc/" + dataSource);
        connection = ds.getConnection();
int isolation = connection.getTransactionIsolation();
 out.println("<br>Isolation  set: " + isolation);
              
        statement = connection.prepareStatement(sql);

        if (statement.execute()) {  // result set is available 
            resultSet = statement.getResultSet();
            if (resultSet != null) {
                ResultSetMetaData rsmd = resultSet.getMetaData();
                out.println("<br>Columns in result set: " + rsmd.getColumnCount());
                
                // out.println("<br>Space names: ");
                while (resultSet.next()) {
                    out.println("<br>&nbsp;&nbsp;" + resultSet.getString("Fld1") +
                                "&nbsp;&nbsp;" + resultSet.getString("Fld2"));  //"spacename"));
                }
            }    
        } else { // only update count available
            out.println("Update count: " + statement.getUpdateCount()); 
        }
    }
    catch (NameNotFoundException exception) {
        out.println(exception.toString());
    } 
    catch (NamingException exception) { 
        out.println(exception.toString());
    } 
    catch (SQLException exception) { 
        out.println(exception.toString());
    }
    finally {
	try {
	    if (resultSet != null) {
		resultSet.close();
	    }
	    if (connection != null) {
		connection.close();
            }
        }
	catch(Exception discard) {
        }
    }
{beanshell}

The following is the datasource definition:

  <Resource name="jdbc/PhsDev" auth="Container" type="javax.sql.DataSource"/>
    <ResourceParams name="jdbc/PhsDev">
    <parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
    <parameter><name>driverClassName</name><value>com.phs.client.jdbc.PhsJDBCDriver</value></parameter>
    <parameter>
       <name>url</name>
       <value>jdbc:scwapi://host/NameServer;uid;pswd;db;schema;uid2;pswd2</value>
    </parameter>
    <parameter><name>maxActive</name><value>20</value></parameter>
    <parameter><name>maxIdle</name><value>10</value></parameter>
    <parameter><name>maxWait</name><value>-1</value></parameter>
    <parameter><name>removeAbandoned</name><value>true</value></parameter>
    <parameter><name>removeAbandonedTimeout</name><value>600</value> </parameter>
    <parameter><name>logAbandoned</name><value>true</value> </parameter>
  </ResourceParams>

Versions used: Confluence Version: 2.6.0 Build:#913 Sep 27, 2007 on Tomcat 5.0.25

Also tried the transactionIsolation switches without success. Any ideas would be appreciated.
Thanks.

Is there any way to reference a specific parameter by number rather than just '?'. As it is I am having to repeat the same value many times to get the values plugged in to the right place in my queries.

Hi Bob,

What is the status on the jira issue created on SQL Macro :

Can u suggest the jar compatibility such that which mysql jar should need to use to work it out and if possible attach that jar in attachments and let me know please.

Hi.. Thanks for you comments in Jira for SQL-26. I know there is no such column as 'syed' in SPACES table but thats the basic query and it is working in mysql very well with out any issues.

The exact problem is that if the macro finds any column or string tries to rename with keyword as 'as' then its getting failed. Even i know that its perfectly executing complex queries too. Only failing for 'as' keyword in queries.

 Please have a look in such queries.

 
Thanks in Advance.

Hi Bob,

I am trying to do a simple query that would return a date.

select my_date from my_table;

 The dates are just a date field in the oracle database, so the hh:mi:ss are 00:00:00.  Using the sql-plugin when it displays the rows it displays the date with the 00:00:00.   In other tools such as Toad or sqlplus it doesn't show the 00:00:00.  Do you know why this is happening? 

Thanks, 

Bob,

If I want to use the output of a some arbitrary Confluence macro (e.g. a
{username} macro) as a variable in a sql macro, is there a preferred way to do that? For example, I'm looking to be able to do something like this within the body of sql macro:

select * from my_table where name='{username}'

Thanks,
Pete

Hi, I'm trying to run a select from a database into a table, where one field is editable and can be updated using the run plugin.

When actually running the update  the following Oracle error is returned
sql: java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction
<UPDATE STATEMENT>

{sql-query:dataSource=test|output=wiki|}
SELECT "TESTTABLE"."ID" as "id",
'{run:replace=desc1:' || "TESTTABLE"."DESCRIPTION"  || ':-\|titleRun=update}
{sql:dataSource=testdb}
UPDATE TESTTABLE SET DESCRIPTION = ''$desc1'' WHERE ID = ''' || ("TESTTABLE.ID") || '''
{sql}
{run}' as "Description",

FROM "TESTTABLE"
{sql-query}

Hi, while trying to use this macro I got this error.

sql-query: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client was: ...... )

I thought it is similar problem with Mingy Liu's so I tried to find CONFLUENCE_HOME/confluence.cfg.xml file, but this file does not exist.

Maybe it's because I'm using Confluence is 2.5.7

Can anyone help me? thanks

While I'm at it, I have one more question. I don't know where to post this so I will post it here...

I'm currently retrieving an image from remote location to Wiki page by using !http://www.example.com/examplepic!

This works fine and when I print it, the image is there.

But when I create a PDF file, it says "Cannot resolve external resource into attachment."

This is because I didn't save this image as an attachment.

So is there any macro/plugin that can save image as an attachment?

I cannot simply download image and attach because this image will change all the time.

Anyone can help?

Thanks so much.

I am hoping someone can help with a little dilema I'm in. I have been using the sql plugin quite extensively

 and have just recently accidently created a page that when opened causes my server to run out of memory and become unresponsive.

Is it possible to either open the page in edit mode directly (ie without first viewing it) or delete the page without viewing it.

Adam

We started using this plugin last night and ecountered a problem in which the sql macro tried to query a database that was in the process of being rebuilt. The result was that tomcat seemed to crash and had to be restarted. Is this a problem that you have seen before?

It is most likely a configuration error. You can do some research following this. Your data is a bit messed up. For example what is actory="org.objectweb.jndi.DataSourceFactory". If you still have problems, I suggest creating an issue and attach your server.xml file. Copy your markup but make sure you enclose it within {noformat} macro.

I'm afraid i may know the answer to this already, but need to ask anyway

i have a table that has customer/project status.  it will return Green, Yellow or Red.  i wrote a user macro that will do a replaceAll turning Green into   Green, Yellow into (!) Yellow and so on.  this macro works great around normal text and even wiki tables.  however, it does nothing when i wrap it around a SQL query that is set to output=wiki.

i'm thinking this is trying to substitute before the sql macro fully outputs its wiki table, but wanted to run it by you.  any ideas?

one other thing i noticed: the cache plugin, when wrapped around the sql macro, prevents table sorting, highlighting, etc. however, autototal works. also, the sortIcon parameter does not seem to get passed through.

---

just as i decided to write this, the consistent behavior of it not working suddenly went away, and now cached tables sort and sortIcon works.  hmph. 

Hi Bob,

I've really been enjoying the power of the combination of
{run} and {sql}, but I'm wondering if I'm using the two optimally, particularly with respect to dealing with session information and using sql to construct other sql. The basic pattern I've been using is to use {sql} to construct data-driven URL links which invoke a {run} macro which in turn is wrapped around
{sql} to do the actual work. I'm associating session information with the current logged in user via a sql table and passing the current logged in user as a parameter via the run URL mechanism.

I'm using this mechanism, for example, to enable users to make changes to an SQL table by generating a URL link on each row to invoke {run} to edit that row, etc. I always pass the current user as a parameter to run so that the sql can get at the session information associated with that user.

Is this as good as it gets? Are there other models you can recommend?

Pete

I would like to define the number of decimal places per column. As I have to call predefined stored procedures I am not able to modify the sql syntax itself. Is there another possibility elsewhere?

Hi ,
I have some new requirement for this SQL macro. If you can provide me some suggestion on implementation , i would like to work on it.
The requirement is that suppose a select sql has a column which is of Array type, example float[] , then the output should display all the values for this array as separate rows. also if there is a column in the select sql which is not an array type , the value of this column should be repeated on each row of the array.

Please let me know if it is not clear. I wrote some java code to do it but i would like to customize the SQL Marco so that, i can make better use of confluence, otherwise I might have to think of different options to develop an application which is more data orientanted -doesnot have much business requirement.

Thanks so much for your help

Bharathi

Hi Bob - I added JIRA connection details to my Confluence server.xml but could not get it to work. Confluence works just fine. Could you please help identify what's wrong with these entries?

SERVER.XML
<Context path="" docBase="../confluence" debug="0" reloadable="false">
<Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource" username="CONFUNAME" password="PWD1" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/aoconfluence?autoReconnect=true&useUnicode=true&characterEncoding=utf8" validationQuery="Select 1" />

<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource" username="JIRAUNAME" password="PWD2" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/jiradb?autoReconnect=true&useUnicode=true&characterEncoding=UTF8" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" />
<Manager pathname="" />
</Context>

WEB.XML
<resource-ref>
<description>Connection Pool</description>
<res-ref-name>confluence</res-ref-name>
<res-type>javax.sql.Datasource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

In my SQL Plugin I got this:
 {sql:dataSource=jiraDS|showsql=false}
select 'USERS' as Type, count(distinct username) as "Count" from USERBASE
 {sql}

The ERROR that I get is: sql: dataSource: jiraDS not found.

Thanks,

Thanks for the help

I am looking for a way to pass a dynamic parameter into the SQL sentence, as for exemple the page name or a subset of a page name or data coming from variable of a scaffold template. I have tried to understand how it could be done using the run macro, but did not get any success. This question is almost the same as the one raised by Derek Stevens on Apr 11, 2006.

I want to implement some drill down from the result of a sql list where a column will have a link to a new page, created, if not already existing, from a template which contain a sql request for collecting more details.

Let say, I have a SQL statement that return a list of projects, with project number, project name, manager, status.... I would like to make a link from the project number (easily done with the SQL request with some concat statements) that will be used to create (or link to) a page containing project details and latest figures about the project, like hours spend, invoices sent, ... coming from a sql query. So I need to pass the project number from the list of the parent page, using the link, into the SQL statement of the child page beeing created based on a template.

I would appreciate any help (directions or better, code sample).

Christophe 

Hi,

I have a MySQL table with some contact information (name, email, address, phone etc...).
What I would like is that users can update this data from their confluence space. 

Ideally this would be some kind of form in which users update the information and then the SQL plugin would be used to execute the UPDATE statement.
Is this possible? If so how? Does someone have an example of how to do this?

Thanks for the help,

 Andree

I can't get this to work, we are using Tomcat 5.5.23 with SQL 2005 backend. I have copied the jtds-1.2.2.jar into the lib folder and added the following text to the server.xml file:

"jdbc/sql"
auth="Container"
type="javax.sql.DataSource"
driverClassName="net.sourceforge.jtds.jdbc.Driver"
url="jdbc:jtds:sqlserver://XXXXX:1433/XXXXXX"
username="XXXXXXXX"
password="XXXXXXXX"
maxActive="20"
maxIdle="10"
maxWait="-1"/>

 When I add this text to a page:

{sql-query:dataSource=sql|output=wiki}
select * from TB_REPORT
{sql-query}

I get:

Any help would be much appreciated as this looks like a really good plugin!

Cheers,

Liam

I have a requirement where i need to query the database and display the values in table format with column sort option.SQL Query: 

 Output:

1. Will the SQL plugin alone suffice ?

2. I know Table Plug in has column sort option, how can I integrate Table plugin with this SQL plugin to fulfill my needs?

3. How can I show my report in table format effectively with the help of plugins?

4. I need to export the table in CSV format, will I able to use CSV Macro ?

I am novice to this area, so any sample code will be of great help for me.

Thanks in advance,

Rwandan

The sortIcon parameter doesn't work for me. I'm on Confluence 2.7 and SQL Plugin 3.4.0. The sort works, but the url for the icon is

http://confluenceURL/download/resources/org.swift.confluence.sql:sql/img/down.gif

and results in a broken image link icon. I tested the url

http:///confluenceURL/download/resources/org.swift.confluence.sql/img/up.gif

and that works. Anybody else have this problem or know how I can fix it?

Thanks,
Jessica

I am trying to make use of this SQL plugin and I think I should be able to do what I want.  I want the queries to be defined in a file on the server so that the database structure is not readily available in the wiki markup, but I need these to be paramaterized.  When I use the run plugin I can paramatarize a query that is in the wiki markup, but is it possible to use it or something else to paramaterize a query that is in a file on the server?

Thanks!

Evan

I realize this could introduce security issues (SQL Injection, etc.) for some users, but is there any way to pass in a URL Parameter to page containing the SQL macro, and have it passed as a parameter to the query at runtime?

In our situation, we only run queries against our reporting server which is Read Only.

We would like to have the data source configuration moved into the plugin configuration screen rather than the server.xml file.  Are there any plans to do this by any chance?

Is it possible to replace the p1, p2 values in the query with user input on the page or captured on a previous page?

Hello,

I have a small issue when I use the chart macro with sql data and after that a sql request displaying information in the table format like outloined below:

{chart}
{sql-query:dataSource=source|table=false}
select name, sum(amount) from anytable
group by name
{sql-query}
{chart}
{sql-query:dataSource=source|output=wiki|autoTotal=true|enableSorting=true}
select name, amount, date from anytable
{sql-query}

It seems that special options in the table display like autoTotal or sorting by clicking on the heading do not work if the table is placed after a chart.

Any idea ?

Thanks

Hello,

is there any possibility to run PL/SQL Procedures within the sql - macro. I tried to do so, but I get an error ORA-00900: invalid SQL statement.

My simple test procedure looks like:
PROCEDURE Test
IS
dfg number ;
BEGIN
dfg := 0;
begin
dbms_output.put_line('Have a nice day ...');
end;
end Test;

Thanks!
Roland

Since i had major problems with this plugin getting access to my datascources on sql 2005 i just thought i'd paste my server.xml config here in case others have the same problems.
sqljdbc.jar version 1.2 in /common/lib and set as classpath in system env(CLASSPATH=C:%Confluence_home%\common\lib\sqljdbc.jar).
Recource name (in ths case timereporting) in web.xml at the bottom.

But in future versions i really hope the config can be integrated with the plugin-setup.

"" docBase="../confluence" debug="0" reloadable="false">
"jdbc/TimeReporting"
auth="Container"
type="javax.sql.DataSource"
username="xxxxxxx" (a local SQL account)
scope="Shareable"
password="xxxxxxx"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://srmwse01:1433;databaseName=TimeReporting"
validationQuery="Select 1" />

<ResourceLink global='jdbc/jiraDS' name='jdbc/jiraDS' type="javax.sql.DataSource"/>
<Resource
    name="jdbc/jiraDS"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="org.postgresql.Driver"
    url="jdbc:postgresql://localhost:5432/jira-ent"
    username="yyy"
    password="xxx"
    maxActive="25"
    maxIdle="5"
    maxWait="10000"
/>     

Hi, I have added this xml to my server.xml in order to be able to query my jira database on the same server - but I can't seem to get pass an error message saying :

sql-query: dataSource: jiraDS not found.

Any idea what the problem might be? I'm using Confluence 2.8.2, also running on postgresql.

Wow, that was an intense afternoon of troubleshooting trying to get the SQL plugin working for my situation.

But finally got it working and its great. Just like to thank you Bob Swift for all your contributions to Confluence.

I was basically just trying to connect an external MS SQL 2005 database, which is actually simple but because there was no direct intructions this page just confused me

All good now, cheers!

I have a sql statement:

select pagename, pageurl from test_page

I need to display pagename with the pageurl anchor if the pageurl is not null.

I had a marco like this:
riteurl: pagename|pageurl

How could I pass the pagename, pageurl to the riteurl macro?

Thanks,

Carol

Based on what I have read, I believe what I'm trying to do is possible. There is a lot of good information on this page, but nothing that describes how to achieve my goal concisely... so here it goes.

I'd like a page with a form that has a text input, at least three radio buttons, and a submit. When a user submits, the text input and selection become part of the query for the sql plugin and a new page is displayed with the output of the query.

In my case, the text input and radio button would be used in the where clause of the sql server.

For example, if the form had the following:

Text Input: emplyee infromation (with a text input box)

Radio buttons: First Name, Last Name, Department

 If the user typed Smith in the text input and selected the "Last Name" radio button then clicked submit,  the query would be something like this: select * from employees where lastName="Smith' and the page is updated with the output of the query and the form allowing them to search again.

Hello,

Anybody has an exmaple how to use sql plugin along with content format macro?

http://confluence.atlassian.com/display/CONFEXT/Content+Formatting+Macros

The following code won't dispaly the color, instead, it will just display something like ' {bgcolor:Green}02/23/2008 {bgcolor}':
select  '{bgcolor:' || trackingColor || '}' || trackingdate  || '{bgcolor}'
from wiki_test where trackingid = '1'

But if I just list the '{bgcolor:Green}02/23/2008 {bgcolor}' in wiki, it displays the date in green background.

Thanks a lot!

Carol

Hi. Being a newbie to JBoss, confluence, etc, I had a hard time getting this plugin working with my configuration (Confluence 2.8.1, JBoss 4.2.0 GA, Apache 2.0.54 - all running from a [Fedora Core 4] installation). I thought I'd outline the steps I followed to extract data from an Oracle 9.2.0.4 data repository.

My confluence installation uses Oracle 9.2.0.4 as it's backend database. For this reason, I didn't have to concern myself with installing any new database connectivity drivers / jar files.

Step 1. Install the SQL plugin.

I used the Confluence "Administration"/ "Plugin Repository" to install.

Step 2. Create your datasource file in the JBoss deploy folder used by your confluence installation: /{my jboss install dir}/server/{my confluence dir}/deploy/

For my installation the actual folder location / file name was:

[user@myserver home]$ vi ~/jboss-4.2.0.GA/server/confluence/deploy/oracle-ds.xml

Step 3. configure your datasource file ('oracle-ds.xml') to use your oracle server credentials.

This sample datasource configuration references multiple oracle data repositories/schema's on the same physical oracle database.

<!-- ==================================================================== -->
<!--  Datasource config for Oracle originally from Steven Coy             -->
<!-- ==================================================================== -->
<datasources>
  <local-tx-datasource>

    <jndi-name>OracleDSSageUSDev</jndi-name>
    <connection-url>jdbc:oracle:thin:@hostname.us.sagepub.org:1521:sageus</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    <user-name>???***???</user-name>
    <password>***???***</password>
    <min-pool-size>20</min-pool-size>
    <max-pool-size>50</max-pool-size>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
    <metadata>
      <type-mapping>Oracle9i</type-mapping>
    </metadata>
  </local-tx-datasource>

  <local-tx-datasource>
    <jndi-name>OracleDSSageUKDev</jndi-name>
    <connection-url>jdbc:oracle:thin:@hostname.us.sagepub.org:1521:sageuk</connection-url>
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    <user-name>???***???</user-name>
    <password>***???***</password>
    <min-pool-size>20</min-pool-size>
    <max-pool-size>50</max-pool-size>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
    <metadata>
      <type-mapping>Oracle9i</type-mapping>
    </metadata>
  </local-tx-datasource>

</datasources>

Step 4. Stop and restart your JBoss instancing running Confluence.

Tail the the Jboss 'server.log' to ensure that the new datasource file ('oracle-ds.xml') is loaded by JBoss without error.

[user@myserver home]$ tail -f /home/jira/confluenceHome/logs/atlassian-confluence.log
...
...
2008-09-26 17:12:07,255 INFO  [org.jboss.resource.connectionmanager.ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=OracleDSSageUSDev' to JNDI name 'java:OracleDSSageUSDev'
2008-09-26 17:12:07,263 INFO  [org.jboss.resource.connectionmanager.ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=OracleDSSageUKDev' to JNDI name 'java:OracleDSSageUKDev'
...
...

Step 5. Create a test page for 'SQL Plugin'

To get my configuration running, I had to prefix the 'dataSource' name in confluence macro with 'java:'. Special thanks to Andy Brook for this tip (Jan 2007).

{sql-query:dataSource=java:OracleDSSageUSDev|output=wiki|escape=true}
select product_id, title from my_product_table where rownum < 20 order by product_id
{sql-query}

Hi, This may have been covered earlier but I can't seem to find anything that specifically helps.
I'm trying to create a page with a query that includes the currently logged in wiki user as a parameter.

Please see the code below. Where I have the parameter p1, I'd like to replace that with a macro (or something) that means that p1 will contain the userid of the currently logged in user. Any assistance would be greatly appreciated!

h3. My Visitors 

{sql-query:datasource=confluence|p1=gralston|showSql=true} 
SELECT company, visitor, date_of_visit  
FROM OFFICE_VISITORS 
where created_by = ? 
order by date_of_visit desc 
{sql-query} 

Hello,

Do you have examples on how to use sql-query1 and sql-query2?

I have a table having departmentId and departmentName, another table having departmentId and employeeName. I like to display the result like this:
departmentName employeeName1 employeeName2 employeeName3... in one row.

wondering if this can be done?

Thanks,

Carol

I am having a problem using the sql-query in conjunction with the chart macro.

I am trying to render a pie chart using a simple table. the table contains a product type and a count of each product (i.e. red, 4 - green, 49, yellow, 3)

For some reason the chart only shows the count of the last product. If i set the chart to bar type it works fine. Also if I set dataDisplay to true the table looks correct.

It could be an issue with the chart macro but maybe someone has an idea of how to fix it. 

Here is the syntax

{chart:legend=true\|width=300\|height=200\|3D=true\|dataDisplay=before\|type=pie\|pieSectionLabel=%0% = %1%\|tables=content}


{sql:dataSource=datasource\|id=content\|}
select product as "Product", count as "Count"
from install_base
{sql}

{chart}

Hi Bob,

Do you have an example on how to use 'expandArray' feature?

Thanks,

Carol

Hi

Is there a particular reason that the SQL plugin won't work against Confluence 2.5.1 (running inside a Jira 3.10.1 standalone server against an MS-SQL db if that matters)?

Or is it just that it has only been tested against 2.5.2+ ?

Thanks very much
neil

I am using confluence v2.9.2 along with Websphere v 6.1. I am using direct JDBC connection as there is known issue in websphere with oracle datasource. Refer CONF-3580.

How I can use this plug-in with direct JDBC connection.
Please let me know if there is any other way to achieve it.

Thanks

We're running Confluence 2.9 (on a test server, more up to date on production with 2.9.2)

We're having trouble getting this to work with a mysql database, not the confluence db, and not on the same host (verified the connection parameters with mysql client).

We get: sql-query: javax.naming.NamingException: Cannot create resource instance

What are we doing wrong?

In conf/server.xml we have:
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
                   maxActive="100" maxIdle="30" maxWait="10000"
                   username="reporter" password="our-secret-password" driverClassName="com.mysql.jdbc.Driver"
                   url="jdbc:mysql://bubbles:3306/testdb?autoReconnect=true"/>

and in WEB-INF/web.xml, we have

    <resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/TestDS</res-ref-name>
        <res-type>javax.sql.Datasource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

... I left out the macro that I was trying to execute

{sql-query:dataSource=TestDS}
select count(*) from TEST_USERS
{sql-query}

Hi Bob,

We are facing an issue while trying to access our metrics page using SQL Plugin.
sql: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
select companyname,geo from customer_details

Kindle let me know what could be the root cause for this issue.

Datasource:<Resource
name="jdbc/confluence"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@my_db_server:1521:db_schema"
username="username"
password="password"
connectionProperties="SetBigStringTryClob=true"
maxActive="25"
maxIdle="5"
maxWait="10000"
/>

Thanks

Solved the issue I reported earlier - the .csv I was importing into MySQL was imperfect. 

Hi everybody,

I am using confluence 2.2.1 and trying to install compatiable SQL plugin. but i am not able to get the appropriate plugin. Please help me with the plugin.

Hey all.  We are using this plugin and find it very valuable for our business.  From a security perspective, I am wondering if we can turn off the script functions that we do not want enabled for our wiki.  For example, we don't want to expose or allow people to write raw sql queries in wiki pages.  Instead, we want to control queries through a process and store them in files to use the #filename script within the SQL macro.  Is it easy to configure teh jar file or the plugin to only allow this function of the plugin?  If so, can you recommend the changes necessary?  I appreciate any and all responses.

Hi,

    I have used SQL plugin 3.2.1 3.0 as well as 3.1. but any of these are not compatiable for confluence 2.2.1

Please help me with this problem

Hi guys,
I tried installing the plugin by following the guide but encountered problems.
Here's what I did:

1. I shutdown confluence application.
2. Downloaded mysql-connector-java-3.0.10-stable-bin.jar and put the file in my <confluence-home>/lib.
3. Edited my server.xml
4. Edited my web.xml
5. Since I installed confluence not using a datasource, I edited my confluence.cfg.xml

When I tried to start it up, my confluence page didn't load.

Am I missing something?

Thanks om advance.

Hi All.

I am running confluence 2.10.1, and I've been trying to get the SQLite plugin to work.

This is the error I get when I call the plugin from confluence:

sql-query: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (java.sql.SQLException: SQLite.Exception: unknown error in open)

{sql-query:dataSource=trac|output=wiki}
select * from ticket where id=1013;
{sql-query}

&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Host name="localhost" debug="0" appBase="webapps" unpackWARs="true" autoDeploy="false"><Context path="" docBase="../confluence" debug="0" reloadable="false">
<Resource name="jdbc/voice20" auth="Container" type="javax.sql.DataSource".... stuff removed ... />
<Resource name="jdbc/mis" auth="Container" type="javax.sql.DataSource".... stuff removed ..../>

<Resource name="jdbc/trac"auth="Container" type="javax.sql.DataSource"driverClassName="SQLite.JDBCDriver"url="jdbc:sqlite:/var/lib/trac/Mig33/db/trac.db" />

Doug.

Hi,

I've installed the SQL-Plugin and had made changes to server.xml and web.xml as per the instructions given out in http://confluence.atlassian.com/display/CONFEXT/SQL+Plugin

I'm still unable to configure the page to use the SQL plugin because I'm unable to access the data source that I configured. I had infact tested out my configuration using http://confluence.atlassian.com/display/DOC/Troubleshooting+External+Database+Connections

I was able to test the database configuration successfully, but it errors out when i test the data source.

ERROR MESSAGE:

Connecting to database via Datasource:
Gain InitialContext?: ok

Locate Datasource (jdbc/ETDHub) in InitialContext?: Couldn't locate Datasource (jdbc/ETDHub).

Check your JNDI configuration:
javax.naming.NameNotFoundException: Name jdbc is not bound in this Context

Open a connection via Datasource (jdbc/ETDHub)? Couldn't open a connection on Datasource (jdbc/ETDHub): java.lang.NullPointerException

I've also created a JIRA incident under the SQL-Plugin project - SQL-53

Can you please advise me on the issue and to help rectify it

I'm getting the following error when using the SQL plugin on confluence:

sql-query: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException: javax.naming.NamingException: Cannot create resource instance 

select ric, smactiveflg from eq_gtick_pme_security_master where ric='IBM.N';

I tried to install this plugin but it doesn't seem to be able to connect. I used the same credentials as confluence is using but I keep gettin an error. I opened a JIRA ticket too but I am not sure if that is how I should report my problem.

Hi Bob,

I keep getting this error message on my 1st view on pages that contain this macro.

sql: Unexpected program error: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was93371 milliseconds ago.The last packet sent successfully to the server was 93371 milliseconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Any idea what is the problem? thanks for the help.