Discussion:
Equivalent of SQL "DROP TABLE IF EXISTS MY_TABLE" ?
wolfgang127us-/E1597aS9LR3+
2005-09-26 09:11:26 UTC
Permalink
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 ??



Regards,
Wolfgang



__________________________________
For All Sports Fans!
http://pr.mail.yahoo.co.jp/yells/
Bernt M. Johnsen
2005-09-26 10:09:26 UTC
Permalink
Post by wolfgang127us-/E1597aS9LR3+
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"))
throw e;
}
--
Bernt Marius Johnsen, Database Technology Group,
Sun Microsystems, Trondheim, Norway
Bernt M. Johnsen
2005-09-26 10:39:23 UTC
Permalink
Post by Bernt M. Johnsen
Post by wolfgang127us-/E1597aS9LR3+
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 ??
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"))
--
Bernt Marius Johnsen, Database Technology Group,
Sun Microsystems, Trondheim, Norway
Rick Hillegas
2005-10-03 16:40:38 UTC
Permalink
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
Post by Bernt M. Johnsen
Post by Bernt M. Johnsen
Post by wolfgang127us-/E1597aS9LR3+
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 ??
try {
stmt.executeUpdate("DROP TABLE MY_TABLE");
} catch (SQL_Exception e) {
if (!e.getSQLState().equals("proper SQL-state for table does not exist"))
if (!e.getSQLState().equals("42Y55"))
Lance J. Andersen
2005-09-26 12:56:09 UTC
Permalink
Having this type of functionality in some form (see the MS or Sybase
docs) is beneficial. I get this question constantly from internal users.
Post by Bernt M. Johnsen
Post by wolfgang127us-/E1597aS9LR3+
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 ??
try {
stmt.executeUpdate("DROP TABLE MY_TABLE");
} catch (SQL_Exception e) {
if (!e.getSQLState().equals("proper SQL-state for table does not exist"))
throw e;
}
wolfgang127us-/E1597aS9LR3+
2005-09-26 13:35:44 UTC
Permalink
Hi there,

Thank you guys for the advice.

I just wanted to prepare a file having those SQL statements and
process it using ANT, but now I gotta implement it programmatically..

Can you guys put this functionality in wishlist ???

Thanks anyways !

Regards,
Wolfgang




__________________________________
For All Sports Fans!
http://pr.mail.yahoo.co.jp/yells/
Matt Sanford
2005-09-26 13:52:10 UTC
Permalink
Hello,

I agree exists in the DDL would be nice, but I am also a long-time
Sybase user. In the mean time, for my code I am doing a select from
SYS.SYSTABLES to check for existence. Something like :

select count(*) from SYS.SYSTABLES where TABLENAME = 'MY_TABLE'

The only thing that goofed me up was the MY_TABLE is in all caps
even if not created that way. Unfortunately, this does not work in the
SQL-only ant environment you described.

Thanks;
-- Matt Sanford
Post by wolfgang127us-/E1597aS9LR3+
Hi there,
Thank you guys for the advice.
I just wanted to prepare a file having those SQL statements and
process it using ANT, but now I gotta implement it programmatically..
Can you guys put this functionality in wishlist ???
Thanks anyways !
Regards,
Wolfgang
__________________________________
For All Sports Fans!
http://pr.mail.yahoo.co.jp/yells/
Continue reading on narkive:
Loading...