Hi.
I’m trying to create a java class that deletes all data in a db. When it’s done I’d like only empty tables. The problem is that I don’t know anything about the tables and the structure.
I’ve got two methods for this, the firs one, getTabs(), returns all tables in my db. The second one does DELETE FROM <table>. This works as long as there is no connection to other tables.
But if one of the records is used in another table as FOREIGN KEY, I get an : “violates foreign key constraint” message.
This can be fixed doing “DELETE FROM <table>” in a correct order. But I need to figure out a way to “find” the correct order.
Any one who has any ideas?
[code]
public List<String> getTabs() {
List<String> tabeller = new ArrayList<String>();
try {
Statement st = conn.createStatement();
DatabaseMetaData dmd = conn.getMetaData();
String types[] = {"TABLE"};
ResultSet rs = dmd.getTables(null, null, null, types);
while(rs.next()) {
tabeller.add(rs.getString(
}
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
return tabeller;
}
public void emptyTabs(List<String> tabs) {
Statement st;
try {
st = conn.createStatement();
for(String a : tabs) {
st.execute("DELETE FROM " + a + ";");
}
st.close();
}catch (SQLException ex) {
ex.printStackTrace();
}
}
[/code]
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
If you are using oracle you can use CASCADE DELETE. but you need to give the cascade option while creating the table, then you can delete the records directly in the table which is at the higher level.
if the database is other than oracle there might be a similar option as CASCADE. you can just google it to check.
>> If you are using oracle you can use CASCADE DELETE
>> Which db are you using?
>> If you are using Oracle you can use the connect by feature of oracle to get the table hierarchy
Folks - pls see :) the Q is posted in the PostgreSQL area.
kristian_gr, as David suggests, you can't do that in the Java code (that's also not the right place to do it). You should look towards implementing a trigger or a cascade operation this way:
http://pgsqld.active-ventu
http://www.freebsddiary.or
Business Accounts
Answer for Membership
by: suprapto45Posted on 2007-03-06 at 02:25:19ID: 18660511
>>"This can be fixed doing “DELETE FROM <table>” in a correct order. But I need to figure out a way to “find” the correct order."
As far as I know, program won't be able to check. The constraint(s) is/are created by you so only you know the best which tables should be deleted first.
Hopefully other experts may have a solution for you.
David