Closed Statement issue

F

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;
}
 
A

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
 
F

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?
 
M

Mike Schilling

francan said:
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.
 
Joined
Dec 31, 2009
Messages
6
Reaction score
0
1/ open conn on App's startUp (never close conn)
Code:
     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
 
Joined
Dec 31, 2009
Messages
6
Reaction score
0
hii,

1/ open conn on App's startUp (never close conn)
Code:
     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
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...
    }
}

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            
        }

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 ...
        }
    }

... kopik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,708
Messages
2,569,347
Members
44,654
Latest member
7Jhamal74

Latest Threads

Top