Back to Workshop Selecting a Java IDE Problem playing DTMF tones (source code) Installing Cloudscape/Derby with Tomcat and ODBC Free, heavy duty, databases Accessing GET data from JSF and Studio Creator/NetBeans VWP A Web Presence at Minimum Cost RAD Web Development Tools HTML/CSS Multi column layout
After a lengthy struggle I seem to have the Apache Derby/IBM Cloudscape database installed and working with a Tomcat server and I can access a database via an ODBC connection. The operating system is Windows XP. These notes are intended to save you some time if you want to do something similar. This page is incomplete because I don't completely understand what I am doing. Please insert "I think" or "Maybe" before every sentence and be particularly careful if it is already there.
I chose Cloudscape from the IBM Cloudscape site instead of Apache Derby because Cloudscape has a Microsoft-style installer and, at the time, only the Cloudscape documentation was up to IBM standards. The Derby documentation should be comparable by now. The same database is offered by Sun who have renamed it Java DB. The name Derby is used here to refer to all versions.
An ODBC connection allows Microsoft Access to be used as a front end to Derby and allows database access from Microsoft compilers. If you do not need the latter consider using OpenOffice Base instead of Access and avoid the need for an ODBC connection.
An ODBC connection requires the use of a DB2 driver which restricts the location of the database and the names you can use for, and in, your Derby database. I think DB2 limits database names to eight characters in length. Table and Column names may be restricted to 18 characters. In both cases you should make all the alphabetic characters upper case, start with a letter and use only alphabetic, numeric and the underscore characters. The database must be in the Derby system directory when you access it via an ODBC connection. Unfortunately, the Derby system directory must be specified every time you invoke Derby.
" Installing DB2 Run Time Client Windows "
describes how to install the DB2 ODBC driver and connect to a Derby database. Before you begin you should change the "Start In" directory for all the Derby and DB2 commands to point to your Derby system directory. Mine is C:\Data\Derby. The Derby system directory defaults to the current directory so, for these commands, it will be the "Start In" directory.
The article includes the line
ij>connect 'jdbc:derby:net://localhost:1527/DataBaseName;create=true:user=usr;password=pwd;';
I believe that uses the DB2 drivers and should be updated to use the new Derby drivers so it should be
ij>connect 'jdbc:derby://localhost:1527/DataBaseName;create=true;user=usr;password=pwd;';
The single quotes are required. You probably already have a database and you have not implemented password protection so you would use
ij>connect 'jdbc:derby://localhost:1527/MYDB;';
Note that if you need to specify a path to your database, the name is too long or the name is not in an acceptable format the Derby connection will succeed but the subsequent DB2 actions will fail.
The "enterprise scenario" in
" Integrate Cloudscape Version 10 or Derby with Tomcat "
results in an installation of Derby which runs in the same JVM as Tomcat. As a consequence, the Derby system directory defaults to the Tomcat home directory. I added the line
-Dderby.system.home=C:\Data\Derby
to the Java Options in the Java tab of the Apache Tomcat Properties. The Java tab is displayed if the Configure option is chosen from the System Tray icon but not if properties is selected from the services directory listing. You can also insert this line via the registry.
To get HelloCloudscape.jsp to work I had to add a file InstallDir\webapps\CloudscapeEnterprise\META-INF\context.xml containing the text
<?xml version="1.0" encoding="UTF-8" ?>
<Context>
<Resource auth="Container"
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."
name="jdbc/Tours" type="javax.sql.DataSource" driverClassName="org.apache.derby.jdbc.EmbeddedDriver" maxIdle="2" maxWait="5000"
url="jdbc:derby:C:\Program Files\IBM\Cloudscape_10.0\demo\databases\toursDB" maxActive="4" />
</Context>
