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.
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 The code you need is Table <value> already exists
; it's probably X0Y68
.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.