Skip to end of metadata
Go to start of metadata

 

 

Archive

Icon

This page has been replaced by Data source configuration - application server and Data source configuration - problem determination. Some of the information is out of date with respect to new releases of the plugin. However, we have kept the page as it has comments and answers that may still be helpful.

 

Summary

The SQL Plugin for Confluence uses datasources defined to the application server to gain access to the database. The datasource is refenced using the datasource parameter. The exact configuration of a datasource is application server and database specific. The configuration must be done by a Conflunece administrator and the Confluence application server must be restarted for changes to be active. The configuration must be done very carefully as any errors will result the datasource being unusable (sad). Each database you need to access in Confluence needs to a have a datasource configuration.

These are the major elements of the configuration

  1. Application server datasource configuration - consult your application server documentation on how this is done and problem determination mechanisms. Note that the configuration may be specific to the application server version. For example, older Tomcat versions have some different configurations.
  2. JDBC driver specific for the database to be accessed - consult your database documentation and other sources for the best JDBC driver to use.
  3. Installation of JDBC driver on the application server
  4. Database access - consult your database documentation on how to configure your database for access

Various users have provided some examples that covers many standard situations. This page can be used to share that type of information as well as problem determination information. This is the best place to start especially if you are not an expert in this area.

Just Upgraded Confluence?

Icon

A common problem is errors after upgrading Confluence. Ensure that your upgrade procedures include updating your server.xml and copying all the JDBC drivers you use into the appropriate location. Also, check out this page for isolating these or application server version differences that cause problems. If you get errors immediately after an upgrade, this is the most likely cause.


Confluence standalone installation (Tomcat)

Many users install the standard Confluence standalone distribution. This section will discuss configuration specific to this application server environment. The most recent versions of Confluence use newer versions of the Tomcat application server where the following configuration examples apply:

JDBC driver location

Put the driver jar in <install-dir>/lib - this is the directory that has other tomcat jars. If you have an older version of Confluence that does not have this directory, look for <install-dir>/common/lib instead.

Tomcat configuration

Modify <install-dir>/conf/server.xml to add one or more datasource resource elements. server.xml is an example of adding a few datasources for various local and non-local databases.

The essential element is something like the following where the datasource name is myDS:

Resource must be within the confluence context tag

Icon
...
<Context path="" docBase="../confluence" debug="0" reloadable="false">
...
<Resource ... />
...
</Context>


Database examples

The sql macro markup would be: {sql:datasource=myDS| ... }

Database

Information

JDBC driver example

Resource configuration

PostgreSQL

PostgreSQL JDBC

postgresql-8.2-507.jdbc3.jar

<Resource name="jdbc/myDS"
    auth="Container"
    type="javax.sql.DataSource"
    username="confluence"
    password="confluence"
    maxActive="100"
    maxIdle="10"
    driverClassName="org.postgresql.Driver"
    url="jdbc:postgresql://localhost:5432/confluence"
    validationQuery="Select 1"
/>

MySQL

MySql connector

mysql-connector-java-3.1.14-bin.jar

 <Resource name="jdbc/myDS"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost/mydatabase?autoReconnect=true"
    username="username"
    password="password"
    maxActive="25"
    maxIdle="5"
    maxWait="10000"
/> 


See SQL-146 for info on how to configure multiple result sets.

Oracle

Thin Driver
Oracle JDBC

ojdbc6.jar

<Resource
    name="jdbc/myDS"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="oracle.jdbc.OracleDriver"
    url="jdbc:oracle:thin:@www.some_server.com:1521:mysid"
    username="username"
    password="password"
    connectionProperties="SetBigStringTryClob=true"
    maxActive="25"
    maxIdle="5"
    maxWait="10000"
/> 

Oracle Advanced

Thin Driver
Oracle JDBC

ojdbc6.jar

See Tomcat 6.0 using Oracle Universal Connection Pool

Microsoft SQL Server

jtds driver

jtds-1.2.2.jar

<Resource
    name="jdbc/myDS"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="net.sourceforge.jtds.jdbc.Driver"
    url="jdbc:jtds:sqlserver://mysqlserver:1433/mydatabase"
    username="username"
    password="password"
    maxActive="20"
    maxIdle="10"
    maxWait="-1"
/> 

