db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Equivalent of SQL "DROP TABLE IF EXISTS MY_TABLE" ?
Date Mon, 03 Oct 2005 16:40:38 GMT
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"))
>
>  
>


Mime
View raw message