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