Devart Forums

Version 5.5.16, Name = qryUpDt

SQL.Text = SELECT USR_GUID, INC_TYP FROM CAS_INC WHERE OBJ_GUID = :OBJ_GUID
OBJ_GUID is the only Primary Key on the table

I open as follows: (Much of this is unnecessary, but I was trying to solve this bug, so I manually set things that are normally set automatically)

qryUpDt.ParamByName('OBJ_GUID').DataType := ftString;
qryUpDt.ParamByName('OBJ_GUID').ParamType := ptInput;
qryUpDt.ParamByName('OBJ_GUID').AsString := rec_obj_guid; <--- rec_obj_guid correctly contains a value
qryUpDt.UpdatingTable := 'CAS_INC';

qryUpDt.Prepare;
qryUpDt.Open; <--- Only ONE record is retrieved, which is correct
qryUpDt.Edit;

I loop through the non-key fields (not the OBJ_GUID parameter) and set to new values, then I want to post the updated fields. I go:

qryUpDt.Post; <--- Error occurs here


Project my_project.exe raised exception class EDatabaseError with message 'Update failed. Found 2 records'.

Upon examination of the table, the qryUpDt.Post changed ALL the records in the table to the values I set intended for the one record that was retrieved in the .Open. The edits are committed, even though the "Update failed" error appears. It's as if the "WHERE" clause, with the OBJ_GUID parameter is chopped off, so the .Post tries to update all the records in the table. In this case, there were only two. If there are eight records, the same error occurs, reporting that 8 records were found (and updated incorrectly), instead of two.

This exact same coding (except for the fields list) (with the OBJ_GUID as the only parameter and is the key field) is used to update every other table in my DB and it works correctly. There is no trigger causing this. I can issue an UPDATE sql manually that is the same as what should be generated by the TIBCQuery component, and it updates only one record, which is correct.

What am I doing wrong in this case? Thanks for any help you can provide.

Figured out the solution:

SQL should include the key field, as a field, not just in the WHERE clause... like this:

SQL.Text = SELECT OBJ_GUID, USR_GUID, INC_TYP FROM CAS_INC WHERE OBJ_GUID = :OBJ_GUID

Not sure if this is necessary, but I then set the update table and keyfield property as well:

qryUpDt.KeyFields := 'OBJ_GUID';
qryUpDt.UpdatingTable := 'CAS_INC';

Now the IBCQuery is working properly.

Yes, you are right. The OBJ_GUID is required in SELECT. When automatically generating an UPDATE ... WHERE ... , all the fields specified in SELECT are listed in the WHERE clause. If the OBJ_GUID field is not specified in SELECT, it won't be present in the WHERE clause. This will lead to an attempt to update several records.
The TIBCQuery.KeyFields property affects the approach of generating the WHERE clause at automatic generating of the update query. If it is empty, then all the fields from SELECT are listed in WHERE, as described above. If you specify the OBJ_GUID field name in TIBCQuery.KeyFields, then WHERE will be generated as WHERE OBJ_GUID = <field value>. However, in this case as well, the OBJ_GUID field is required in SELECT.