| Summary: | Getting following error => java.sql.SQLException: Logical handle no longer valid | ||
|---|---|---|---|
| Product: | Tomcat 4 | Reporter: | Raju Joshi <rs_joshi> |
| Component: | Catalina | Assignee: | Tomcat Developers Mailing List <dev> |
| Status: | RESOLVED INVALID | ||
| Severity: | blocker | ||
| Priority: | P3 | ||
| Version: | 4.1.24 | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | All | ||
*** Bug 22158 has been marked as a duplicate of this bug. *** 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. |
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>