No DbConcurreney Error althoug update affects 0 rows

No DbConcurreney Error althoug update affects 0 rows

Postby georg.werner » Tue 01 Aug 2006 13:47

Hello,

I've got the following Problem with the tableAdapter:

A DbConcurrency Exception is only thrown when the "delete" command
affects 0 records.

If the "update" command affects 0 records no Exception is thrown.

The concurrency Check is done with a timestamp field.

Adapter settings:

this._adapter.UpdateBatchSize = 1;
this._adapter.ContinueUpdateOnError = false;

The problem seems to be a timestamp vaule (Direction = out from the stored procedure).
The timestamp value is changed regardless if the row is beeing changed or not the number of affected rows is 1.
georg.werner
 
Posts: 4
Joined: Tue 01 Aug 2006 13:40

Postby Alexey » Wed 02 Aug 2006 08:46

Please describe the problem in detail.
Send us small test project if possible to reproduce the problem; it is
desirable to use 'test' schema objects, otherwise include definition of
your own database objects. Do not use third party components.
If it is impossible for you to create test project please send us a piece of
your code where the error occurs.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

DataAdapter: Affected Records shows 1 although no records are changes

Postby georg.werner » Fri 09 Feb 2007 12:37

Hello,

I have prepared a test project but don't know where I can upload it.
Anyway I describe the problem here again.

You can reproduce it with mysql 5, corelab MysqlAdapter 35x and VS 2005.

Create Mysql Test Table:

Code: Select all
'testupdate', 'CREATE TABLE `testupdate` (
  `testupdate_id` int(11) NOT NULL,
  `tu_text` varchar(32) default NULL,
  `timestamp` datetime default NULL,
  `editor` varchar(32) default NULL,
  PRIMARY KEY  (`testupdate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'


Fill Table with some data..

Create a stored procedures your table:
Code: Select all
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`proc_testupdateLoadAll` $$
CREATE  PROCEDURE `proc_testupdateLoadAll`()
    SQL SECURITY INVOKER
BEGIN

   SELECT
      `testupdate_id`,
      `tu_text`,
      `timestamp`,
      `editor`
   FROM testupdate;

END $$
DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`proc_testupdateUpdate` $$
CREATE  PROCEDURE `proc_testupdateUpdate`(
`original_timestamp` DATETIME,
`p_testupdate_id`  INT(11),
`p_tu_text`  VARCHAR(32),
#IT YOU SET THIS PARAMETER TO IN (DEFAULT) THE CONCURRENCY VIOLATION WILL BE FIRED
OUT `p_timestamp`  DATETIME,
`p_editor`  VARCHAR(32)
)
    SQL SECURITY INVOKER
BEGIN
   DECLARE _NOW DATETIME;
   SET _NOW = NOW();
   SET `p_timestamp` = _NOW;
   UPDATE testupdate
   SET
      `tu_text` = `p_tu_text`,
      `timestamp` = _NOW,
      `editor` = `p_editor`
   WHERE
      testupdate_id = `p_testupdate_id`
   AND (timestamp = original_timestamp OR ISNULL(timestamp))
;

END $$

DELIMITER ;



I don't post the insert and delete stored procedures here.
We want to use a custom (not generated) table Adapter
and a generated DataSet (Designer).
We delete the generated TableAdapters after creating the dataSet.


Code: Select all
..snip
#region UpdateCommand
      this._adapter.UpdateCommand = new CoreLab.MySql.MySqlCommand();
      this._adapter.UpdateCommand.Connection = this.Connection;
      this._adapter.UpdateCommand.CommandText = "proc_testupdateUpdate";
      this._adapter.UpdateCommand.CommandType = System.Data.CommandType.StoredProcedure;

..snip

param = new CoreLab.MySql.MySqlParameter();
      param.ParameterName = "timestamp";
      param.DbType = System.Data.DbType.DateTime;
      param.MySqlType = CoreLab.MySql.MySqlType.DateTime;
      param.IsNullable = true;
      
        //NOT IF YOU SET THE PARAMETER DIRECTION TO INPUT CONCURRENCY VIOLATION WILL BE FIRED     
        param.Direction = param.Direction = ParameterDirection.Output;
      param.SourceColumn = "timestamp";         
      this._adapter.UpdateCommand.Parameters.Add(param);
..snip

The problem is passing out the new timestamp value:
We set the param.Direction to Output and the p_timestamp variable in the stored procedure to OUT.
the tableAdapter.Update command now always reports 1 row affected even if no row is changed.

We check the concurrency with the timestamp filed.
You could read the data with your app, then change the timestamp field
with a mysql browser and then change this record with your app and save it with dataAdapter.Update.
No record is changes because the timestamps doesn't macht.
The Problem is, that DataAdapter reports 1 record affected.
So nobody can be notified that nothing is changed.
If we change the OUT directions to IN so that no value is passed out the table adapter reports 0 records affected and am exception is fired.

Regards
Georg
georg.werner
 
Posts: 4
Joined: Tue 01 Aug 2006 13:40

Postby Alexey » Fri 09 Feb 2007 16:14

Are you using the latest version of MySQLDirect .NET?
Please send your zipped project to me directly (alexeyi at crlab dot com).
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Uploads

Postby georg.werner » Mon 12 Feb 2007 23:09

Hello,

did you receive my DemoProject files?

regards
Georg
georg.werner
 
Posts: 4
Joined: Tue 01 Aug 2006 13:40

Postby Alexey » Tue 13 Feb 2007 13:44

Yes, your project have been received and now is being investigated.
I'll inform you about results as soon as possible.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby georg.werner » Tue 06 Mar 2007 22:17

Hello Alexey,

anything new about the bug?

regards
Georg Werner
georg.werner
 
Posts: 4
Joined: Tue 01 Aug 2006 13:40

Postby Alexey » Wed 07 Mar 2007 07:31

We have fixed this bug today. Look forward to the new build.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL