ddl in transaction

[Former Member]

Can we for a specific session or transaction block set automatic commit(side effect) to off? Or is there a "ddl in tran" equivalent in ASA.

[VolkerBarth]

What are you trying to achieve?

As documented here, most SQL Anywhere DDL statements do an automatic commit as side effect, so they close a transaction by design.

When you are trying to execute DDL from within a stored procedure, there are certainly alternatives to do so. Say, if you need to create several objects in one transaction, have a look at the CREATE SCHEMA statement which allows to group several DDL statements in one transaction (or at least it rollbacks all statements when one statement fails, so I assume it just uses one single transaction...).

[Former Member]

Hi Volkver,

Thanks for your answer. One of the customer software deliveries is an automatic database Deployment tool for the Sybase database production environment with an ASA 12.0.1 database server. their intention is that all their database increments (script files) are deployed one by one by the automatic Deployment tool. The increment is deployed within a transaction mechanism that is supported by the Deployment tool. A usual single script files contains a set of both DDL and DML statements to be able to do a complete functional increment. Normally each script would be committed, but when a script fails the whole increment should be rolled back till the previous committed script/increment. The overall deployment should also be stopped when this exception event occur. So far it only works for the DML SQL statements in the scripts but NOT! for the DDL statements in the script. As you understand they want both the DDL and DML statements of the script to be rolled back when something fails.

They also tried to switch the 'Chained' database option to get it done but it makes no difference when it is on or off. They created this type of Deployment for other database vendors, that worked for both the DML and the DDL statements. Please would you advise on what can be done to make this work? Do you know if there is a work-around for this issue?

[former_SQLA_member1694880]

There are no workarounds. Accept reality as it is.

[VolkerBarth]

A common method for SQL Anywhere to guarantee that a database schema upgrade is either successfully completed or completely rolled back would be to do a database backup just beforehand - and restore that in case an error is thrown during the upgrade.

That does work well with embedded databases. It may not work well in an "actively used multi-user database" - however, a script with a bunch of (by definition blocking) ALTER TABLE statements that have to be rolled back (or undone) lateron in case of an error won't make concurrent users happy, either...