Closed Statement issue

[francan]

I am getting closed exception messages with my below jdbc connection
to Oracle 9i from Tomcat 6 container:
java.sql.SQLException: Closed Statement
and
java.sql.SQLException: Closed Statement: next



private Connection connection;
public ArrayList<MoneyBean> getMoneyList()
{
ArrayList<MoneyBean> list = new ArrayList<MoneyBean>();
connection = new DatabaseConnector.getConnection();

if(connection != null)
{
ResultSet rs = null;
PreparedStatement preparedStatement = null;

try {
String BankQuery = "Select money from Bank";
preparedStatement = connection.preparedStatement
(BankQuery);
rs = preparedStatement.executeQuery();
while(rs.next())
{
MoneyBean money = new MoneyBean();
money.setMoney(rs.getString("money"));
list.add(money);
}
}
}
catch(SQLException ex)
{
System.out.println(ex);
}
finally
{
try
{
if(rs != null)
{
rs.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}

try
{
if(preparedStatement != null)
{
preparedStatement.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}

try
{
if(connection != null)
{
connection.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
return list;
}

 

[Arne Vajhøj]

I am getting closed exception messages with my below jdbc connection
to Oracle 9i from Tomcat 6 container:
java.sql.SQLException: Closed Statement
and
java.sql.SQLException: Closed Statement: next

private Connection connection;
public ArrayList<MoneyBean> getMoneyList()
{
ArrayList<MoneyBean> list = new ArrayList<MoneyBean>();
connection = new DatabaseConnector.getConnection();

if(connection != null)
{
ResultSet rs = null;
PreparedStatement preparedStatement = null;

try {
String BankQuery = "Select money from Bank";
preparedStatement = connection.preparedStatement
(BankQuery);
rs = preparedStatement.executeQuery();
while(rs.next())
{
MoneyBean money = new MoneyBean();
money.setMoney(rs.getString("money"));
list.add(money);
}
}
}
catch(SQLException ex)
{
System.out.println(ex);
}
finally
{
try
{
if(rs != null)
{
rs.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}

try
{
if(preparedStatement != null)
{
preparedStatement.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}

try
{
if(connection != null)
{
connection.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
return list;
}

It may help us if you told us where the exception was thrown.

The exceptions seems to indicate a closed connection. Any chance
another thread could have closed the connection ?

Arne

 

[John B. Matthews]

preparedStatement = connection.preparedStatement(BankQuery);

This doesn't compile. Can you show your actual code and stack trace?

 

[francan]

It may help us if you told us where the exception was thrown.

The exceptions seems to indicate a closed connection. Any chance
another thread could have closed the connection ?

Arne- Hide quoted text -

- Show quoted text -

The exception occurs here:
catch(SQLException ex)
{
System.out.println(ex);
}

Not sure what you mean by another thread? Do you mean another class
with a Database connection?

 

[Arne Vajhøj]

The exception occurs here:
catch(SQLException ex)
{
System.out.println(ex);
}

Not sure what you mean by another thread? Do you mean another class
with a Database connection?

Thread as in hmm thread.

http://en.wikipedia.org/wiki/Thread_(computer_science)

Arne

 

[Mike Schilling]

The exception occurs here:
catch(SQLException ex)
{
System.out.println(ex);
}

No, that's where the exception is caught. If you print out ex's stack
trace, you'll see where it was thrown.

 

[kopik]

1/ open conn on App's startUp (never close conn)

     public static void Conn() throws DatabaseException {
        IsConnDb = "No";
        try {
            if (dbConn == null || dbConn.isClosed()) {
                Class.forName(driverDajOkno).newInstance();
                dbConn = DriverManager.getConnection(url, "user", "psw");
                IsConnDb = "Yes";
            }
        } catch (InstantiationException e) {
            throw new DatabaseException(e);
        } catch (IllegalAccessException e) {
            throw new DatabaseException(e);
        } catch (ClassNotFoundException e) {
            throw new DatabaseException(e);
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
    }
[CODE]

+

can handle all types and datails with... for example

[CODE]
public class DatabaseException extends Exception {
 
    private static final long serialVersionUID = 1L;
 
    /**
     * Construct an exception based on another exception.
     *
     * @param e The other exception.
     */
    public DatabaseException(Exception e) {
        super(e);
        //logs that or anything handle with catched err...
    }
}
[CODE]


---> how many real db contains one table without any row??,
2/ test conn against nullTable, then build needed statement
[CODE]
        try {
            rst = stmt.executeQuery("Select Id From nullTables");
            rst = null; // or rst.close();
            stmt = null // or stmt.close();
            DbCheckStatus = "Connected";
        } catch (SQLException ex) {
            Logger.getLogger(className.class.getName()).log(Level.SEVERE, null, ex);
            DbCheckStatus = "Disconnected";           
        }
 
        if (DbCheckStatus.equals("Disconnected")) {
            dbConn = null; // connection probably expired in this session, no !!! close(); == only err. statement occured
            IsConnDb = "No";
            Conn(); // try to open conn again, or to eliminate some serverSide fault            
        }
[CODE]

3) Create statement(s)
4) Create ResultSet(s) and process it/them
5) Close Resultset(s) // or = null;
6) Close statement(s)// or = null;
7/ on app's exit

[CODE]
     public static void closeDbConn() throws SQLException {
        try {
            if (!dbConn.isClosed()) {
                dbConn.close();
                IsConnDb = "No";
            }
        } catch (SQLException e) {
            e.printStackTrace();
            IsConnDb = "No";
            // something ...
        }
    }
[CODE]

... kopik

 

[kopik]

hii,

1/ open conn on App's startUp (never close conn)

     public static void Conn() throws DatabaseException {
        IsConnDb = "No";
        try {
            if (dbConn == null || dbConn.isClosed()) {
                Class.forName(driverDajOkno).newInstance();
                dbConn = DriverManager.getConnection(url, "user", "psw");
                IsConnDb = "Yes";
            }
        } catch (InstantiationException e) {
            throw new DatabaseException(e);
        } catch (IllegalAccessException e) {
            throw new DatabaseException(e);
        } catch (ClassNotFoundException e) {
            throw new DatabaseException(e);
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
    }

+

can handle all types and details with... for example

public class DatabaseException extends Exception {
 
    private static final long serialVersionUID = 1L;
 
    /**
     * Construct an exception based on another exception.
     *
     * @param e The other exception.
     */
    public DatabaseException(Exception e) {
        super(e);
        //logs that or anything handle with catched err...
    }
}

2/ test conn against nullTable, then build needed statement

        try {
            rst = stmt.executeQuery("Select Id From nullTables");
            rst = null; // or rst.close();
            stmt = null // or stmt.close();
            DbCheckStatus = "Connected";
        } catch (SQLException ex) {
            Logger.getLogger(className.class.getName()).log(Level.SEVERE, null, ex);
            DbCheckStatus = "Disconnected";           
        }
 
        if (DbCheckStatus.equals("Disconnected")) {
            dbConn = null; // connection probably expired in this session, no !!! close(); == only err. statement occured
            IsConnDb = "No";
            Conn(); // try to open conn again, or to eliminate some serverSide fault            
        }

3) Create statement(s)
4) Create ResultSet(s) and process it/them
5) Close Resultset(s) // or = null;
6) Close statement(s)// or = null;
7/ on app's exit

     public static void closeDbConn() throws SQLException {
        try {
            if (!dbConn.isClosed()) {
                dbConn.close();
                IsConnDb = "No";
            }
        } catch (SQLException e) {
            e.printStackTrace();
            IsConnDb = "No";
            // something ...
        }
    }

... kopik