how to create table if it doesn't exist using Derby Db

Question

I am new to apache derby and I cant seem to make work

    CREATE TABLE IF NOT EXISTS table1 ...

as can be achieved in MySql etc. I am getting a 'Syntax error: Encountered "NOT" at line 1, column 17.', when I try to run this SQL statement in my Java program.

I checked in the documentation page for Derby Db Create Statements, but couldn't find such an alternative.

Answer 1

Create the table, catch the SQLException and check SQL status code.

The full list of error codes can be found here but I couldn't find Table <value> already exists; it's probably X0Y68. The code you need is X0Y32.

Just run the code once and print the error code. Don't forget to add a test to make sure the code works; this way, you can catch changes in the error code (should not happen ...).

In my projects, I usually add a helper class with static methods so I can write:

} catch( SQLException e ) {
    if( DerbyHelper.tableAlreadyExists( e ) ) {
        return; // That's OK
    }
    throw e;
}

Another option is to run a SELECT against the table and check the status code (which should be 42X05). But that's a second command you need to send and it doesn't offer any additional information.

What's worse, it can fail for other reasons than "Table doesn't exist", so the "create-and-ignore-error" is better IMO.