1

In DB2 we want to check if a table exists or not and depending on that we want to create the table. I searched on the net and I always find the same anwser, but I keep getting an error when I want to run the query.

begin 
   declare continue handler for sqlstate '42710' begin end; 
   execute immediate 'CREATE TABLE HTMLMONIDB.DLW_C(MSG_ID varchar(119) NOT NULL, DIRECTION varchar(8) NOT NULL, TO_PARTY_NAME varchar(60) NOT NULL, PRIMARY KEY CLUSTERED (MSG_ID ASC, DIRECTION ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'; 
end

I keep getting the same error over and over.

java.security.PrivilegedActionException: java.sql.SQLSyntaxErrorException: [SQL0104] Token 'CREATE TABLE SHEMA.TABLENAME was not valid. Valid tokens: : <IDENTIFIER> <PLI_STRING>

Not sure what the error means, so any help is appreciated!

Thanks!

Rein Van Leirsberghe
  • 755
  • 1
  • 7
  • 16
  • Is that really exactly the CREATE TABLE statement you are using? Adjust it to proper executable code first. – jarlh Nov 13 '15 at 12:12
  • DB2 has no syntax for checking if table exists in [CREATE TABLE](http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html) query. See this for checking if a table exists (with jdbc) http://stackoverflow.com/a/2942795/1737819. – Developer Marius Žilėnas Nov 13 '15 at 12:20
  • @Spookiecookie the approach shown in the question works fine in DB2. It will create a table if it doesn't exist, but if the table does exist, it fails with an error, and the `continue handler` suppersses that error. The problem is in the create table syntax. –  Nov 13 '15 at 13:50

1 Answers1

2

The problem is with your create table syntax.

This doesn't appear to resemble valid DB2 syntax. Instead, it looks a lot like SQL Server syntax. Consult the documentation for your specific version of DB2.

In terms of debugging this problem, you should first get a create table statement working directly in an SQL client before trying to put it in dynamic SQL. Then you should get your compound SQL statement working before trying to incorporate it into Java.