Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DELETE statement deletes record but returns 0

 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Timothy,
Driver odditiy? It should return 3, but some drivers are a bit flakey when it comes to return values.
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8927
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oooops sorry... I'm using MySQL database...
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ouch! Point taken. Bad commute day, huh?
 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic