JDBC-CONNECTION POOL

[Ramesh Sunkara]

Hi Guys,

I am unable to connect to DATABASE

This is the code I used in my Java file to get connection:

	try
 	   {

		javax.naming.InitialContext context=new InitialContext(); //here am getting exception

		//Look up the data source( ADMIN 1)
		javax.sql.DataSource dataSource1=(javax.sql.DataSource)context.lookup("java:comp/env/jdbc/admin1");

		//Get Connection from the pool
		java.sql.Connection conn1=dvr_dataSource.getConnection();

		//Look up the data source( ADMIN2)
		javax.sql.DataSource dataSource2=(javax.sql.DataSource)context.lookup("java:comp/env/jdbc/admin2");

		//Get Connection from the pool
		java.sql.Connection conn2=asset_dataSource.getConnection();

	    }catch(Exception ex){
		System.out.println("Cannot get DBconnection");
           }

Server.xml

<Context path="/dbcp" docBase="dbcp" debug="5" reloadable="true" crossContext="true">

      <Resource name="jdbc/adamin1" auth="Container" type="javax.sql.DataSource" />

                <ResourceParams name="jdbc/admin1">
  	     <parameter>
		<name>factory</name>
		<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
	     </parameter>

	    <parameter>
		<name>driverClassName</name>
		<value>oracle.jdbc.OracleDriver</value>
	     </parameter>

	    <parameter>
		<name>url</name>
		<value>jdbc:oracle:thin:@16.18.7.50:1521:VINT</value>
	     </parameter>

	    <parameter>
		<name>username</name>
		<value>admin1</value>
	     </parameter>

	       <parameter>
		<name>password</name>
		<value>1*admin</value>
	      </parameter>

	       <parameter>
		<name>maxActive</name>
		<value>20</value>
	      </parameter>

	       <parameter>
		<name>maxIdle</name>
		<value>10</value>
	      </parameter>

	      <parameter>
		<name>maxWait</name>
		<value>-1</value>
	      </parameter>
              </ResourceParams>
       


             <Resource name="jdbc/adamin2" auth="Container" type="javax.sql.DataSource" />

                   <ResourceParams name="jdbc/admin2">
  	     <parameter>
		<name>factory</name>
		<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
	     </parameter>

	    <parameter>
		<name>driverClassName</name>
		<value>oracle.jdbc.OracleDriver</value>
	     </parameter>

	    <parameter>
		<name>url</name>
		<value>jdbc:oracle:thin:@16.18.7.50:1521:VINT</value>
	     </parameter>

	    <parameter>
		<name>username</name>
		<value>admin2</value>
	     </parameter>

	       <parameter>
		<name>password</name>
		<value>2*admin</value>
	      </parameter>

	       <parameter>
		<name>maxActive</name>
		<value>20</value>
	      </parameter>

	       <parameter>
		<name>maxIdle</name>
		<value>10</value>
	      </parameter>

	      <parameter>
		<name>maxWait</name>
		<value>-1</value>
	      </parameter>
              </ResourceParams>
      
</Context>

--------------------------------------------------------------------------------------------------------

Web.xml

<resource-ref>
<description>DVR DATA SOURCE</description>
<res-ref-name>jdbc/admin1</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-sharing-scope>Shareable</res-sharing-scope>
<res-auth>Container</res-auth>
</resource-ref>

<resource-ref>
<description>ASSETDATA SOURCE</description>
<res-ref-name>jdbc/admin2</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 downloaded jndi.jar and kept it in jre/lib/ext and even in tomcat lib also and set both class paths

[Ramesh Sunkara]

This is the Exception I got

Exception in thread "main" javax.naming.NoInitialContextException: Need to speci
fy class name in environment or system property, or as an applet parameter, or i
n an application resource file:  java.naming.factory.initial
        at javax.naming.spi.NamingManager.getInitialContext(Unknown Source)
        at javax.naming.InitialContext.getDefaultInitCtx(Unknown Source)
        at javax.naming.InitialContext.getURLOrDefaultInitCtx(Unknown Source)
        at javax.naming.InitialContext.lookup(Unknown Source)
        at mediacloud.Recordings.main(Recordings.java:28)

