Mysql multi-layer development and data update

Problem:
Development Environment: delphi7se + mysql 5.0.67 + dbexpress 2.0 + mysql50open
Development Control: tsimpledataset + datasoure + dbgrid/dbedit
Permission: mysql has granted the select/update permission to the user.
If the data is modified, applyupdates (0) always reports an error.
Add outputdebugstring (pchar (strList) and monitor two error messages in view/debug windows/event log:
"Record not found or changed by another user ."
"Unable to find record. No key specified ."

Solution:
From http://delphi.ktop.com.tw/board.php? Cid = 30 & fid = 66 & tid = 35255

The terraform contains one TSQLConnection, TSQLQuery, TDataSetProvider, TClientDataSet, TDataSource, TDBGrid, and TDBNavigator.
TSQLConnection specifies the Connection Name to connect the Connection to the resource.
TSQLConnection on TSQLQuery statement, specify SQL (for the simplest SELECT * FROM ...), then, all fields are added to the fields attribute of TSQLQuery. Except for the primary key, ProviderFlags of other columns are removed from pfInWhere (note that pfInWhere of the primary key must be checked, remove non-primary keys)
TSQLQuery on TDataSetProvider Region
The TDataSetProvider on the TClientDataSet dataset, and the post of vodafterpost (content is the data volume transaction and ApplyUpdate) and ReconcileError (call HandleReconcileError)
TDataSource, TDBGrid, and TDBNavigater. Set the basic controllability ..

After the slave row is created, the slave row is created in two copies (A and B), and then the slave row is created...
For the same item, A is active (PK is not active), B is active (no PK is included), and can be successfully stored (, if B tries to update the token, it will find that the information is based on B's latest)
For the same token, A initiates an action (including PK), stores the token, and B initiates an action (whether including or not PK ), the error message "Record not found or changed by another user" is displayed.
Press Cancel, select correct, and change the pkvalue to the new pkvalue entered by.

Cause:
There are different opinions, but there are basically two points to note:
Reference: "error message" Record not found or changed by another user "appears when ClientDataSet is submitted
The reason for the analysis may be that the updated data cannot be found. There are several reasons for the problem:
1. No primary key
2. Some fields cannot be recognized
Some solutions are nothing more than setting the UpdateMode attribute of DSP, removing some default values of fields
I checked these problems carefully and thought they were impossible. I analyzed the cause carefully: There are two ways to update records:
Clientdataset. append;
....
Submit and update again
Another method is to insert tables in large batches and enable dataset update.
This problem does not exist in the first solution. I began to analyze the problems that may exist in the 2nd solutions. There are the following statements:
Insert into tableA (field1, field2) SELECT field1, ''FROM tableb
A few days ago, we found that the CommandText of ClientDataSet could not be identified using SELECT '', prompting invalid parameters.
I wonder if this is the cause, so changing ''to'' is normal. "

According to the log analysis of mysql, I encountered 2nd cases, but because of the large number of fields, it is difficult to modify, and I do not want to manually write SQL statements.
The SQL statement returned by datasnap is
Update students set
Previous name = '99'
Where
Class = '1' and
Register student ID = '20140901' and
ID card No. = 'xxx' and
Name = 'Gu xx' and
Used name = ''and
Gender = 'femal' and
Nationality = 'Han nationality 'and
Native province = 'jiangsu 'and
Native City and County = 'xxx City 'and
Home address = 'unit 702, Unit 2, No. 51, xxx Road 'and
Contact number = '000000' and
Graduation school = 'xxx' and
Father name = ''and
Father unit = ''and
Mother name = ''and
Mother unit =''
It can be seen that all fields involved in the form are uploaded back to mysql regardless of whether they are modified or not.
After modifying according to the above method, the SQL statement returned by datasnap is
Update students set
Previous name = '11'
Where
Registered student ID = '20140901'

Update students set
Used name = 'qq ',
Father name = 'A ',
Father unit = 'bbbbbb ',
Mother name = 'cc ',
Mother unit = 'ddddd'
Where
Registered student ID = '20140901'

COMMIT
Now, it only involves fields in the form where values are modified.
The comparison between the two phases shows that the subsequent SQL statements are simpler and more efficient.

In addition, the Foreign statement also makes sense. This is caused by non-standard mysql SQL:
As I posted in a reply to this thread in dbexpress, I suspect
Posters problem is the fact that MySQL does not follow the standard
Convention of returning the number of records affected by an update.
Most DBMS's return the number of records found that matched the WHERE
Clause in the update SQL statement. MySQL doesn't do this. It returns
The number of records that had data changed by the SQL update
Statement. So, if an update SQL WHERE clause matches one record,
The UPDATE clause does not actually make changes to the column values
(For example SET MyField = '000000' and MyField already contains '000000 '),
MySQL returns a RowsAffected count of zero. 802.1x interprets this
An unable to find record error.

Conclusion:
Reference
"I think the problem lies in TSimpleDataSet.
Mr. LI suggested that you use the TSQLQuery + TDataSetProvider + TClientDataSet method at the conference and the book.
Although TSimpleDataSet is composed of these three element packages, many Property Method events are hidden.
When you modify the ProviderFlags of the TField pulled out by TSimpleDataSet, The TField. ProviderFlags of the TClientDataSet region is actually modified on the worker.
We should modify TField. ProviderFlags of TSQLQuery.
Therefore, no matter how you change it, the SQL statement statements observed by TSQLMonitor are not affected.
Therefore, we recommend that you use TSimpleDataSet.
I personally do not use TSimpleDataSet (according to Mr. Li's suggestion, some Simple tasks can be handled by it, other tasks are well written into three elements)
Other advantages of using TSQLQuery + TDataSetProvider + TClientDataSet
When you use ADO, you only need to convert TSQLQuery into TADOQuery.
When you want to change to a three-dimensional model, you only need to move TSQLQuery and TDataSetProvider to the middle-end model"

Method 2: (no trial, spare)
From http://www.diybl.com/course/7_databases/database_other/200838/103354.html
''Record not found or changed by another user ''. This error is a problem I encountered when I was working on the project. I found some references to solve it. As follows:
Controls used: TDataSetProvider, TADOQuery, and TClientDataSet
1. when DataSetProvider. when UpdateMode = upWhereAll, during update, the where field specifies all fields. For example, if you have three fields a, B, and c, you have modified the c Field, the command modified in app server is update... set c = new c where a = old a and B = old B and c = old c
If one of the old one a, B, and c has been changed by others, then the where clause cannot be found to modify the record. Therefore, the report "records are modified by others" is irrelevant to "locking.

2. When DataSetProvider. UpdateMode = upWhereKeyOnly, the where field specifies the key field during update, for example,
You have three fields a, B, and c. You have modified the c field. The a is the key field. The command modified in the app server is
Update... set c = new c where a = old a (compare with field a only) Your program should be designed so that the customer cannot modify the value of the primary key field.
Your customers can only modify the values of other fields. Other fields will not appear in the where clause, and you will not be confused.

(Solution in 2nd ways. Note that if your table does not have a primary key, double-click dataset to add all your fields, set InKey to true in the ProviderFlasgs attribute of a field with a unique value (This field must have a unique value, which can be equivalent to a primary key, that is, it can uniquely identify the row record), TADOQuery, set InKey to true for all field characters with unique values in the TClientDataSet Control)

Author: "breeze and chaos"