Re: Equivalent of SQL "DROP TABLE IF EXISTS MY_TABLE" ?

[Rick Hillegas]

Hi Wolfgang,

Building on Bernt's suggestion, you can wrap the java bits in a 
table-dropping procedure. That takes you a step closer to what you want 
since you can then invoke the procedure from a sql script. Something 
like the following:

In some public class on the classpath:

   public    static    void    dropTable( String schema, String table )
   {
       try {
           Connection            conn = DriverManager.getConnection( 
"jdbc:default:connection");
           PreparedStatement    ps = conn.prepareStatement
               ( "drop table " + schema + "." + table );
           ps.execute();
           ps.close();
       }
       catch (SQLException e) {}
   }

Then the following script works:

create procedure DROP_TABLE
   ( schemaName varchar( 128 ), tableName varchar( 128 ) )
   parameter style java
   modifies sql data
   language java
   external name 'z.dropTable'
;

call DROP_TABLE( 'app', 'foo' );

create table app.foo( keyCol int primary key );

Cheers,
-Rick

Bernt M. Johnsen wrote:

Bernt M. Johnsen wrote (2005-09-26 12:09:26):
                         

[EMAIL PROTECTED] wrote (2005-09-26 18:11:26):
                           

Hi there,

To initialize my database, I want to use an SQL 
equivalent of "DROP TABLE IF EXISTS MY_TABLE" .

I've tried it but of course didn't work

Does Derby support this kinda SQL or Is there any workaround
I can drop a table if it exists ??
     

One way could be:
       try {
           stmt.executeUpdate("DROP TABLE MY_TABLE");
       } catch (SQL_Exception e) {
           if (!e.getSQLState().equals("proper SQL-state for table does not 
exist"))
   


In Derby it is:
            if (!e.getSQLState().equals("42Y55"))