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