My CLASSPATH variable

C:\Program Files\Tomcat 5.5\common\lib\servlet-api.jar;
C:\ORACLE\product\10.2.0\client_1\jdbc\lib\classes12.jar;
C:\Program Files\Tomcat 5.5\common\lib\ojdbc14.jar;
C:\Program Files\Java\jdk1.6.0_18\bin;
C:\Program Files\Java\jdk1.6.0_18\jre\lib\ext\jndi.jar;
C:\Program Files\Tomcat 5.5\common\lib\jndi.jar;

[Ulf Dittmer]

Is that the complete stack trace? That looks as if the code is not running as part of a web app; is it? If not, then there will indeed not be a JNDI context to use - standalone applications don't have one unless you set it up as described in http://java.sun.com/products/jndi/tutorial/beyond/env/context.html

[Rob Spoor]

Moving to JDBC

[Ramesh Sunkara]

No its is in web-application only.
Just for debugging I wrote main function and called from command prompt

[Ulf Dittmer]

Just for debugging I wrote main function and called from command prompt

You can't run it in this way outside of the container. What exception are you getting if you run it inside of a servlet container?

[Ramesh Sunkara]

 public  ArrayList getResults(String subsId)throws SQLException
  	{
    		 		   	
	         String sql = "SELECT CALLSIGN,PROGRAM_NAME,RECORD_ACTUAL_ST_TIME,ASSET_ID FROM DVR_REQUEST_TB REQ,
                                DVR_SCHEDULE_TB SCH,DVR_SCHEDULE_ASSET_TB ASS WHERE REQ.SUBS_ID="+subsId+" 
                               AND REQ. REQUEST_ID=SCH. REQUEST_ID AND SCH.SCHEDULE_ID=ASS.SCHEDULE_ID";
	   try{
		  ps1=dvr_conn.prepareStatement(sql); // Here am getting Null Pointer Exception. But when I executed the same command in oracle am able to get results
		  rs1=ps1.executeQuery();

	  	   while(rs1.next())
		     { 
		      recordingsDS t=new recordingsDS();	
		       location_query = "SELECT LOCATION FROM DVR_ASSET_FORMAT_TB WHERE ASSET_ID="+rs1.getInt(4);
		       ps2=asset_conn.prepareStatement(location_query);
		       rs2=ps2.executeQuery();
		          while(rs2.next())
		            {	
		               temp2="http://"+rs2.getString(1);
		               t.setLocation(temp2);
		             }
		          t.setCallSign(rs1.getString(1));
		          t.setProgName(rs1.getString(2));
		          t.setTime(rs1.getDate(3));
		          records.add(t);
		       }
	            }
                    catch(Exception e){
		              e.printStackTrace();
		       }
	            finally {
                                            
                                try{
			              rs1.close();ps1.close();dvr_conn.close();rs2.close();ps2.close();asset_conn.close();
		                      }
		                 catch(Exception e){}
		      }		

	   return(records);
 	}

}

I checked for logs files for the Exception

[Ulf Dittmer]

I checked for logs files for the Exception

What does this mean - that there were no exceptions? If so, does the code work correctly? If not, what does it do?

[Ramesh Sunkara]

Ya am not getting any error or exception.

Just my jsp page is being blank [ am using the resultset retuend contents to dispaly. Nothinh is coming in jsp page ]

[Paul Sturrock]

Ramesh Sunkara wrote:Ya am not getting any error or exception.

Just my jsp page is being blank [ am using the resultset retuend contents to dispaly. Nothinh is coming in jsp page ]

..and there is no HTML source?

[Ramesh Sunkara]