DB2 for IBM i

JTOpen

jt400.jar

<Resource
    name="jdbc/myDS"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="com.ibm.as400.access.AS400JDBCDriver"
    url="jdbc:as400://ibmi;prompt=false;translate binary=true; extended metadata=true"
    username="username"
    password="password"
    maxActive="20"
    maxIdle="10"
    maxWait="-1"
/>

Derby

Derby

derbyclient.jar

<Resource
    name="jdbc/myDS"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="org.apache.derby.jdbc.ClientDriver"
    url="jdbc:derby://localhost:1527/dbname"
    username="username"
    password="password"
    maxActive="20"
    maxIdle="10"
/>

Other databases

Support for other databases depends on the capabilities provided by the JDBC driver. The SQL Plugin for Confluence expects certain JDBC features to be available. In some cases, parameter choices can affect which capabilities are needed. User testing and community support is about the only help that is available for less popular databases/JDBC drivers. If you have had success with other databases, please add information to this page.


WebLogic 9.1 Application Server

  1. In the WebLogic console, under Services > JDBC, create a data source (see WebLogic's documentation on how to Configure JDBC data sources for more information).
  2. Take note of the JNDI name you used to create the data source.
  3. Point the SQL macro to that JNDI name (datasource parameter).

Problem determination

Here are some common errors and examples.

  1. Double check all the configuration values and that JDBC driver is installed - use the information above
  2. Make sure you restarted Confluence after making configuration changes
  3. Make sure your database is configured from remote access from the Confluence server with the user and password provided. If you have problems, connect using an independent SQL client from the server to verify access. Consult your database documentation for how to configure remote access and database permissions

Error - no JDBC driver

  • Check your driver location. The driver must be installed in a location that is on the classpath of your application server. For standalone installations, that would be <install-directory>/lib.

    sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException: 
    org.apache.tomcat.dbcp.dbcp.SQLNestedException: 
    Cannot load JDBC driver class 'net.sourceforge.jtds.jdbc.Driver'
    

Error - database server not found

  • Check your application server (server.xml) configuration and ensure the DNS host name is valid on the server running confluence

    sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException:
     org.apache.tomcat.dbcp.dbcp.SQLNestedException:
     Cannot create PoolableConnectionFactory (Unknown server host name 'myserver'.)
    

Error - port

  • Database may not be listening on the requested port - verify port configuration in server.xml and database

    sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException:
     org.apache.tomcat.dbcp.dbcp.SQLNestedException:
     Cannot create PoolableConnectionFactory (Network error IOException: Connection refused)
    

Error - user authentication

  • Application server configuration (server.xml) has the wrong password

    sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException:
     org.apache.tomcat.dbcp.dbcp.SQLNestedException:
     Cannot create PoolableConnectionFactory (FATAL: password authentication failed for user "bad")
    

Error - validation query

  • Validation query not supported or incorrect syntax - remove the validation query or ensure it is correct for your database

    sql: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Syntax error: Encountered "<EOF>" at line 1, column 8.)
    

Error - user authority

  • User does not have permission to the database or table - modify your database configuration

    sql: org.postgresql.util.PSQLException: ERROR: permission denied for relation test01
    

Error - bad server.xml configuration

  • The resource configuration is outside of the confluence context - change your application server configuration (server.xml) to ensure the resource is within the confluence context

    sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException:
    org.apache.tomcat.dbcp.dbcp.SQLNestedException: 
    Cannot create JDBC driver of class '' for connect URL 'null' 
    

Error - class not found

  • After upgrading Confluence or your application server, this error can occur if your datasource configuration is out of date. Look for something like factory="org.apache.commons.dbcp.BasicDataSourceFactory" in your server.xml. Either remove it or rename it to factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory". Reference is SQL-68.

    java.lang.ClassNotFoundException: org.apache.commons.dbcp.BasicDataSourceFactory
    

Error - Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)

  • Make sure your database server is accepting connections from the Confluence server TCP address and user is authorized to connect.

Error - MyDS not found

  • The datasource names are case sensitive, make sure you use exactly the same name as what you have configured!

Non-standard JDBC drivers

