Bug in Sybase 17 when not closing reader in ADO.Net after executing a procedure

[former_SQLA_member1694974]

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();
            }
        }
    }
}

[chinmaydixit]

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?

[chris_keating]

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?

[VolkerBarth]

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

[former_SQLA_member1694974]

@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?

[VolkerBarth]

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

[jack_schueler]

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

It may and does.

[VolkerBarth]

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';