CODEWITHSUNDEEP
javasqldb2
Rein Van Leirsberghe
Rein Van Leirsberghe

Reputation: 765

Create table if not exists in DB2

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!

Upvotes: 1

Views: 1176

Answers (1)

user1919238
user1919238

Reputation:

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.

Upvotes: 2

Related Questions