This has been reported as a bug. I post here as a caution for PG users.
A simple situation –
a) Typical parent, child relationship in Dct
b) Child table has a PKey on a backend sequence (ChildID).
c) Child PKey is marked READONLY in Dct.
d) Child table has a FKey on ParentID.
From trace, the SQL generated for a Child record is something like:
Update ChildTbl set price=9.99 WHERE parentid=1 AND qty=3 AND desc=‘some description’;
Notice the ChildTbl PK value IS NOT included. This is because the column is READONLY in the Dct (afterall, it is a backend sequence value). However, the result is a situation where multiple rows could be updated by the generated SQL statement instead of just one because uniqueness is NOT guaranteed by parentid, qty, desc conditions.
I’ve seen something similar caused by using WATCH field switch. Could it be that you have it set on another field? Or that there is a Unique Key with the components showed (eg.parentid,qty,desc)
Are you calling the update form from a browse, and does the browse have the child file primary key as an additional sort order? If not, try adding it as an additional sort order. I suspect the update form is trying to identify the child record by whatever fields it has determined makes that record unique - based on the sort order used in your browse?
a) yes, form called from browse
b) have tested both with & without child primary key in additional sort
c) agree, update form is trying to identify child record by whatever fields included in update,
instead of simply the primary key
I think I’m on the same page as Federico…when the browse opens the form it is going to do a REGET to arm the watch for the concurrency checking. If it doesn’t think it has the information (from the browse’s sort order) to identify the record uniquely it would, IIRC, give you an “operation not supported” error.
Personally I don’t think the key field being readonly should make a difference, particularly if it is included in the browse’s key. If it’s in the where clause it IS only being read. Like Federico suggests, you might manage to fool stuff up by incorrectly excluding columns from the WATCH.
In the theory, the same as what I thought. With a PK defined, other information for uniquely identifying the record should not be needed.
In testing so far, the only change that has resulted in the PK column included in the generated update where clause, has been to remove READONLY in the Dct for that column and replace it with WATCH. Adding or removing the PK column to the browse additional sort did not make a difference.
That is really interesting. I see a similar behavior with the MSSQL driver but my PK is also included along with the other sort columns from the index.
UPDATE DBO.BidItemStatus SET DESCRIPTION = 'Bid Item Complete D', CHGDATETIME = '2024-04-23 11:04:39.4800000'
WHERE BISUNIQUE = 6 AND COMPANYUNIQUE = 1
AND DESCRIPTION = 'Bid Item Complete' AND ICONINDEX = 4 AND CHGDATETIME = '2012-01-19 10:43:06.0400000'
In this case BISUnique is my primary key column.
Also, I am not appending the PK column to the sort order because the index used by the browse is an unique index, too.
I think the other columns are included as part of Clarions concurrency check. If the update fails you get the ‘Changed by another Station’ message.
I agree that READONLY does appear to alter the behaviour in a bad way.
I Wasn’t aware of WATCH, that would seem to be the thing to use.
MSSQL driver without READONLY on the PK column in the dictionary.
MSSQL driver with READONLY on the PK column in the dictionary.
ODBC driver without READONLY on the PK column in the dictionary.
ODBC driver with READONLY on the PK column in the dictionary.
All of these running against a MSSQL server/database.
The results were the same regarding the update statement. The PK column was included along with all the other columns in the record as part of the WHERE statement.
I have to really dig around to find a VM with PostgreSQL installed.
Very surprised, as that is not what I am seeing in my testing with PG and I can’t think of anything else right now that might logically explain the difference.
My understanding of the WATCH flag is that if you use it on any field in the file or view, then only the field or fields with the WATCH on will be part of the concurrency check. Anything without will be ignored.
That means that if you are using | WATCH on any column, then you need to need to make sure you put it on your key field too, along with the READONLY.
I do suspect that the driver is not doing exactly what it should. That update statement really needs to be:
update the record where and ;
and I think the READONLY tag is causing the key field to be be omitted when it is not also a WATCH field (when you have anything tagged as a WATCH field).
I am testing right now, so this will be easy to check. I had always thought of WATCH & READONLY as one or the other.
Perhaps this will explain the difference in what I see vs what Rick is reporting. My habit has been a liberal use of WATCH instead of relying on the default - all fields.
Confirmed now in my testing.
When WATCH is used, the where clause in the generated update statement looks to be constructed using field order within the table for those fields having the WATCH attribute. In addition, this seems to be independent of key declarations in the Dct. I question whether this is always a safe implementation.
So it is an issue with WATCH and not with READONLY, right? I think it worths updating your PTSS report with that information, and perhaps your first message in this thread.
WATCH switch option used incorrectly can lead to massive overwritten of data as noted, and I think documentation of that option is not very clear to help preventing that.
I reported those issues on PTSS 42715 on 2018-08-25, but the report is still in the initial state “submitted”.
The driver currently checks to see how many records were updated when it does an PUT or DELETE. If it finds that 0 records were updated it gives you the “record changed by another station” error, which is the optimistic concurrency check. A put or delete should always only affect one row, so the driver could also issue an error if the number of rows affected was more than one. However, I think at that point it would have to be up to the user to correct the (immediate) problem because the change would already have been committed. But more importantly the developer would have to be alerted since it is something that requires a program change. It’s like an ASSERT, a put/delete should change only one row, if it doesn’t, something is wrong.