SQL Anywhere - rollback transaction with DDL

[Former Member]

I'm trying to run some SQL update scripts as part of an installaion/update process on an SQL Anywhere database. The scripts naturally include DDL i.e. create and alter tables, indexes etc. I was hoping to wrap it all in a neat BEGIN TRANSACTION and COMMIT/ROLLBACK depending on the outcome. However, it seems that the DDL statements cause an implicit commit, so the rollback is useless.

Has anyone encountered this restriction and what (if any) are the alternatives, other than switching to a different RDBMS that does support rollback for DDL?

Thanks Simon

[VolkerBarth · Accepted solution]

The following question seems rather similar:

ddl in transaction

From what I know, there are a few workarounds:

• In case you have full control over the database (say, it's a single-user database), make a database backup beforehand and return to that in case any statement of your update sripts fails.

• In case only new tables and views are added, the particular CREATE SCHEMA statement may be of help - it groups several CREATE TABLE/CREATE VIEW/GRANT statements in one single transaction. (However, it would not group other DDL or DML in the same transaction...)

• In case the DDL is expected to fail because the same update script may be applied to different current versions (so some database may already contain tables or columns or other database objects that are contained in the script whereas other database may not have them), you may use IF statements to check the current database schema and apply only selected DDL statements. Or you could use the "smarter" DDL variants like CREATE TABLE ... IF NOT EXISTS, CREATE OR REPLACE VIEW and the like that do handle existing database objects without errors.

---

As to the "different DRDBMS that does support rollback for DDL":

What exactly would this mean in a multi-user database: When altering a table with no automatic commit, would that not require all other transactions that try to access that table to wait until the transaction is committed? I'd think this would potentially block other users for longer times...