• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Trouble Configuring a Data Source for MySQL using JSTL in JBoss

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a web app, deployed to JBoss and I'm trying to configure a data source. The latest error I'm getting, on attempting to access a page is:

Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver"

The web app is deployed to the 'smt' context:

Here's my code:

smt-ds.xml:

<datasources>
<local-tx-datasource>
<jndi-name>jdbc/smt</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/smt</connection-url>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>smt</user-name>
<password>pass</password>
<metadata>
<type-mapping>MySQL</type-mapping>
</metadata>
<new-connection-sql>select count(*) from quote_of_the_day</new-connection-sql>
<check-valid-connection-sql>select count(*) from quote_of_the_day</check-valid-connection-sql>
</local-tx-datasource>
</datasources>

web.xml (relevant part anyway):

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>smt</display-name>

<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/smt</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

<serv...

jboss-web.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jboss-web PUBLIC "-//JBoss//DTD Web Application 2.3//EN"
"http://www.jboss.org/j2ee/dtd/jboss-web_3_0.dtd">

<jboss-web>

<resource-ref>
<res-ref-name>jdbc/smt</res-ref-name>
<jndi-name>java:jdbc/mysql</jndi-name>
</resource-ref>

</jboss-web>

qotd.jsp (excerpt):

<sql:query var="rs" dataSource="jdbc/smt">
select quote, submitted_by, author, date_submitted
from quote_of_the_day
where id = ?
<sql aram value="${maxid.rows[0].id}" />
</sql:query>
<TABLE class="body">
<TR>
<TD align="center" class="area_title" colspan="2">Change the Quote of the Day</TD>
</TR>
<TR>
<TD colspan="2" align="center" class="alert">${param.message}</TD>
</TR>
<tr><td height="5"><!-- spacer --></td></tr>
<tr>
<td class="label" valign="top">Quote:</td>
<td>
<TEXTAREA name="new_quote" cols="30" rows="5">${rs.rows[0].quote}</TEXTAREA>
</td>
</tr>...

It deploys clean, with no errors about trying to reference any jndi or whatever. I can access other parts of the web app where there's no need for a database connection. But, if I try this page, I get:

javax.servlet.ServletException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver"
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:848)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)
org.apache.jsp.qotd_jsp._jspService(org.apache.jsp.qotd_jsp:135)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)

root cause

javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver"
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(QueryTagSupport.java:276)
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(QueryTagSupport.java:159)
org.apache.jsp.qotd_jsp._jspx_meth_sql_query_0(org.apache.jsp.qotd_jsp:268)
org.apache.jsp.qotd_jsp._jspx_meth_c_when_0(org.apache.jsp.qotd_jsp:189)
org.apache.jsp.qotd_jsp._jspx_meth_c_choose_0(org.apache.jsp.qotd_jsp:156)
org.apache.jsp.qotd_jsp._jspService(org.apache.jsp.qotd_jsp:117)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)

note The full stack trace of the root cause is available in the Apache Tomcat/5.5.9 logs.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


No suitable driver


That message is caused by either an incorrect url in your Data Source definition, or JBoss can't find the MySQL driver. Are your driver files in the lib directory for the server your have created this Data Source on?

One more this, could you also change your display name? We have a Naming Policy here which asks for a first and a last name. You can change this here. Thanks
[ April 11, 2006: Message edited by: Paul Sturrock ]
 
M Anderson
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Regarding my name, Mike Anderson is already taken, so I went with 'M'.

I have the 'mysql-connector-java-3.1.12-bin.jar' in the lib directory under my server, and you can see the URL I'm using in the code I pasted in my original post. Also, I can reach the data base using regular, plain-old Tomcat 5.0 using this URL.

Any other ideas?
[ April 11, 2006: Message edited by: M Anderson ]
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Regarding my name, Mike Anderson is already taken, so I went with 'M'.


Fair enough. You might get the odd request from other bartenders etc. keeping it as it is though.


I have the 'mysql-connector-java-3.1.12-bin.jar' in the lib directory under my server, and you can see the URL I'm using in the code I pasted in my original post. Also, I can reach the data base using regular, plain-old Tomcat 5.0 using this URL.


Which lib directory? %JBOSS_HOME%/server/%SERVER_NAME%/lib is where it should be.

Not likely to be the cause, but you could strip your DataSource definition back to the bare minimum (no new-connection-sql elements etc.). I wouldn't expect these to cause this sort of bother, but its always easier to sort a problem if you work with the smallest testable unit.

Ultimately JBoss will just be using the DriverManager for its JDBC stuff, and the rules for that are: the driver is in the classpath & the URL is correct. Since you say both are correct there is no other reason I know of that will cause this error.
 