If you are using a non-standard JDBC driver for accessing less common databases, there are potential problems with unsupported or non-standard behavior. Experiment on a test instance to ensure it works as expected. Here are some workarounds for problems you might encounter. If you continue to have problems, you will need to debug the code on your installation.

  1. Hangs - SQL-86 is an example. Use multipleResults=false to disable multiple results sets.
  2. Avoid using horizontal rowOrientation

Broken database connections - java.sql.SQLRecoverableException

Add a validation query to your configuration. This helps the application server connection pool recover from database connection problems.

validationQuery="Select 1"

Other errors

For other errors replated to connectivity, google your symptoms and application server combination

SQL exceptions

Consult your database documentation as SQL has some database specific differences (sad). It is best to validate your SQL outside of Confluence before putting it on a page. Use a database admin tools (pgAdmin for instance), some general SQL client, or various Eclipse database plugins.

  • No labels

54 Comments

  1. If you encountered the following errors while starting up Confluence,

    Please add the quotes for MySQL as below

    1. I corrected the formatting of the configuration examples. All should have quotes.

      1. Hi Bob, thanks :)

  2. HI Bob,

    I've gone through this 5 plus times and I keep getting the following error:

    I'm also thinking of giving up.

    Help appreciated!

    1. You can create a issue with more details if you like. I suspect your datasource configuration might be ok, but perhaps your database end is not right. Try setting up a JDBC connection from the same server as Confluence to the same database using your favorite jdbc database tools (example: SQuirreL). Probably easier to sort out your problem there. You can google your symptoms and find references like http://confluence.atlassian.com/display/DOC/Configuring+a+MySQL+Datasource+in+Apache+Tomcat?focusedCommentId=191004756#comment-191004756.

      1. Thanks for the quick response. I've read the page you referenced and googled.

        I'm running Confluence 3.3, Tomcat 6

        My DB is MYSQL 5.1 running on the same server via MAMP (all on OS X), I can connect to the db with a client app although -I have to change the mysql socket path, so could the mysql socket path not being standard be the problem. – I created a symlink for the mysql socket and it did not change a thing.

        Thanks

        1. Sorry, I am at the end of my knowledge on this.

  3. I configured sql macro in server.xml and confluence.cfg.xml and it works in general on conf. 3.4.6. with MySql 5.1. If I now include symbols or special characters within a page I get an error saying that the page cant be stored. Without such a symbol or special character I have no problem. I works also by oing back to prior sql server connection.

    What do I have to configure in addition that symbols and special characters do work?

    1. Storing of a page is a Confluence function, so you should contact Atlassian support.

      1. Thanks for your hint. So I asume that this behavior is odd and not related to configuration – I will raise an issue. 

  4. Bob,

    I use Confluence 3.3.1 - One-Click Evaluation Installer (EXE) for windows, I am having a hard time figuring out where to add datasource.

    I don't see server.xml in C:\Documents and Settings\royce\Application Data\Atlassian Evaluation\work\confluence\3.3.1\tomcat\conf, but I do see tomcat-users.xml and web.xml.

    There is nothing in C:\Documents and Settings\royce\Application Data\Atlassian Evaluation\work\confluence\3.3.1\tomcat\work\null\localhost\confluence.

    Just for reference I put my jdbc driver in C:\Program Files\Atlassian\Confluence Evaluation 3.3.1\lib

    Any clue?

    1. Not sure with that install, but it is normally with web.xml in ...\conf. Make sure it is not a hidden file in that directory. Also, search for server.xml in C:\Program Files\Atlassian\Confluence Evaluation 3.3.1 (and all subdirectories).

      1. Thanks for the reply Bob. I tried but gave up. I use the 3.3.1 - Standalone for Production Usage (ZIP Archive) install instead with instructions on this page and it is working.

          1. Hey Bob, I wasn't sure where to report this. On the Confluence SQL Plugin page, the URL is broken.https://plugins.atlassian.com/plugin/details/225

            1. Seems ok now. Perhaps plugin exchange site was down?

  5. Hi Bob,

    Is there way to use integrated security for Microsoft Sql Server?

    -Vincent

    1. Don't know what you mean.

      1. <Resource
        name="jdbc/myDS"
        auth="Container"
        type="javax.sql.DataSource"
        driverClassName="net.sourceforge.jtds.jdbc.Driver"
        url="jdbc:jtds:sqlserver://mysqlserver:1433/mydatabase"
        username="username"
        password="password"
        maxActive="20"
        maxIdle="10"
        maxWait="-1"
        />

        When adding the data source to Server.xml, a username and password is specified. How would I use the credentials of the user running TomCat:

        jdbc:sqlserver://localhost;integratedSecurity=true; (http://stackoverflow.com/questions/167464/can-i-connect-to-sql-server-using-windows-authentication-from-java-ee-webapp)

        1. The link you provided seems to provide an answer. If not that, then I don't know. That is an application server config question.

          1. I tried a couple of different configs and the value below did the trick (on my dev machine).

            I still got the following error on a different machine with a similar setup:

            I've tried all the obvious things, installing the ntlmauth.dll in the bin directory, and tried to place it in the same path as the Java Path environment variable.

            If you have any insight into this, it would be greatly appreciated.

            Thanks,
            Vincent

            1. Thanks for posting the solution you have. Sorry, I can't help with the other and you already have been googling. Maybe someone else can help?

  6. I am having trouble due to an error saying dataSource not found.  I verified the spelling and matching case of the name.  I access the SQL server using SQuirreL.  I have the resource example from this page in the server.xml.  I saw some mention it needs to be in confluence.cfg.xml.  I am not sure about that but I pasted it there as well.  The jdbc driver is in the WEB-INF\lib folder as well as confluence\lib folder.  

    I am not sure if there is a log I can check to see what is going on during the attempt.  Does the SQL plugin write to a log?  I looked at the Atlassian.Confluence.log and the Catalina log.   But found nothing.

    Is there a way I can "debug" this?  

    I am running stand alone Confluence 4.0 on Windows Server 2008 and SQL Server 2008 R2 with the JDBC driver jTDS 1.2.5

    1. Datasource is an application server thing. The app server uses your configuration to create the connection. The plugin is simply a user of that datasource. Check the app server logs and make sure the name you are using is the name the app server created.

      1. Can you tell me which logs to check?  I looked everywhere I could imagine for a reference to SQL or the data source name I was trying.  I got no results in the logs I could locate.

        1. Here are some references:

          1. logs/catalina.out in the Confluence installation directory is usually the application server log for standalone installation
          2. http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html
          3. http://confluence.atlassian.com/pages/viewpage.action?pageId=16121981 is a jira reference, but confluence is probably similar

          By the way, I would double check your configuration if you are using standalone installation. It must be exactly right. This is the most likely cause of datasource errors.

          1. Bob I appreciate your help but I am still at a loss. 

            To be honest I am not sure how I can "double check" the configuration of my standalone installation any better.  Clearly there is a disconnect between what is needed and what I've done. I did review what is in my confluence.cfg.xml file which is where Confluence looks for the database details for the actual Confluence data. I found that it named the data source myDS so I altered the data source I put in the Server.xml to be called myNewDS. The contents of my resource definition are shown below ( the names have been changed )

            This is what is in my confluence.cfg.xml

            1. Open an issue and attach your server.xml file (edit out anything sensitive) and I will see if there is anything obvious. Do not change confluence.cfg.xml, server.xml should be the only thing that needs changing. The jtds driver needs to be in the <install>/lib directory (loaded with the app server start).

              1. What version of Confluence and what version of the Plugin?...

                We had the same issue, the plugin does not get the driver from the same place as confluence it self does for older versions, or so it seems.

                So while we had the driver installed as we also used the same SQL Server for the confluence data, it was not installed in the above mentioned location (<install>/lib)... creating a copy of the driver in that location fixed it for us, so maybe that is the issue here as well?..

                This was on Confluence 3.4 and Plugin 4.2, for Confluence 4.0 I didn't have any issues, it worked out of the box.

  7. Hello, am having trouble due to an error saying dataSource not found.  I verified:

    •  the spelling and matching case of the name.  
    • I access the SQL server using mysql cliente.
    • I am running:
      • Linux XXXXXX 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
      •  Atlassian Confluence 4.1
        • Instaled in "/usr/local/atlassian-confluence-4.1/"
        • data in "/usr/local/atlassian-confluence-data/"
      • mysql-connector-java-5.1.18-bin.jar copied in "[CONFLUENCE_HOME]/lib" and "[CONFLUENCE_HOME]/confluence/WEB-INF/lib/"

     the server.xml is:

    after shutdown and startup the "/usr/local/atlassian-confluence-data/confluence.cfg.xml" contains:

    ¿any idea? ¿is posible debug this plugin?

    1. Please open a support issue for things like this so it doesn't clutter up this page for others.

      First, always check out: SQL plugin datasource configuration. A quick look at that suggest your resource name is missing jdbc.

      Further notes:

      1. Datasources are application server things, so you need the driver in application server lib only.
      2. Only server.xml needs to be changed.
  8. Hi Bob

    Have just done a clean install of 4.2/restore from backup. Some pages use the sql macro, but it doesn't seem to be in the plugin exchange. Actually the same appears to be true for the run macro.

    Both these are pretty useful and hope that they are/will be compatible with 4.2. Is there something we need to do?

    Thanks
    Mark

    1. Just get the download url from the plugin exchange and install using that. I haven't completed tests yet, but they should continue to work in compatibility mode just fine. See https://studio.plugins.atlassian.com/wiki/display/TBL/2011/09/19/Confluence+4.0+compatibility+for+Table+and+other+plugins

  9. Configuring Confluence to use DB2 for z/OS as a SQL plugin datasource

    (I understand that Atlassian will, as of Confluence 4.3, be dropping DB2 as a supported platform. However, that does not prevent one from using DB2 as an external datasource with the SQL plugin.)

    My employer develops software that runs on IBM z/OS ("mainframes"). Our z/OS-based automated testing software stores test results in DB2 for z/OS. We also have other z/OS applications that store data in DB2 for z/OS. We embed SQL queries to DB2 for z/OS in Confluence pages.

    The following procedure has been tested using Confluence 4.2 to submit queries to DB2 9.1 for z/OS running on z/OS 1.13.

    This procedure assumes that you have Confluence administrator permission, and that you have direct access to the file system where Confluence is installed.

    Install the Confluence SQL plugin

    Straightforward, nothing more to say.

    Copy the DB2 for z/OS JDBC type 4 driver .jar files to the Confluence lib directory

    Copy (for example, via binary FTP) the following two .jar files:

    db2jcc4.jar
    db2jcc_license_cisuz.jar

    from your DB2 for z/OS installation in the following z/OS UNIX directory (the higher levels of this path might vary, depending on your specific installation):

    /usr/lpp/db2/jcct4v3/classes

    to the Confluence lib directory.

    Is the license .jar required?

    Noticing the lack of the number "4" after "db2jcc" in the file name db2jcc_license_cisuz.jar, I was curious whether this "license" .jar was required when using the db2jcc4.jar (JDBC type 4 driver) as opposed to the alternative db2jcc.jar (type 2). I thought perhaps that the license information had been embedded inside db2jcc4.jar, making it "standalone". So I tried running Confluence without the license .jar (stopped Confluence, deleted the license .jar from the lib directory, restarted Confluence). Instead of a table of results, the Confluence page with the embedded SQL query showed the following error:

    sql-query: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory ( ... The version of the IBM Universal JDBC driver in use is not licensed for connectivity to QDB2 databases. To connect to this server, please obtain a licensed copy of the IBM DB2 Universal Driver for JDBC and SQLJ. An appropriate license file db2jcc_license_*.jar for this target platform must be installed to the application class path. Connectivity to QDB2 databases is enabled by any of the following license files: [ db2jcc_license_cisuz.jar ]. ERRORCODE=-4472, SQLSTATE=42968)

    So, yes: the license .jar is required for db2jcc4.jar.

    Add a <Resource> element to the Confluence server.xml file

    Open server.xml (in the Confluence conf directory) in an XML editor or a text editor.

    Tip for Windows users: If you do not have any better options (such as any XML editor, or a nice text editor such as jEdit; er, which also happens to be a nice XML editor (wink) ), then WordPad is a better choice than Notepad in this specific case, because Notepad does not correctly recognize the line breaks in this file. I could be more specific, but if you're faced with a choice between WordPad and Notepad to edit files, then I figure you already have enough to worry about (wink).

    Add the following element as the new last child of the <Context> element:

    Replace the following placeholders with actual values:

    Placeholder

    Value

    DB2T

    The datasource name that you will refer to in {sql} macros

    uid

    TSO user ID

     ********

    TSO password

    zmf1

    Hostname of your z/OS system

    446

    TCP/IP port number specified by the DB2 distributed data facility (DDF) for DRDA clients ("DRDA port", also known as TCPPORT)

    DB2TLOC

    DB2 location name (LOCATION)

    Tip: you can find the TCPPORT and LOCATION values in the DB2 master job log, after the following message:

    DSNL004I  DB2 system DDF START COMPLETE

    Validation query

    The validationQuery attribute of the <Resource> element is an arbitrary SQL query that Confluence uses to validate its connection to the data source. (I think that) this could be replaced with any valid query.

    If you specify an invalid query, Confluence somewhat misleadingly reports a problem with the query that you have embedded in your page. I found this out the hard way: my first choice, "SELECT 1" , which I copied from an existing <Resource> element for a different database system, caused the {sql} macro to report the following error:

    DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=<END-OF-STATEMENT>

    Clearly, "SELECT 1" is a little too minimal for DB2 for z/OS (wink).

    Currently, I am using the following validation query:

    that I confected simply by appending "FROM SYSIBM.LUNAMES" (where SYSIBM.LUNAMES is a "built-in" DB2 for z/OS table) to the "SELECT 1" that I had copied from elsewhere.

    I asked a local DB2 expert for advice, and they suggested the following query, which I'll probably use instead:

    The advantage of this query is that, if there is a problem, it might help to be able to see the result (the timestamp) in a log.

    If in doubt, check that your validation query works by entering it directly into DB2 for z/OS (for example, from the ISPF primary menu, select DB2 Admin > Execute SQL statements > Execute SQL statements from screen input).

    Restart Confluence

    For example, on a Windows server that runs Confluence as a service: Start > Control Panel > Administrative Tools > Services > right-click the Atlassian Confluence service, and then click Restart.

    Add a {sql-query} macro to a Confluence page

    Try it out.

    For example:

  10. Thanks for the contribution (smile).

  11. Hello,

    has someone probably hints for configuring a jdbc type 4 driver to direct access and display a microsoft access .mdb file ?

    i like to use the Jackcess library or any other suggested.

  12. I've recently changed from Filemaker 11 to 12 Advanced. We had some issues with the previous connections settings, mostly the DBadmin getting narky as connections weren't being closed, they would stay open indefinitely. I now use the following resource settings to connect to a Filemaker 11 and 12 (32 and 64bit respectively) using the fmjdbc.jar bundled with Filemaker 12 Advanced. Working fine so far (smile)

     The maxIdle="0" parameter means I don't get any persistent connections, they didn't seem to be creating any traffic to the database but the admin wasn't happy with them. Quite new to this myself but perhaps this may be of use elsewhere. 

     

    1. Thanks for the contribution!

  13. I am no SQL expert, I am experimenting and having good success. But I have a problem, and i think its with setup.  My datasources work fine when I select, but if I try to use local variables with mysql i get the following

    {sql:dataSource=lifecycleDS|output=wiki} 
    declare myint int
    {sql}
    sql: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare myint int' at line 1

    declare myint int

    1. This error is coming from your database, so you will need to deal with that locally.

  14. I should add if I use this:

    {sql-query:dataSource=lifecycleDS|output=wiki} 
    DECLARE myint int
    {sql-query}

    I get a read only error

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

    DECLARE myint int

    1. Some databases enforce the read-only directive used sql-query to limit what can be done using this macro. See SQL-query Macro. So, if you are doing anything that gives that error, you need to switch to sql macro. 

  15. So I confirmed that I have all rights to the data base.  When i use sql-query I get the error above (as you say it's enforcing read-only).  When I use sql I get the syntax error.  Could that still be a read-only problem

    I may be using the bundled JDBC driver as this page indicates:

    MySQL JDBC Drivers bundled with Confluence
    The JDBC drivers for this database are bundled with Confluence. You do not have to download or install any JDBC drivers to use this database with Confluence, if you are using a direct JDBC connection. If you are connecting via a datasource, you will still need to download and install the drivers manually. See Database JDBC drivers for more information on the bundled JDBC drivers.

    Could that be related to my problem?

    1. I think you are getting to the database just fine and I don't think it is related to the read only part. Run a simple query (like a select) first to make sure everything is working as expected before you try more exotic things. 

  16. You are correct,  a command like select * from spaces works perfect (see below).  What I am trying to do is insert results of query into a temporary table for further analysis. 

    The reason it seems like a connector issue is because I am querying across 3 totally different databases.  2 mysql and one mssql.  The mysql databases are the stock confluence one and a local one.  The declare command will not work in either mysql datasource.  However the declare command works great in the msSql datasource.

    results of select * from spaces limit 2

    PACEID

    SPACENAME

    SPACEKEY

    SPACEDESCID

    HOMEPAGE

    CREATOR

    CREATIONDATE

    LASTMODIFIER

    LASTMODDATE

    SPACETYPE

    SPACEGROUPID

    65537

    Demonstration Space

    ds

    98349

    98334

     

    2011-11-04 11:44:29.0

     

    2012-07-19 16:42:03.0

    global

     

    819201

    Accelerometers

    accelerometer

    589827

    589828

    admin

    2012-07-19 17:02:15.0

    admin

    2012-07-19 17:02:15.0

    global

     

    1. Good. Well you have to deal with the limitations of the database you have (sad). You might be able move some of your logic into a database function to work around it.

  17. Im running MySql as part of my confluence install.  I have full rights.  Is this normal behavior for MySql.  Clearly the database can be written to by the confluence user.  I am not sure how then the JDBC pluggin thinks that the connection is Read-only.

    Frustrating.

    1. If you are using the sql macro, then it should not be read only. Only the sql-query macro informs the jdbc driver/database to expect read-only request. Try sql macro with a simple insert or update statement to your test database. Your early post indicated the problem was with your declare request. I am still not sure what you are trying to do with that anyway. 

  18. Hi Bob, did you ever encounter some client using Informix? Any experience with this DB? Thanks anyway for your effort! Best, Matin

    1. No, I don't have any experience with Informix nor has any community member mentioned it before. Google informix and tomcat datasource to find information on setting up a datasource and getting the right JDBC driver. Once you get it working, please post the result for others.

      1. Informix DB is working fine like this (did not deeptest by now, just simple selects with some joins)

        • If not installed find and download latest JDBC driver at http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=ifxjdbc
        • tar xvf the downloaded file
        • setup with  (java -jar ~/JDBC/setup.jar install -i console) to confluence install dir (should be /opt/atlassian/confluence - driver is installed to /lib where the JDBC driver jar will be expected by catalina / SQL Plugin)
        • configure datasource  in /opt/atlassian/confluence/conf/server.xml (replace values in [] with your appropriate values)

        <Resource

        name="jdbc/myDS"
        auth="Container"
        type="javax.sql.DataSource"
        driverClassName="com.informix.jdbc.IfxDriver"
        url="jdbc:informix-sqli://[HOST_OR_IP]:[IP_PORT]/[INFORMIX_DBNAME]:INFORMIXSERVER=[NAME]"
        username="[user]"
        password="[cleartext_passwd]"
        maxActive="20"
        maxIdle="10"
        maxWait="-1"

        />

        Restart of confluence required after every change on server.xml to take effect

        Thanks Bob for not googleing for me (wink)

         

         

        1. Thanks for contributing (smile).

  19. Hi there

    We have migrated the confluence 3.5.13 to 4.3.6  (in UAT phase). We found that SQL Query does not renders properly and ends up with

    Error rendering macro 'sql' : org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class 'oracle.jdbc.OracleDriver'

    the page loads fine in current PROD 3.5.13

    Having asked Atlassian Suport, they said that Oracle JDBC driver is not in the 4.3.6 version. I compared all the jar files between PROD and UAT and there is NO ojdbc jar present in PROD. But the macro works fine in PROD.

    having read this thread , I installed the ojdbc-6.jar in UAT box and restarted confluence and the error disappeared in UAT. Seems macro works.

     

    Now my question is once we upgrade to 4.3.6 for the PROD does this driver needs to manually installed again? When it is working in 3.5.13 without the jar why it does not work in 4.3.6 ? has changes made ? please advice.?

     

    correct me if I'm missing something

     

    thanks

    Viswa

    1. No, nothings changed. See Problem determination first error paragraph.