The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

Hi,

We are currently using the .NET OleDB Provider to connect to a SQL Anywhere 11.0.1 database and everything is working fine. We were looking into moving to the SQL Anywhere .NET provider for performance reasons, but we have not been able to get transactions to work like they are for us currently. In the code below we create an SATransaction from the connection and then execute an insert on that transaction. We then call the transaction's Rollback, but the row that we just added is not rolled back. Is there something that we are missing or doing incorrectly?

Imports System.Data
Imports NUnit.Framework
Imports iAnywhere.Data.SQLAnywhere

<TestFixture()>
Public Class TestSQLAnywhereNetProvider
    Dim Connection As SAConnection

    <SetUp()>
    Public Sub Setup()
        Connection = New SAConnection("Data Source=Sybase Has4win; UID=<UID>; PWD=<Password>;POOLING=True;")
        Connection.InitString = "SET TEMPORARY OPTION CONNECTION_AUTHENTICATION='<Our Authentication Key'"
        Connection.Open
    End Sub

    <TearDown()>
    Public Sub TearDown()
        Connection.Close
        Connection.Dispose
    End Sub

    <Test()>
    Public Sub TestTransactionRollsback()
        Dim CountQuery As SACommand = Connection.CreateCommand()
        CountQuery.CommandText = "SELECT COUNT(*) FROM DBA.t_version"

        Assert.AreEqual(1, CountQuery.ExecuteScalar(), "Number of rows in t_version") 
        Dim TransactionOne As SATransaction = Connection.BeginTransaction()
        Dim Query As SACommand = Connection.CreateCommand 
        Query.CommandText = String.Format("INSERT INTO DBA.t_version(major, minor, revision, path, cutdate) VALUES('{0}','{1}','{2}', '', '2014-01-01')", 11, 12, 13)
        Query.Transaction = TransactionOne
        Query.ExecuteNonQuery()

        CountQuery.Transaction = TransactionOne 
        Assert.AreEqual(2, CountQuery.ExecuteScalar(), "Number of rows in t_version") 
        TransactionOne.Rollback

        Assert.AreEqual(1, CountQuery.ExecuteScalar(), "Number of rows in t_version") 
    End Sub

This test fails on the last assert, because there are two rows in the table.

Thanks,

asked 16 Apr '14, 13:21

dmurty's gravatar image

dmurty
61128
accept rate: 100%

edited 16 Apr '14, 14:23

Why do you call ExecuteReader() for an INSERT?

(16 Apr '14, 14:09) Breck Carter

Was trying different things. If we call executeNonQuery it gives the same results

(16 Apr '14, 14:17) dmurty

Your code seems correct, you can check if the Rollback is really the problem if you check the database table by yourself after the call to Rollback. I guess it might be more a problem with the ExecuteScalar not really revisiting the table data... By the way which exact Version of 11.0.1 are your using?

(17 Apr '14, 03:18) Martin

I tried the equivalent C# test wtih a .NET 2.0 provider version 11.0.1.30692. It works for me. I populated a table with 3 rows. The counts went from 3 to 4 and back to 3.

What version of iAnywhere.Data.SQLAnywhere are you using?

(17 Apr '14, 11:45) JBSchueler

Running SQL Anywhere Network Server Version 11.0.1.3069 OEM Authenticated Edition

Referencing iAnywhere.Data.SQLAnywhere.v4.0 version 11.0.1.30694

(17 Apr '14, 12:48) dmurty

OK, so I think I figured it out. I created a C# test just to make sure that that was not the issue and it too failed. I then suspecting the database. In looking at some of the options I noticed that the option chained was turned off. Once I turned that on the tests pass! For some reason the OLEDB provider worked with the Chained option off.

permanent link

answered 17 Apr '14, 14:26

dmurty's gravatar image

dmurty
61128
accept rate: 100%

1

Ah, that's why Breck always recommends to set CHAINED "on" to prevent the obstacles of server-side autocommit mode - glad you got it working:)

FWIW: If you feel you have solved the case, I'd suggest that you turn your own comment into an answer and accept it.

(17 Apr '14, 14:46) Volker Barth
1

When you enter into transaction mode, the .NET provider sets the isolation level but does nothing about ensuring that chained is "on". The OLE DB provider goes to great lengths to control autocommit behaviour.

(17 Apr '14, 15:09) JBSchueler
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
×22
×4

question asked: 16 Apr '14, 13:21

question was seen: 1,623 times

last updated: 17 Apr '14, 15:09