We found a huge bug in SQL Anywhere 17. If a procedure is executed and the connection is closed without calling reader.Close(), the changes will NOT be written to the database. The behaviour was different in Sybase 12!

Take a look at: // cDataReader.Close(); When using .Close() everything seems to work.

A sample for reproduction:

using Dapper;
using Sap.Data.SQLAnywhere;
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ProcedureBug
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var connection = new SAConnection("SERVER=setup;DBN=simplic;uid=dba;pwd=sql;links=tcpip;charset=utf-8"))
            {
                connection.Open();

                connection.Execute(@"
                    CREATE TABLE IF NOT EXISTS ""admin"".""Bug_Sample"" (

                        ""Guid"" UNIQUEIDENTIFIER NOT NULL,
                        ""SampleText"" VARCHAR(200) NULL,
                        PRIMARY KEY(""Guid"" ASC)
                    ) IN ""system"";");

                connection.Execute(@"
                    CREATE OR REPLACE PROCEDURE admin.SampleInsert(@in_ui uniqueidentifier, @in_txt varchar(200))
                    BEGIN
                        INSERT INTO admin.Bug_Sample (Guid, SampleText) VALUES (@in_ui, @in_txt);
                        MESSAGE 'Hey!';
                        SELECT NewId() as NewGuid;
                    END;
                ");

                var id = Guid.NewGuid();
                var text = "SAMPLE TEXT01";

                ExecuteSqlWithResult("SELECT * FROM admin.SampleInsert(?, ?)", "", id, text);
            }
        }

        public static void ExecuteSqlWithResult(string SqlStatement, string ConnectionName, params object[] Parameter)
        {
            using (SAConnection connection = new SAConnection("SERVER=setup;DBN=simplic;uid=dba;pwd=sql;links=tcpip;charset=utf-8"))
            {
                connection.Open();
                IDataReader cDataReader = null;

                try
                {
                    SACommand cCommand = new SACommand(SqlStatement, connection);

                    if (Parameter != null)
                    {
                        foreach (var cParam in Parameter)
                        {
                            cCommand.Parameters.Add(new SAParameter() { Value = cParam });
                        }
                    }

                    cDataReader = cCommand.ExecuteReader(CommandBehavior.CloseConnection);

                    while (cDataReader.Read())
                    {
                        // ....
                    }
                }
                catch (Exception ex)
                {
                    if (cDataReader != null)
                    {
                        cDataReader.Close();
                    }

                    if (connection != null)
                    {
                        if (connection.State != ConnectionState.Closed)
                        {
                            connection.Close();
                        }
                    }

                    throw ex;
                }

                // Without this line, no data will be written!!!!
                // cDataReader.Close();
                connection.Close();
            }
        }
    }
}

asked 26 Oct, 09:51

benedikteggers's gravatar image

benedikteggers
4117
accept rate: 100%

edited 26 Oct, 10:46

I have never seen this behavior, have used ADO.NET with SA with Dapper and EF. I don't explicitly close the connection. Connection pool is enabled by default and my understanding is that the connection goes back to the pool for the connection string. Did you check your autocommit?

(27 Oct, 09:44) chinmaydixit

The SQLA 12 behavior appears to be incorrect. The DataReader "reads a forward-only stream of rows from a data source". Given that its role is to read, it would not be expected that updates, deletes, or inserts to be performed and as such commits. In any event, the DataReader.Close should be explicitly called when done with the DataReader otherwise the connection associated with the DataReader cannot be used for any other purpose. In SQLA, the side effect of explicitly closing the DataReader is a commit when running in autocommit mode. Given that this is documented in the Close method suggests that commits are not expected in the DataReader in general.

I cannot find any documentation to dispute the SQLA 17 behavior. Do you have any sources to suggest DataReader commit behaviors is inconsistent with the SQLA 17 behaviour?

permanent link

answered 26 Oct, 21:21

Chris%20Keating's gravatar image

Chris Keating
6.2k38104
accept rate: 30%

I don't usually use ADO.Net with SQL Anywhere, but according to my understanding of the docs, that's expected behaviour:

For the reader's Close() method:

Explicitly call the Close method when you are finished using the SADataReader.

When running in autocommit mode, a COMMIT is issued as a side effect of closing the SADataReader.

For the connection's Close() method:

The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled. If Close is called while handling a StateChange event, then no additional StateChange events are fired. An application can call Close multiple times.

So, without the reader's Close() call, the connection's close has to rollback the uncommitted transaction. (In my limited understanding, that is.)

permanent link

answered 26 Oct, 11:02

Volker%20Barth's gravatar image

Volker Barth
37.8k347515785
accept rate: 34%

converted 27 Oct, 02:40

@Volker Barth

Ok, this was different in ado.net sybase 12. Especially, there is no rollback in the transaction log. Has this something todo with the isolation level?

(26 Oct, 15:23) benedikteggers
Replies hidden

I don't think so. But it may or may not have to do with v17's auto commit feature?

(26 Oct, 15:56) Volker Barth
1

Re: But it may or may not have to do with...

It may and does.

(27 Oct, 09:49) JBSchueler

Does the behaviour change, when you add "ClientAutocommit=yes" to the connection string you are using with SAConnection?

And what does the following reveal?

select connection_property('auto_commit');
select * from sysoption where "option" = 'auto_commit';
(27 Oct, 11:38) Volker Barth
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:

×196
×38

question asked: 26 Oct, 09:51

question was seen: 136 times

last updated: 27 Oct, 11:40