Derby with Tomcat(From IBM)

Integrate Cloudscape Version 10 or Derby with Tomcat A cookbook for adding the relational database manager into the servlet container

Level: Intermediate

Lance Bader ([e-mail] ), Senior Software Engineer, IBM Corporation

11 Aug 2005

Cloudscape™ is a relational database manager implemented in Java™. Tomcat is the reference servlet container implemented in Java. This article is a cookbook, providing step-by-step recipes for integrating them in three common scenarios: the prototype integration, the casual integration, and the enterprise integration.

<!----> <script type="text/javascript"> capture_referrer(); </script> <!---->

<!----> <!---->

Show developerWorks content related to my search: <Listener className="org.apache.derby.tomcat.ServerLifecycleListenerWithNetworkServer" debug="0" />

Hide developerWorks content related to my search: <Listener className="org.apache.derby.tomcat.ServerLifecycleListenerWithNetworkServer" debug="0" />

Show descriptions | Hide descriptions

Show descriptions | Hide descriptions

1 - 2 of 2 search results | View all search results

1)	Integrate Cloudscape Version 10 or Derby with Tomcat
	Learn to intergrate Cloudscape and Tomcat with three common scenarios: the prototype integration, the casual integration, and the enterprise integration.
2)	Integrate Cloudscape Version 10 or Derby with Tomcat
	Note: This is the second version of this article. The materials and techniques provided have been enhanced to be compatible with Cloudscape Version 10 and Derby. If you are using Cloudscape Version 5, see Integrating Cloudscape and Tomcat .

<!----> <!---->

<!---->

<!----><!---->

Overview

Note: This is the second version of this article. The materials and techniques provided have been enhanced to be compatible with Cloudscape Version 10 and Derby. If you are using Cloudscape Version 5, see Integrating Cloudscape and Tomcat .

Tomcat is an open source Java 2 Enterprise Edition™ (J2EE) servlet container that is used as the reference implementation for Java Servlet and JavaServer Pages technology. The implementation is accurate, fast, easy to use, and offered free of charge under the Apache Software License.

Cloudscape is a small footprint, standards-based, relational database manager implemented entirely in Java. It can be completely embedded in any Java application, enabling a silent install and a single administration scope. Unlike other database managers that run in a separate process, embedded Cloudscape runs in the same Java virtual machine as your application. This significantly reduces database overhead. Yet it supports complex SQL, transactions, and JDBC without compromise so you can be sure your application can be migrated to IBM® DB2® Universal Database™ when it grows large enough to require enterprise-wide capacity.

In August 2004, IBM contributed the Cloudscape code to the Apache Software Foundation (ASF). The Apache project, called Derby, is an incubator project. Developers can contribute to and download Derby under the Apache Software License. Starting with Cloudscape Version 10, IBM's Cloudscape offering is based on the Derby code. You can also download Cloudscape from IBM. There is no license fee for the Cloudscape download, but you can purchase support from IBM.

Except for several sample scripts that must be modified if you use Derby, the materials and techniques provided in this article will work equally well no matter which implementation you use.

Assumptions

The materials and techniques discussed here are based on the following software platforms.

