1

I've tried looking for a similar question but I could find one, so I'll post one! I'm creating a Java program that drops certain tables for me, MySQL one looks like this:

String[] tablesToDrop = new String[]{tableName,tableName2};
Connection con = DriverManager.getConnection(dbUrl, userName,password);
Statement stmt = con.createStatement();

for (int i = 0; i < tablesToDrop.length; i++) {
                System.out.println("Dropping " + tablesToDrop[i] + " Table..");
                stmt.executeUpdate("DROP TABLE IF EXISTS " + tablesToDrop[i]);
            }

My question is how would this look for SQL Server 2008 version? the stmt.executeUpdate(???); part? I want to drop a table if it exists, getting the table names from an array and passing it into a for loop.

Mantas
  • 3,179
  • 4
  • 20
  • 32
  • not an sqlserver expert, but http://www.sqlservercentral.com/forums/Topic478289-338-1.aspx – gefei Sep 04 '12 at 10:58
  • 2
    While the answers given will work, I would suggest that allowing your application to drop tables is bad practice and potentially dangerous, allowing the application far too much access to the database. I would recommend using temporary tables or table variables for transient data structures instead. – podiluska Sep 04 '12 at 11:35
  • This is weird, the guy edited my post so much that it appears nothing to do with Java, yet it does. Did the guy even read my post or just randomly changed tags? -.- – Mantas Sep 04 '12 at 13:06

2 Answers2

3

Java communicates with DB via driver. It must be absolutely transparent for the application programmer. So, no difference between MySql and MS SQL server. Just change driver.

It could be however difference in SQL dialects. Typically people try to avoid such differences by using Java-to-DB mapping frameworks (e.g. JPA, Hibernate etc.) But since you are using plain JDBC it is your responsibility.

I think that statement you are trying now will work. But for future if you want to use plain JDBC and support several DB types I'd suggest you to hold your SQL statementes in separate file (e.g. properties file) and read them from there. You can even abuse ResouceBundle mechanism using SQL dialect as "locale".

AlexR
  • 114,158
  • 16
  • 130
  • 208
  • +1 the answer is great because it explains many things. I would like to add a link to another post because there is a difference in the dialects : http://stackoverflow.com/questions/7887011/how-to-drop-table-if-exists-in-sql-server-2005 –  Sep 04 '12 at 11:07
  • My drivers change dynamivally, it detects which type database I'm connecting to, but the query is executed within the server itself. This program is for a company that have their structure for ages now, I would do that but at the moment I'm nobody in the company :) – Mantas Sep 04 '12 at 11:12
  • Yes it does explain a lot, +1 from me as well, but I'm simply looking for the query to execute, thank you sir. – Mantas Sep 04 '12 at 11:15
  • the query to execute i provided in a link in my comment. something similar was also provided in another answer by @AnandPhadke as i see now. –  Sep 04 '12 at 11:16
3
IF EXISTS(select * from sysobjects where name='tblname') drop table tblname
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
  • Thank you, Hopefully this works since I do not have a test database set up in the company just yet, can't try these things on a live system at the moment. – Mantas Sep 04 '12 at 11:16
  • So the statement should look like this: stmt.executeUpdate("IF EXISTS(select * from sysobjects where name='tablesToDrop[i]') DROP TABLE tablesToDrop[i]); ? – Mantas Sep 04 '12 at 11:18
  • what about oracle?? – Stunner Mar 05 '20 at 07:06