Create table if not exists?

[Chris <sh...@yahoo.com>]

MySQL has a really nice syntax for creating tables if they don't exist 
already:

CREATE TABLE IF NOT EXISTS foo (...)

How can I get the same functionality in Derby? I haven't been able to 
figure out the SQL command to check for the existence of a table.

Re: Create table if not exists?

[Blair Zajac <bl...@orcaware.com>]

Chris wrote:
> MySQL has a really nice syntax for creating tables if they don't exist 
> already:
> 
> CREATE TABLE IF NOT EXISTS foo (...)
> 
> How can I get the same functionality in Derby? I haven't been able to 
> figure out the SQL command to check for the existence of a table.

You can use this (Scala code follows):

       val metadata = connection.getMetaData
       val rs = metadata.getTables(null,
                                   null,
                                   null,
                                   Array("TABLE"))

This should work for any database, but I know it works for Derby, PostgreSQL and 
Oracle.

Regards,
Blair

Re: Create table if not exists?

[Emmanuel Cecchet <ma...@frogthinker.org>]

Kathey Marsden wrote:
> alternately you can use the DatabaseMetaData to see if the table is there
I don't know if this is still the case, but Derby could only do that in 
a transaction so make sure you call conn.setAutoCommit(false) before.
>    DatabaseMetaData dmd = conn.getMetaData();
>    ResultSet rs = dmd.getTables(null,"APP", "FOO",null);
If you just want the table (not views or system tables), you should 
specify it using:
ResultSet rs = dmd.getTables(null,"APP", "FOO", new String[]{"TABLE"});
>    if (!rs.next()) {
>        s.executeUpdate("CREATE TABLE FOO (I INT)");
>    }
This is probably the cleanest method especially if you want this DDL 
statement to be part of a larger transaction, not triggering exception 
in the middle is usually much better.

Hope this helps,
Emmanuel

-- 
Emmanuel Cecchet
FTO @ Frog Thinker 
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet

Re: Create table if not exists?

[Kathey Marsden <km...@sbcglobal.net>]

Chris wrote:
> MySQL has a really nice syntax for creating tables if they don't exist 
> already:
>
> CREATE TABLE IF NOT EXISTS foo (...)
>
> How can I get the same functionality in Derby? I haven't been able to 
> figure out the SQL command to check for the existence of a table.
>
>
You could catch the SQLException and ignore it, e.g.
    try {
        s.executeUpdate("CREATE TABLE FOO (I INT)");
       
    } catch (SQLException se) {
        if (!se.getSQLState().equals("X0Y32"))
        throw se;
    }

alternately you can use the DatabaseMetaData to see if the table is there

    DatabaseMetaData dmd = conn.getMetaData();
    ResultSet rs = dmd.getTables(null,"APP", "FOO",null);
    if (!rs.next()) {
        s.executeUpdate("CREATE TABLE FOO (I INT)");
    }