MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Vladislav Vaintroub Date:July 8 2010 8:21pm
Subject:bzr commit into connector-net-6.0 branch (vvaintroub:823) Bug#54895
View as plain text  
#At file:///H:/connector_net/6.0/ based on revid:reggie.burnett@stripped

  823 Vladislav Vaintroub	2010-07-08
      Make sure MySqlDataAdapter.Update() works for custom stored procedure driven update commands 
      that make use of UpdateRowSource.FirstReturnedRecord ( Bug#54895)

    modified:
      CHANGES
      MySql.Data/Provider/Source/datareader.cs
      MySql.Data/Tests/Source/DataAdapterTests.cs
=== modified file 'CHANGES'
--- a/CHANGES	2010-07-02 18:36:52 +0000
+++ b/CHANGES	2010-07-08 20:21:58 +0000
@@ -1,3 +1,5 @@
+- Make sure MySqlDataAdapter.Update() works for custom stored procedure driven update commands 
+  that make use of UpdateRowSource.FirstReturnedRecord (Bug#54895)
 - Call DataTable.AcceptChanges() for affected tables at the end of MySqlAdapter.Update, 
   to match the documented IDataAdater behavior (bug#54863)
 - Fix race condition (concurrent reading/modification of the same DataSet) in StoredProcedure.GetParameters()

=== modified file 'MySql.Data/Provider/Source/datareader.cs'
--- a/MySql.Data/Provider/Source/datareader.cs	2010-06-10 16:48:40 +0000
+++ b/MySql.Data/Provider/Source/datareader.cs	2010-07-08 20:21:58 +0000
@@ -55,6 +55,10 @@ namespace MySql.Data.MySqlClient
 		private Hashtable fieldHashCS;
 		private Hashtable fieldHashCI;
 
+		// Used in special circumstances with stored procs to avoid exceptions from DbDataAdapter
+		// If set, AffectedRows returns -1 instead of 0.
+		private bool disableZeroAffectedRows; 
+
 		/* 
 		 * Keep track of the connection in order to implement the
 		 * CommandBehavior.CloseConnection flag. A null reference means
@@ -77,6 +81,12 @@ namespace MySql.Data.MySqlClient
 			this.statement = statement;
 			fieldHashCS = new Hashtable();
 			fieldHashCI = new Hashtable(StringComparer.InvariantCultureIgnoreCase);
+
+			if (cmd.CommandType == CommandType.StoredProcedure &&
+			cmd.UpdatedRowSource == UpdateRowSource.FirstReturnedRecord)
+			{
+				disableZeroAffectedRows = true;
+			}
 		}
 
 		#region Properties
@@ -140,7 +150,21 @@ namespace MySql.Data.MySqlClient
 			// RecordsAffected returns the number of rows affected in batch
 			// statments from insert/delete/update statments.  This property
 			// is not completely accurate until .Close() has been called.
-			get { return (int)affectedRows; }
+			get 
+			{
+
+				if (disableZeroAffectedRows)
+				{
+					// In special case of updating stored procedure called from 
+					// within data adapter, we return -1 to avoid exceptions 
+					// (s. Bug#54895)
+					if (affectedRows == 0)
+						return -1;
+
+				}
+
+				return (int)affectedRows;
+			}
 		}
 
 		/// <summary>

=== modified file 'MySql.Data/Tests/Source/DataAdapterTests.cs'
--- a/MySql.Data/Tests/Source/DataAdapterTests.cs	2010-07-01 16:04:18 +0000
+++ b/MySql.Data/Tests/Source/DataAdapterTests.cs	2010-07-08 20:21:58 +0000
@@ -896,5 +896,75 @@ namespace MySql.Data.MySqlClient.Tests
             Assert.AreEqual(ds.Tables["T"].Rows.Count, 1);
             Assert.AreEqual(ds.Tables["T"].Rows[0]["field"], "row2");
         }
+
+        /// <summary>
+        /// Bug#54895
+        /// ConcurrencyException when trying to use UpdateRowSource.FirstReturnedRecord 
+        /// with UpdateCommand and stored procedure.
+        /// </summary>
+        [Test]
+        public void UpdateReturnFirstRecord()
+        {
+            string createTable = 
+            "CREATE TABLE `bugtable` ( " +
+              "`id_auto` int(11) NOT NULL AUTO_INCREMENT," +
+              "`field` varchar(50) DEFAULT NULL," +
+              "counter int NOT NULL DEFAULT 0," + 
+              "PRIMARY KEY (`id_auto`)" +
+            ")";
+
+            string procGetAll = 
+            "CREATE PROCEDURE sp_getall_bugtable()" +
+            " BEGIN " +
+                "select * from bugtable;" +
+            " END ";
+
+            string procUpdate = 
+            "CREATE PROCEDURE sp_updatebugtable(" +
+                "in p_id_auto int, " +
+                "in p_field varchar(50)) " +
+            "BEGIN " +
+                "update bugtable set field = p_field, counter = counter+1 where id_auto=p_id_auto; " +
+                "select * from bugtable where id_auto=p_id_auto; " + /*retrieve updated row*/
+            "END ";
+
+            execSQL(createTable);
+            execSQL(procGetAll);
+            execSQL(procUpdate);
+
+
+            /* Add one row to the table */
+            MySqlCommand cmd = new MySqlCommand(
+                "insert into bugtable(field) values('x')", conn);
+            cmd.ExecuteNonQuery();
+
+
+            DataSet ds = new DataSet();
+            MySqlDataAdapter da = new MySqlDataAdapter();
+
+            da.SelectCommand = conn.CreateCommand();
+            da.SelectCommand.CommandType = CommandType.StoredProcedure;
+            da.SelectCommand.CommandText = "sp_getall_bugtable";
+
+            da.UpdateCommand = conn.CreateCommand();
+            da.UpdateCommand.CommandType = CommandType.StoredProcedure;
+            da.UpdateCommand.CommandText = "sp_updatebugtable";
+            da.UpdateCommand.Parameters.Add("p_id_auto", MySqlDbType.Int32, 4, "id_auto");
+            da.UpdateCommand.Parameters.Add("p_field", MySqlDbType.VarChar, 4, "field");
+            da.UpdateCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
+           
+
+            da.Fill(ds, "bugtable");
+            DataTable table = ds.Tables["bugtable"];
+            DataRow row =  table.Rows[0];
+            row["field"] = "newvalue";
+            Assert.AreEqual(row.RowState, DataRowState.Modified);
+            Assert.AreEqual((int)row["counter"], 0);
+
+            da.Update(table);
+
+            // Verify that "counter" field was changed by updating stored procedure.
+            Assert.AreEqual((int)row["counter"], 1);
+        }
     }
 }


Attachment: [text/bzr-bundle] bzr/vvaintroub@mysql.com-20100708202158-x3tg5mtv15scxb57.bundle
Thread
bzr commit into connector-net-6.0 branch (vvaintroub:823) Bug#54895Vladislav Vaintroub8 Jul