Icon View Incident Report

Serious Serious
Reported By: David Strange
Reported On: 5/31/2004
For: Version 4.07 Build 1
# 1741 Renaming of Tables Resulting in Corruption in Internal File System and Table Not Found Errors

The following query worked like a dream with 4.05. It may also work with 4.07, but I can't get it to. I set up a new computer on the
weekend and transferred all my stuff across, and while at it I downloaded the latest DBISAM and Delphi 7 update.


The error I get is "DBISAM Engine Error # 11010 Table 'MemHoles' does not exist". Hmmm, seems to me it is being created in the query. I checked things out by commenting out the alter table bit under MemHoles and then I get a field missing error.

I don't seem to get an error with the almost identical MemParts query.

 Q := TDBISAMQuery.Create(nil);
 try
   with Q do
   begin
     DatabaseName := 'clink';
     SQL.Clear;

     // main table
     SQL.Add('select * ');
     SQL.Add('into "\memory\MemCabinet"');
     SQL.Add('from CabinetDB');
     if (NewRecord = true) then
       SQL.Add('where 1=2;')
     else
       SQL.Add('where CabinetID = ' +
QuotedStr(GUIDToString(CabinetModelRecNum)) + ';');

     // parts table
     // read in parts
     SQL.Add('select * ');
     SQL.Add('into "\memory\MemParts"');
     SQL.Add('from CabinetPartsDB');
     if (NewRecord = true) then
       SQL.Add('where 1=2')
     else
       SQL.Add('where CabinetNum = ' +
QuotedStr(GUIDToString(CabinetModelRecNum)));
     SQL.Add('order by name;');
     // add new field
     SQL.Add('alter table "\memory\MemParts"');
     SQL.Add('add column Used boolean Default ''True'';');
     // add index
     SQL.Add('create index idxName');
     SQL.Add('on "\memory\MemParts" (Name);');

     // holes table
     // read in holes
     SQL.Add('select * ');
     SQL.Add('into "\memory\MemHoles"');
     SQL.Add('from CabinetHolesDB');
     if (NewRecord = true) then
       SQL.Add('where 1=2')
     else
       SQL.Add('where CabinetNum = ' +
QuotedStr(GUIDToString(CabinetModelRecNum)));
     SQL.Add('order by PartNum, HoleName;');
     // add new field
     SQL.Add('alter table "\memory\MemHoles"');
     SQL.Add('add column Used boolean Default ''True'';');
     // add index
     SQL.Add('create index idxName');
     SQL.Add('on "\memory\MemHoles" (PartNum, HoleName);');
     SQL.Add('create index idxHoleID');
     SQL.Add('on "\memory\MemHoles" (HoleID);');

     // run query
     ExecSQL;
     Close;
   end;
 finally
   Q.Free;
 end;



Comments Comments
The problem was caused by the internal renaming going on during the ALTER TABLE operation on one of the in-memory tables. The renaming operation was corrupting the internal file system and resulting in the file not being found. In 4.07 the internal file system was converted to using binary searches and sorted lists for maintaining the file pointers, and the rename operation cause the list to become unsorted.


Resolution Resolution
Fixed Problem on 6/1/2004 in version 4.08 build 1
Image