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: 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(); } } } } |
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? |
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:
For the connection's Close() method:
So, without the reader's Close() call, the connection's close has to rollback the uncommitted transaction. (In my limited understanding, that is.) 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 '21, 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 '21, 15:56)
Volker Barth
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 '21, 11:38)
Volker Barth
|
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?