2 Replies Latest reply on Jan 14, 2008 1:06 PM by Deon Horton

    JBOSS JSTL <sql:dataSource> issue

    Deon Horton Newbie

      I have seen many posting on this issue, but none of them solved my issue.

      I can not get <sql:dataSource> tag to connect to my data Source file.

      I am now thinking my app/server is setup wrong.

      I can get the JSP to work if I do not use the datasource file.
      I have that option commented out in the sample code below.
      Here is the quick sample JSP .

      <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
      <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>
      
      
      <sql:setDataSource dataSource="OracleDS" />
      <!-- driver="oracle.jdbc.driver.OracleDriver"
       url="jdbc:Oracle:thin:@pxxxyyyzzzz:1521:pedcd50k" user="xxxxxx"
       password="yyyyyyy" />
      -->
      <sql:query var="data" >
       select * from coll_active_campaigns
      </sql:query>
      
      <html>
       <head>
       <title></title>
       </head>
      
       <body>
       <br />
       <form>
       <table align="left" cellspacing=2 cellpadding=3 border="1" width="627"
       height="203">
       <c:forEach var="camp" items="${data.rows}">
       <tr>
       <td>
       ${camp.schedule_id}
       </td>
       <td>
       ${camp.campaign_id}
       </td>
       <td>
       ${camp.cd_campaign}
       </td>
       <td>
       ${camp.campaign}
       </td>
       <br />
       </tr>
       </c:forEach>
       </table>
      </form>
       <br />
      
       </body>
      </html>
      


      <datasources>
       <local-tx-datasource>
       <jndi-name>OracleDS</jndi-name>
       <connection-url>jdbc:oracle:thin:@pxxxxxyyyyzzzzz:1521:pedcd50k</connection-url>
      
       <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
       <user-name>xxxxxxx</user-name>
       <password>yyyyy</password>
       <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
       <!--valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name-->
       <!-- Checks the Oracle error codes and messages for fatal errors -->
       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
       <!-- sql to call when connection is created
       <new-connection-sql>some arbitrary sql</new-connection-sql>
       -->
      
       <!-- sql to call on an existing pooled connection when it is obtained from pool - the OracleValidConnectionChecker is prefered
       <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
       -->
      
       </local-tx-datasource>
      
      </datasources>
      
      


      I get this error from JBoss:

      10:43:00,131 ERROR [[jsp]] Servlet.service() for servlet jsp threw exception
      javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver"
      at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(QueryTagSupport.java:276)
      at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(QueryTagSupport.java:


      Is there something I am missing in the setup?

      The datasource file seems to be ok, it deploy correctly on startup.

      10:42:38,696 INFO [WrapperDataSourceService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=OracleDS' to JNDI name 'java:OracleDS'

      I am using JBoss 4.0.5, Oracle 10.

        • 1. Re: JBOSS JSTL <sql:dataSource> issue
          jaikiran pai Master

          Based on a similar discussion in the JCA forum, it seems that the sql:datasource looks for the datasource in the java:comp/env namespace. So in your case, it looks for java:comp/env/OracleDS

          In order to setup the datasource in the java:comp/env namespace, you will have to add the following entries in web.xml and jboss-web.xml:

          web.xml:

          <resource-ref>
           <res-ref-name>OracleDS</res-ref-name>
           <res-type>javax.sql.Datasource</res-type>
           <res-auth>Container</res-auth>
           </resource-ref>


          jboss-web.xml:

          <jboss-web>
          
           <resource-ref>
           <res-ref-name>OracleDS</res-ref-name>
           <jndi-name>java:/OracleDS</jndi-name>
           </resource-ref>
          </jboss-web>
          


          Place these contents in your web.xml and jboss-web.xml at the appropriate places and see if it works.

          P.S: I have zero experience with jstl, so i cant guarantee that this is going to work.

          • 2. Re: JBOSS JSTL <sql:dataSource> issue
            Deon Horton Newbie

            Thanks, That worked great for me!

            The part I was missing was the jboss-web.xml file.

            The JNDI name was java:OracleDS not java:/Oracle in case anyone else will follow this posting later.

            Thanks again!