0

I'm trying to execute two DML statements in DB2 using JDBC and I keep getting a syntax error:

ALTER TABLE "TEST" ALTER COLUMN "COL1" SET DATA TYPE INT;
ALTER TABLE "TEST" ALTER COLUMN "COL1" SET NOT NULL;

If I execute them one by one everything is OK.

I've tried with no semicolons, only one semicolon on the first statement. I also tried with several \r\n and I really can't understand what the problem is.

The exception is the following:

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;
ALTER TABLE "TEST";1" SET DATA TYPE INT;<space>, DRIVER=3.65.77

Does anyone know what might be the problem?

rpvilao
  • 1,116
  • 2
  • 14
  • 31

1 Answers1

0

Obviously, the execute() method accepts only a single statement, not multiple statements delimited by whatever you might try. It's clearly stated in the API documentation: "Executes the given SQL statement".

You can wrap multiple statements into a (single) compound statement:

begin execute immediate 'alter ...'; execute immediate 'alter ...'; end
mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • It's not that obvious... MySQL, Oracle, PostgreSQL, SQLServer and H2 allow it. Also, not pretty sure but I think that compound statements cannot be used for DML statements. But, even so, I've already tried that. – rpvilao Jun 14 '13 at 16:01
  • "_MySQL, Oracle, PostgreSQL, SQLServer and H2 allow it_" -- not true. I am not going to test all these databases for you, but an attempt to run something like `altTbl.execute("alter table test modify ( col1 int); alter table test modify ( col1 not null enable)");` on an Oracle database will cause `ORA-00911: invalid character`. – mustaccio Jun 14 '13 at 16:42
  • For DB2 it has to be a compiled (not inlined, indicated by the `ATOMIC` keyword) statement, and the DDL statements must be executed dynamically via `EXECUTE IMMEDIATE`. I modified the answer accordingly. This approach would also work in an Oracle database. – mustaccio Jun 14 '13 at 16:57