find last failed

C

ceolivas

New Member
Hi there,

We are running an ERP application from Totvs - Datasul, in a table with about 1,000,000 records from time to time we experience a failure in an statement of find last record.

It returns the first record instead of the expected last record,
does anyone has experienced this behavior ?. :confused:

This module has about 10 users concurrently registering manufacturing records.

Our database is on 10.1A

Best Regards
 
You do realize that find last is probably not doing what you think it is. Since you can't put a BY clause on a find, the sort order is up for grabs. Hence, the FIRST or LAST record meeting a set of criteria is pretty arbitrary. You should be using FOR EACH or some other block oriented statement so that you can specify a BY clause and then LEAVE after the first record. Of course, it is possible that you shouldn't be doing that either since you shouldn't be treating one record in the set as "special".
 
Hi Thomas,

thanks for your reply, the operation this procedure is doing is to append a record into a shopfloor operations record. The soubroutine finds the last record in the table takes the manufacturing record number and then get's into a for each routine to add the the record, if in the meantime other user has added a new production records adds one to the number until is capable of creating the new record.

The issue here is that when I tried to replicate in the test environment doing a million find last operations it does not fails, but when I tried in the production environment it seem to loose track of the record id in the index or something like that.

Best Regards
 
"find last and add 1" as a technique has many shortfalls. It is especially prone to failure under heavy load with many concurrent users.

Your specific issue would be much more easily understood if you posted some sample code along with table and index definitions. Otherwise it is all just ephemera and penumbra.
 
In addition to shortfalls as Tom mentions, FIND LAST has no BY clause, so how do you expect it to give you a predictable result?

Do the FOR EACH ... BY to get the record you want and LEAVE. Highly repeatable!
 
FWIW, adding to Thomas's... you could also do:

Code: 
FOR LAST <table> NO-LOCK <where clause if needed> BY <table>.<increment field>:
END.

The last <table>.<increment field> will be available after the END. The ideal situation here would be that the increment counter field would be the primary part of a key.

I must say, though, that Tom B's point is well made... in a multi-user environment this logic will not always work unless you code to cover the contingencies of multiple users hitting the table at the same time returning the same value.

Sequence numbers in the DB were made for this (unless you cannot lose a value), or you could have a table with a record that has the counter where you can lock it and increment in teeny-tiny transaction. And there are no doubt other ways to deal with this.
 
You must log in or register to reply here.
Back
Top