File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Clean database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Clean database " Watch "Clean database " New topic
Author

Clean database

avihai marchiano
Ranch Hand

Joined: Jan 10, 2007
Posts: 342
Hey,

is there a way (i dont mind to use tool) to clean the DB.
Dont delete the tables, only delete the content.

if i do it one by one i failed with constraints error.

can i some how get the order or have a workaround or tool that will do it for me?

Thank you
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19544
    
  16

As long as there are no circular references, you can find the order by trying. By following the next approach you'll set up several (overlapping) tree structures, which you can then delete from root to leaf.

If a table can be cleared (DELETE FROM tablename), then it is a root node. If it is not, you should be able to read from the error which table(s) depend(s) on that table, and those tables will be the parents of the current table. You'll just continue until a table can be deleted without problems.

You could automatically build these trees, if you can determine from the error messages which table you should try to delete next.


Alternatively, you can store all tables in a collection, and keep trying to clear one at a time, removing all successfully cleared tables, until either nothing can be removed (circular references) or the collection is empty:



SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
avihai marchiano
Ranch Hand

Joined: Jan 10, 2007
Posts: 342
Too much complicated.

There is no tool or command to get the order?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29259
    
140

Avihai,
Another approach is to:
1) Disable all constraints (This can be done through SQL - the command varies by database)
2) delete data from all tables
3) Re-enable all constraints (mirror image of the sql from step 1)


[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
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Trunc table
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Connect to DB using Squirrel SQL, select all tables, right-click, delete records
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Drop the database and restore from a backup, excluding data?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
avihai marchiano
Ranch Hand

Joined: Jan 10, 2007
Posts: 342
Thanks a lot for your answers , i thought that can be an easier way.

Just a sample command.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Clean database
 
Similar Threads
deleting rows with referential integrity
how to delete multiple browse
Class for safe deletion
How to delete more then one record in single query using iBATIS
Deploying servlets as a war file