M Anderson
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So, it's in this folder:

C:\jboss\jboss-4.0.2\server\mydefault\lib

I cut my smt-ds.xml file down to this:

<datasources>
<local-tx-datasource>
<jndi-name>jdbc/smt</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/smt</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>smt</user-name>
<password>pass</password>
</local-tx-datasource>
</datasources>

I'm still seeing the same problem...

Do you have any other suggestions, maybe, about how to go about debugging it?
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm stumped. You seem to have this configured exactly as documented. Might be worth checking the contents of your driver jar - another long shot is it might be corrupt. For debugging you could download the JBoss source and run it in a debugger - tedious, but I can't think of any reason other than the two I've already mentioned as to why this would happen.
 
M Anderson
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Fair enough. Thanks for your help. I'll be sure to post the solution when I figure it out.
 
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In addition to jar file, try putting the zip file of the driver in you server lib folder too. I have mysql-connector-java-3.1.7.zip in my classpath in addition to jar file
 
M Anderson
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So, I added the following scriptlet to one of my jsp files to see if I could get to the datasource manually:


And it works. I get a nice, neat listing of the 'names' in the 'qr_contacts' table.

Note that trying to find wouldn't work, as I might have expected based on the jboss-web.xml reference declarations. I tried putting a in my jstl tags for the dataSource attr, but it still doesn't work.

Here's the output so you can see the type of the DataSource, if it gives anyone any ideas:

10:15:29,495 INFO [STDOUT] jdbc/smt o: org.jboss.resource.adapter.jdbc.WrapperDataSource@25b64d
10:15:29,635 INFO [STDOUT] Name is: ... my data printed here, omitted on purpose ...

I'm continuing to dig, but just wanted to share what I found so far.
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry for the VERY late answer, but as I was experiencing exactly the same problems in 2009, and learned a lot from this thread I thought it might be useful to post my conclusions.
To make a long story short these conclusions are that out of the box jboss 5.0 will never work with jstl-sql tags. The problem is in the org\apache\taglibs\standard\tag\common\sql\DataSourceUtil.java class. I downloaded the Jboss sources, and found that class under jboss-5.1.0.GA-src\thirdparty\sun-jstl\lib\jstl-sources. It uses the traditional Context envCtx = (Context) ctx.lookup("java:comp/env"); jndi-lookup, but in jboss we need a simple "java:" lookup. When I changed this class to use "java:", and injected the compiled class in my jboss-5.0.1.GA\server\default\deploy\jbossweb.sar\jstl.jar, my jsp's started to work. It's a dirty solution, but at least it works...
 
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Erik Vande Velde wrote: I downloaded the Jboss sources, and found that class under jboss-5.1.0.GA-src\thirdparty\sun-jstl\lib\jstl-sources. It uses the traditional Context envCtx = (Context) ctx.lookup("java:comp/env"); jndi-lookup, but in jboss we need a simple "java:" lookup. When I changed this class to use "java:", and injected the compiled class in my jboss-5.0.1.GA\server\default\deploy\jbossweb.sar\jstl.jar, my jsp's started to work. It's a dirty solution, but at least it works...



I guess what you ended up doing, is to access the datasource from it's global JNDI name instead of the ENC name. I have no experience on JSTL, but going by one similar issue, i think the JSTL tag looks for the datasource under the ENC (i.e. java:comp/env namespace of the web module). So you will have to configure your jboss-web.xml and web.xml with appropriate resource-ref entries for the datasource. Have you done that? If yes, then please post those files and the datasource file. Also post the relevant exception stacktrace.

While posting logs or xml content or code, please remember to wrap it in a code block by using the Code button in the message editor window. Please use the Preview button to ensure that your post is correctly formatted.
 
Erik Vande Velde
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I attached the complete TestDBJboss.war file (extension changed to .wra to allow upload). It contains the datasource related stuff in web.xml and jboss-web.xml. In my deploy directory I also have a mysql-ds.xml that looks like this:
<code>
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>jdbc/TestDB</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/javatest</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>javauser</user-name>
<password>javadude</password>
</local-tx-datasource>
</datasources>
</code>
A bit of background: I'm porting a tomcat 6 web application to jboss 5.0. The test program was originally used to quick-test the datasource configuration on tomcat, to my surprise I could not simply run it on jboss. Googling around showed me that I needed the jboss-web.xml, but even that didn't really help. Looking further I stumbled on this thread, and I used Mr. Andersons scriptlet below (test.jsp in the war) to isolate the problem, and found out that the scriptlet indeed works. To get the testDB.jsp (using jstl, no scriptlet) to work I had to change the DataSourceUtil.java class as described before.
reply
    Bookmark Topic Watch Topic
  • New Topic