Hi Thomas,
I got this to work using the following configuration:
Tomcat 5.5.9 with these edits to the server.xml file:
<Host>
....
<Context path="/DerbyClasspathTest"
docBase="DerbyClasspathTest" debug="5"
reloadable="true" crossContext="true">
<!-- Datasource for Derby database -->
<Resource name="jdbc/derby"
type="javax.sql.DataSource" auth="Container"
description="Derby database"
driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
url="jdbc:derby:classpath:derbydb" username="APP"
password="APP" maxActive="4" maxIdle="2"/>
</Context>
</Host>
I placed the derby.jar file in
$TOMCAT_HOME/common/lib.
I added this entry to my web.xml:
<resource-ref>
<res-ref-name>jdbc/derby</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
I created a simple servlet and put this in it's init
method for a simple test:
DataSource ds = null;
Connection conn = null;
try {
// Obtain our environment naming context
Context initCtx = new InitialContext();
Context envCtx = (Context)
initCtx.lookup("java:comp/env");
// Look up our data source
ds = (DataSource) envCtx.lookup("jdbc/derby");
initCtx.close();
envCtx.close();
} catch (NamingException namingExc) {
namingExc.printStackTrace();
}
try {
conn = ds.getConnection();
String query = "select * from APP.foo";
Statement stmt = conn.createStatement();
ResultSet results = stmt.executeQuery(query);
ResultSetMetaData rsmd = results.getMetaData();
int numberCols = rsmd.getColumnCount();
while (results.next()) {
for (int i = 1; i <= numberCols; i++) {
System.out.println("Select *
from APP.foo is: "
+ results.getString(i));
}
}
results.close();
stmt.close();
} catch (SQLException sqlExcept) {
sqlExcept.printStackTrace();
}
}
I created a database, derbydb using ij and created a
table foo in it and inserted one row. I then imported
it using Eclipse to the WEB-INF/classes directory.
I created a WAR file from Eclipse and placed the WAR
file in my Tomcat webapps directory.
Then I started Eclipse from within Tomcat and called
up the servlet. The row was returned from the db
looking at the Eclipse console.
As I mentioned before I haven't used Spring so I don't
know if this is complicating anything. But I have
verified that you can connect to the database using
the database connection URL you wanted to, using the
classpath identifier.
(By the way, thanks! I need that for something I'm
working on too!)
HTH somewhat,
Susan
--- Thomas Dudziak <[EMAIL PROTECTED]> wrote:
> On 11/11/05, Susan Cline <[EMAIL PROTECTED]>
> wrote:
>
> > I'm not sure I can help, but I'm wondering if
> there is another way to do
> > what you are trying to accomplish. It sounds like
> you can only use the
> > jdbc:derby:classpath protocol to connect to a
> database if it *is* contained
> > in a jar file.
>
> The manual specifically says that it dosn't have to
> be in a jar:
>
> "In most cases, you access databases from the file
> system as described
> above. However, it is also possible to access
> databases from the
> classpath. The databases can be archived into a jar
> or zip file or
> left as is."
>
>
(http://db.apache.org/derby/docs/10.1/devguide/cdevdvlp91854.html)
>
> > You mentioned:
> >
> > Database access is configured in Spring to use the
> DriverManagerDataSource.
> >
> > Can you send me that configuration please?
>
> This is a standard Spring DriverManagerDataSource
> configuration:
>
> <bean id="dataSource"
>
>
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
> <property
>
name="driverClassName"><value>${jdbc.driverClassName}</value></property>
> <property
>
name="url"><value>${jdbc.runtime.url}</value></property>
> <property
>
name="username"><value>${jdbc.username}</value></property>
> <property
>
name="password"><value>${jdbc.password}</value></property>
> </bean>
>
>
> with the jdbc. properties defined like this:
>
>
jdbc.driverClassName=org.apache.derby.jdbc.EmbeddedDriver
> jdbc.username=app
> jdbc.password=
> jdbc.runtime.url=jdbc:derby:classpath:derbydb
>
> > Also, from the web application can you show how
> you are connecting to the
> > database?
> > Is your first attempt to access Derby in the web
> app via a JSP or via a Java
> > class?
>
> I simply use the data source that I get from Spring,
> like with
> getConnection. Nothing fancy. Also no database
> access from a JSP
> (that's evil right ?).
> Also, database access works when ij was run on the
> database before the
> webapp accesses it.
>
> > Can you send the code for this as well?
> >
> > Also, have you tried putting the database in a jar
> file and using the
> > 'classpath' protocol to see if it does work this
> way?
>
> Nope, because that isn't really an option for me as
> I want to be able
> to change data in the database, and
> databases-in-a-jar are read-only.
> The problem is that I cannot really use a
> "directory" database url
> because then I would have to hardcode the path where
> the webapp gets
> deployed, into the configuration which of course is
> not good. And
> giving a relative path is also nto possible as I
> don't know what the
> 'current directory' is when the webapp starts - this
> depends on the
> used webserver, and how the webserver was started.
>
> Tom
>
