Re: Does derby support if not exists?

[Narayanan]

rebels_mascot wrote:
Hi,
Does derby support "CREATE TABLE IF NOT EXISTS tblName(...)"? When I try
that command I just get back the following:
ERROR 42X01: Syntax error: Encountered "NOT" at line 1, column 17.

Any suggestions?

Thanks,
Brian
  
Hi Brian,

I do not think derby supports

CREATE TABLE IF NOT EXISTS

Here is the create table syntax from the latest derby manuals

http://db.apache.org/derby/docs/dev/ref/rrefsqlj24513.html

But you can use jdbc to execute this statement and catch the 
SQLException that will be thrown
in this case and do something useful here like for example print that 
the table already exists and that
the create failed.

try {
   statement.executeUpdate(<create table statement>);
} catch (SQLException sqle) {
System.out.println("Create table operation failed");
}

If you think you would be needing this often I think you can translate 
this into a
Java stored procedure that can be used in derby and call the stored 
procedure everytime
you want to do this operation.

you can read more about stored procedures in derby here

http://wiki.apache.org/db-derby/DerbySQLroutines

Narayanan