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

[Rick Hillegas <Ri...@Sun.COM>]

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):
>>>>>>>>>>>>>                          
>>>>>>>>>>>>>
>>>>>>>>>>>>>>wolfgang127us@yahoo.co.jp 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"))
>
>  
>