Re: Help please with derby + tomcat

[Stanley Bradbury <St...@gmail.com>]

gary gilbert wrote:

> Stan,
>  
> I saw a post you made a while back on implementing derby in embedded 
> mode with tomcat.  I am a real newbie with both tomcat and derby but I 
> am trying to set up a JSP (which I am also a newbie at) web 
> application to be able to use derby on tomcat.
>  
> I created a directory in my tomcat_home directory called 
> Databases/JPetStoreDB and put the derby directory tree in there.
>  
> I then put the following in my server.xml
>  
> <!-- Global Datasource for Derby JPetStoreDB database -->
>          <Resource name="jdbc/JPetStoreDB"
>               type="javax.sql.DataSource"  auth="Container"
>               description="Derby database for JPetStoreApp"
>               maxActive="100" maxIdle="30" maxWait="10000"
>               username="" password=""
>               driverClassName="org.apache.derby.jdbc.EmbeddedDriver "
>               url="jdbc:derby:Databases/JPetStoreDB"/>
>
> and the finally in  webapps/myapp/WEB_INF/web.xml I put the following
>  
>    <resource-ref>
>     <description>JPetStore DataSource</description>
>  <res-ref-name>jdbc/JPetStoreDB</res-ref-name>
>  <res-type>javax.sql.DataSource</res-type>
>  <res-auth>Container</res-auth>
>  <res-sharing-scope>Shareable</res-sharing-scope>
>    </resource-ref>
>  
> I created a index.jsp file and imported the jstl taglib for sql and 
> tried to connect
>  
> <sql:query var="myquery" dataSource="jdbc/JPetStoreDB">
> select * from category
> </sql:query>
>  
> when I tried to run the code I got errors null pointer, no driver 
> found etc.  The only way I could get it to work is if I used absolute path
>  
> <sql:query var="myquery" 
> dataSource="jdbc:derby:c:\tomcat\Databases\JPetStoreDB,org.apache.derby.jdbc.EmbeddedDriver">
> select * from category
> </sql:query>
>  
> unfortunately the absolute path is not what I want to work :( 
>  
> Any help/advice would be greatly appreciated.
>  
> Sincerely,
>  
> Gary Gilbert

Hi Gary -
I have not used the SQL Tag library - if the following information does 
not resolve your problem I recommend posting the question to the tomcat 
user list at users@tomcat.apache.org - many very knowledgeable people 
respond to issues posted there.  I am spinning up myself on J2EE issues 
so did a little research on the SQL tag library and found the following  
information in the Sun J2ee Tutorial.  It appear that you need to use 
the tag "setDataSource" instead of dataSource.  The fact that you have 
to supply a completely qualified Derby connection URL 
'jdbc:derby:c:\tomcat\Databases\JPetStoreDB' indicates to me that the 
Global JNDI datasource is not being used (it is reading the entry as 
DriverManager parameters).  The example from the Sun Tutorial is:

---
The setDataSource tag allows you to set data source information for the 
database. You can provide a JNDI name or DriverManager parameters to set 
the data source information. All of the Duke's Bookstore pages that have 
more than one SQL tag use the following statement to set the data source:
The setDataSource tag allows you to set data source information for the 
database. You can provide a JNDI name or DriverManager parameters to set 
the data source information. All of the Duke's Bookstore pages that have 
more than one SQL tag use the following statement to set the data source:

<sql:setDataSource dataSource="jdbc/BookDB" />

The query tag performs an SQL query that returns a result set. For 
parameterized SQL queries, you use a nested param tag inside the query tag.

In bookcatalog.jsp, the value of the Add request parameter determines 
which book information should be retrieved from the database. This 
parameter is saved as the attribute name bid and is passed to the param 
tag.

<c:set var="bid" value="${param.Add}"/>
<sql:query var="books" >
  select * from PUBLIC.books where id = ?
  <sql:param value="${bid}" />
</sql:query>

---   You might want to compare this version of |bookcatalog.jsp| to the 
versions in JavaServer Pages Technology <JSPIntro.html#wp100465> and 
Custom Tags in JSP Pages <JSPTags.html#wp74644> that use a book database 
JavaBeans component.

<sql:query var="books" 
  dataSource="${applicationScope.bookDS}">
  select * from PUBLIC.books where id = ?
  <sql:param value="${bid}" />
</sql:query>

----

The URL for this is:  
http://java.sun.com/j2ee/1.4/docs/tutorial/doc/JSTL7.html

I am cc'ing the derby-user maillist so others interested in using Derby 
with Tomcat can find this information as well.

HTH