Does derby support if not exists?

[rebels_mascot <bp...@gmail.com>]

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?
-- 
View this message in context: http://www.nabble.com/Does-derby-support-if-not-exists--tp15991727p15991727.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Does derby support if not exists?

[Narayanan <V....@Sun.COM>]

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