Connection to Microsoft SQL Server (mssql)

This document explains the specific aspect of connecting Cocoon to a mssql Database.

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

Restrictions

The latest drivers only work on SQL 2000, but earlier beta ones used to work on SQL 7, though never supported).

Also note, as far as I can remember, you HAVE to use SQL Server authentication, not NT authentication. - Any one know different?

Downloading the jdbc drivers

The jdbc drivers can be downloaded here .

Last time I looked (4-dec-2002) it was on the top downloads on the top right hand side of the page. If that changes, happy hunting.

You get an installer program, so run the installer, and look in the lib folder where you chose to install it.

Drop the 3 jar files, namely msbase.jar, mssqlserver.jar and msutil.jar either in $COCOON_HOME/web-inf/lib or $TOMCAT_HOME/common/lib/.

Setting Web.xml

In the $Cocoon_Home/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 MsSQL:   --> 
        com.microsoft.jdbc.sqlserver.SQLServerDriver

Setting cocoon.xconf

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

  <jdbc name="YourPoolName">
    <pool-controller min="5" max="10"/>
    <dburl>jdbc:microsoft:sqlserver://YourServerName:1433;DatabaseName=YourDatabaseName;SelectMethod=Cursor;</dburl>
    <user>YourUsername</user>
    <password>YourPassword</password>
  </jdbc>

Note the SelectMethod=Cursor; addition into the JDBC URL. This prevents errors when using database actions due to auto-commit defaulting to false. Using <auto-commit>true</auto-commit> does not make any difference to the old db actions. If you do not do this you will get an exception saying something like:

org.apache.cocoon.ProcessingException: Could not prepare statement :position = 0: 
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Can't start a cloned connection while in manual transaction mode.

Please see http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b313181 for more details...

Setting sitemap.xmap

When you want to use the connection 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

has anyone tried the jTDS JDBC driver from source forge http://jtds.sourceforge.net, communication via TDS is the approach i am using from php and it seems to be working (using freeTDS). When i get round to it i'll give it a go from cocoon

  • No labels