File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JBoss/WildFly and the fly likes Trouble Configuring a Data Source for MySQL using JSTL in JBoss Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » JBoss/WildFly
Bookmark "Trouble Configuring a Data Source for MySQL using JSTL in JBoss" Watch "Trouble Configuring a Data Source for MySQL using JSTL in JBoss" New topic
Author

Trouble Configuring a Data Source for MySQL using JSTL in JBoss

M Anderson
Greenhorn

Joined: Mar 17, 2006
Posts: 25
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.


Mike -- SCJP, SCWCD
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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 ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
M Anderson
Greenhorn

Joined: Mar 17, 2006
Posts: 25
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

Joined: Apr 14, 2004
Posts: 10336


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

Joined: Mar 17, 2006
Posts: 25
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Mar 17, 2006
Posts: 25
Fair enough. Thanks for your help. I'll be sure to post the solution when I figure it out.
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
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

Joined: Mar 17, 2006
Posts: 25
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.
Erik Vande Velde
Greenhorn

Joined: Jul 06, 2009
Posts: 2
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...
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10290
    
168

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.


[My Blog] [JavaRanch Journal]
Erik Vande Velde
Greenhorn

Joined: Jul 06, 2009
Posts: 2
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trouble Configuring a Data Source for MySQL using JSTL in JBoss