Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Records missing in browse on proDataSet when primary key changed

Query Result Set seemingly truncated

Fewer rows in browse than expected

Record appearing out of order in implied sort

Off-End event firing multiple times

Records in local temp-table not shown in browse

FACT(s) (Environment):

Filter Data-Source on field other than primary key
Using batching to fetch data into the ProDataSet
All Supported Operating Systems
OpenEdge 10.x
OpenEdge Category: ProDataSet

CHANGE:

Changed primary key of record in result set

CAUSE:

In the following two scenarios, and possibly more, Progress will sort the data-source result set by ROWID: - When filtering a data-source using an equality match on an index. - When the sort specified in the data-source query does not match any available index on the client. In these scenarios, unless the client query is sorted by ROWID, it may appear that any number of records have gone missing from the result set. This sort of thing could go hidden in many situations when the ROWID sort coincidentally matches the primary key sort, that is if the client query is sorted using the primary key. The catalyst, in this instance, is typically that a primary key value has changed in a row that formerly would have appeared in an early batch in the result-set. The following two examples demonstrate the sequence of events and their consequences. Both examples feature a ProDataSet built against a customer table. In both cases customer 11 has been reassigned to customer 5000: Example #1 The filter on the data-source buffer uses an equality match on the salesrep index and no sort is specified. The default sort, in the data-source query, is ROWID. The modified customer record, whose ROWID value didn't change along with the key value, is fetched in the first batch of records and returned to the client. Meantime the client query, which is using a default sort on customer number, logically shows the modified customer record at the end of its batch of records and when the OFF-END event fires signifying the need to fetch more records from the data-source, all records returned have a customer number which is lower than the highest one in the current query result set. The query therefore ignores the newly acquired results since they are not higher than the highest record already available. Example #2 The filter on the data-source buffer uses an equality match on the salesrep index and a sort is specified on a field other than the primary index, like name, and there is no index in the client temp-table to support the sort. Again, the default sort in the data-source query ends up being on ROWID while the client sort is on the name field. The records show up on the client with the reassigned record showing in the last position and subsequently no records show up beyond the first batch in the browse. This occurs because when the records get added to the temp-table they have to be added to the end of an index so that, after the addition of them, the still-running client query will pick them up. If there is no index they get added in random order and may or may not get added to the query's result list. In both of the above examples the records are in the client temp-table and, if the query is forced to re-open, they will be shown but until then they don't exist to the client query. Also in both of the above examples, when the initial OFF-END event fires after the first batch was received, the query thinks no records were returned and keeps firing the OFF-END event until the data-source reaches the end of the available data (based on the filter settings).

FIX:

- In the case of Example #1 the fix is to specify a sort on the data-source query which will bring the records to the client in the order they are shown in the client query. e.g. sort the data-source query by customer number. - In the case of Example #2 the fix is to define an index in the client side temp-table to support the sort. e.g. add an index on the name field.