CODEWITHSUNDEEP
sqlitejdbcprepared-statement
yaylitzis
yaylitzis

Reputation: 5534

Count deleted records, returns 0

I perform a DELETE in my table from my servlet by calling a method action.deleteBox(box); which executes the delete function

deleteBox method

Connection c = null;
PreparedStatement stm = null;
sq = "DELETE FROM BOXES WHERE ...";

try {       
    Class.forName(typeDB);
    c = DriverManager.getConnection(path);    
    stm = c.prepareStatement(sq);
    //...
    stm.executeUpdate();

} catch (SQLException e) {             
    System.out.println(e.getMessage());
} finally {
        System.out.println("my stm is closed : " + stm.isClosed());
        if (stm != null)
                    stm.close();
        if (c != null)
                    c.close();
}

the delete is executed fine. Then I want to check how many records were deleted from the previous delete: So I call this method:

public int countDeletesRecords() 
        throws SQLException, ClassNotFoundException {

    Connection c = null;
    PreparedStatement stm = null;
    sq = "SELECT changes() as \"deletedRows\" ";
    int deletedRows=-1;

    try {       
        Class.forName(typeDB);
        c = DriverManager.getConnection(path);    
        stm = c.prepareStatement(sq);       
        ResultSet rs = stm.executeQuery();

        if (rs.next()) {
            deletedRows = rs.getInt("deletedRows");
        }

    } catch (SQLException e) {             
        System.out.println(e.getMessage());
    } finally {
        System.out.println("my stm is closed : " + stm.isClosed());
        if (stm != null)
            stm.close();
        if (c != null)
            c.close();
    } 

    return deletedRows; //I get 0..
}

and I get 0, while 1 records where deleted.

Upvotes: 0

Views: 1716

Answers (2)

CL.
CL.

Reputation: 180060

The documentation says:

This function returns the number of rows modified, inserted or deleted by the most recently completed INSERT, UPDATE or DELETE statement on the database connection

You are creating a new database connection in which no DELETE statement was ever executed.

Upvotes: 1

hmjd
hmjd

Reputation: 121971

While this does not directly answer the question an alternative, simpler, approach would capture the return value of executeUpdate(), that returns the number of affected (in this case, deleted) rows:

final int deletedRowCount = stm.executeUpdate();

Upvotes: 3

Related Questions