• Windows XP Professional® with all service packs and updates as of June 1, 2005 (see http://www.microsoft.com/windows )
• Java Version 1.4.2 ( IBM Windows 32 build cn1420-20040626) (see http://www.ibm.com/java and search for "IBM developer kits")
• Tomcat Version 5.0.28 (see http://jakarta.apache.org/tomcat/ )
• Cloudscape Version 10.0.2.0 (see http://www.ibm.com/software/data/cloudscape ).
• Derby snapshot Version 10.0.2.1 (see http://incubator.apache.org/derby ).

You should be able to apply the information in this article to other operating systems and subsequent versions, but there is always a chance that platform changes and product enhancements can render it inaccurate or obsolete. Keep this is mind when you apply this information to your environment.

Alternative integration scenarios

There is more than one way to integrate Cloudscape with Tomcat. To illustrate the most useful alternatives, I have collected them into three sample scenarios:

 

The prototype integration

If you are developing a Web application for a prototype or proof-of-concept exercise, this scenario may suffice. No changes are required to the Tomcat configuration and everything can be contained in your Web application's archive (WAR).

The casual integration

If you need a database manager to implement a realm for Tomcat's user authentication or if you need your database manager to support multiple Web applications, this scenario would be more suitable. In this scenario, Cloudscape JAR files are added to the Tomcat configuration together with a class that implements the Tomcat LifecyleEvent interface. This class initializes and shuts down Cloudscape as the Tomcat server initializes and terminates.

The enterprise integration

If you need efficient connection pooling to support multiple simultaneous users and you want to keep your Web application independent of the JDBC™ URL specifications and database names, you need this scenario. In this scenario, Tomcat references and, therefore, has access to the entire Cloudscape library in the Cloudscape installation directory. JNDI Data Source definitions provide efficient connection pooling. In addition, the Cloudscape Network Service is started so that remote applications and administration tools can access the database while it is being used by Web applications.

Typically, you will choose the scenario that most closely matches your situation and use the alternatives described there. If, for some reason, the scenario contains an alternative that proves to be unsuitable, check the other scenarios for a better alternative.

HelloCloudscape.jsp

HelloCloudscape.jsp is a very simple JavaServer Page (JSP) that is used to verify that Cloudscape has been correctly integrated with Tomcat. It has been extended from the hello.jsp included with the Tomcat sample programs.

To match the integration, each scenario has a slightly different implementation of this JSP. The differences are explained in the scenario details, but in all cases, HelloCloudscape.jsp provides an HTML form for obtaining the information necessary to create a connection and execute a SQL command.

• In the Prototype Scenario and the Casual Scenario, the first field in the form obtains the Cloudscape database connection Uniform Resource Locator (URL) and the second field is used to enter the SQL command to be executed.
  
  
• In the Enterprise Scenario, the first field in the form obtains the JNDI resource name of the JDBC data source and the second field is used to enter the SQL command to be executed.

When the form is submitted, the request is sent back to HelloCloudscape.jsp which

1. Redisplays the input form
2. Echoes the information submitted on the form
3. Acquires a connection to the database
4. Executes the provided SQL command
5. Displays the results of the SQL command.

If an Exception is thrown during this process, HelloCloudscape.jsp does its best to display the Exception message and the Java stack trace.

Note: Do not install HelloCloudscape.jsp on a production Web site. Make sure HelloCloudscape.jsp is removed from your Web application before it is deployed to a production Web site. HelloCloudscape.jsp would expose sensitive data because an attacker can use it to examine and modify any Cloudscape database at your site.

Example Cloudscape database connection URLs

A Java database connection (JDBC) URL provides the information used to identify and establish a connection to a particular database.

For embedded Cloudscape systems, the Cloudscape JDBC driver name is org.apache.derby.jdbc.EmbeddedDriver and the URL has the following form.

jdbc:derby:database [;attributes ]

where

 

jdbc

is the standard prefix for all JDBC URLs.

derby

is the subprotocol identifier for a URL that is to be interpreted by the Cloudscape or Derby JDBC driver.

database

is replaced with an identifier that references the Cloudscape database and is typically expressed as a qualified path to the directory that contains the database.

attributes

are optional attributes, separated by semicolons, to be applied to the connection.

Here are some useful examples.

• jdbc:derby:C:\Program Files\IBM\Cloudscape_10.0\demo\databases\toursDB will establish a connection to the sample Tours database provided with Cloudscape assuming that Cloudscape was installed in the directory named C:\Program Files\IBM\Cloudscape_10.0, the default installation directory on a Windows platform. Note that Cloudscape is not confused by the Windows drive notation, the backslash separators, or the blank in the directory name.
  
  
• jdbc:derby:C:\Temp\Demo\NewDatabase;create=true will create a new database in the C:\Temp\Demo\NewDatabase directory named 'NewDatabase' and establish a connection. If necessary the Temp and Demo directories are created as well. If the database cannot be created, a SQLException is thrown indicating that the database cannot be found. If the database already exists, a connection is established and a SQLWarning is issued.
  
  
• jdbc:derby:C:\Temp\Demo\Data;user=Mary;password=contrary will establish a connection to the database named 'Data' located in the C:\Temp\Demo\Data directory using Mary as the user identifier and contrary as the password.
  
  
• jdbc:derby:C:\Temp\Demo\Data;shutdown=true will cause Cloudscape to shut down the database named 'Data' located in the C:\Temp\Demo\Data directory. This form will shut down a single database by performing a final checkpoint. The Cloudscape system and any other active database remains active.
  
  
• jdbc:derby:;shutdown=true will cause Cloudscape to shut down the entire system. A final checkpoint is performed on all active databases, the Cloudscape JDBC driver is removed from the driver registry, and a SQLException is thrown to indicate that shutdown was successful and no connection was returned.
  
  

For client applications that connect to a Cloudscape network server, the DB2 universal driver is used and the JDBC driver name is com.ibm.db2.jcc.DB2Driver . The URL has the following form.

jdbc:derby:net://host :port /database [;cAttributes ][:dAttributes ;]

where

 

jdbc

is the standard prefix for all JDBC URLs.

derby

is the subprotocol identifier for a URL that is to be interpreted by the DB2 universal JDBC driver.

net

is the subsubprotocol identifier for a URL that references a remote network server.

host

is replaced with the host name or IP address where the target Cloudscape network server resides.

port

is replaced with the port number where the target Cloudscape network server is listening for requests (1527 by default).

database

is replaced with an identifier that references the Cloudscape database and is typically expressed as a qualified path to the directory on the server that contains the database. Note that if path information is included, the name must be enclosed by double quotes (") to prevent the path's slash (/) characters from being misinterpreted.

cAttributes

are optional Cloudscape attributes, separated by semicolons (;), to be applied to the connection. Cloudscape attributes will be interpret by Cloudscape at the network server and, when necessary, are separated from the driver attributes by a colon (:).

dAttributes

are optional DB2 universal driver attributes, separated by semicolons and terminated by a semicolon (;), to be applied to the connection. Unlike Cloudscape attributes, the driver attributes affect the behavior of the DB2 universal driver and are not sent to the network server. Note that when specified, the driver attributes are separated from the rest of the URL with a colon (:).

Here are some useful examples.

• jdbc:derby:net://localhost:1527/"C:\Temp\Demo \Data";create=true:user=Mary;password=contrary; will establish a remote connection to a Cloudscape network server on the local host, listening on port 1527. It will create a new database in the C:\Temp\Demo\Data directory and establish a connection. If necessary the Temp and Demo directories are created as well. If the database cannot be created, a SQLException is thrown, indicating that the database cannot be found. If the database already exists, a connection is established and a SQLWarning is issued. The DB2 universal driver always requires a user name and password so, for this connection, Mary is the user identifier and contrary is the password. Note that the user identifier and password are driver attributes and so are preceded by a colon, separated by a semicolon, and terminated by a semicolon.
  
  
• jdbc:derby:net://localhost:1527/"C:\Temp\Demo \Data";shutdown=true:user=Mary;password=contrary; will cause the Cloudscape network server on the local host, listening on part 1527, to shut down the database located in the C:\Temp\Demo\Data directory. This form will shut down a single database by performing a final checkpoint. The Cloudscape system and any other active database remains active.
  
  

Improper shutdown and subsequent restart recovery

Note: If Tomcat is shut down without issuing the proper Cloudscape shutdown, Cloudscape will act as though the system failed. No information will be lost, but when the first database connection is made after Cloudscape is restarted, Cloudscape will use its log files to recover committed transactions and roll back uncommitted transactions. This places the database into a consistent valid state. Recovery can be costly, so using the proper shutdown command improves startup performance.

Back to top

The prototype scenario

This is the simplest scenario. It sacrifices run-time efficiency for simplicity. It is usually sufficient for prototype and proof-of-concept development exercises. This scenario has the following properties.

• Everything you need is included in your Web application archive. After installing your finished Web application, there is no need for additional installation tasks to modify the Tomcat configuration.
  
  
• The Cloudscape classes can only be accessed by the Web application under development.
  
  
• Every time a form is submitted to HelloCloudscape.jsp, it dynamically loads the Cloudscape JDBC driver. Loading the driver the first time initializes the Cloudscape system. Subsequent loading incurs a little overhead, but otherwise has no effect.
  
  
• Every time a form is submitted to HelloCloudscape.jsp, it creates a new connection.
  
  
• For a given database, Cloudscape activates the database when the first connection is established.
  
  
• If Tomcat is shut down while a Cloudscape database is active, it is equivalent to a system failure. No database changes are lost, but Cloudscape will have to perform recovery the next time the database is activated.
  
  
• Once the database is active, it cannot be used by any other Cloudscape system until the database is shut down.

If you have not installed the Web application under development, use these instructions.

1. Stop the Tomcat server.
   
   
2. Download the CloudscapeDemo.zip file.
   
   
3. Extract CloudscapeDemo.zip into the Tomcat webapps directory (typically InstallDir \webapps where InstallDir is replaced with the Tomcat installation directory name).
   
   
4. Copy derby.jar from the Cloudscape library into the library for the demonstration Web application (typically InstallDir \webapps\CloudscapeDemo\WEB-INF\lib where InstallDir is replaced with the Tomcat installation directory name).
   
   
5. Start the Tomcat server.
   
   
6. Start your browser and navigate to HelloCloudscape.jsp (typically http://localhost:8080/CloudscapeDemo/HelloCloudscape.jsp).
   
   
7. Test the integration by submitting appropriate JDBC URL and SQL commands in the HTML form provided by HelloCloudscape.jsp.
   
   
8. Develop your Web application. Use the database logic from HelloCloudscape.jsp as sample code for the database logic in your Web application.
   
   
9. Delete HelloCloudscape.jsp from your Web application as soon as it is practical.
   
   

If you have already installed the Web application under development, use these instructions.

1. Download the HelloCloudscape.jsp file.
   
   
2. Copy HelloCloudscape.jsp into the directory where you place common JSP files for your application (typically InstallDir \webapps\appl where InstallDir is replaced with the Tomcat installation directory name and appl is replaced with your Web application directory name).
   
   
3. Copy derby.jar from the Cloudscape library into the library for your Web application (typically InstallDir \webapps\appl \WEB-INF\lib where InstallDir is replaced with the Tomcat installation directory name and appl is replaced with your Web application directory name).
   
   
4. Start the Tomcat server.
   
   
5. Start your browser and navigate to HelloCloudscape.jsp (typically http://localhost:8080/appl /HelloCloudscape.jsp where appl is replaced with your application directory name).
   
   
6. Test the integration by submitting appropriate JDBC URL and SQL commands in the HTML form provided by HelloCloudscape.jsp.
   
   
7. Continue your Web application development. Use the database logic from HelloCloudscape.jsp as sample code for the database logic in your Web application.
   
   
8. Delete HelloCloudscape.jsp from your Web application as soon as it is practical.
   
   

Back to top

The casual scenario

This scenario allows multiple Web applications and even a Tomcat JDBC realm to use Cloudscape at the same time. In addition, the Tomcat server life cycle events are used to trigger Cloudscape initialization and shutdown. This scenario has the following properties.

• Minor changes are required to the Tomcat configuration.
  
  
  • The Cloudscape classes are placed in Tomcat's common library.
    
    
  • ServerLifecycleListener, a Java class that implements Tomcat's LifecycleEvent interface, is added to the common library.
    
    
  • The Tomcat server configuration (server.xml) is modified to register the ServerLifecycleListener class.
    
    
• When Tomcat starts, the ServerLifecycleListener will load the Cloudscape JDBC driver, thus initializing Cloudscape.
  
  
• When Tomcat stops, the ServerLifecycleListener will shutdown the Cloudscape system, thus forcing a final checkpoint to every active database and eliminating the need for recovery processing when the databases are reactivated.
  
  
• Every time a form is submitted to HelloCloudscape.jsp, it creates a new connection.
  
  
• For a given database, Cloudscape activates the database when the first connection is established.
  
  
• Once the database is active, it cannot be used by another Cloudscape system until the database is shut down.

For this scenario, follow these instructions.

1. Stop the Tomcat server.
   
   
2. Download the CloudscapeCasual.zip and ServerListener.zip files.
   
   
3. Extract CloudscapeCasual.zip into the Tomcat webapps directory (typically InstallDir \webapps where InstallDir is replaced with the Tomcat installation directory name).
   
   
4. Extract ServerListener.zip into a convenient directory and review ServerListener\org\apache\derby\tomcat\ServerLifecycleListener.java.
   
   
5. Copy the ServerListener\classes directory into Tomcat's server directory (typically InstallDir \server where InstallDir is replaced with the Tomcat installation directory name).
   
   
6. Copy derby.jar from the Cloudscape library into Tomcat's common library (typically InstallDir \common\lib where InstallDir is replaced with the Tomcat installation directory name).
   
   
7. Back up and edit the Tomcat server configuration (InstallDir \conf\server.xml where InstallDir is replaced with the Tomcat installation directory name) and add the following element at the beginning of the <Server> element.
   

   <Listener
   className="org.apache.derby.tomcat.ServerLifecycleListener"
   debug="0" />

   
   
   
8. Start the Tomcat server.
   
   
9. Start your browser and navigate to HelloCloudscape.jsp (typically http://localhost:8080/CloudscapeCasual/HelloCloudscape.jsp).
   
   
10. Test the integration by submitting appropriate JDBC URL and SQL commands in the form provided.
    
    
11. Continue your Web application development. Use the database logic from HelloCloudscape.jsp as sample code for the database logic in your Web application.
    
    
12. Delete the CloudscapeCasual directory from your Tomcat Web applications as soon as it is practical.
    
    

Back to top

The enterprise scenario

Like the casual scenario, this scenario allows multiple Web applications and even a Tomcat security realm to use Cloudscape at the same time. Unlike the casual scenario, it adds a Cloudscape network server so that remote applications and administration tools can access the database while it is being used by Web applications. This scenario has the following properties.

• Changes are required to the Tomcat configuration.
  
  
  • The catalina.properties file is modified so that the entire Cloudscape library is included in common classpath.
    
    
  • ServerLifecycleListenerWithNetworkServer, a Java class that implements Tomcat's Lifecycle Event interface, is added to Tomcat's server library.
    
    
  • The Tomcat server configuration (server.xml) is modified to
    
    
    • Register the ServerLifecycleListenerWithNetworkServer class
      
      
    • Define a global JNDI resource for a Cloudscape data source
      
      
    • Define a default context that links to the global Cloudscape data source.
      
      
• There is no need to copy the JAR files into the Tomcat directory tree. Instead, the Cloudscape JAR files are included in the classpath common to Tomcat and all Web applications. Fixes and upgrades can be applied to the Cloudscape installation and the Tomcat site will automatically use them.
  
  
• When Tomcat starts, the ServerLifecycleListenerWithNetworkServer will load the Cloudscape JDBC driver, thus initializing Cloudscape, and start a network server to handle remote requests.
  
  
• When Tomcat stops, the ServerLifecycleListenerWithNetworkServer will shut down the network server and Cloudscape, thus forcing a final checkpoint to every active database and eliminating the need for recovery processing when the databases are reactivated.
  
  
• When a form is submitted to HelloCloudscape.jsp, it uses the data source name provided to obtain a connection from the connection pool managed by the data source.
  
  
• As always, for a given database, Cloudscape activates the database when the first connection is established.
  
  
• When a database is active, other Cloudscape systems can still access the database by using remote access.

For this scenario, follow these instructions.

1. Stop the Tomcat server.
   
   
2. Download the CloudscapeEnterprise.zip and ServerListener.zip files.
   
   
3. Extract CloudscapeEnterprise.zip into the Tomcat webapps directory (typically InstallDir \webapps where InstallDir is replaced with the Tomcat installation directory name).
   
   
4. Extract ServerListener.zip into a convenient directory and review ServerListener\org\apache\derby\tomcat\ServerLifecycleListenerWithNetworkServer.java.
   
   
5. Copy the ServerListener\classes directory into Tomcat's server directory (typically InstallDir \server where InstallDir is replaced with the Tomcat installation directory name).
   
   
6. Back up and edit the catalina.properties file (typically InstallDir \conf\catalina.properties where InstallDir is replaced with the Tomcat installation directory name).
   
   
   1. Find the common.loader key.
      
      
   2. At the end of the value assigned to this key, add a comma and a wildcard for all the Cloudscape JAR files (typically CloudDir /lib/*.jar where CloudDir is replaced with the Cloudscape installation directory name). This will add all the Cloudscape JAR files to the classpath that is common to Tomcat and all the Web applications.
      
      
   3. Save the updated file.
      
      
7. Back up and edit the Tomcat server configuration (InstallDir \conf\server.xml where InstallDir is replaced with the Tomcat installation directory name).
   
   
   1. Find the <Server> element in the configuration document.
      
      
   2. Add the following element at the beginning of the <Server> element.
      

      <Listener
      className="org.apache.derby.tomcat.ServerLifecycleListenerWithNetworkServer"
      debug="0" />

      
      
      
   3. Find the <GlobalNamingResources> element in the configuration document.
      
      
   4. Add the following element into the <GlobalNamingResources> element.
      

      <!-- Datasource for Cloudscape sample database -->
      <Resource
        name="jdbc/Tours"
        type="javax.sql.DataSource"
        auth="Container"
        description="Cloudscape sample database"
      />
      

      
      Note that the value specified for the name attribute becomes the data source name and must match the references in the other elements.
      
      
   5. Add the following element into the <GlobalNamingResources> element after the <Resource> element added above.
      

      <!-- Resource parameters for the Cloudscape sample database -->
      <ResourceParams name="jdbc/Tours">
        <parameter>
          <name>maxWait</name>
          <value>5000</value>
        </parameter>
        <parameter>
          <name>maxActive</name>
          <value>4</value>
        </parameter>
        <parameter>
          <name>url</name>
          <value>jdbc:derby:C:\Program Files\IBM\Cloudscape_10.0\demo\databases
            \toursDB</value>
        </parameter>
        <parameter>
          <name>driverClassName</name>
          <value>org.apache.derby.jdbc.EmbeddedDriver</value>
        </parameter>
        <parameter>
          <name>maxIdle</name>
          <value>2</value>
        </parameter>
      </ResourceParams>
      

      
      Note that the value of the name attribute must be the same as the value specified for the name attribute in the matching <Resource> element.
      
      In this example, the url parameter specifies the sample database provided with Cloudscape and you may need to change the value to match your environment. In addition, if your database requires user authentication, you will need to add parameter definitions for user and password .
      
      
   6. Find the <Engine> element in the configuration document.
      
      
   7. If the current <Engine> element does not have a <DefaultContext> , add the following segment at the beginning of the <Engine> element. Otherwise, copy the <ResourceLink> element from this example into the existing <DefaultContext> .
      

      <!-- The default context element is used to provide configuration
      settings to represent default configuration properties for
      contexts that are automatically created.
      -->
      <DefaultContext>
        <!-- This resource link adds the global data source to all
        Web applications.
        -->
        <ResourceLink
          name="jdbc/Tours"
          global="jdbc/Tours"
          type="javax.sql.DataSource"
        />
      </DefaultContext>
      

      
      Note that the value of the name attribute must be the same as the value specified for the name attribute in the matching <Resource> element.
      
      This example assumes that the data source is to have a global scope. Alternatively, to limit the scope of a data source, the <ResourceLink> element can be placed in the context of each Web application that will reference the data source. See the Tomcat configuration documentation for more information.
      
      
   8. Save the updated file.
      
      
8. Back up and edit the Web application deployment descriptor (InstallDir \webapps\CloudscapeEnterprise\WEB-INF\web.xml where InstallDir s replaced with the Tomcat installation directory name).
   
   
   1. Find the example <resource-ref> element in the document. It will look like this.
      

      <resource-ref>
        <description>
          This element references a resource factory for a data
          source that provides java.sql.Connection instances that
          connect to a particular database that is configured in
          the server.xml file. Its resource reference name must match
          the resource name defined there. By using a resource
          reference, the servlets and JavaServer pages remain
          independent of the JDBC driver names and JDBC URLs while
          gaining the performance advantage of a connection pool.
        </description>
        <res-ref-name>
          jdbc/Tours
        </res-ref-name>
        <res-type>
          javax.sql.DataSource
        </res-type>
        <res-auth>
          Container
        </res-auth>
        </resource-ref>
      

      
      Note that the body of the <res-ref-name> element must be the same as the value of the name attribute of the matching <Resource> element in the Tomcat configuration.
      
      
   2. If you have not modified the resource name used in this example, no change is needed. If you have modified the resource name, modify the body of <res-ref-name> element to match. If you have added additional data source definitions, use this <resource-ref> element as a template and add a reference to each data source that you would like to test.
      
      
   3. Save the modified file
      
      
9. Start the Tomcat server.
   
   
10. Start your browser and navigate to HelloCloudscape.jsp (typically http://localhost:8080/CloudscapeEnterprise/HelloCloudscape.jsp).
    
    
11. Test the integration by submitting the appropriate JDBC data source name ("jdbc/Tours" if you followed this example) and SQL commands in the form provided.
    
    
12. Continue your Web application development. Use the database logic from HelloCloudscape.jsp as sample code for the database logic in your Web application. Use the example configuration changes described here as templates to create the JDBC data source definitions for your application. Remember, you must restart Tomcat whenever you change the server configuration.
    
    
13. Tune your data source as necessary
    
    
14. Remove the example data source from the Tomcat configuration and delete the CloudscapeEnterprise directory from your Tomcat Web applications as soon as it is practical.
    
    

Back to top

Implementing a Tomcat realm with Cloudscape

A realm is a repository of

• Logon identifiers (also known as user names) that identify a user
• Passwords that are used to authenticate a given logon identifier
• Roles that each user is authorized to assume.

Tomcat implements container-managed security by connecting to a realm and using the data stored there to authenticate users and verify their right to access servlets and JavaServer pages. Cloudscape tables can be used as the repository for the realm.

To implement a realm in a database, Tomcat requires

• A table, often called the users table, that contains at least two columns, one for the logon identifier and one for the password.
  
  
• Another table, often called the roles table, that contains a least two columns, one for the logon identifier that uses the same column name as the users table, and one for the name of a role assigned to that logon identifier.

Note that a given logon identifier must be unique but can be assigned to any number of roles. You are free to choose any table name and any column name because Tomcat can be configured to use the names you have chosen.

Follow these instructions to create a realm.

1. Download the Sql.zip file.
   
   
2. Extract Sql.zip into a convenient directory and review the files provided.
   
   CrtUsers.bat is used to create and initialize your tables. It uses the Cloudscape ij utility to process the CrtUsers.sql SQL script.
   
   DrpUsers.bat is used to drop your tables. It is handy when you have made an error so severe, the best option is to erase the tables and start over. Like CrtUsers.bat, it uses the Cloudscape ij utility to process the DrpUsers.sql SQL script.
   
   
3. In CrtUsers.bat, check the values of the CLOUDSCAPE_HOME and CATALINA_HOME environment variables. If necessary, edit the file and change the values to match your installation.
   
   
4. Similarly, in DrpUsers.bat, check the values of the CLOUDSCAPE_HOME and CATALINA_HOME environment variables. If necessary, edit the file and change the values to match your installation.
   
   
5. Edit CrtUsers.sql.
   
   
   1. If necessary, modify the CONNECT statement so that the JDBC URL specifies the directory where you would like the database to be created.
      
      
   2. If necessary, modify the schema for the tables named Users and Roles.
      
      
   3. Modify the first INSERT statement to define your site administrator. If you have modified the Users table schema, be sure to make the matching changes to the Insert statement.
      
      
   4. If necessary, add INSERT statements for any other users and roles that you would like to add during initialization.
      
      
   5. To be compatible with the Tomcat demonstration programs, the script contains definitions for three users, named tomcat , both , and role1 . If you intend to use the sample programs, modify these definitions to match your schema changes. Otherwise, just delete the user definitions and their role definitions.
      
      
   6. Save the modified SQL script.
      
      
6. Edit DrpUsers.sql.
   
   
   1. If necessary, modify the CONNECT statement so that the JDBC URL matches the value specified in CrtUsers.sql.
      
      
   2. If necessary, modify the DROP statements so that the table names match the names specified in CrtUsers.sql.
      
      
   3. Save the modified script.
      
      
7. Open a command prompt, change the current directory to the directory that contains the scripts, and execute CrtUsers.bat. Examine the output to verify that all the commands were processed successfully. If there are errors or if you decide to make changes, execute DrpUsers.bat to drop the tables and start over.

Once you have implemented a realm in Cloudscape tables, you must configure Tomcat to use the realm. There are two alternatives.

1. Define a JDBC realm. This is the simplest solution if the database is used exclusively by Tomcat for container-managed security.
   
   
2. Define a data source realm. This is the best solution if Web applications also read and/or update the information in the realm's tables. In this case, you will benefit from efficient connection pooling and keep all the Web applications independent of the database driver and JDBC URL.

Back to top

Configuring a Tomcat JDBC realm that uses Cloudscape

Follow these instructions to configure a JDBC realm that uses Cloudscape.

1. Stop the Tomcat server.
   
   
2. Back up and edit the Tomcat server configuration (InstallDir \conf\server.xml where InstallDir is replaced with the Tomcat installation directory name).
   
   
   1. Find the <Engine> element in the configuration document.
      
      
   2. If necessary, remove any existing <Realm> elements nested in the <Engine> element.
      
      
   3. Add the following <Realm> element to the <Engine> element.
      

      <Realm
        className="org.apache.catalina.realm.JDBCRealm"
        debug="99"
        driverName="org.apache.derby.jdbc.EmbeddedDriver"
        connectionURL="jdbc:derby:C:\Program Files\Apache Software 
          Foundation\Tomcat 5.0\Realm"
        userTable="Users"
        userNameCol="LogonId"
        userCredCol="Password"
        userRoleTable="Roles"
        roleNameCol="Role"
      />
      

      
      If necessary, replace the attribute values for userTable , userNameCol , userCredCol , userRoleTable , and roleNameCol to match the values used in your schema.
      
      In this example, the connectionURL parameter specifies the Cloudscape database that implements the realm. It should match the URL that was used to create the tables so you may need to change the value to match your environment. In addition, if your database requires user authentication, you will need to add values for connectionUser and connectionPassword attributes.
      
      
   4. Save the updated file.
      
      
3. Start the Tomcat server.
   
   
4. Test the logon identifiers and roles that you defined in your initial realm. Be sure that the Tomcat administrator can use the administration Web application. Exercise both valid and invalid use cases.
   
   
5. Remove the extraneous users and roles from the realm as soon as it is practical. Update your realm as necessary.
   
   

Back to top

Configuring a Tomcat data source realm that uses Cloudscape

Follow these instructions to configure a data source realm that uses Cloudscape.

1. Stop the Tomcat server.
   
   
2. Back up and edit the Tomcat server configuration (InstallDir \conf\server.xml where InstallDir is replaced with the Tomcat installation directory name).
   
   
   1. Find the <GlobalNamingResources> element in the configuration document.
      
      
   2. Add the following element into the <GlobalNamingResources> element.
      

      <!-- Datasource for Cloudscape realm database -->
      <Resource
        name="jdbc/Realm"
        type="javax.sql.DataSource"
        auth="Container"
        description="Cloudscape realm database"
      />
      

      
      Note that the value specified for the name attribute becomes the data source name and must match the references in the other elements.
      
      
   3. Add the following element into the <GlobalNamingResources> element after the <Resource> element added above.
      
      

      <!-- Resource parameters for the Cloudscape realm database -->
      <ResourceParams name="jdbc/Realm">
        <parameter>
          <name>maxWait</name>
          <value>5000</value>
        </parameter>
        <parameter>
          <name>maxActive</name>
          <value>4</value>
        </parameter>
        <parameter>
          <name>url</name>
          <value>jdbc:derby:C:\Program Files\Apache Software Foundation\
          Tomcat 5.0\Realm</value>
        </parameter>
        <parameter>
          <name>driverClassName</name>
          <value>org.apache.derby.jdbc.EmbeddedDriver</value>
        </parameter>
        <parameter>
          <name>maxIdle</name>
          <value>2</value>
        </parameter>
      </ResourceParams>
      

      
      Note that the value of the name attribute must be the same as the value specified for the name attribute in the matching <Resource> element.
      
      In this example, the url parameter specifies the Cloudscape database that implements the realm. It should match the URL that was used to create the tables so you may need to change the value to match your environment. In addition, if your database requires user authentication, you will need to add parameter definitions for user and password .
      
      
   4. Find the <Engine>> element in the configuration document.
      
      
   5. If the current <Engine> element does not have a <DefaultContext> , add the following segment at the beginning of the <Engine> element. Otherwise, copy the <ResourceLink> element from this example into the existing <DefaultContext> .
      

      <!-- The default context element is used to provide configuration
      settings to represent default configuration properties for
      contexts that are automatically created.
      -->
      <DefaultContext>
      <!-- This resource link adds the global data source to all
      Web applications.
      -->
      <ResourceLink
        name="jdbc/Realm"
        global="jdbc/Realm"
        type="javax.sql.DataSource"
      />
      </DefaultContext>
      

      
      Note that the value of the name attribute must be the same as the value specified for the name attribute in the matching <Resource> element.
      
      This example assumes that the data source is to have a global scope. Alternatively, to limit the scope of a data source, the <ResourceLink> element can be placed in the context of each Web application that will reference the data source. See the Tomcat configuration documentation for more information.
      
      
   6. If necessary, remove any existing <Realm> elements nested in the <Engine> element.
      
      
   7. Add the following <Realm> element to the <Engine> element.
      

      <Realm
          className="org.apache.catalina.realm.DataSourceRealm"
          debug="99"
          dataSourceName="jdbc/Realm"
          userTable="Users"
          userNameCol="LogonId"
          userCredCol="Password"
          userRoleTable="Roles"
          roleNameCol="Role"
      />
      

      
      If necessary, replace the attribute values for userTable , userNameCol , userCredCol , userRoleTable , and roleNameCol to match the values used in your schema.
      
      
   8. Save the updated file.
      
      
3. For each Web application that will read or update the Cloudscape realm, back up and edit the Web application deployment descriptor (InstallDir \webapps\AppName \WEB-INF\web.xml where InstallDir is replaced with the Tomcat instal