Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Hello,

We would like to use the SATransaction in a using statement. But in case of an exception, the transaction is not rolled back and the transaction is still open. This prevent us from opening a second transaction.

In Microsoft SQL Server the Dispose function of a SqlTransaction makes an automatic rollback.

Example: Update the surname of contact 1 and add a "x" after the surname. The Exception should rollback the update because it is in a transaction.

  using (IDbConnection connection = new SAConnection("DSN=SQL Anywhere 12 Demo")) {
    connection.Open();

    String oldValue = null;
    using (IDbCommand command = connection.CreateCommand()) {
      command.CommandText = "SELECT Surname FROM Contacts WHERE ID = 1";
      oldValue = command.ExecuteScalar().ToString();
    }

    try {
      using (IDbTransaction transaction = connection.BeginTransaction()) {
        using (IDbCommand command = connection.CreateCommand()) {
          command.Transaction = transaction;
          command.CommandText = String.Format("UPDATE Contacts SET Surname = '{0}' " +
            "WHERE Id = 1", oldValue + "x");
          command.ExecuteNonQuery();

          throw new Exception();
        }
        transaction.Commit(); // never reached in this example
      }
    }
    catch (Exception) {
      // go ahead
      // but transaction isn't rollbacked
    }

    // -> the surname of contact 1 is now "Hildebrandx" and the transaction is still open

    using (IDbTransaction transaction = connection.BeginTransaction()) {
      // It's not possible to open a new transaction because the first
      // transaction isn't commited or rolled back
    }
  }

I think this is a bug. Of course I can put this in a try/catch but SATransaction is IDisposable so a using statment is allowed.

asked 21 Oct '13, 12:50

Stefan's gravatar image

Stefan
81238
accept rate: 0%


We've modified the ADO.NET provider to fix this problem in CR #749295. The next EBF should have the fix (11.0.1.3057, 12.0.1.3996, 16.0.0.1709 and higher).

permanent link

answered 21 Oct '13, 16:52

Minghai%20Chang's gravatar image

Minghai Chang
12633
accept rate: 20%

edited 22 Oct '13, 10:40

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175

Thats great! Thank you

(22 Oct '13, 04:11) Stefan
Replies hidden

You can accept this as the answer, so everyone sees the state of your question

(22 Oct '13, 05:02) Martin
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:

×76
×59
×39

question asked: 21 Oct '13, 12:50

question was seen: 2,517 times

last updated: 22 Oct '13, 10:40