No DbConcurreney Error althoug update affects 0 rows

[georg.werner]

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.

[Alexey]

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.

[georg.werner]

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

[Alexey]

Are you using the latest version of MySQLDirect .NET?
Please send your zipped project to me directly (alexeyi at crlab dot com).

[georg.werner]

Hello,

did you receive my DemoProject files?

regards
Georg

[Alexey]

Yes, your project have been received and now is being investigated.
I'll inform you about results as soon as possible.

[georg.werner]

Hello Alexey,

anything new about the bug?

regards
Georg Werner

[Alexey]

We have fixed this bug today. Look forward to the new build.