Availability

Skip to end of metadata
Go to start of metadata

On this page

Macros

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}

Example chart from Site statistics

{chart:title=Confluence Content|type=pie|displayData=true|width=500|height=300
|dataOrientation=vertical|pieSectionLabel=%0% - %1%|legend=false|
tables=content|attachment=Confluence content.png}

{sql:dataSource=confluenceDS|id=content}
  select contenttype as Type, count(distinct CONTENT.title) as "Count" 
    from CONTENT
    group by contenttype
    having count(distinct CONTENT.title) > 0
  union
  select contenttype as Type, count(*) as "Count"
    from CONTENT 
	where contenttype='COMMENT'
    group by contenttype 
	having count(*) > 0
  union
  select 'ATTACHMENTS' as Type, count(distinct title) as "Count" 
    from ATTACHMENTS 
  order by "Count" DESC
{sql}

{chart}

Confluence 3.x wiki macros

Duplicate macros are available for Confluence 3.x style wiki markup for recursive use.

  • 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

Security

  1. This macro and the datasource parameter can be restricted to trusted users by configuring security using Macro Security for Confluence.
    • The sql and sql-query macros are controlled separately. The duplicate macros (sql1, sql2, sql-query1, etc...) cannot be configured separately as they inherit from the macro they duplicate.
  2. Only some databases/JDBC drivers support read only connections to be established without restrictions on the database permission level. The sql-query macro uses this support to make it more convenient by only requiring a single datasource configuration. If you database/JDBC driver does not prevent updates when using the sql-query macro, then configure an additional datasource that only has read-only permission on your database. Use macro security at the datasource level to control access. For example, PostgreSQL supports this, but it is not supported on MS Sequel Server (SQL-52).
  3. On untrusted sites, prevent SQL injection attacks by using parameter markers. This in only necessary when the SQL statements are partially constructed from user input. See Wikipedia: SQL injection. Parameter markers are supported by SQL for Confluence.
  4. Users requiring Anti-XSS support must upgrade to 4.1.0 or higher
  5. The disableAntiXss parameter on the sql and sql-query macros can be enabled by configuring security using Macro Security for Confluence.

