Record not found or changed by another user

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for MySQL in Delphi and C++Builder
Post Reply
dcraggs
Posts: 9
Joined: Wed 31 Jan 2007 13:19

Record not found or changed by another user

Post by dcraggs » Wed 31 Jan 2007 13:26

Hi,

We have just upgraded from version 2.60.0.0 to 3.10.4.0 of dbexpmda.dll.

We are now getting a problem when editing records. If a field is blank, if you add a value then save the change, we are getting the error "Record not found or changed by another user".

When we revert back to the old version of dbexpmda.dll we do not get this problem.

Any ideas on what is going wrong?

Thanks,

Dave

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Thu 01 Feb 2007 10:20

We could not reproduce the problem. Check if the primary key of the table is retrieved to the client application. Probably changing value of the TDataSetProvider.UpdateMode property to upWhereKeyOnly will help you to solve this issue.
If this will not help, please send a complete small sample to evgeniyD*crlab*com to demonstrate it, including script to create and fill table.

Also supply us the following information:
- exact version of Delphi, C++ Builder or Kylix;
- exact version of MySQL server and MySQL client.

dcraggs
Posts: 9
Joined: Wed 31 Jan 2007 13:19

Post by dcraggs » Thu 01 Feb 2007 20:25

I'll try and sort something out tomorrow.

Dave

dcraggs
Posts: 9
Joined: Wed 31 Jan 2007 13:19

Post by dcraggs » Fri 02 Feb 2007 13:00

Right - reproduced the problem.

It is to do with the OnReconcileError event in TSimpleDataset.

The code in the event is -

Action := HandleReconcileError(Dataset,UpdateKind,E);

I have a table with a char(2) field. I insert a record with that field set to spaces, post and applyupdates. If you now edit the record, and insert a value into that field. on posting and applyupdate, the event is fired. And the reconcile error dialog appears.

This does not happen with dnexpmda.dll version 2.60.0.0.

I have a sample project that demonstrates this. The main part of the code is a s follows:

CRSQLConnection1.DriverName := 'MySQL';
CRSQLConnection1.LibraryName := 'dbexpmda.dll';
CRSQLConnection1.VendorLib := 'libmysql.dll';
CRSQLConnection1.GetDriverFunc := 'getSQLDriverMySQL';
CRSQLConnection1.Params.Values['Database'] := 'CoreLabTest';
CRSQLConnection1.Params.Values['HostName'] := 'localhost';
CRSQLConnection1.Params.Values['User_Name'] := 'root';
CRSQLConnection1.Params.Values['Password'] := '';

CRSQLConnection1.Connected := true;

SQLQuery1.SQLConnection := CRSQLConnection1;
SimpleDataSet1.Connection := CRSQLConnection1;

// Delete table if it exists
try
SQLQuery1.SQL.Text := 'Drop Table CoreLabTest';
SQLQuery1.ExecSQL;
except
end;

// Create table
SQLQuery1.SQL.Text := ' CREATE TABLE CoreLabTest (aKey int AUTO_INCREMENT PRIMARY KEY NOT NULL , Ref1 char (2) NULL , Ref2 char (2) NULL )';
SQLQuery1.ExecSQL;

SimpleDataSet1.DataSet.CommandText := 'Select * from CoreLabTest';
SimpleDataSet1.Open;

// insert a record
SimpleDataSet1.Insert;
SimpleDataSet1.FieldByName('Ref1').AsString := '';
SimpleDataSet1.FieldByName('Ref2').AsString := '';
SimpleDataSet1.Post;
SimpleDataSet1.ApplyUpdates(-1);

// update a record
SimpleDataSet1.Edit;
SimpleDataSet1.FieldByName('Ref1').AsString := 'x';
SimpleDataSet1.FieldByName('Ref2').AsString := 'x';
SimpleDataSet1.Post;
SimpleDataSet1.ApplyUpdates(-1);

SimpleDataSet1.Close;

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Tue 06 Feb 2007 09:53

We received your sample. As soon as we have any progress in this case or we need any additional information, we will let you know.

dcraggs
Posts: 9
Joined: Wed 31 Jan 2007 13:19

Post by dcraggs » Mon 12 Feb 2007 13:43

Any progress on this?

Dave

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Tue 13 Feb 2007 14:54

We have thoroughly checked this issue. You are right, this problem does not appear in DbxMda 2. In DbxMda 3 we changed behaviour working with empty values of the CHAR fields to avoid a problem with dbExpress. In the DbxMda 3, empty values for CHAR fields are converted to NULL values. The TSimpleDataset component uses an internal Provider with the UpdateMode property set to upWhereAll, so all the fields from recordset appear in the WHERE clause of update SQL commands generated by the TSQLResolver object of this Provider.
Changing the current DbxMda behaviour to the previous one will cause problems for other users. You can try to replace the CHAR field types in your table with the VARCHAR field type. This will help you avoid the problem.

dcraggs
Posts: 9
Joined: Wed 31 Jan 2007 13:19

Post by dcraggs » Thu 22 Feb 2007 09:35

Just spotted the reply - we have reverted back to using v2 of the DLL but would like to use the 'latest and greatest' but really don't want to do any database changes.

Do you plan to fix this properly any time in the future?

Dave

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Thu 22 Feb 2007 14:25

To fix this problem completely, changes in dbExpress itself are required. We are unable to fix this problem if there are no changes in Borland's code. If you are user of DbxMda Professional, you can change behaviour of the driver to fix this problem for your case.

Post Reply