OracleConnection underlyingConn after Close(); do not work

[oleg1962]

Good afternoon!

Prompt please, why after conn.close (); and the subsequent conn = getConnection (); I receive an error

ERROR [STDERR] java.sql.SQLException: closed connection
ERROR [STDERR] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

ERROR [STDERR] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

ERROR [STDERR] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)

ERROR [STDERR] at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:895)

ERROR [STDERR] at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:802)

ERROR [STDERR] at com.titan.processpayment.ProcessPaymentBean.SelectJGeom(ProcessPaymentBean.java:64)

ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

Also I can work further only after reboot JBoss

public class ProcessPaymentBean implements javax.ejb.SessionBean
............................
 conn = getConnection ();
 pstmt = conn.prepareStatement("select MKT_ID, NAME, SHAPE from MDSYS.cola_markets");
 rs = pstmt.executeQuery();
 pstmt2 = conn.prepareStatement("insert into MDSYS.cola_markets2 (MKT_ID, NAME, SHAPE) values (?, ?, ?)");

 while (rs.next()) {
 state = rs.getString(1);
 stateAbrv = rs.getString(2);
 st = (STRUCT) rs.getObject("SHAPE");
 geom = JGeometry.load(st);
 pstmt2.setString(1, state);
 pstmt2.setString(2, stateAbrv);
 st2 = JGeometry.store(geom, conn);
 pstmt2.setObject(3, st2);
 pstmt2.executeUpdate();
 }
.....
conn.close();
.....
conn = getConnection ();
//I receive the message on an error
//Also I can work further only after reboot JBoss


private Connection getConnection() throws SQLException {
 //http://forums.oracle.com/forums/thread.jspa?threadID=279238
 try {
 DataSource myJBossDatasource = (DataSource) context.lookup("java:OracleDS");
 java.sql.Connection conn = myJBossDatasource.getConnection();
 WrappedConnection wrappedConn = (WrappedConnection)conn;
 java.sql.Connection underlyingConn = wrappedConn.getUnderlyingConnection();
 oracle.jdbc.driver.OracleConnection oracleConn =
 (oracle.jdbc.driver.OracleConnection)underlyingConn;
 return oracleConn;
 }
 catch (SQLException ex) {
 throw new EJBException(ex);
 }
}

 <enterprise-beans>
 <session>
 <description>A service that handles monetary payments</description>
 <display-name>ProcessPaymentEJB</display-name>
 <ejb-name>ProcessPaymentEJB</ejb-name>
 <home>com.titan.processpayment.ProcessPaymentHomeRemote</home>
 <remote>com.titan.processpayment.ProcessPaymentRemote</remote>
 <ejb-class>com.titan.processpayment.ProcessPaymentBean</ejb-class>
 <session-type>Stateless</session-type>
 <transaction-type>Container</transaction-type>
 <env-entry>
 <env-entry-name>minCheckNumber</env-entry-name>
 <env-entry-type>java.lang.Integer</env-entry-type>
 <env-entry-value>2000</env-entry-value>
 </env-entry>
 <resource-ref>
 <description>DataSource for the Titan database</description>
 <res-ref-name>jdbc/titanDB</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
 </resource-ref>
 </session>
 </enterprise-beans>

For earlier it is grateful for the help

[weston.price]

You are getting the underlying Oracle connection and closing it. At this point, the connection has become unusable because you have directly closed the physical connection and as a result, it is no longer valid.

Close the handle, NOT the actual connection. The example psuedo code illustrates this:

 Connection conn = DataSource.getConnection();
 Connection oracleConn = conn.getUnderlyingConnection();

 //Do JDBC stuff heere

 //CLOSE THE HANDLE, NOT THE UNDERLYING CONNECTION!!!!
 conn.close();

 conn = DataSource.getConnection();
 oracleConn = conn.getUnderlyingConnection();

As far as the 'reboot' thing is concerned, I believe what you mean is that your application has become unusuable. The app server as whole would not be effected by this.

[oleg1962]

It Work's
Thank you very much !!!