JNDI datasource question

[Nikhi l Shah]

I am doing a very simple test application trying to connect MSSQL database from my JSP page

I defined a datasource using Tomcat web Server. I see following entry in server.xml file:

    <Resource
      name="LTdata"
      type="javax.sql.DataSource"
      password="password"
      driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
      maxIdle="2"
      maxWait="5000"
      username="user"
      url="jdbc:sqlserver://dbserver;DatabaseName=dbname;"
      maxActive="4"/>

In my JSP I am getting an error on this line:

DataSource ds = (DataSource)ctx.lookup("java:/comp/env/LTdata");   

Do you see any obvious error?


The error:

javax.servlet.ServletException: Name LTdata is not bound in this Context

[amit punekar]

Hi,
Declare the Resource reference in web.xml file as below

  
	<resource-ref>
		 <description>Your datasource desc</description>
		 <res-ref-name>LTdata</res-ref-name>
	 	<res-type>javax.sql.DataSource</res-type>
	 	<res-auth>Container</res-auth>
	</resource-ref>

[Nikhi l Shah]

Thanks Amit for your reply

I did your recommended change, now I am getting:

javax.naming.NameNotFoundException: Name LTdata is not bound in this Context
at org.apache.naming.NamingContext.lookup(NamingContext.java:770)

Any suggestion?

Thanks again for your help, (btw, I am also punekar )

[amit punekar]

hi,
there is an extra "/" in "java:/comp/env/LTdata".
It should be "java:comp/env/LTdata".

[Nikhi l Shah]

Thanks amit I removed that extra slash earlier.

[Nikhi l Shah]

I am getting a little different error now:

java.sql.SQLException: No suitable driver
at java.sql.DriverManager.getDriver(DriverManager.java:243)

I am connecting to MSSQL so I have 2 dll

\webapps\gdesktop\WEB-INF\lib\sqljdbc4.jar
\webapps\gdesktop\WEB-INF\lib\sqljdbc.jar

[Joe Ess]

Since JNDI is a server resource, you have to put your JDBC driver where the server can find it. I have mine in $CATALINA_HOME/common/lib

[Nikhi l Shah]

I got it in common/lib directory

I opened the page where I get the error and then tried to move the 2 files sqljdbc4.jar & sqljdbc.jar. I got the error that file is in use and cnnot be moed. So looks like the jar files are getting used but still I get this error:

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: Cannot create JDBC driver of class '' for connect URL 'null'
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:460)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:355)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.genesyslab.uadthin.filter.CachePreventFilter.doFilter(CachePreventFilter.java:30)
com.genesyslab.uadthin.filter.UTF8Filter.doFilter(UTF8Filter.java:127)


root cause

javax.servlet.ServletException: Cannot create JDBC driver of class '' for connect URL 'null'
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:841)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:774)
org.apache.jsp.custom.ltCustomerData_jsp._jspService(ltCustomerData_jsp.java:85)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.genesyslab.uadthin.filter.CachePreventFilter.doFilter(CachePreventFilter.java:30)
com.genesyslab.uadthin.filter.UTF8Filter.doFilter(UTF8Filter.java:127)


root cause

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1150)
org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
org.apache.jsp.custom.ltCustomerData_jsp._jspService(ltCustomerData_jsp.java:61)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.genesyslab.uadthin.filter.CachePreventFilter.doFilter(CachePreventFilter.java:30)
com.genesyslab.uadthin.filter.UTF8Filter.doFilter(UTF8Filter.java:127)


root cause

java.sql.SQLException: No suitable driver
java.sql.DriverManager.getDriver(DriverManager.java:243)
org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1143)
org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
org.apache.jsp.custom.ltCustomerData_jsp._jspService(ltCustomerData_jsp.java:61)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.genesyslab.uadthin.filter.CachePreventFilter.doFilter(CachePreventFilter.java:30)
com.genesyslab.uadthin.filter.UTF8Filter.doFilter(UTF8Filter.java:127)

[Nikhi l Shah]

My JSP code:

<%@ page language="java" buffer="none" contentType="text/html; charset=utf-8" %>
<%@ page import="javax.naming.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="com.microsoft.sqlserver.jdbc.*" %>

<%
    //try{
      Context ctx = new InitialContext();
      if(ctx == null ) 
          throw new Exception("Boom - No Context");

      DataSource ds = (DataSource)ctx.lookup("java:comp/env/LTdata");

      if (ds != null) {
        Connection conn = ds.getConnection();
              
        if(conn != null)  {
            out.println("Got Connection "+conn.toString());
            Statement stmt = conn.createStatement();
            ResultSet rst = stmt.executeQuery("select getdate()");
            if(rst.next()) {
            }
            conn.close();
        }
      }
    //}catch(Exception e) {
    //  e.printStackTrace();
	//  	out.println("Nikhil");
    //}
 
%>
    

server.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<Server>
  <Listener className="org.apache.catalina.core.AprLifecycleListener"/>
  <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"/>
  <Listener className="org.apache.catalina.storeconfig.StoreConfigLifecycleListener"/>
  <Listener className="org.apache.catalina.mbeans.ServerLifecycleListener"/>
  <GlobalNamingResources>
    <Environment
      name="simpleValue"
      type="java.lang.Integer"
      value="30"/>
    <Resource
      auth="Container"
      description="User database that can be updated and saved"
      name="UserDatabase"
      type="org.apache.catalina.UserDatabase"
      pathname="conf/tomcat-users.xml"
      factory="org.apache.catalina.users.MemoryUserDatabaseFactory"/>
    <Resource
      name="LTdata"
      scope="Shareable"
      type="javax.sql.DataSource"
      driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
      password="password"
      maxIdle="2"
      maxWait="5000"
      validationQuery="select getdate();"
      username="user"
      url="jdbc:sqlserver://server:1433;DatabaseName=databasename;"
      maxActive="4"/>
  </GlobalNamingResources>
  <Service
      name="Catalina">
    <Connector
        port="8080"
        redirectPort="8443"
        debug="0"
        minSpareThreads="25"
        connectionTimeout="20000"
        maxThreads="150"
        maxSpareThreads="75">
    </Connector>
    <Connector
        port="8009"
        redirectPort="8443"
        minSpareThreads="100"
        maxThreads="1000"
        debug="0"
        maxSpareThreads="150"
        protocol="AJP/1.3">
    </Connector>
    <Engine
        defaultHost="localhost"
        name="Catalina">
      <Realm className="org.apache.catalina.realm.UserDatabaseRealm"/>
      <Host
          appBase="webapps"
          name="localhost">
      </Host>
    </Engine>
  </Service>
</Server>
    

[amit punekar]

Hi,
You need to put the JAR files of SQL driver in <<tomcat_dir>>\lib so that they are visible to Server runtime.

[Nikhi l Shah]

Friends Thank you very much for your help!!

Adding this entry to the context.xml file fixed my issue:

<Context>
<Resource name="LTdb" auth="Container"
type="javax.sql.DataSource" username="username" password="password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://servername:1433;DatabaseName=ocs_irv;"
maxActive="8" maxIdle="4"/>

[Ram Para]

Try after adding the following three Jars to lib.

msbase.jar
mssqlserver.jar
msutil.jar