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

asked 05 Jul '13, 11:23

Simon%20Smith's gravatar image

Simon Smith
121338
accept rate: 0%

edited 05 Jul '13, 12:55

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270


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...

permanent link

answered 05 Jul '13, 18:43

Volker%20Barth's gravatar image

Volker Barth
30.6k304455662
accept rate: 32%

edited 05 Jul '13, 18:45

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×47
×9

question asked: 05 Jul '13, 11:23

question was seen: 1,285 times

last updated: 05 Jul '13, 18:45