1

I have a method called dropTable that sends a SQL query to a database to check whether a table exists or not. If it does, it drops the table. I send another query telling it to create a table.

When I run the code, I get an error saying

ERROR: syntax error at or near "IF" Position: 1` that points to the line with the first execute method.

I have print statements to see where to code reaches and it never reaches past the execute method in question.

public static void dropTable(Connection conn, String name, String description) {
    Statement st = null;
    try {
        st = conn.createStatement();

        System.out.println("hello");
        st.execute("IF EXISTS (SELECT * FROM sys.tables WHERE NAME = '" + name + "' AND TYPE = 'U') DROP TABLE " + name);
        System.out.println("done");

        st.execute("CREATE TABLE " + description);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Am I doing something wrong?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Jake Jackson
  • 1,055
  • 1
  • 12
  • 34
  • I think it's also worth noting that using `+` to build up SQL statements means that they are prone to SQL injection, e.g. if description was set to `my_table; DROP DATABASE my_database;` then you might have issues? – Richard Hansell Jan 06 '20 at 14:01

1 Answers1

0

In Microsoft SQL Server 2016, objects can DIE (Drop If Exists).

DROP IF EXISTS - new thing in SQL Server 2016

Try using DROP TABLE IF EXISTS dbo.TableName if your version supports it.

Stuart Frankish
  • 818
  • 1
  • 11
  • 27
  • Top guy. Cheers. – Jake Jackson Jan 06 '20 at 13:56
  • In fact, I have another error: when I run this it creates the table nicely, but when I run it again I get the error `ERROR: relation "topurls" already exists` - it points to the 2nd execute method (the one about creating the table). – Jake Jackson Jan 06 '20 at 14:02
  • What is it you're actually trying to achieve here? Whats the purpose of dropping a table given `name` and creating a new table using `description`? This seems like a logical problem. You aren't checking if the table name you are creating already exists before trying to create it. – Stuart Frankish Jan 06 '20 at 14:15
  • It's part of a project I have to do. Literally, if the table exists we need to drop it and then recreate it. My current issue is that my code (my new code you helped me with) doesn't recognise that it exists, so it doesn't delete, meaning my next execute causes and error when it tries to create the table again. – Jake Jackson Jan 06 '20 at 14:19
  • Is the table structure changing or expected to change each time? If not, you can just `TRUNCATE` the table. Also, should `type = "U"` be `xtype = "U"`? (Could just be a compatibility thing) – Stuart Frankish Jan 06 '20 at 14:20