JavaDB - CREATE TABLE IF NOT EXISTS - how to?

Question

I'm trying to create table using JavaDB (Derby), but if I try to add IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS etc (ID BIGINT PRIMARY KEY, title VARCHAR(150))

I get the error:

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "NOT" at line 1, column 17.

As I remember it works on MySQL. What is the syntax/simplest method to check if table exists?

EDIT:

Finally I found the solution:

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getTables(null, "APP", "ETC", null);
if (rs.next()) {
    System.out.println("Table " +  rs.getString(3) + " exists");
}

I noticed that table name is case sensitive if it is created with using quotes "etc", and if not - the name must be upper case. According API documentation: "must match the table name as it is stored in the database".

score -3 · Answer 1 · answered Aug 08 '13 at 12:04

-3

if not exists (select * from sysobjects where name='etc' and xtype='U')
create table etc(
   ID BIGINT PRIMARY KEY, title VARCHAR(150)
);

It will create a table called etc if it doesn't exist.

More of this over here.

Good luck

answered Aug 08 '13 at 12:04

ClaireG

1,244
2
11
23

1

Thanks, but there is no such sysobjects table in my database. Table list: SYSCONGLOMERATES SYSTABLES SYSCOLUMNS SYSSCHEMAS SYSCONSTRAINTS SYSKEYS SYSDEPENDS SYSALIASES SYSVIEWS SYSCHECKS SYSFOREIGNKEYS SYSSTATEMENTS SYSFILES SYSTRIGGERS SYSSTATISTICS SYSDUMMY1 SYSTABLEPERMS SYSCOLPERMS SYSROUTINEPERMS SYSROLES SYSSEQUENCES SYSPERMS. Still get the erorr.. – Ernestas Gruodis Aug 08 '13 at 12:13
Try this link; there are more useful things that you can try replacing the first select statement. http://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle – ClaireG Aug 08 '13 at 12:18
1

None of above methods worked for me.. Maybe for other databases, but not for javaDB/Derby. – Ernestas Gruodis Aug 08 '13 at 13:09

JavaDB - CREATE TABLE IF NOT EXISTS - how to?

1 Answers1