Bug 22159

Summary: Getting following error => java.sql.SQLException: Logical handle no longer valid
Product: Tomcat 4 Reporter: Raju Joshi <rs_joshi>
Component: CatalinaAssignee: Tomcat Developers Mailing List <dev>
Status: RESOLVED INVALID    
Severity: blocker    
Priority: P3    
Version: 4.1.24   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Raju Joshi 2003-08-06 06:40:06 UTC
When trying to run the following JSP with Tomcat I am getting the logical 
handle error.
This error is not generated when I run the same code as a Java application.
Also I am able to run this jsp successfully with iPlanet servers.
Please change the oracle server, username,password in the jsp code below to 
values appropriate to your installations.


<%@page language="java" session="false" %>
<%@ page import="java.sql.CallableStatement"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.ResultSetMetaData"%>
<%@ page import="java.sql.SQLException"%>
<%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.Types"%>
<%@ page import="java.sql.Date"%>

<%@ page import="oracle.jdbc.pool.OracleConnectionCache"%>
<%@ page import="oracle.jdbc.pool.OracleConnectionCacheImpl"%>
<%@ page import="oracle.jdbc.pool.OracleConnectionPoolDataSource"%>
<%@ page import="oracle.sql.STRUCT"%>
<%@ page import="oracle.sql.StructDescriptor"%>
<%@ page import="oracle.sql.ARRAY"%>
<%@ page import="oracle.sql.ArrayDescriptor"%>
<%@ page import="java.util.StringTokenizer"%>

<html>
<head><title>Connection test</title>
</head>
<body>
<h1>Connection test</h1>

<%!
 private static OracleConnectionCache connCache = null;

 private static Connection getConnFromCache (String url, String user, String 
passwd)
  	throws SQLException
 {
   Connection conn;
   OracleConnectionPoolDataSource ocpds = null;
   OracleConnectionCacheImpl conn_cache = null;

   if (connCache == null)
    {
	 ocpds = new OracleConnectionPoolDataSource ();

	 ocpds.setURL (url);
	 ocpds.setUser (user);
	 ocpds.setPassword (passwd);

	 conn_cache = new OracleConnectionCacheImpl ();
	 conn_cache.setConnectionPoolDataSource (ocpds);
	 conn_cache.setURL (url);
	 conn_cache.setUser (user);
	 conn_cache.setPassword (passwd);

	 conn_cache.setCacheScheme (OracleConnectionCacheImpl.DYNAMIC_SCHEME);
	 conn_cache.setMaxLimit (10);
	 conn_cache.setMinLimit (1);

	 connCache = conn_cache;
	}

   	conn = connCache.getConnection (user, passwd);
   	return conn;
  }


 public static oracle.sql.STRUCT get_mystruct_instance (String sValue, Object[] 
oaString, Float oFloat, String sDate, Connection oConn)
  throws SQLException{
	 StructDescriptor oStDesc = StructDescriptor.createDescriptor 
("EMP_REC1", oConn);
	 Object oaStructAttrs [] = new Object [4];

	 ArrayDescriptor oArrDesc = ArrayDescriptor.createDescriptor
("STRARRAY", oConn);
	 ARRAY oArray = new ARRAY (oArrDesc, oConn, oaString);
	 
	 oaStructAttrs[0] = sValue;
	 oaStructAttrs[1] = oArray;
	 oaStructAttrs[2] = oFloat;
	 oaStructAttrs[3] = Date.valueOf (sDate);

	 STRUCT mystruct_inst = new STRUCT (oStDesc, oConn, oaStructAttrs);
	 return mystruct_inst;
 }

 public void main (String [] args) throws SQLException
  {
   	 if (args.length < 3){
	 	System.out.println ("Usage: java MyApplication <DB url> <DB 
username> <DB password>");
	 	System.exit (1);
	 }

   	 Connection myConn = null;
   	 Statement myStmt = null;
   	 try{
		 myConn = getConnFromCache (args [0], args [1], args [2]);
		 myStmt = myConn.createStatement ();
		 
		 /* DB Schema Creation */
		 try{
		 	myStmt.executeUpdate("Drop table emp_list1");
		 	myStmt.executeUpdate ("create or replace type strarray 
is array(10) of varchar2(30)");
		 	myStmt.executeUpdate ("create or replace type emp_rec1 
as object (empname varchar2(30), addr_list strarray,salary float,join_date 
date)");
		 }catch(Exception e){;} // ignore if objects already exists
		 
		 myStmt.executeUpdate ("create table emp_list1 (empno integer 
primary key, emprec emp_rec1 )");
		 myStmt.executeUpdate ("create or replace procedure 
insert_emp_rec1(in1 in Integer, in2 in emp_rec1) as begin insert into emp_list1 
values(in1, in2); end;");
		 myStmt.executeUpdate ("create or replace procedure get_emp_rec1
(in1 in Integer, in2 out emp_rec1) as begin select emprec into in2 from 
emp_list1 where empno = in1; end;");
		 myStmt.close ();

		 /*Insert data into the emp_list1 table by calling the stored 
proc insert_emp_rec1*/
		 Object oaString[] = new Object[2];
		 oaString[0] = new String("196 Florence");
		 oaString[1] = new String("Arden Hills");
		 STRUCT mystruct_inst = get_mystruct_instance ("Subhash", 
oaString, new Float(150000.50),"1999-07-19", myConn);
		 CallableStatement myCstmt = myConn.prepareCall ("{call 
insert_emp_rec1(?, ?)}");
		 myCstmt.setObject (1, new Integer(1), Types.INTEGER);
		 myCstmt.setObject (2, mystruct_inst, Types.STRUCT);
		 myCstmt.execute ();
		 myCstmt.close ();
		 myConn.close ();

		 /*Obtain a fresh connection and fetch the data inserted in the 
previous execute statement.*/
		 myConn = getConnFromCache (args [0], args [1], args [2]);
		 CallableStatement stmt;
		 stmt = myConn.prepareCall ("{call get_emp_rec1(?, ?)}");
		 Object oObject = new Integer(1);
		 stmt.setObject(1, oObject, Types.INTEGER);
		 stmt.registerOutParameter (2, Types.STRUCT, "EMP_REC1");
		 stmt.execute ();

		 Object value = stmt.getObject(2);
		 if ( value instanceof java.sql.Struct ) {
			Object[] values = (Object[])((java.sql.Struct)
value).getAttributes ();
			for (int j = 0; j < values.length; ++j)
		    System.out.println ("values [" + j + "] = " + values [j]);
		 }
		 stmt.close();
		 myConn.close ();
	    }
	    catch (SQLException sqle){
		 sqle.printStackTrace();
		 System.out.println ("Exception message: " + sqle.getMessage 
());
		 try{
			myStmt.close ();myConn.close ();
		    }catch(Exception e){;}
		 throw sqle;  
	    }
	}
%>

<%
	String[] arg = 
{"jdbc:oracle:thin:@atharna:1521:wind62","rajoshi","rajoshi"};
	try{
	    main(arg);
	}catch(SQLException e){
		out.print(e);
	}
%>
</body>
</html>
Comment 1 Remy Maucherat 2003-08-06 07:13:21 UTC
*** Bug 22158 has been marked as a duplicate of this bug. ***
Comment 2 Remy Maucherat 2003-08-06 09:57:06 UTC
I recommend you debug this further yourself, as all your code seems out of the
Tomcat scope, and the error seems SQL relqted. This will not be looked into at
this point.