Hi,

I want to Empty a very large table and DELETE FROM xx takes very long. Its much faster to Drop the table and recreate it. But if I execute these queries after each other:
 CREATE TABLE atable_tmp (LIKE atable)
 DROP TABLE atable;
 RENAME TABLE atable_tmp atable;

I get an error saying:
ElevateDB Error #600 File manager error (Cannot rename the file E:\ElevateDB DataFiles\Data2Ba\artikelen_tmp.EDBTbl to E:\ElevateDB DataFiles\Data2Ba\artikelen.EDBTbl (OS Error: Het systeem kan het opgegeven bestand niet vinden.
))

The OS Error is saying in Dutch that the system cannot find the file specified!

Kick,

> I get an error saying:
> ElevateDB Error #600 File manager error (Cannot rename the file E:\ElevateDB DataFiles\Data2Ba\artikelen_tmp.EDBTbl to E:\ElevateDB DataFiles\Data2Ba\artikelen.EDBTbl (OS Error: Het systeem kan het opgegeven bestand niet vinden.
> ))

I tried that here and got the same result. Obviously the table is
physically stored on disk only after entering at least one record. If
you enter a record, the renaming will work.

Tim has to say if it's a bug.

Uli

Kick


Uli's not quite right. The physical table should be created if you just open it. eg use a select statement. However, in my opinion it is a bug. You shouldn't have to have the physical files to rename a table.

You should be able to do

CREATE TABLE atable_tmp (LIKE atable)
 DROP TABLE atable;
 SELECT * FROM atable_tmp;
 RENAME TABLE atable_tmp atable;

haven't tested it though.

One thing I found made a profound difference when clearing a table was to close down any triggers associated with it.

Finally, if you don't mind taking a risk, and are prepared to use Delphi you could use this

function UnsafeTableClear(const TableName, dbPath: string): boolean;
begin
Result := True;
if FileExists(dbPath + TableName + Engine.TableExtension) then Result := Result and DeleteFile(dbPath + TableName + Engine.TableExtension);
if FileExists(dbPath + TableName + Engine.TableIndexExtension) then Result := Result and DeleteFile(dbPath + TableName + Engine.TableIndexExtension);
if FileExists(dbPath + TableName + Engine.TableBlobExtension) then Result := Result and DeleteFile(dbPath + TableName + Engine.TableBlobExtension);
end;

It just ignores the engine and zaps the files off disk. ElevateDB with then re-create them when you go to use them. What it doesn't do is reset any autoincs and it certainly doesn't honour any triggers.

Roy Lambert

Roy,

Thanks for input. I will try both approaches, to see what works best.

Kick


Just remember the zap the disk files comes without a warranty and with a lot of health warnings

Roy Lambert

Kick,

<< I want to Empty a very large table and DELETE FROM xx takes very long.
Its much faster to Drop the table and recreate it. But if I execute these
queries after each other:
 CREATE TABLE atable_tmp (LIKE atable)
 DROP TABLE atable;
 RENAME TABLE atable_tmp atable;

I get an error saying:
ElevateDB Error #600 File manager error (Cannot rename the file
E:\ElevateDB DataFiles\Data2Ba\artikelen_tmp.EDBTbl to E:\ElevateDB
DataFiles\Data2Ba\artikelen.EDBTbl (OS Error: Het systeem kan het opgegeven
bestand niet vinden.
))

The OS Error is saying in Dutch that the system cannot find the file
specified! >>

This is a bug in the RENAME TABLE due to the fact that the table files
weren't created yet.  I'll have a fix for this in the next build.

--
Tim Young
Elevate Software
www.elevatesoft.com