215 Comments

  1. Hi Bob,

    Hope you are doing good.  I am trying to use SQL plugin for one of our cooperate databases.  The query gets executed and resultset is returned but it hangs completely to display the result. I did tried to print the string coming back from generateOutput method of SQLMacro . Can you please tell me what is wrong ? I am using the latest verion for SQLPLuging with 2.10 version of confluence.

    Thanks so much for your help

    Bharathi

    1. Please open an issue for these types for problems where you need to provide a bunch of data.

      1. Make sure the sql runs correctly outside of Confluence and produces meaningful results
      2. Does the query produce too many rows to display in a reasonable time? Consider using limit to control unexpectedly large results coming back
      3. Limit the query to a single row and see if that works
      4. Limit the query to only a few columns and see if that works
      5. If still having problems, open an issue with wiki markup, other details, and results of your investigation from the questions above
  2. Hi,

    I need some help concerning sql plugin:

    • How does the resursive usage work? I neigther found an example nor a documentation.
    • How to make a query, which needs access to more than one datasource?

    e.g.: SELECT * FROM source1.tableA, source2.tableZ

    1. I removed your duplicate post. We lost user supplied examples during the migration to this site as comments were not migrated. An SQL statement can only deal with a single database. However, using the sql macros, you can accomplish some interesting data or actions that deals with data from multiple sources. Here are a couple of usage scenarios:

      1. Use an outer sql-query with output=wiki to produce a table where one (or more) columns is another sql-query (this is where you need the recursive use macros). The result is a table where data comes from 2 (or more) datasources.
      2. Use an outer sql-query with output=wiki to produce a table where one (or more) columns is a run macro with an embedded sql macro that shows a button in the column. When pressed, the run macro runs the sql macro doing an insert or update (for instance)
      1. Hi,

        thanks for your reply. The same ideas I already tested, but up to now I didn't hit the right syntax, here an example,

        which doesn't work:

        {sql-query:dataSource=sourceA|limit=5|macros=true|output=wiki}
          SELECT colA1, colA2, {sql-query1:dataSource=sourceB}SELECT colB1, colB2 FROM tableB{sql-query1} FROM tableA
        {sql-query}
        
        1. {sql-query:dataSource=sourceA|limit=5|macros=true|output=wiki}
            SELECT colA1, colA2, '{sql-query1:dataSource=sourceB}SELECT colB1, colB2 FROM tableB{sql-query1}' FROM tableA
          {sql-query}
          
          1. Sorry, but I still encouter problems...

            {sql-query:dataSource=sourceA|limit=5|macros=true|output=wiki}
            SELECT DocumentIdentifier, DocumentName, '{sql-query1:dataSource=sourceB|macros=true|output=wiki}SELECT name_lief, telefon_lief FROM lieferanten{sql-query1}' FROM documenttrail
            {sql-query}
            
            

            ...this causes an "sql-query: Unexpected program error: java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating '"' not found.

            SELECT DocumentIdentifier, DocumentName, '" error.

            1. Make sure your SQL works outside of Confluence in your favorite sql tool (jdbc based). If you still suspect a macro problem, open an issue with more details.

              1. Bob,

                Was there a fix for that error? I'm currently recieving the same error

                "sql-query: Unexpected program error: java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating '"' not found."

                I've verified that it is working in Sql Editor "TOAD", but for some reason can't get it to work in Confluence

                Thanks.

                1. The macro is just reporting the problem returned from the database/jdbc driver. I haven't used TOAD, but it is probably not JDBC based. Try simple sql statements and verify those work. See if you can rework the sql in some way that avoids whatever jdbc is complaining about. If it is a complex SQL, see if you can get parts of it working before combining it.

  3. Hi There,

    First, thank you for the work you've put into this plugin, it's awesome. 

    I've just migrated my JIRA instance from Sybase to PostgreSQL.  We use a stored procedure to  generate release notes in confluence (via the SQL plugin).

    The problem is, that in Sybase if you do multiple selects, you get multiple JDBC ResultSets.  So in our proc, we had a cursor looping over a temp table, generating a single-row, multi-column table, multiple times.   And then if we did this:

     \
    {sql:dataSource=newJiraDS\|output=html\|heading=false\|rowOrientation=horizontal\|p1=projectName\|p2=version}
     exec sp_conf_projectIssues ?, ?
    {sql}

    We'd get a nice table for *each* issue, with one row per column.

    With the new PG proc, I'm not able to return multiple resultsets (or am I??). Instead, I've got a single table being returned, multi-row, multi-column.  And of course, the layout is not the same now. I'm struggling with comprehending (before even trying), how to use the 'recursive' macros.  I'm thinking the outer query would pull just the issue ID's in the release, and then generate multiple sql plugin statements, one for each issue ID, somehow pulled as a parameter from the outer query results. And when those generated statements are run, it would get me back to a single table, single-row, multi-column. But from the supplied example, this doesn't seem to be how the recursive stuff works.

    Any hints for me?

    1. My recommendation would be to work on the sql side first. I would suspect Postgres can also be made to generates multiple results as well.

      1. Hi Bob,  you'd think so, and I did as well.  But I'm at a loss to find how to do it based on the documentation.  People here also don't seem to think it can be done: http://www.dbforums.com/postgresql/1659824-returning-multiple-sets-sets-sets-function.html

        I did kludge an "n+1" solution .   Something like:

        {sql:dataSource=newJiraDS\|output=wiki\|heading=false\|border=0\|rowOrientation=vertical\|p1=projectname\|p2=version}
        select * FROM  sp_conf_projectIssuesOuter(?,?)
        {sql}
        
        

        and then the sp_conf_projectIssuesOuter outputs based on this:

        select 
         '{sql:dataSource=newJiraDS|output=html|heading=false|rowOrientation=horizontal}select * FROM  sp_conf_projectIssues('||i.ID||'){sql}'
        FROM 
         project p,
         projectversion pv, 
         nodeassociation n,
         jiraissue i, 
         issuetype t
        WHERE
        ... etc...
        

        and because the 'outer' is output=wiki, these generated sql plugin calls are then ... called.

  4. HI Bob, is it possible to use wiki markup in the NoDataMessages? i want to format this awfull red block with some link and other style.

  5. Hi,

    is it possible to use the sql plugin with the embedded HSQL, when setup Confluence as Standalone version?

    If so, what is the correct value for parameter dataSource?

    Regards

    1. Don't know. You probably need to configure a datasource using information from Confluence's configuration files. Post the info if you get it working (smile).

  6. Hi Bob,

    I have a question on Query which needs to access more than one Datasource. I am trying create query as below :

    
    {sql-query:dataSource=sourceA|macros=true|showsql=false|output=wiki}
    select distinct emp_id , emp_name from employee
    minus
    '{sql-query1:dataSource=sourceB|macros=true|showsql=false|output=wiki}
    select distinct emp_id , emp_name from employeeB
    {sql-query1}'
    {sql-query}
    

    Is this possible? If yes , would you please let me know what is the problem with the syntax?
    I am getting error saying - ORA-00928: missing SELECT keyword for the first query but displays data for second query.

    Thanks so much for your help
    Bharathi

    1.  Bob,

      Could you please reply to my above question? I am still waiting on it . Is it possible? Is there any way i can modify the code so that above type of queries can be supported? any suggestions would be appreciated.

      Thanks so much for your help

      Bharathi

    2. I have worked around this issue by utilizing only one datasource and referencing the other databases within the SQL statement utilizing two different methods:

      Method 1
      use Database1
      select * from databasetable

      Method 2
      select * from database2.dbo.databasetable

      becareful with the USE Database1 statement as it will force ALL of your SQL statements to utilize that database if you don't add the USE DatabaseX at the top of every statement.

    3. You were pretty close. You need table=false for the inner query. When doing complex things, always make sure you test the lower level stuff first separately. In this case, your sql-query1 needs to produce a single string (not a html table).

  7. hi,

    We have the need to access an external database. We have two data servers, confluence is in one, and the data we need to fetch is in another.

    I read the documentation for this plugin, and it seems is not possible to do this task.

     Does anyone know how can we resolve this issue?

    Thanks in advanced

    1. We access over 30 remote databases, so yes it is possible (smile)! You just need to configure a datasource for each database you want to access. See SQL plugin datasource configuration.

      1. i already read, I'm very newbie in this matter, the IT department, of my organization, tells me that is not possible to configure the plugin to access more than one Database, in this case confluence data base.

        Can you make me a "draw" how can I do this.

        I have confluence database in one server, and we need to access to another database located in another server.

        Thanks in advanced

        1. Hi,

          read the documentation Bod has linked above.

          You have to locate the matching xml configuration file of you tomcat instance. e.g. something like...
          c:\Program Files\Apache Software Foundation\Tomcat 5.5\conf\Catalina\localhost\ROOT.xml
          ...which can differ according to the tomcat version you use and the current configuration.
          Within this configuration file add a additional resource (cf. linked doc above!) e.g.:

          <Context path="" docBase="c:/confluence/confluence" debug="0" reloadable="true">
          
          	<Resource name="jdbc/mysql_confluence"
          	    auth="Container"
          	    type="javax.sql.DataSource"
          	    driverClassName="com.mysql.jdbc.Driver"
          	    url="jdbc:mysql://localhost/confluence?autoReconnect=true"
          	    username="XXXXXXXXXXXXXXXXXXX"
          	    password="XXXXXXXXXXXXXXXXXXX"
          	    maxActive="25"
          	    maxIdle="5"
          	    maxWait="-1"
          	/> 
          	
          	<Resource
          	    name="jdbc/otherserver"
          	    auth="Container"
          	    type="javax.sql.DataSource"
          	    driverClassName="net.sourceforge.jtds.jdbc.Driver"
          	    url="jdbc:jtds:sqlserver://dbsrv:1433/databasexyz"
          	    username="XXXXXXXXXXXXXXXXXXX"
          	    password="XXXXXXXXXXXXXXXXXXX"
          	    maxActive="20"
          	    maxIdle="10"
          	    maxWait="-1"
          	/>
          	
          </Context>
          

          ...after a tomcat restart you can query the new datasource. But keep in mind: distinct queries, its not possible to do one query to two datasources at the same time.

          1. And there are some examples attached, not sure they are total up to date, but it will give you an idea - server.xml.

          2. Thanks for helping (smile). Your warning about queries being from only one database (datasource) is absolutely correct, but there are some advanced user tricks using the Run Self-Service Reports for Confluence to do some things across multiple databases (smile).

            1. Hi,

              Thanks, I asked to IT department to do the suggestions you made, now when I wrote a query I get the following error:

              sql-query: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class 'net.sourceforge.jtds.jdbc.Driver'

              SELECT * FROM Lista_Funcionarios

              What I'me doing wrong?

              1. You must tomcat provide the corresponding database connector (e.g. for mysql "mysql-connector-java-5.1.12-bin.jar" needs to be downloaded an placed in (cf. doc) XXX folder of tomcat)...

                XXX be may something like "C:\Program Files\Apache Software Foundation\Tomcat 5.5\common\lib"

                1. it is working! thanks for your help

                2. hi,

                  after we create the third connection, we get the following error message.

                  sql-query: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Login failed for user 'susig'.)

                  select * from gcdrules

                  thanks in advanced

  8. Hi Bob,

    I have a question on Query which needs to access more than one Datasource. I am trying create query as below :

    {sql-query:dataSource=sourceA|macros=true|showsql=false|output=wiki}
    select distinct emp_id , emp_name from employee
    minus
    '{sql-query1:dataSource=sourceB|macros=true|showsql=false|output=wiki}
    select distinct emp_id , emp_name from employeeB
    {sql-query1}'
    {sql-query}
    

    Is this possible? If yes , would you please let me know what is the problem with the syntax?
    I am getting error saying - ORA-00928 : missing SELECT keyword for the first query but displays data for second query.

    Bob , please help me

    Thank you
    Bharathi

    1. Yes something like this can be done and is another example of what I mentioned just above. However, it is very tricky to get everything just right. I recommend a few things when doing this:

      1. Start with a simple example first!
      2. Run the embedded query macro separately to ensure it produces the information you expect!
        • Normally the inner macro needs to construct the data so it provides valid input for outer query - sometimes very hard to do. Sometimes easier to do using Groovy script
      3. Run the outer macro separately with results of the above and make sure that works as expected.
      4. Then combine and verify.
        I see at least one error in your example - use table=false on the inner macro since you just want the raw data (not a html table)
  9. Unknown User (frank knobloch)

    When i do something like this

    {chart:title=Kommentare|type=timeSeries|displayData=true|width=500|height=300
    |tables=content|dataOrientation=vertical|timePeriod=Week|dateFormat=yyyy-ww|domainaxisrotateticklabel=true}
    {sql:dataSource=s6_sportics_deprecated|id=content}
    SELECT DATE_FORMAT( postTimestamp,  "%Y-%v" ) AS 
    Woche, COUNT( * ) AS Anzahl FROM  `Comment` where postTimeStamp >= "2010-01-04 00:00:00" and posttimeStamp <= now()
    GROUP BY Woche ORDER BY Woche ASC
    {sql}
    {chart}
    

    we see table and chart. Is it possible to hide the table? Wi just want to see the chart.

    1. That is a chart option - displayData=false or just remove displayData from the chart macro.

      1. Unknown User (frank knobloch)

        I'm ashamed, thanks...

  10. Hi!

    Thanks for awesome macro, it helps me really well!

    One thing I cannot get is how to perform this:

    Use an outer sql-query with output=wiki to produce a table where one (or more) columns is a run macro with an embedded sql macro that shows a button in the column. When pressed, the run macro runs the sql macro doing an insert or update (for instance)

    The goal is to create a table of data from db, with update and delete functionality for each row.

    Is it real? Please could you give an example?

    1. Yes that can be done! The sql gets pretty messy (smile). Using noDataMessage is optional, but it means you get some visual feedback after pressing the button.

      {sql-query:datasource=testDS|output=wiki}
          select '{run:titleRun=update ' || a1 
              || '} {sql:dataSource=testDS|noDataMessage=' || a1 
              || ' row updated} update test01 set a1 = \'' || a1 
              || '\' where a1 = \'xxx\' {sql} {run}' as "update" from test01
      {sql-query}
      
      1. Fantastic! Some kind of magic indeed.
        Thanks a lot for help, I killed two days on searching a solution!
        That's what I get and it's working:

        {sql-query:datasource=law|output=wiki}
         select '{run:titleRun=Change row|replace=row::Row# ' || oid || ' :group:100%,
        a1:' || a1 || ':&nbsp;,
        a2:' || a2 || ':&nbsp;}
        
        {sql:dataSource=law} update courts set court = \'$court\' , year = \'$year\' where court = \'' || court || '\'
        {sql}
        
        {run}'
        as "update" from courts
        order by oid
        {sql-query}

        Honestly I dont't like nbsp here, but that's only way to keep description field clear (am I right?)

        Regards!

      2. I encountered a serious problem with data output. If there are commas in data itself - they ruins all the output, adding new fields after every comma.

        Let's take previous example - if we have a1 like string "text, another text" then we'll get not one but two input fields: [text] and [        ]

        Second field is empty.

        Is it a bug or there's some solution already?

        Many thanks in advance!

        1. I don't see that in my example, so it is something you are doing. Check your example again and if you still have difficulty open an issue and give details.

          1. Here's the issue

            Symbols ' and : crashes the output too.

  11. Hi guys,

    Is the following possible? I want to create a hyperlink from an embedded sql query. For example,

    {sql-query:dataSource=Warehouse|output=wiki}
    select
    concat('[http:/foo/display/baa/',code,'_',name,'+(',id_experiment_class,')]') as href
    from
    experiment_class
    limit 5
    {sql-query}
    

    returns a list of the webpages that I can cut and paste into my browser. But is it possible to create a link so the cut and paste is not required?

    Best,

    Roger

    1. I updated the above to include output=wiki and added wiki syntax for linking ([ and ]) around your link.

      1. Exactly what I needed - many thanks, Roger

  12. Hi,

    I am wondering if it is possible to populate a combobox (dropdown, select, etc.) from a SQL query.  Any help would be appriciated.

    Thanks,

    David

  13. Hey Bob,

    Love the plugin, my company really appreciates the work you've done!

    I created a stored procedure with a simple select statement and then do a call on a confluence page and get the following error:

    sql-query: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed

    However, the datasource is given write permissions. Any clue why this may be?

    Thanks!

    1. sql-query macro requests read only support from the database driver which may or may not actually enforce that request. Your driver does support it - that is generally good! You can use the sql macro for operations that may not be read only.

  14. Is there a way to monitor the number of connections that have been used in the connection pool specified in the database configuration?   JMX?

    I've recently seen an error that suggests our pool is exhaused - but our underlying database is still happy to receive direct connections outside of Confluence.

    1. Don't know. Maybe a Tomcat or app server expert can comment.

    2. Geoff,

          JMX is a perfectly valid way of monitoring the connection pool state. In v6 the you can find the relevant bits under Catalina:type=DataSource,class=javax.sql.DataSource,name="jdbc/(your datasource name here)"

      the pool parameters (both defined and active) are visible there.  e.g.

      maxActive: 10

      maxIdle: 2

      [...]

      numActive: 1

      numIdle: 2

      As for pool configuration, depending on the version of Tomcat, the resource configuration and (on some occasions) the driver version, pooling is configurable in the resource definition - things that have always helped us track down and resolve pool issues are to include the "abandoned db connections" directives... (http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html)

      Param

      Default

      Description

      removeAbandoned="true"

      false

      When available db connections run low DBCP will recover and recycle any abandoned dB connections it finds.

      removeAbandonedTimeout="60"

      300 seconds

      Use the removeAbandonedTimeout attribute to set the number of seconds a dB connection has been idle before it is considered abandoned.

      logAbandoned="true"

      false

      The logAbandoned attribute can be set to true if you want DBCP to log a stack trace of the code which abandoned the dB connection resources.

      Hope this helps.

      --

      Mike

  15. Unknown User (lisriba)

    Is there a way to combine an external script (SQL-QUERY:script=...) and parameters specified by the RUN Macro?

    I have a page which renders correctly when all statements are in the macro body.

    When I try to point the SQL statement to an external script, it treats the variables as literal strings.

    Is this possible, or not worth bothering with?

    [I'm constructing dynamic pages (using run-once) for all our products, and I'd prefer to reference external scripts in case I need to modify them in the future.]

    1. Good question. The short answer is no - the run macro only has access to the data within the body of the macro. Consider:

      1. Use SQL parameter markers
        {run:replace=name:Joe|autoRun=true}
        {sql:datasource=testDS|output=wiki|p1=$name|p2=Mary|showSql=true}
        select * from test_address where firstName in (?, ?)
        {sql}
        {run}
        
      2. Create a Run improvement request to provide some sort of ability to include file data
      1. Unknown User (lisriba)

        Do SQL parameter markers work with script=?

        I've been trying it out (omitting the RUN macro to keep things simple) and keep getting SQL errors.

        This works successfully:

        Page Body
        {sql-query:dataSource=eareplica|p1=%lrc%}
        select * from interfacelanguage where langcode = 'en' and interfaceid like ?
        {sql-query}

        But it fails if I split things up:

        attach.txt
        select * from interfacelanguage where langcode = 'en' and interfaceid like ?
        Page Body
        {sql-query:dataSource=eareplica|p1=%lrc%|script=^attach.txt}

        And the error I get is:

        sql-query: java.sql.SQLException: Incorrect syntax near 'ï'.

        I've been trying it with and without quote marks around the parameter, to no avail.

        What am I missing?

        1. Yes, parameter markers work just the same no matter the source of the sql statement. I tried a similar example and it worked fine. 2 thing to check:

          1. Make sure you close your macro with an ending {sql-query} - it still requires a body. This is likely your problem.
          2. Check your attachment for corruption.
  16. Unknown User (lisriba)

    I'm having problems getting {table-plus} to work with the results of an {sql-query}

    If I recreate the table using wiki markup, the table-plus highlighting and sorting work correctly, but when I nest an {sql-query} within the {table-plus} macro commands, I get no sorting and no highlighting.

    Any clue what I might be doing wrong with SQL parameters? (I've tried it both output=html and output=wiki, and neither has much effect).

    {table-plus:heading=1|enableSorting=true|enableHighlighting=true|sortColumn=1|sortIcon=true|columnTypes=I,S,S,S,S,S,S,S|enableHeadingAttributes=false}
    {sql-query:dataSource=eareplica|output=html|heading=1|escape=true|convertNull=false|table=true}
    
    1. No need for table-plus,SQL supports the same common table parameters and behavior.

      1. Unknown User (lisriba)

        Oh, cool! Thanks.

        That works and makes matters much easier.

  17. I have a long running, complex SQL query which generates multiple tables which I want to display as charts. Since I can't do this with the chart plugin out of the box I need to embed the chart plugin commands in the SQL. So I need to use nested SQL plugins (outer query not table formatted, inner query table formatted). But I'm using temp tables to build results and they are not persistent between the nested sql plugins. Any ideas how to achieve this.

    Pseudo code:

    {sql:dataSource=blah|output=wiki|heading:1|table=false|showSQL=true}
    
    <complex SQL to build temp table #tickets>
    
    select '{chart:type=bar|dataDisplay=true|width=1100|height=400|legend=true|dataOrientation=vertical|ylabel=Count}
    {sql1:dataSource=blah|output=wiki|heading:1|table=true|showSQL=true}
    
    select assignee, count(*) as tickets
    from #tickets t
    group by assignee
    order by count(*) desc
    {sql1}
    {chart}'
    
    select '
    {chart:type=bar|dataDisplay=true|width=1100|height=400|legend=true|dataOrientation=vertical|ylabel=Count}
    {sql1:dataSource=blah|output=wiki|heading:1|table=true|showSQL=true}
    
    select status, count\(*) as tickets
    from #tickets t
    group by status
    order by count(*) desc
    {sql1}
    {chart}'
    {sql}

     

    1. It is kind of messy doing it this way, although you can probably make it work. Before I look at the details, have a look at my post which is a much easy way to solve this - hopefully that will work for you.

  18. I'm trying to use the sql plugin to call a stored procedure in Oracle. I see from the comments here that other people have used "exec package.procname ?" with other databases, but this gives me "sql: java.sql.SQLException: ORA-00900: invalid SQL statement" for an Oracle function that returns a sys_refcursor (a Java ResultSet). I've also tried "call package.procname( ? )" and just "package.procname( ? )", all with varying error messages. The proc exists, and I can get results from it with other clients.

    What I would do in Java code is something like

       stmt = ( CallableStatement ) conn.prepareCall( "? = procname( ? )" );
       setString( 2, 'whatever' );
       m_stmt.execute();
       rs =  ( ResultSet ) stmt.getObject( 1 );

    How can I do something like this with the sql plugin?

  19. We are having an issues with the display format of Uptime on the admin/systeminfo.action page with this plugin enabled.
     - Normal view (plugin disabled): 2 days, 18 hours, 35 minutes, 16 seconds
     - Skewed view (plugin enabled): 2 , 18 , 35 , 16
    Has anyone else noticed this problem or have a fix for it?
    Environment: Confluence 3.4.9 - Windows Server 2008 R2 6.1 - Apache Tomcat/6.0.32 - 1.6.0_17 - 14.3-b01 - Java HotSpot(TM) 64-Bit Server VM - MySQL 5.1.51-community - com.mysql.jdbc.Driver - mysql-connector-java-5.1.11

    1. Since it is the admin/systeminfo screen, I would suggest opening a support issue with Atlassian.

      1. Yeah, I have one with them already, they said "As for the uptime display, this seems like it may be impacted by a plugin. The best way to test this is to enable Plugin Support Mode / Safe Mode, which will temporarily disable all user-installed plugins, and see if this is still displaying incorrectly."  Which I did, and found the four plugins that affect it's display:

        I disabled ALL plugins, then went down the list, one-by-one, enabling & refreshing the System Information page until the Uptime displayed in the incorrect format.  Then disabled the offending plugin again and continued down the list.  Each of the 4 above affected it individually (if any one was enabled, the Uptime was displayed wrong).

  20. And as of upgrading v3.5, the Uptime is displayed correctly (even with all the above plugins enabled)

  21. Is there any way to call an Oracle stored procedure?

    I asked a longer version of this question above but there were no replies.

    1. Still no answers, so I looked the source for the sql plugin, and I'm pretty sure that it isn't currently possbile to call an Oracle stored proc.

      I considered submitting a patch to allow this, but I couldn't get the build to work (perhaops because I was using Maven 2, not Maven 1.0). What would be required is to look at the supplied SQL and guess whether it's a stored proc or a query. One obvious way is to test whether it begins with "select" (case insensitive) and perhaps also "delete" or "update" too. Then if we think it's a stored proc, make a CallableStatement instead of a PreparedStatement, and allow the user to specify where the output param is, and what the data type is (in the Oracle case, "OracleTypes.CURSOR", but something else for another db). So, all do-able, but some effort involved to implement and test.

      My work-round is that I will create views and query them instead of using stored procs.

      1. Suggest you open an improvement issue and we can discuss further there.

  22. Posting a discussion with Bob regarding searching within a result set:

    I am creating a set of wiki pages with database attributes, reports and metrics. The team is looking for a solution where the wiki pages can be integrated with the database to keep the documentation always current with minimal maintenance. The iframe option (embedding chron-job-generated html output) will not work as users cannot search within iframes.

     Will the SQL Plugin work?

    Bob: Yes, it is used for that in many cases

    Before we use the plugin and make changes to the Confluence XML file,

    Bob: just a technical note, the Confluence XML file normally is NOT changed for the SQL plugin. A application server datasource is required, usually involves server.xml addition.

    we want to make sure users can search within the content displayed using the SQL query.

    Bob: This is possible! It requires specific usage - see: How to index SQL data

    The documentation shows that we can choose the output to be either HTML or wiki markup- in which case I assume the content is searchable.

    Bob: Either works

  23. How do I enable the disableAntiXss parameter? I didn't find any documentation about what to add to my macro-security.properties.

    1. Examples: sql.disableAntiXss=*ANY or sql-query.disableAntiXss=confluence-administrators

      1. Thanks!

        Is it possible to enable the disableAntiXss parameter only for some datasources?

        1. No, but you can construct your authorized user list based on one or more datasource authorized user lists to get the equivalent I would think. If you think there is something missing to make this fit your use case, please open an issue.

  24. Unknown User (hahler)

    Hey Bob,

    I'm creating a table with a hyperlink with your sql-plugin. From your comment above I found that I need to use output=wiki and the brackets to get the linking working. This works so far. But now I noticed that sometimes my tables get corrupted. Escape=true isn't useful. It does not resolve the problem and makes just that the wiki link gets broken. When switching to html the table looks fine, but my link doesnt work. Trying to use html code to embed the link with <a href=" etc. doesn't work or I'm doing it wrong. Here are two screens and my code for the corrupted table.

    Update: Can't upload image. But here is the description header looks alright all the way and in the data line it flips and starts a second line before having finished the first line. So conten that belongs in first line gets displayed in a second line. like a line break.

    {sql:dataSource=Musterauftrag\|output=wiki}
    SELECT '\[' + '68377' +
    '\|[http://mhfs95.mankiewicz.de:8080/display/MP/Details+Musterauftrag?run_1=run|https://service.gmx.net/de/cgi/derefer?TYPE=3&DEST=http%3A%2F%2Fmhfs95.mankiewicz.de%3A8080%2Fdisplay%2FMP%2FDetails%2BMusterauftrag%3Frun_1%3Drun]
    &run_1_MusterzettelID=' + '68377' + '\]' as "MA-Nr.",ErfDatumText as "Erfasst
    am",CONVERT(VARCHAR(10), Wunschtermin, 104) AS "Geplanter
    Endtermin",Projekt,ZielText,ATABearbeiter1 as Bearbeiter,Status
    FROM dbo.tabMzKopfdaten
    WHERE dbo.tabMzKopfdaten.MusterzettelID = 68377
    OR dbo.tabMzKopfdaten.MusterzettelID = 68377
    {sql}
    

    Do you have any clue why this isnt working properly?

    1. If your column data messes up Confluence formatting, you need to surround with the noformat macro or use other techniques to correct the data. We use a database function that does this using the div macro:

      CREATE OR REPLACE FUNCTION confluence_text(text)
        RETURNS text AS
      $BODY$ select '{div:style=left:1em;right:1em} ' || $1 || ' {div}' $BODY$
        LANGUAGE 'sql' STABLE
        COST 100;
      ALTER FUNCTION confluence_text(text) OWNER TO postgres;
      COMMENT ON FUNCTION confluence_text(text) IS 'Confluence - wrap text in a div to protect against table format errors';
      
      1. Unknown User (hahler)

        I talked with our server administrator, but he wasn't able to fully understand your database function. We are using MS SQL Server 2005.

        Should I place the noformat macro just around the sql plugin or what's the idea behind that one?

        Anyway we could qualify the problem! This issue just happens when the data entry of the sql database contains line breaks. This seems to me like something appropiate that should not mess up confluence formatting. Is there any chance that we can get that fixed within your plugin or confluence?

        1. For output=wiki, the column data is put into a standard Confluence wiki table - you can do this manually on a page with your data and see what happens to the formatting. Open an issue if you want to discuss further. I suggest you narrow it down to what column causes the problem. Make sure you have the div macro installed (or use the noformat macro instead). For that column, use:

          '{div:style=left:1em;right:1em} ' || <existing column data> || ' {div}'
          
          1. Unknown User (hahler)

            Hey Bob

            No success with that approach neither. I would have to put the div blocks in the sql statement but that does not work.

            Anyway as mentioned above the output via output=html works fine. Line breaks are no problem for html. The only thing that I cannot get working with output=html is a hyperlink. That seems to me as something that should be done easily but I tried everything from manually inserting the html code for the link just like this

            SELECT '<a href="Link' + 'DataField' + '>' + 'DataField' + </a> which generates the correct syntax but it simply is not getting parsed as html and so no hyperlink

            another approach was to use a User Macro to create the link as html seems not to work. But no luck with that neither as SQL plugin is claiming about the html syntax. Is there any way to create a hyperlink for the sql statement by using output=html. I'm really getting desperated with that. =/

            1. Either wiki or html should work. If you want to stick with html, then you need to look at SQL Plugin 4.1.0 released and avoid anti-XSS support messing up your link html. Open an issue if you want to discuss further.

  25. Has anybody checked the compatibility with Confluence 3.5?

    1. Yes. Always check the plugin exchange for most current compatibility. Page updates may not get done in a timely manner.

  26. I want to have a query where the user viewing the page can enter a value, and then have that value be used as a parameter marker for the query. Is this possible?

  27. It would also be very helpful to have more detail on just how the parameter markers are used. For example, 

    {sql-query:dataSource=mydatasource|p1=$CreditId|showsql=true}
    
    SELECT credit_factsheet_id, provider_id, credit_id, factsheet_type_id, details, culture_id, language_id FROM credit_factsheet WHERE credit_id = ?
    {sql-query}
    

    How does $CreditId get populated?

    1. You need to use the Run Self-Service Reports for Confluence and define CreditId as a user input parameter, then $CreditId will be replaced with the value the user provides. There are examples on the plugin page. You also might want look at the Confluence Reporting Howto.

  28. Hey Bob

    I wanted to ask if you have any experience with using sql plugin in order to build a whole database application with inserting and updating values. We started with scaffolding plugin to manage our raw materials. We somehow had the idea that it will be easy later on to fill the scaffolding database with yet existing data- but its definitely not. So we came across with the idea to use an sql database as layer below. This database would be easy to fill. We just need something that makes it easy to update fields and everything. We already tested the necessary sql statements and we see that somehow with run plugin you can achieve inserting or updating after pushing a button but it seems to be a small part from the whole puzzle.

    I found this piece of code in the comments but unfortunately its not working. I have just changed the testDS with our database we use locally but I get the error that we use wrong syntax with this sign. "|" Any idea how to achieve something similar to the scaffolding idea with sql and run plugin?

    {sql-query:datasource=testDS|output=wiki}
        select '{run:titleRun=update ' || a1
            || '} {sql:dataSource=testDS|noDataMessage=' || a1
            || ' row updated} update test01 set a1 = \'' || a1
            || '\' where a1 = \'xxx\' {sql} {run}' as "update" from test01
    {sql-query}
    
    1. Yes, the run and SQL can be used to do table maintenance. A new run plugin version will make it easier - more info will be available in about a week. There is a how to in run plugin page that you can look at for the idea.u

  29. Bob,

    Great plugin. I was wondering if there were any additional formatting options. I'm creating a chart of data over time and there's a lot of individual date points, so the chart looks pretty blocky with all the little data point icons.

    Tim

    1. That is a CHRT question, but I don't recall there being an option for that.

      1. D'oh, sorry about that. Too many tabs open, forgot which plugin I was on.

  30. Bob--hoping for some of your magic.

    I'm trying to run a query to populate the drop down options in the run macro. However, when I try the following, it outputs it as plain text and does not render it. I've also tried setting output=wiki. Any ideas?

    {sql-query:dataSource=RxIT1023722241MySQL|table=false|macros=true}
    SELECT '{run:autoRun=true|hideparameters=true|hiderun=true|replace=year:2011::hidden}'
    {sql-query}
    
    $year
    
    {run}
    

    Results:

    <script type="text/javascript">
    function run_moreDisplayToggle_1() { // toggle display of "more" rows of parameter table on or of
    //alert("test");
    var table = document.getElementById("run_table_1");
    var rows = table.getElementsByTagName("tr");
    ...
    
      1. Have you looked at How to generate choice values using SQL?
      2. Do not interleave macros (that have bodies)! It is always bad.
        {x} {y} {x} {y}
        
      1. We were in the process of trying to get our administrators to implement the {groovy} plugin but we used the 'Alternative - manual' solution on the How to generate choice values using SQL page.

        What's interesting is that the following works partially:

        {sql-query:dataSource=RxIT1023722241MySQL|table=false|output=wiki}
        SELECT '{run:autoRun=true|hideparameters=true|hiderun=true|replace=year:2011::hidden}'
        {sql-query}
        
        $year
        
        {run}
        

        However, I believe it renders after the page is loaded and doesn't catch the close tag for the macro.

        1. The manual method is a cut and paste from a query - not your example.

          1. I understand, that's along the lines of what we are presently doing. I appreciate your help as always!

  31. Hello,

    I am trying to solve the following problem:

    I have a query from a database a that return me a list of project numers I want to use in a second query from database b. So the total query will look like

    Select ProjectNumber, ProjectName from Projects where ProjectNumber in(select ProjectNumber from ProjectTable2)

    Project sits in the A database, ProjectTable2 in the B database that ar not joinable (different technologies and access rights).

    Is there a way to make the subquery "select ProjectNumber from ProjectTable2" returning someting like (123,232,432,234,254) that I could pass to the second query. I tried with user macro but without success.

    Thank you for any help

    Christophe

    1. Have you tried doing a subquery in your sql statement? SELECT (SELECT ...

      My only worry is connecting to both databases.

      Otherwise, I think you may need to use something like the groovy macro.

  32. Unknown User (wflespeak6)

    In our Confluence instance we have to restart Confluence if the (ms sqlserver) database is bounced.

    If we attempt to execute the macro (before the Confluence restart) the error message "connection to database server  has been reset".

    Is there a way to prevent this from happening?

    Will a connectionTimeout of "-1" help with this?

    We current have the configuration as:

    <Connector className="org.apache.coyote.tomcat4.CoyoteConnector" port="8082" minProcessors="5"
                       maxProcessors="75"
                       enableLookups="false" redirectPort="8444" acceptCount="10" debug="0" connectionTimeout="20000"
                       useURIValidationHack="false" URIEncoding="UTF-8"/>

    Please advise, thanks!

    1. You might want to add a validation query as suggested from http://confluence.atlassian.com/display/DOC/Configuring+a+MySQL+Datasource+in+Apache+Tomcat. The type of database probably doesn't matter.

      Why is the validationQuery element needed? When a database server reboots, or there is a network failure, all the connections in the connection pool are broken and this normally requires an application server reboot. However, the Commons DBCP (Database Connection Pool) which is used by the Tomcat application server can validate connections before issuing them by running a simple SQL query, and if a broken connection is detected, a new one is created to replace it. To do this, you will need to set the "validationQuery" option on the database connection pool.

  33. Thank you Bob - The macros are dynamite!.

    I'm having difficulty getting a SQL query with an embedded link-page macro to render correctly.

    Heres the query:

    {sql:datasource=SQT_Automation_Data|output=wiki|macros=true} SELECT TRANS_LABEL AS "Transaction Name",
    '{link-page:' + TRANS_LABEL + ' Response Times|source=Transaction Response History|live=true}Details{link-page}' AS "Details",
    round(avg(TRANS_DURATION),2) as "Average Response Time"
    from perf_measurements where TRANS_LABEL like '%SMOKE TEST%'
    group by TRANS_LABEL order by "Average Response Time" DESC
    {sql}
    

    The query runs successfully, sort of. Except that it doesn't do what I need. The link-page macro runs, but its output renders as escaped HTML, rather than as the link I want. And, the query column result I was hoping to embed into the link-page isn't expanded as SQL. For example:

    Transaction Name

    Details

    Average Response Time

    DAILY SMOKE TEST Location_Add_Window_Load

    <span><a href="/pages/createpage-name.action?templateId=109707265&linkCreation=true&parentPageString=Smoke+Test+Response+Times&name=%27+%2B+%22Transaction+Name%22+%2B+%27+Response+Times&spaceKey=~swad01&fromPageId=109379647&live=true&templateName=Transaction+Response+History" title="Create Page: ' + TRANS_LABEL + ' Response Times">Details<sup><img src="/images/icons/plus.gif" height="7" width="7" align="absmiddle" alt="" border="0"/></sup></a></span>

    7.04


    Is what I'm trying to do simply not possible? I know I can concatenate square brackets and get links, but I need to use the specified template for page creates, which is why I'm attempting to use link-page.

    Thank you!

    1. Set macros=false or remove it. output=wiki is all you need in your case.

      1. That is too easy! thank you! Now I have to figure out where I misread the documentation.

  34. I must be missing something fundamental...I installed the 4.2.1 plugin using the UPM and everything looked good.  I shutdown Confluence (v4.0), modified the server.xml file to include my datasource, and restarted Confluence.  The Atlassian Confluence Log shows no errors. But I am unable to access the sql macro in a page - Confluence treats it like text!  I tried going through the macro browser, but there is no SQL entry.  I tried just typing "{sql.." but it never offered to auto complete it.  Any ideas what I'm doing wrong?  

    1. Confluence 4.0 compatibility for Advanced Tables and other plugins contains information on Confluence 4.0. Only migration mode is available until a new release is available (sad).

      1. Thanks for the quick reply... The availability of the SQL Plugin was a large part of my recommendation of Confluence as a intranet/customer portal solution for my company.  I am really looking forward to your Confluence 4.0-ready release!  

  35. Hi, Bob. 

    I love this plugin but am having some trouble putting together a specific SQL query. I'd like to run a query on the 10 pages with the highest number of comments created in the past week. This gives us a good sense of where the conversation is happening on our wiki.

    I know this has to be possible, but I'm not having any luck. 

    Ideally, the output would be like this:

    Page title

    Comments created in past week

    Where to find Waldo

    50

    Carmen Miranda - fact or fiction?

    35

    Oregon Trail comes to Kansas

    25

    etc.

     

  36. select 
        (select c2.title from content as c2 where c2.contentid = c1.pageid) as "Page title",
        count(*) as "Comments created in past week" 
    from content as c1
    where c1.pageid is not null and c1.contenttype = 'COMMENT' and c1.creationdate > now() - interval '7 days'
    group by c1.pageid 
    order by 2 desc
    
    1. Bob, this worked perfectly. 

      We are trying to get a good sense of our company's usership on the wiki. I have quite a few SQL queries set up already, but have run into some snags with the ones listed below.

      Is it possible to run SQL queries on the following?

      Number of searches performed in Confluence, grouped by user 

      Number of communities belonged to (Community-Bubbles plugin), grouped by user

      Number of watched pages, grouped by user? 

      I realize the Community-Bubbles one might be outside of your purview, since it's a specific plugin. 

      Thanks for any advice you can offer. 

      Kris

      1. You would have to dig into the Confluence DB schema to find the right query for the Confluence stuff. Not sure where Community bubbles stores its data, probably bandana or similar - it bit harder to find and query on.

        1. You were right--it's in the bandana table. 

    2. This question refers to the query you provided above

      select
          (select c2.title from content as c2 where c2.contentid = c1.pageid) as "Page title",
          count(*) as "Comments created in past week"
      from content as c1
      where c1.pageid is not null and c1.contenttype = 'COMMENT' and c1.creationdate > now() - interval '7 days'
      group by c1.pageid 
      order by 2 desc
      

      Ideally, I'd like users to be able to click through to the page. Is it possible to inject the hyperlink into the page title so users can click through to the pages listed? 

      1. Yes, use something like '[' || c2.title || ']' and make sure you specify output=wiki on the sql macro.

        1. Thanks for the advice. My final code looked like this:

          {sql:dataSource=mydatabase|output=wiki|enableSorting=false}
          
          select
          case
          when contenttype = 'PAGE' then '[' || spacekey || ':' || c.title ||']'
          when contenttype = 'BLOGPOST' then '[' || spacekey || ':' || '/'||extract(year from c.creationdate)||'/'||extract(month from c.creationdate)||'/'||extract(day from c.creationdate)||'/'||c.title ||']'
          end as "Page Title",
          com.comments as "Comments created in past day"
          from (
          select c.PageID, count(c.contentID) as Comments
          from content c
          where c.contenttype = 'COMMENT'
          and c.PageID is not null
          and c.creationdate > now() - interval '2 days'
          group by c.PageID
          ) com
          inner join content c on com.pageID = c.contentID
          left outer join spaces s on c.spaceid = s.spaceid
          order by com.comments desc
          limit 5
          
          {sql}
  37. How can I escape characters in the sql used?  I am trying to use the LIKE clause in my sql and it looks like the percent characters cause issues when used in conjunction with a variable.  I am passing in the variable value through the url.  I need to have a condition like the following "WHERE Link LIKE '%Chevy%'".

    The generated sql just shows blanks as per the following

    select '[' + Name + '|' + Link + ']' as Name,
    convert(varchar,RunDate,101) as [Run Date]
    from Autos
    where Link like '' order by RunDate desc
    

    The following is the code i am using

    {replace-and-render}
    {replace-item:%development_project%}{report-info:variable:Development Project}{replace-item}
    {replace-body}
    {run:autorun=true|replace=Auto:%Auto%:Auto}
    
    
    {report-on:injected=true}
    {sql-query:dataSource=Autos|output=wiki|showsql=true}
    select '[' + Name + '|' + Link + ']' as Page, 
    convert(varchar,RunDate,101) as [Run Date] 
    from Autos
    where Link like '%$Auto%' 
    order by RunDate desc
    
    
    {sql-query}{report-on}
    {run}{replace-body}
    {replace-and-render}
    
    1. I would suggest an issue if this needs any more detailed discussion.

      Standard recommendation is always to get simple pieces working before putting them together in more complicated markup. Specifically I see problems with:

      1. You have a replace and render with variable name *%development_project%, yet that variable isn't found in the replace body. So that isn't doing what you think.
      2. The run macro has an Auto variable with initial value of %Auto% - that seems strange especially when that variable is being used in a place which also is wrapped with % - is that an injected value?
      3. Not sure why you are using both replace and render and report-on together.
      1. I removed all the weird code which was inherited and it works perfectly.  Thanks for your help!

  38. In the event that someone would like to encrypt their database connectivity with SSL, below is the connection string for the JTDS driver and MS SQL.

    <Resource name="jdbc/DataSourceName" auth="Container" type="javax.sql.DataSource" driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://SQLServer.Domain.Root;instancename=sql;database=Database;ssl=require;"
    username="user"
    password="password"
    maxActive="20"
    maxIdle="10"
    maxWait="-1"
    />

    All the SSL options I found at http://jtds.sourceforge.net/faq.html.

  39. Hi Bob,

    first, I'd like to thank you for your great plugins on behalf of me and all my co-workers! They really make our lifes as developers easier.

    Is it normal, that the SQL Plugin 4.2.1 doesn't show in the Macro Browser in Confluence 4? It did before we upgraded from 3.5.9 to 4. It still displays the SQL queries without a problem and we can create new ones without problem within a wikimarkup block.

    Tried to un- and reinstall the plugin but this didn't change anything.

    Thanks a lot!

    Raffael

      1. hi Bob, i've installed the 4.2.2 on confluence 4.1.4 but the browser still show nothing

        1. That is expected behavior for that version, see the page I referenced that says that.

          1. oh yes i figured out late i will try it with wiki markup then

            sorry for that. I hope new version will come out fast, thanks for ur support

  40. Love the plugin, guys. It works great, pulling the information just as I need it. Unfortunately, I have a small problem with the tables. My 4th column renders correctly, pulling the LASTMODDATE from the CONTENT table, but sorting doesn't work on it. I can't tell which number it is sorting on. It doesn't make any sense.

    1. I have had to do a data type conversion within my SQL statement to make that work when my date or time is a varchar or nvarchar type in the SQL table (ie convert(date,column) or convert(datetime,column))

      1. Awesome! That worked. I did CONVERT(varchar,LASTMODDATE) and the output was something like Nov 10 2011 9:30AM

        EDIT: Well, it sort of worked. Since I used varchar, the sort thinks that 1:00 is newer than 2:23. Datetime didn't work. I believe the table code already converts timestamp to datetime. Or they render the same. The sort was identical after that, so that's why I went with varchar.

        1. If you mean the sorting isn't working, the date format may not be recognized - see Advanced date sorting or use SQL functions to simplify the date.

  41. I am looking to use the SQL pluging to query data which is working just fine but also looking to be able to export the resulting query to excel or tab delimited text if possible. Any thoughts on how to export the data returned by the query?

      1. what about the export option on the run macro?

        {run-now:autoExport=true|exportFile=^address.html|exportVersion=replace}
        {sql-query:datasource=testDS}
        
        select * from address
        {sql-query}
        
        {run-now}
        1. This only exports the html of the rendered body contents. Useful for capturing a snapshot of the body at a point in time.

          1. is it supposed to export the html output of the SQL query (the table)

            I just keep getting a blank file with this

            {run-now:autoExport=true|exportFile=^export.html|exportVersion=replace}
            {sql-query:dataSource=SiebelQA|escape=true|limit=250}
            select * from siebel.s_repository order by created desc
            {sql-query}
            {run}
            1. Yes. You have a typo - {run-now} at the end.

              BTW - so how are you planning to convert to csv or excel?

              1. It works now with correct closing tag and the exported html will open in excel so that may work, if the export had an option for tab delimited txt file that would be better.

                I am curious if the the above code runs 1 database query and both exports and renders for display.

                I am using so I can write queries to let people run and export as needed.

                1. Didn't know excel could do that for html files (with tables).
                  Yes, the query is runs once.

                  1. Bob, if you ever get a chance, tab delimited csv output would be great as an export option. HTML export and opening with excel sort of works but multi-line text will sometimes spill over into multiple rows in excel when coming from html format.

  42. Hi Bob.

    I am using 4.2.2 version . I am seeing an issue with output type =html. In SQL i have some html code which is displayed  as is without converting it to html presentation.

    
    {sql:dataSource=444|showsql=false}
    select
    '<a href='||url||'>'||title||'</a>' HotTip,
    description,
    testtype,
    discipline
    FROM
    test
    WHERE
    testtype= 'test'
    {sql}
    
    

    This is the output. Please see that it displays all html tags.

    || OTTIP || DESCRIPTION || DATATYPE || DISCIPLINE ||
    | <a  href=http://ttt/epw_well/us/epw_help/HT-W002-DownloadingWellHeaderInfofromPE.pdf>HT-W002-WellHeader-Downloading  Info from P.E.</a> | How to query Power Explorer for Well Header Info | WellHeader | Well |
    | <a  href=http://ttt/epw_well/us/epw_help/HT-W001-Wellbore.pdf>HT-W001-Wellbore</a> | This is a basic how to query a Wellbore tip. | WellHeader | Well |
    | <a  href=http://ttt/epw_well/us/epw_help/HT-W003-FindingScoutTicketsInPE.pdf>HT-W003-FindingScoutTicketsInPE</a> | How to find Scout Tickets in Power Explorer | WellHeader | Well |
    

    Please let me know how to fix this issue.

    Thanks
    Bharathi

    1. Bharathi, you need to use the escape=true option in your sql macro tag, for example

      {sql-query:dataSource=XXX|escape=true|limit=5000|showSql=true}
  43. Keith,

    No it didnot work. no change in the output . it still show html tags.

    Thanks

    Bharathi

    1. It works for me but I am using the sql-qeuery macro and not sql, not sure if that makes a difference.

      1. i tried that too but it does not work .

        1. Likely your site has turned on anti-XSS support - see SQL Plugin 4.1.0 released for how to resolve.

          1. Thanks Bob, It is now working fine . I followed the Macro security plugin instructions. 

            Bharathi

  44. How is it intended to work when heading is greater than 1?

    I expected these two snippets to behave in the same way, but I noticed they don't (with SQL-query, that first data row (the "Total") still participates in sorting):

    {sql-query:datasource=testcase|heading=2}SELECT Name, Value
    FROM (
      SELECT
        'Total' AS Name,
        10 AS Value,
        9 AS SortOrder
      UNION SELECT 'Part 1', 5, 1
      UNION SELECT 'Part 2', 4, 1
    ) t
    ORDER BY SortOrder;{sql-query}
    {table-plus:heading=2}|| Name || Value ||
    | Total | 9 |
    | Part 1 | 5 |
    | Part 2 | 4 |{table-plus}

    I considered using autoTotal=true, but I don't think it will work for my application, because I need some of the items in my "Total" row to be averages.

    Thanks!

    1. The code forces heading=1 when heading > 1. I am not sure what the consequences of allowing that to be more than 1. Write up an issue and perhaps it can be changed. Certainly, if we let it through to the page, then the sorting would work the same as the non-sql table.

      As a workaround you can always surround it with {table-plus:heading=2} ... {table-plus}.

      1. Thanks! I created SQL-144.
        I tried that workaround, but it made the table non-sortable.

  45. hi people :) good morning,

    can you unsure us that SQL Plugin remains compatible with confluence 4.1.9?

    thanks in advanced

    best regards

    1. Works in compatibility mode on Confluence 4.x. See the compatibility news item at the top of this page.

      1. thanks for your faster answer

  46. Is it possible to set a default values for sql plugin per page ?

    E.g. Set defaults to {sql:dataSource=blah|output=wiki|table=false}

    and then use only {sql} ... {sql} ?

    1. Not at this time. Please create a improvement request. Run CLI Actions in Confluence has profiles - perhaps something like that would be useful here.

  47. Great plugin, currently setting it up to provide stats on our databases for management types, who are pleasantly pleased with the ouput.

    However I have come across a strange problem demonstrated buy the 2 snippets below:

    {sql:datasource=myinformationschema||output=wiki| columnLabel=true}
    
    SELECT TABLE_SCHEMA FROM information_schema.`TABLES`
    {sql}

    &

    {sql:datasource=myinformationschema||output=wiki| columnLabel=true}
    
    SELECT TRIGGER_SCHEMA FROM information_schema.`TRIGGERS`
    {sql}

    I've pruned these down to their simplest form and I can repeat the results on 2 different confluence installs.

    The first instance produces the output as expected, however the second instance produces nothing.

    Does anyone have any idea why this might be occuring?

  48. we're going to upgrade to confluence 4.2, when we’d like to know if this plugin run ok on this new version. I look on compatibility table and 4.2 isn’t mentioned.

    We love this plugin, together with Run is :D and we don’t want to lose it!

    1. Sorry, missed notification on this. Yes it is compatible and it is marked as such since about May 5. See the notice on the dashboard about Confluence 4.x compatibility

       

  49. Any help will be greatly appreciated so thank you in advance!!! This is an awesome plugin, but my company is running into an issue with large queries.  After a query executes for a number of minutes (depending on your datasource "maxWait" setting as well as your "hibernate.c3p0.timeout"" property in your confluence.cfg.xml file), I get the same error message printed to the screen regardless of these settings.  The error message printed to the screen says, "Rendering this content exceeded the timeout of 120 seconds". In the Confluence log it says: "com.atlassian.confluence.content.render.xhtml.XhtmlTimeoutException: Rendering this content exceeded the timeout of 120 seconds." Is there any way to adjust this setting so large queries will run? 

    Note: A large query would be around 10-20k rows.

    Kind regards,
    Eddie 

    1. That is a Confluence rendering thing. I am not sure about that. Try using the future macro and for future reference when you get it working, make sure you are using the Cache for Confluence (smile). I would suggest you post your question where the Atlassian guys and others hang out on https://answers.atlassian.com since it isn't specific to sql although certainly that is good way to hit the limit (sad).

  50. Pages that have not been updated for a year or more

    I'm adding this comment because a user asked the following question in a comment on the Confluence Storage Format page:

    Is there any way to list up the entire documents which is not updated one year? And with author name?

    Rather than adding this comment to that page, I thought it more appropriate to add it here. (I will add a comment to the Confluence Storage Format page that links to this comment.)

    The following {sql-query} macro generates a table of pages that have not been updated for a year or more. (At least, I think it does; that is what I mean it to do.)

    {sql-query:jndi=ConfluenceDS|output=wiki}
    select '[' || A.title || '|' || S.spacekey || ':' || A.title || ']' as "Title", S.spacename "Space", to_char(B.lastmoddate, 'YYYY-MM-DD') as "Last updated", A.lastmodifier As "User"
    from CONTENT A
    inner join
    (
    select title, max(lastmoddate) lastmoddate from CONTENT
    where contenttype='PAGE' and lastmoddate < date_trunc('month', current_date) - interval '1 year'
    group by title
    ) B
    on A.title = B.title and A.lastmoddate = B.lastmoddate
    inner join spaces S
    on A.spaceid = S.spaceid
    order by B.lastmoddate
    {sql-query}

    The generated table contains the following columns:

    • Title
      Page title (formatted as a link to the page)
    • Space
      Name of the space to which the page belongs
    • Last updated
      Date of last modification
    • User
      ID of user who last modified the page

    The table is sorted in "Last updated" order: that is, oldest pages first.

    This query was developed and tested using Confluence 4.2 with a PostgreSQL 8.4.7 database and SQL plugin 4.2.2.

    Please feel free to snigger at the SQL and suggest corrections or improvements. In particular, I would appreciate suggestions for better techniques for returning only the most recent modification of a page (older than a year). I delve into SQL every few years, and then forget most of it until next time, rather like a sheep being "startled to see the sun rising in the morning, and astonished by all the green stuff in the fields" (Douglas Adams).

    Sincere thanks to Bob Swift for the SQL plugin.

    1. Hi Graham - great post!

      I got it working with MySQL database.

      Thanks,

      Doods

  51. Hi Bob,

    I hope it is not too much to ask, but could you please help me get the following data displayed using your SQL Plugin?

    1. 10 most visited pages
    2. 10 most watched pages (including the number of users watching these 10 pages)
    3. 10 most viewed pages
    4. 10 most active users
    5. list of users who have not logged in for the last month
    6. number of daily log-ins
    7. number of hits based on page family/structure - so if I have A, B, C parent pages and they have child pages A1, A2, B1, B2, C1, and C2, is it possible to display the sum total for A (incl A1 and A2), B (incl B1 and B2) and C (incl C1 and C2)?

    Thanks in advance.

    Doods

    1. Sorry, don't have time. Perhaps someone has done one or more similar things. Site Statistics might have some additional pointers.

  52. Hi Bob

    Great product your plug ins really make the difference to Confluence.

    I'm not sure if this question is best asked here or under the Run macro.

    We have scenario where we have a template for each customer and on a particular page it may execute half a dozen sql statements or so......

    What we would like to have is a table at the top of the document with the customer code in and then use this as a parameter throughout the rest of the page.

    Key things here are we don't want the ask the user for input.

    We don't really want our end users editing SQL / Run macro etc.

    I have seen the run macro with the URL parameters option but we ideally want the page to just load first time after a search with no additional buttons or actions required by the user.

    I hope you can help 

    Thanks

    Chris

    1. Yes, this is more of a run question. There are a few options. Is the page just for view by customers?

      1. Use run-now with the customer number hard coded on the page.
      2. You can use the url capability to have one page that gets viewed where the customer number gets passed in - again use the run-now macro or the equivalent parameterization of the run macro to not show parameters, etc... Use page permissions to restrict edit.
  53. Hi Bob,

    I'm wondering if you can use the result of an SQL query as table in a second SQL query ?

    Like this for example which should list users having the same email address in JIRA and Confluence:

    {sql:dataSource=ConfluenceDS}select C.user_name, C.email_address from cwd_user C, ({sql1:dataSource=JiraDS}select email_address from cwd_user{sql1}) as J
    where J.email_address = C.email_address{sql}

    I tried with the different options: render, table, macros, but I did succeed.

    1. Yes, possible but more complicated. For the general case, you need to use some scripting - Scripting for Confluence to render the first sql and then using that data construct additional sql statement(s). In your case, it is a bit easier.

      1. First, you must use macros=true in the first (outer) sql to get embedded macros rendered before rendering the outer sql
      2. The inner sql must use a different macro than the outer sql - that's a Confluence macro restriction. So use sql-query for one and sql for the other.
      3. Construct a query that produces a list of email addresses from JIRA and use table=false and output=wiki to produce a list like: 'address1', 'address2', ...
      4. Modify your first (outer) query to select from the list.
      5. Test each select separately and then combine.
      6. If you need the to be more precise on the userid and email address combination, you need to make the sql a bit more complicated by concatenating the user id to the email.  
      {sql-query:dataSource=testDS|macros=true}select * from test01 where a1 in (
          {sql:dataSource=testDS|table=false|output=wiki} select ' '' ' || a1 || ' '', ' from test01 {sql}
      'xxxxxxxx')
      {sql-query}
  54. Thank you,

    It is working.

  55. Bob,

    i am using DISTINCT regression but can get the total for status = 'PASSED' but not the TOTAL for COUNT(ID) which is nothing but the total of test cases for that DISTINCT regression

  56. Hi BOB,

    Is there a way i could display the STATUS = 'FAILED and STATUS ='PASSED' as different columns and then add up the total

     

     

    1. Yes, I think so. Use a sql case statement probably.

  57. Hello, 

    I'm hitting up a filemaker database, having no problems until i attempt to get some data out of a container field. The container field contains a reference to a jpeg in the filesystem of the system the database is on, here's where i am currently (dummy query):

     

    SELECT
    
    nameLast AS "Surname"
    ,nameFirst AS "First Name"
    ,'%department' AS "Department"   
    
    FROM
    staff
    
    WHERE (various where conditions based on a RUN macro that encapsulated the SQL)

     

    This all works lovely, until i try and get the photo out (TBH i'm not expecting it to work like this and guessing i'm going to have to put up a http server on the DB box that i can reference the files from)

    I've tried:

     

    SELECT
    
    GetAS(photo, 'JPEG') AS "Picture"
    
    FROM etc....

    And it gives me the file, but the raw file, like you've opened a jpeg as text, if you know what i mean. Read this: http://www.skeletonkey.com/FileMaker_Container_Fields_JDBC and was inspired to try something with Groovy but then realised that's likely what you've done and the issue is Confluence rendering the data.

    Any pointers appreciated.

    Regards,

    james.

     

    1. I would suggest making the column an image reference using a url based on the name served up as you suggested. 

      1. Did exactly that and it works very well. The Filemaker server is using IIS for administration so i just added a new server and set the base dir to the location of the pics, very simple query but thought the markup may be potentially useful:

        {sql-query:dataSource=DATASOURCE|output=wiki|table=false|macros=false}
        
        SELECT
        '{div:style=width:400px;padding:5px;float:left;}{div2:style=float:left;}{img:style=padding:5px;|src=http://hrpics/' + "%photopath" + '}{div2}'
        ,'{div3:style=padding:8px;}*' + nameFirst||' '||nameLast||' \\'||jobTitle||'* \\'||phoneWork||' \\'||phoneMobile||' \\'||"%department"||' \\'||officeSite + '{div3}{div}'
        
        FROM
        sqlStaff
        
        WHERE "archive" <> '1' OR "archive" IS NULL 
        
        ORDER BY 
        
        nameFirst
        
        {sql-query}

        %photopath is a calculated field that the Filemaker dev created for me, it's just the filename, i pre-pend the server. This layout is specifically  to get images and details in blocks and running across the page as a list, create a table and use table filter and you have pretty much an instant (if very simple) people finder.

  58. {report-on:@self|injected=true}
      {sql-query:dataSource=bluetooth|border=1|showSql=true|table=true|sortDescending=true|noDataError=true|noDataMessage=No Data Found|limit=500|sortIcon=true|sortColumn=1|autoTotal=true|autoNumber=true|columnTypes=S,S,S,S,S,S,S,I,I,I}
      SELECT
      x.ID AS ID ,
      x.workspace_user AS 'Workspace User',
      x.workspace AS 'Workspace',
      x.regression AS 'Regression' ,
      x.implementation AS 'Implementation',
      x.dir AS 'Directory', '<a target="regression" href = "http://confluence.broadcom.com/display/~vijayab/tst1/?rID='+ x.ID +'"class=' + 'x.ID" > x.ID </a>' AS 'ID' , 
     
      (SELECT COUNT(*) FROM BCM4350_btfm_A0_tst_table WHERE regression_id=x.ID AND status='PASSED') AS PASSED,
      COUNT(*) AS Total
    FROM (SELECT * FROM BCM4350_btfm_A0_reg_table ) x
      LEFT JOIN BCM4350_btfm_A0_tst_table y ON x.ID = y.regression_id
    GROUP BY x.ID, x.workspace_user, x.workspace, x.regression, x.implementation, x.dir, y.regression_id
      {sql-query}
    {report-on}

    Hi BOB ,

    I am trying to add the HTML HREF tag to the above query , which works for any other column inthe table but not the column name ID 

    Gives me no data found message 

    any idea why ?

    1. Sorry, no. Run the query in a query tool.

    1. If I understand correctly, you only want one row for each regression, so don't you need to do a distinct on the regression column?

    1. I think you need to single quotes for the literal values: '%variable:run_user%', '%variable:workspace_user%'. In any case, when debugging SQL statements inside complex replacements like this, use showSql=true to see the exact SQL that is being run. It is more obvious to find errors or you can copy/paste it into an sql tool that gives better error messages. 

  59. Can you declare local variables and local tables inside the mySQL connector?  I can to it with the msSQL connector, maybe I dont understand SQL well enough.

    if i use the declare statement inside sql call I get a syntax error, if i use the same declare call within the sql-query call I get a read only error.

     

    Thanks!

     

  60. Howdy. I'm writing a program to update a Confluence page with numerous SQL macros. In the new storage format, I believe the body of a macro like this (where the SQL might go) would be 'plain-text', but I don't see anything specific in the docs for the SQL macro that addresses this. Is the following sample correct?

    <ac:macro ac:name="sql-query">
    	<ac:parameter ac:name="dataSource">myDatabase</ac:parameter>
    	<ac:plain-text-body><![CDATA[select * from table;]]></ac:plain-text-body>
    </ac:macro>
      1. Groovy. Thanks!

        Now I'm trying to the folks on the Confluence side to document/confirm how to problematically put a macro in a macro. (smile)

        1. To embed a macro in the body of SQL macro, you will need wiki markup and macros=true. This is supported but is really an advanced/limited use case scenario. 

          1. I am hereby officially hoping I never have to embed a macro in an SQL macro. (smile)

            I think a much more common case would be embedding the SQL macro in the Chart macro, and I'm not seeing anything in the documentation of the new storage format for that.

            In my case, I'm updating a program that:

            • Creates a series of views in the database, and then
            • updates a series of pages on the wiki to use the updated views.
            • Those pages contain chart macros that contain SQL macros.

            I know the Chart macro isn't your responsibility, but any hints on how to put the SQL macro in the Chart macro would be appreciated.

            If the Atlassian folks don't have anything to say about this, I guess my next step would be to create a sample page with just the Chart/SQL macro combo, extract the storage format using the API, and examine the entrails.

            1. I helped develop and owned the chart macro for some time before Atlassian started to support it directly, so I still feel invested in it (smile). The chart macro doesn't care how or who created the table(s) that it is using. I would just look an example and the resulting storage format and go from there.

  61. This SQL plugin is very useful. I don't know SQL so have a help request but in return here are a few uses I've come across that i've found helpful:

    Identify JIRA Roles Where a Group is Used
    {sql-query:dataSource=JiraDS}select * from projectroleactor where roletypeparameter = 'GROUP NAME';{sql-query}
    Identify JIRA Global/Project/Security Schemes Where a Group is Used
    {sql-query:dataSource=JiraDS}select * from projectroleactor where roletypeparameter = 'GROUP NAME';{sql-query}
    Identify JIRA Global/Project/Security Schemes Where a Group is Used
    {sql-query:dataSource=JiraDS}select * from projectroleactor where roletypeparameter = 'GROUP NAME';{sql-query}
    Identify Duplicate Usernames in JIRA
    {sql-query:dataSource=JiraDS}select id,username from userbase where username in (select username from userbase group by username having count(username) > 1) order by username{sql-query}
  62. So here's my help request:

    I'd like to use JIRA to trend our Defect Containment Effectiveness as a quality metric. The data is all there, but in separate places so I need to use something like the SQL macro to pull it together then put a nice chart macro over it for dashboarding.

    To do this I need to report on all defects created after the release date of any of the product versions they affect. In our instance we have:

    • Change Request as our issuetype where defects are tracked.
    • Created is a JIRA field on the JIRA issue (Change Request)
    • Affected Version is a JIRA field on the JIRA issue (Change Request)
    • Request Type is a custom field on the JIRA Change Request with Defect as the option of interest.
    • Escapes is a custom field on the JIRA Change Request used to indicate if a defect is an escape (for those that aren't properly entered to be tracked only by the dates)
    • Release Date is a JIRA field on the Project.

    How can I pull together a count of all defects (Request Type=Defect), by project, that were either

    • created after the release date of the version(s) in the project they affect 
      or
    • have Escapes = Yes

    The following generates a nice report of all the projects and versions 

    SQL to pull project and version
    {sql-query:dataSource=JiraDS|output=wiki}
    select p.pkey, p.pname, v.vname, v.releasedate from project as p, projectversion as v where p.id = v.project order by p.pkey{sql-query}

    And I've used this to pull in a count of JIRA issues by project where a specific field is not empty (that field does not relate to what I need here but when I try to modify it to Request Type = 'Defect' it doesn't work so I left the code as is as it works, albeit not for my purpose:

    SQL for Counting JIRA issues with a specif custom field not being null
    {sql-query:dataSource=JiraDS|output=wiki}select
    project.pname as "Project",
    count(*) as "Total # of Issues"
    from
    jiraissue,
    project,
    customfield,
    customfieldvalue
    where
    project.id = jiraissue.project
    and customfieldvalue.issue=jiraissue.id
    and customfieldvalue.customfield = customfield.id
    and customfield.cfname='SF Account'
    and (
    (customfieldvalue.stringvalue IS NOT NULL))
    
    group by
    project.pname;{sql-query}

     

    But I need to pull these together into one report counting the total # of issues grouped by project with Request Type=Defect and (created after the release date of the version(s) in the project they affect) or (Escapes = Yes)

    Not sure if this is needed - but Request Type custom field id is 10657. Escapes custom field id is 10941.

    I know SQL can join - i just don't know how (sad). Any help is GREATLY appreciated.

    1. Hi Brent,

      This might get you a little closer:

      select *
        from (select p.pkey, 
                     p.pname, 
                     v.vname, 
                     v.releasedate 
                from project p, 
                     projectversion v 
               where p.id = v.project) x,
             (select p.pkey,
                     count(*) as "Total # of Issues"
                from jiraissue j,
                     project p,
                     customfieldvalue cfRequestTypeValue,
                     customfieldvalue cfEscapesValue
               where p.id = j.project
                 and cfRequestTypeValue.issue = j.id
                 and cfRequestTypeValue.customfield = 10657
                 and cfRequestTypeValue.stringvalue = 'Defect'
                 and cfEscapesValue.issue = j.id
                 and cfEscapesValue.customfield = 10941
                 and (cfEscapesValue.stringvalue = 'Yes' OR release date logic)
               group by p.pname) y
       where x.pkey = y.pkey
       order by x.pkey

      I'm not sure how the release date logic works in JIRA, so I left that part out. I'm also not familiar with the database structure from JIRA, but from what you have in your query this looks like it should work to me. This joins your two queries together based on the project key, and displays the results of both.

      1. Thank you so much Jimmy! I'm sure this is very close but it is giving this error: sql-query: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "project" Hint: Perhaps you meant to reference the table alias "p". Position: 787

        I've tried playing with it a bit to correct but am real SQL naive. If you know what might be causing the error I would really appreciate any direction there.

        Thanks again!

        1. I've updated the query. You are correct, the group by statement was incorrectly referencing the table alias p. I see from the error you are getting that you are using a PostgreSQL database, unfortunately i'm not familiar with that database type. Hopefully that change fixes the error for you. If you get any more errors i'll try and help you out but I can't promise anything.

          Thanks,

          Jimmy

          1. Thanks again! Tried it and now getting this error: sql-query: org.postgresql.util.PSQLException: ERROR: syntax error at or near "  from" Position: 11

            Let me know if this is something obvious to you or if it is a postgres specific thing that I can do some googling on. Thanks again - really appreciate you getting me at least this close!

            1. I don't see anything obviously wrong with the syntax. My only guess is that it wants single quotes rather then the double quotes.

              So 

              count(*) as "Total # of Issues"

              may need to be

              count(*) as 'Total # of Issues'
              1. double quotes is correct postgres syntax

              2. I tried this and still getting the same error except now says near position 13: sql-query: org.postgresql.util.PSQLException: ERROR: syntax error at or near "  from" Position: 13

                I tried changing "from project p" to "from project as p" (and the same with the from jiraissue j to from jiraissue as j) but that didn't do it.

  63. Hi Bob,

    I'm very impressed with what you have here and to date have learned a lot from it.  Thank you.  However, as you probably guessed, I come with a request.


    I really hope you can help as this is the last step I need to get the report data necessary for a client.  Failing this its manual effort as I cant set up a link between the two datasources as they reside on different servers with different owners.

    I have implemented the run macro with a SQL-Query macro embedded in it and a SQL macro called within the SQL-Query macro as per earlier information on this page.

    I have the following query put together which sits inside a SQL-Query macro.  I'm trying to use the information from a query on the datasource in the SQL-Query to feed into a SQL macro with another datasource.  The problem is I'm not sure how I can pass that information to the SQL macro

     Presuming the SQL-Query macro returns something similar to the following, how do I populate the parameter in the SQL macro .  I tried various methods in vain is below.

    DECLARE @defectTable TABLE
    (
      id INT,
      Info VARCHAR(20),
      COUNTER INT IDENTITY(1,1)
    );
    INSERT @defectTable
    SELECT 10105 , 'Stuff about id 10105 '
    UNION ALL SELECT 10106, 'Stuff about id 10106'
    UNION ALL SELECT 10107, 'Stuff about id 10107'
    UNION ALL SELECT 10108, 'Stuff about id 10108'
    UNION ALL SELECT 10109, 'Stuff about id 10109'
    UNION ALL SELECT 10110, 'Stuff about id 10110';

    DECLARE @idx INT, @cnt INT, @Var INT;
    SET @idx = 1;
    SELECT @cnt = COUNT(*) FROM @defectTable;
    WHILE @idx <= @cnt BEGIN
    SELECT @Var = id
       FROM @defectTable
      WHERE COUNTER = @idx
    DECLARE @result table
    (
    info VARCHAR(20)
    )
    INSERT @result
    SELECT Info FROM @defectTable where id in (
    {sql:dataSource=DS1|table=false|output=wiki|p1=@Var}SELECT convert(varchar,userstoryid,10) from  table where id in (?){sql}
    )


    SET @idx = @idx + 1
    END;

    I get the error Error rendering macro 'sql-query' : java.sql.SQLException: Parameter #1 has not been set. However if I replace @Var with 10105 it works returning a table with 6 rows containing 10105. 

    If I can get this working then I hope to use similar ideas to have the DS1 data output directly in the Select statement.

     

    1. Thanks for the compliment :). You are certainly trying a very advanced example. I believe what you are trying to accomplished is possible, just difficult to get everything all correct or perhaps requires other similar techniques (scripting covers the most complex cases). Just sticking with the current approach for now, may I suggest you verify the sql macro statement generated includes the proper replacement. Use showSql=true and also have the sql output as plain text to help verify. Sorry, don't have time to go deeper this week, but if you are still having problems, open a support issue.

      1. Hi Bob,

        I’ll do my best and let you know either way.  Thanks for the speedy reply.

         

        Cheers

        Tomás

         

  64. Since they are different databases, SQL alone will not work. You need some scripting or similar. Here is an quick example using groovy from Scripting for Confluence. Need to put together a how to on this. Overview

    1. First render the sql that produces a comma separated list of values from the first datasource
    2. You need to quote strings (sql syntax) but not if they are numeric
    3. Construct a second sql statement using the values from the first
    4. Make a sql-query macro using the SECOND datasource (this example just uses the same datasource for testing convenience!)
    5. Render the second sql-query macro as normal
    6. showSql=true helps debug your render sql
    Example rendering sql results for use in a second sql
    {groovy:output=wiki}
    import com.atlassian.renderer.v2.RenderMode
    
    def sql = "select '''' || a1 || ''',' from test01"
     
    def macro1 = "{sql-query:datasource=testDS|table=false}${sql}{sql-query}"
     
    def values = subRenderer.render(macro1, context, RenderMode.suppress(RenderMode.F_FIRST_PARA))
    values = values.substring(0, values.size() - 1)  // strip off extra , at the end
     
    def macro2 = "{sql-query:datasource=testDS|showSql=true}select * from test01 where a1 in (${values}){sql-query}"
     
    println macro2  // to debug, surround with noformat macro until the sql-query macro looks right
    {groovy}
    
    1. Hello,

      Thanks for the really very quick answer. I have I think everything to do it, one only concern. I have a database where fieldname are composed with blanks inside (I know, that's bad, but it's not from me) so in sql I use to pass the field value enclosed in double quote like "project No" .... Using this do not work. Any further idea ?

      Thank you

      1. I'm not sure if I understand correctly. Are you trying to say when you use a WHERE clause you're selecting "project No"? Have you tried using single quotes?

        1. Hi Norbert,

          my query is :

          select "No PROJET" from table projet

          my database use + as concatenator so the sql should be written, with the commas

          select "No PROJET"+','  from table projet

          The groovy compiler is not happy with that, he believes that the end of the string is the first " encontered...

          If I exchange single quote against double quotes, same issue, my database complains if it gets double quotes string as constant...

          1. What kind of DBMS are you using? Are you able to use the || operator?

            1. I am using a Filemaker PRO... not knowing the  operator so the workaround will be to make a view on it with friendly names...

          2. Groovy has many ways to handle variables and quoting - simple way is to escape any embedded double quote like so: \"

            1. Hi Bob,

              Thank you so much. I finally get it working using the escape character. It's now really perfect, and I have also combined with a run macro. Just great how with very few lines you can do wonderful small reports. Thanks also to Nobert for the help.

  65. Hi Bob. I'm trying to use the sql query macro within a user defined macro in confluence.

    The following works when I use the sql query macro within confluence but not when I embed the sql query macro in a confluence user macro. It’s a simplistic example but it should give you an idea of what I am trying. Do you know a way to define a temporary table within a user macro and use the exec() command to execute it? I like to use this as it allows me to build up a dynamic query when the sql query macro is used in conjunction with the run macro

    CREATE TABLE #Table (  Info VARCHAR(MAX) ); INSERT #Table  Select 'w' union all select 'e'; DECLARE @SQL as VARCHAR(MAX) SET @SQL = 'select * from #Table' exec(@SQL) drop table #Table

    1. I don't know why you would see a difference with the same macro being wrapped by a user macro. You didn't say how it didn't work. I am not familiar with the technique you are using, so I can't help there. As far as *build up a dynamic query*, in many cases, that can be done with parameter markers or just substitution.

      1.  

        You are 100% right, I did not give you info on how it did not work (smile) or what the nature od the dynamic query is.
        So I'm getting an error within the user macro stating that 'Error rendering macro 'xx_issue_status_reporting' : Error occurred rendering template content'

        And by a dynamic query I mean one that lets the user decide what columns of the resultant temporary table, of which there could be 10-15 are outputted to them along with hyperlinks to the relevant issues within JIRA

        1. That error would indicate a problem with what the user macro is outputting. User macros can be tricky to debug. Usually trial and error on narrowing down what is causing the problem by commenting out stuff until it works and then adding stuff back in.

          1. Its the statement CREATE TABLE #Table (  Info VARCHAR(MAX) );  that is causing the problem.  Here is the full snippit.

            </ac:structured-macro> <ac:structured-macro ac:name="sql-query">   <ac:parameter ac:name="dataSource">jira</ac:parameter>   <ac:parameter ac:name="atlassian-macro-output-type">BLOCK</ac:parameter>   <ac:parameter ac:name="output">wiki</ac:parameter>   <ac:plain-text-body><![CDATA[ CREATE TABLE #Table (  Info VARCHAR(MAX) ); ]]></ac:plain-text-body> </ac:structured-macro>