Bug 22158 - Getting following error => java.sql.SQLException: Logical handle no longer valid
Summary: Getting following error => java.sql.SQLException: Logical handle no longer valid
Status: RESOLVED DUPLICATE of bug 22159
Alias: None
Product: Tomcat 4
Classification: Unclassified
Component: Catalina (show other bugs)
Version: 4.0 Beta 1
Hardware: PC All
: P3 blocker (vote)
Target Milestone: ---
Assignee: Tomcat Developers Mailing List
Depends on:
Reported: 2003-08-06 06:38 UTC by Raju Joshi
Modified: 2005-03-20 17:06 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Raju Joshi 2003-08-06 06:38:05 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"%>

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

 private static OracleConnectionCache connCache = null;

 private static Connection getConnFromCache (String url, String user, String 
  	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;
		 myConn = getConnFromCache (args [0], args [1], args [2]);
		 myStmt = myConn.createStatement ();
		 /* DB Schema Creation */
		 	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 
		 }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]);
		 myConn.close ();
	    catch (SQLException sqle){
		 System.out.println ("Exception message: " + sqle.getMessage 
			myStmt.close ();myConn.close ();
		    }catch(Exception e){;}
		 throw sqle;  

	String[] arg = 
	}catch(SQLException e){
Comment 1 Remy Maucherat 2003-08-06 07:13:20 UTC

*** This bug has been marked as a duplicate of 22159 ***