<%@page import ="java.util.ArrayList,java.util.Iterator,mc.recordingsDS,mc.Recordings"%>
<html >
<head>
</head>
<body >
<div id="content">
	<ul>

	<%	HttpSession record=request.getSession(false);
		ArrayList<recordingsDS> records=new ArrayList<recordingsDS>();
  		Recordings rec=new Recordings();
		String subsId =(String)record.getAttribute("subsId");
		try{
		records=rec.getResults(subsId);
		}catch(Exception e){
		out.println("No Programs .");
		}
		Iterator itr=records.iterator();
		recordingsDS t=new recordingsDS();
		
		 while(itr.hasNext()) {
			t=(recordingsDS)itr.next();
		%>
		<li class="menu">
		<a href="<%=t.getLocation()%>" >
		<span class="name"><%out.println(t.getCallSign());%></span><span class="play"></span><span class="name"></span><span class="name">
		<%out.println(t.getProgName());%></span><span class="comment"><%out.println(t.getTime());%></span><span class="arrow"></span></a>
		</li>
	<%  }%>
	</ul>
</div>

<div id="footer">
	<a href="http://athp.hp.com/">Powered by HP</a></div>
</body>
</html>

This is my JSP page

[Ulf Dittmer]

I just noticed that the Java code you posted earlier contains a comment " // Here am getting Null Pointer Exception." - so are you in fact getting an exception? If so, which object is null?

Does your last post mean that the HTML page is completely blank? Of just that some parts of the JSP page that you thought should get executed are not in fact executed, and that the rest of the JSP created HTML output just fine? TellTheDetails

[Ramesh Sunkara]

"the rest of the JSP created HTML output is just fine"-- exaclty this is happening in my case.

[Joe San]

Refer to this post here. It might be helpful for you!

https://coderanch.com/t/489828/Tomcat/JDBC-Exception-JNDI-lookup

[Ramesh Sunkara]

Hi Jothi Shankar,

I did coded just as you said in your link. But I cant figure out. Please help me

[Ulf Dittmer]

Ramesh Sunkara wrote:"the rest of the JSP created HTML output is just fine"-- exaclty this is happening in my case.

OK, finally you're starting to tell us the details. But we still don't know if there is or is not an exception happening anywhere along the code path ... is there? If so, which one? In which line of code? If it's a NPE, which object is null? Etc. etc.

[Ramesh Sunkara]

Hello Ulf Dittmer,

I am ready to tell all the details required. The reason why I didn't pasted all my code is I thought of giving only relevant code. So that .it saves time of who helps me.

Am not "getting any output" displayed in my JSP which I retrieved from DB. When I used plain DB conncetion without using connection pool everything worked fine.

Since I am not getting any output displayed, I checked log file tomact/logs/ I found the "NullPointerException at line:53" Which i commented in my post

[Ulf Dittmer]

OK, so it looks as if the "dvr_conn" reference is null. Where (and how) are you assigning a value to that?

[Ulf Dittmer]

Another issue to ponder is that you should set up JNDI as it's described in http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html (which has no ResourceParam elements).

Also note that you're using different names for the Resource and ResourceParam elements - very confusing, to say the least, and possibly incorrect.

[Ramesh Sunkara]

public void Recordings()
	{
	
	            try
 	                {
		Context envContext=(Context)new InitialContext().lookup("java:comp/env");

		//Look up the data source(DVR ADMIN)
		javax.sql.DataSource dvr_dataSource=(javax.sql.DataSource)envContext.lookup("jdbc/dvradmin"); 

		//Get Connection from the pool(dvr_conn)
		dvr_conn=dvr_dataSource.getConnection();

		//Look up the data source(ASSET ADMIN)
		javax.sql.DataSource asset_dataSource=(javax.sql.DataSource)envContext.lookup("jdbc/assetadmin");

		//Get Connection from the pool
		 asset_conn=asset_dataSource.getConnection();

	                 }catch(Exception ex){
		System.out.println("Cannot get DBconnection");}
                   }

[Ramesh Sunkara]

"Also note that you're using different names for the Resource and ResourceParam elements - very confusing, to say the least, and possibly incorrect. "

Its just typing mistake I corrected it now