Program: Superbase Version: 2.0 Topic: Error deleting key, error reading block. Date: Jan 19, 1993. Summary : The solution to the error messages "Error deleting key - Error 100/101/102/103" or "Error Reading Block - Error 200/201" etc. is to reorganize the file. This technote is intended to explain what these errors are and to give some hints as to how to help avoid them. The "Error 100" message is caused by Superbase going to store a modified record and failing to find the original value of a changed index key at the bottom level of the Index Tree. "Error 101" is similar, except that having deleted the key at the bottom level of the index tree, Superbase failed to find the key at a higher level. The "Error 200/201" messages mean that an index entry is pointing to an "incorrect" place on the disk; incorrect can be not to a record with the same key, or not to a record, or even not even to the database at all. So, what can cause these errors? The reason is that the original key value can't be found or some other corruption has occurred. Sometimes this can be caused by closing Superbase down without securing the data. For example, a) misuse of STORE,1 command, b) an unexpected termination of Superbase (for example, a UAE) or Windows, c) a power failure which shuts the machine off, d) by merely switching the machine off when a session is completed instead of closing down properly. Another possible cause is a minor power-glitch which momentarily interferes with the writing of data to disk. In these sort of cases, the damage is liable to be more than an occasional "Error 200". Apart from a) and d) above, where the solution is obvious, there is little that can be done by the user. Also, the above do not account for all causes, and it is necessary to consider whether design/mis-design of the user database itself can contribute to the occurrence this kind of error. The answer is that it can! So, the rest of this technote discusses file design. First, in essence, Superbase keeps a memory copy of a record so that it knows when data has changed and when indexes need to be modified. Second, it is necessary to understand what 'Calculation count'' in the System Options dialog box (Options/System) means and when it is required to increase it. Assume that your database has field1, field2, and field3, and field4; where field3 is calculated from field1 and field2. As you enter your data, you are prompted for field1 and field2, field3 is calculated as you "pass through it" and you are prompted for field4. Now assume that you add field5 and change the calculation on field3 to involve field1 and field5. As you enter data now, you are prompted for field1 and field2, field3 is partially calculated as you pass through it (from field1), you are prompted for field4 and field5. At this point, the calculation for field3 is "incorrect", but when you go to save the record, Superbase automatically does a further calculation pass through the data and field3 is correctly calculated and the record is stored. So far so good. Now add a few more fields (6, 7 and 8) and make field5 a calculation dependent on field6 and field8. On entry, you enter 1 and 2, 3 is partially calculated from 1, 4 is entered, 5 is passed through, 6, 7 and 8 are entered. At this point, 3 is incorrect (based on 1 only), 5 is blank (based on 6 and 8 but not yet calculated). Now you save the record. Superbase does its automatic "calculation-pass". At this point the calculation for 3 is based on 1 and 5, but 5 is still uncalculated, then 5 is correctly calculated from 6 and 8 and the record is stored. To correctly calculate 3, another calculation pass is required. This is not automatic and has to be set by the operator using the "Calculation count" option on the System Options dialog box. So now assume that the calculation count has been set to 2 and see what happens as we save the record: On entry, you enter 1 and 2, 3 is partially calculated from 1, 4 is entered, 5 is passed through, 6, 7 and 8 are entered. At this point, 3 is incorrect (based on 1 only), 5 is blank (based on 6 and 8 but not yet calculated). Now you save the record. Superbase does its automatic calculation-pass. At this point the calculation for 3 is based on 1 and 5, but 5 is still uncalculated, then 5 is correctly calculated from 6 and 8. At this point the second calculation pass is invoked and 3 is correctly calculated from 1 and 5. Now the record is stored, and, it is correct. So, apart from the data being incorrect if the calculation count is too low, how can this affect an index? Consider what happened before the calculation count was increased and assume that field3 was an indexed field. On storing the record, field3 was incorrect and this value was stored in the index. Now go back into this record and edit it. (Assume that the change does NOT affect fields 1,6 or 8--so no change is made to any of the fields that contribute to field3). On saving the record, field3 is calculated from field1 and field5, but this time, unlike storing a new record, field5 is already correctly calculated (from when the record was originally stored) so the value for field3 is now as intended, but it is not what was originally stored. It is quite possible in this case that "Error 100" or "Error-200" can occur. 1) Re-order the fields This is the best solution. The kind of situation described usually arises when the file has been heavily modified from its original design and once the design-phase is completed, it is well worthwhile to re-order the fields by using a QUERY (or REPORT) to a new file, specifying the fields in a more logical order. (Note that a file created this way is only indexed on the first indexed field that you specify, and therefore you have to create the other indexes yourself). 2) Up the Calculation count Note that this inevitably imposes a time overhead on storing a record, and therefore, should be used where necessary and only as high as is needed. The third area which has to be considered is virtual indexes. A virtual field is a field whose data is not stored on disk, but is calculated when the record is displayed. There are two recommendations which should be implemented when a virtual field is used for an index. 1) The indexed virtual field should not be calculated from other virtual fields. 2) A virtual indexed field based on a cross file calculation may only be used if the other file is totally static and is never edited. The reason for the first recommendation is a very similar argument to that used above when discussing Calculation count. If a file consists of several fields of which field3 is a virtual calculation based on field1 and field2, field6 is a virtual calculation based on field4 and field5, and field 7 is a virtual calculation based on either or both of field3 and field6 and is indexed; then changing any of the fixed fields 1 and 2 changes field3 and changing fields 4 and 5 changes field6. In either or both cases, field7 is changed. It is possible now, for the original value (and thus the current index value) of field7 not to be known to Superbase. If so, then "Error 100/200" may occur on attempting to save the record. The reason for the second recommendation may be easier to explain using an example. The current file 'myfile' looks up a customer file on a code- number and transfers via a cross-file calculation the customer's name and initials to a virtual field to use as an index using a ternary calculation like: LOOKUP(cuscode.MYFILE,cuscode.CUSTOMER)?lastname.CUSTOMER+initia ls.CUSTOMER:"" If the customer file is edited to correct the customer's initials, so that, for instance, 'SMITH R.' became 'SMITH R.J.' in the customer file, now, on attempting to store the appropriate record in myfile, consider what happens: On selecting the record, the correct customer is located and the ternary cross-file is performed to give the answer 'SMITH R.J.". If you now attempt to store this record, Superbase looks for the index value 'SMITH R.J' to delete, and it will not be able to find it, because the value of the field was 'SMITH R.' last time the record was saved -- instant "Error 100". The transferred data must not be edited in the other file. It may be necessary to have cross-file calculations involved in fields used as indexes. The cross-file calculations themselves should always be real and not virtual.