Why is not deleting the register on my database?
publicvoid deleteRegister(){
try{
String driver ="org.hsqldb.jdbcDriver";
String url ="jdbc:hsqldb:file:./db/database";
String userId ="sa";
String password ="";
String sqlDelete ="DELETE FROM CLIENTS WHERE ID_CLIENT = ?";
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, userId, password);
String id = JOptionPane.showInputDialog(null,"Enter ID Client to be deleted:","ID", 3);
int idInt = Integer.parseInt(id);
PreparedStatement pstmt = connection.prepareStatement(sqlDelete);
pstmt.setInt(1, idInt);
int deleted = pstmt.executeUpdate();
if(deleted != 1){
JOptionPane.showMessageDialog(null,"Could not delete the register. Please verify the ID you've entered., "Result.", 3);
pstmt.close();
connection.close();
}
else{
connection.commit();
JOptionPane.showMessageDialog(null,"Register Deleted!","Result, 1);
pstmt.close();
connection.close();
}
}
catch(ClassNotFoundException cnfe){
cnfe.printStackTrace();
}
catch(SQLException sqle){
sqle.printStackTrace();
}
}
[2131 byte] By [
coffee95a] at [2007-10-3 4:34:42]
PreparedStatement
"Returns:
either (1)
the row count for INSERT, UPDATE, or DELETE statements or
(2) 0 for SQL statements that return nothing"
According to your code, you may get report of failed to delete even if the
record was deleted.
if(deleted != 1) {
what exactly is happening when you run the code?
> int deleted = pstmt.executeUpdate();
> if(deleted != 1) {
Please do not rely on code like that. It often doesn't work. It is database and driver dependent.
There are three possible scenarios.
1. The where clause matches nothing.
2. The where clause match something and the delete succeeds.
3. The where clause matches something and the delete fails. An exception will occur.
Case 2 and 3 take care of themselves. It is only case 1 that you are dealing with. And what you should actually being doing there is letting the user pick from a list of known (because you query and display them) entities. But since you are not you can do a select count first to see if the the entered value matches something.
In fact, there's no error message after running this code.
It just doesn't delete anything.
I go and check the database files and all registers still there. But the program shows the message that the register has been deleted and if I try to delete that register again I've a message error, like my program previews. Wonderful, but I restart the program or check the database files and the registers deleted are still there.
I need help.
Thanks!
Ahh... I had that problem on the Hypersonic database... it's a tricky little thing.
You have to either
a) execute the "shutdown compact" SQL statement to "commit" the data from memory to disk or
b) I added ";shutdown=true" to the URL, which has the same effect, but with zero code changes.
So try using the url "jdbc:hsqldb:file:./db/database;shutdown=true"
regards,
Owen
