Connection to MySQL

This document explains the specific aspects of connecting Cocoon to a MySQL Database.

For a general explanation on database connections please refer to the official Cocoon documentation.

Installing the JDBC drivers

• The JDBC drivers can be downloaded from the mySQL.com website. (They are an update of the popular 'Mark Matthews' driver. (See later)
  

• Expand the zip or tar file and locate the jar file mysql-connector-java-2.0.14-bin.jar (if you're using the 'Mark Matthews' driver, the jar file is called something like mm.mysql-2.0.14-bin.jar)

• Copy this file into either $COCOON_LIB/WEB-INF/lib (default ./build/webapp/WEB-INF/lib) or $TOMCAT_HOME/common/lib/

Setting the web.xml configuration

In the $COCOON_LIB/WEB-INF/ directory you will find the web.xml file. Open it and look for this rows

<init-param>
  <param-name>load-class</param-name> 
    <param-value>
<!-- 
 For IBM WebSphere:
        com.ibm.servlet.classloader.Handler ..-->

here you have to add the following line:

<!-- For MySQL Driver: -->
        com.mysql.jdbc.Driver
        <!-- or use org.gjt.mm.mysql.Driver if you have the older 'Mark Matthews' driver-->

Setting the cocoon.xconf configuration

Search for: <datasources> add into this element these lines:

  <jdbc name="YourPoolName">
    <pool-controller min="5" max="10"/>
    <dburl>jdbc:mysql://localhost:3306/YourDatabaseName?autoReconnect=true</dburl>
    <user>YourUsername</user>
    <password>YourPassword</password>
  </jdbc>

The ?autoReconnect=true tip is cribbed from the Tomcat JNDI datasource HOWTO to get around the fact that MySQL by default drops connections that have been idle for 8 hours

Setting the sitemap.xmap configuration

When you want to use the connection with the SQLTransformer you just call the JDBC name inside the pipeline in this way:

<map:match pattern="sqlquery">
  <map:generate src="sql/sqlquery.xml"/>
  <map:transform type="sql">
    <map:parameter name="use-connection" value="YourPoolName"/>
  </map:transform>
  <map:transform type="xslt" src="../stylesheets/transformation.xslt"/>
  <map:serialize type="svg2jpeg"/>
</map:match>

Obviously you have to adapt this sample to fill your need.
SpecificDatabaseConnection

Comments from the readers

Attention: you need to have (mysql jdbc driver, saxon , jdk) working together; I'm working with JDK1.5, Saxon8, Mysql JDBC 3.06-ga