aspose file tools*
The moose likes JDBC and the fly likes DELETE statement deletes record but returns 0 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DELETE statement deletes record but returns 0" Watch "DELETE statement deletes record but returns 0" New topic
Author

DELETE statement deletes record but returns 0

Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
Hi, I have a method that deletes a record



it deletes a record, but return 0. I'm having problems with other parts of my program with this. Do you know the reason why? Thanks!


SCJP 1.5
http://devpinoy.org/blogs/lamia/ - http://everypesocounts.com/
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31074
    
232

Timothy,
Driver odditiy? It should return 3, but some drivers are a bit flakey when it comes to return values.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Jeanne Boyarsky:
Timothy,
Driver odditiy? It should return 3, but some drivers are a bit flakey when it comes to return values.


Just to second that. It's best not to count on those at all because

a) some drivers are flakey

b) depending on what actually happens the database may not return anything useful. An example I have used before is doing TRUNCATEs. On MySQL this always returns 0 and not the deleted record count. By itself this seems okay but then you have to be aware that if you do a DELETE with no restrictions (WHERE clause etc) the database optimizes that into a TRUNCATE and gives you zero back. And the reason it gives you zero is because it doesn't know either since what it actually does is drop the table (read delete the table files) and then re create it.
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
TRUNCATE is a bit different from delete; you get 0 because truncate is a DDL statement, not DML, so it isn't examining the data to do things like cascades, etc., it just drops the data segments from the tablespace (or equivalent storage mapping).

I don't know if this has changed recently, but the MS SQLServer driver at one point never gave you back the row count from a DELETE. The original question didn't mention the specific database, but as Jeanne pointed out some drivers can be flakey. Making sure you have a current driver would be the obvious thing to check.


Reid - SCJP2 (April 2002)
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
Hi, I got my driver from some other people. Could you guys please point me to the download link? Thanks a lot!
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Originally posted by Timothy Sam:
Hi, I got my driver from some other people. Could you guys please point me to the download link? Thanks a lot!


Which database?


Groovy
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
Oooops sorry... I'm using MySQL database...
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Reid M. Pinchback:
TRUNCATE is a bit different from delete; you get 0 because truncate is a DDL statement, not DML, so it isn't examining the data to do things like cascades, etc., it just drops the data segments from the tablespace (or equivalent storage mapping).


You need to read what I actually wrote. In MySQL DELETEs can be optimized into TRUNCATES by the Database. So it is a conjunction of both events happening that may be problematic.
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Timothy Sam:
Oooops sorry... I'm using MySQL database...


http://dev.mysql.com/downloads/connector/j/3.1.html
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Ouch! Point taken. Bad commute day, huh?
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
Hi, after changing my MySQL driver. I still experience the same problem... Maybe I should just quit from checking if a records has really been deleted?
Andy Grove
Greenhorn

Joined: Nov 11, 2003
Posts: 18
The problem is that you are trying to bind a parameter to a LIKE clause. You cannot do this in JDBC. It is not parameterizable in the way that most other query criteria are.

You need to manually construct your SQL statement to include the department id

e.g.

query = "DELETE FROM departments_tbl WHERE department_id LIKE '" + departmentId + "'"

Hope that helps.

Cheers,
Andy Grove
Greenhorn

Joined: Nov 11, 2003
Posts: 18
Oops! Hit the reply button too soon.

Cheers,

<a href="http://www.codefutures.com/weblog/andygrove">Andy Grove</a>
<a href="http://www.codefutures.com/products/firestorm">FireStorm</a>/<a href="http://www.codefutures.com/products/firestorm">DAO</a> 3.0 Generates Java code from relational schemas
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Andy Grove:
The problem is that you are trying to bind a parameter to a LIKE clause. You cannot do this in JDBC. It is not parameterizable in the way that most other query criteria are.

You need to manually construct your SQL statement to include the department id

e.g.

query = "DELETE FROM departments_tbl WHERE department_id LIKE '" + departmentId + "'"

Hope that helps.

Cheers,


This is 100% untrue.

You can parametize LIKE. The tricky part is if you want it to be a wildcard search. You have to put the wildcards on/in the String value yourself.

You cannot do this...

SELECT * FROM TABLE WHERE COLUMN LIKE %?%

That is no good.

But can you do

SELECT * FROM TABLE WHERE COLUMN LIKE ?

Absolutley yes 100% you can.
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Timothy Sam:
Hi, after changing my MySQL driver. I still experience the same problem... Maybe I should just quit from checking if a records has really been deleted?


That's probably a good idea.

Is the record actually being deleted though? I think you said yes but just want to check.

Personally I would rather not DELETE records based on LIKE search. I would prefer to use the key or records that absolutley match some criteria just so nothing unexpected happens.
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Originally posted by Reid M. Pinchback:
Ouch! Point taken. Bad commute day, huh?


It's okay. It's just one of those caveats one should be aware of. I got bitten one time by the DELETE turns into TRUNCATE and I could not figure out what the heck was going on. MySQL actually is full of gotchas. (like the silent conversion of datatypes from VARCHAR to CHAR and vice-versa)
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: DELETE statement deletes record but returns 0