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