2007. január 4., csütörtök

Deleting all records in a table/deleting a table in a database without SQL


Problem/Question/Abstract:

How can we delete all the records in a table without using a SQL statement?
How can we delete a table in a database without using a SQL statement?

Answer:

In Delphi, all versions, we have a component called TTable. We can use a method of that component to delete all the records in a table. Note that we should have privilege on that database to delete records in that table.

We can use the following code to do that:

with Table1 do
begin
  Active := False;
  DatabaseName := 'dbname';
  TableName := 'tablename';
  EmptyTable;
end;

The important thing to note here is that we need to set the Table’s active property to false before calling the EmptyTable method.

If we try to empty the table when the table is open (i.e Active is True), then we will be getting an EDBEngineError exception like the following:

Table cannot be opened for exclusive use.

Also we can use another method of the TTable component to delete the table itself provided we have privileges on that database to do that.

The following code will do that:

with Table1 do
begin
  Active := False;
  DatabaseName := 'dbname';
  TableName := 'tablename';
  DeleteTable;
end;

Here also the table must be closed (setting the Active property to false) before calling the DeleteTable method. Otherwise the method will throw an exception.

If we perform this operation on an open table, you will be getting an EDatabaseError exception like the following:
Table1 : Cannot perform this operation on a open dataset.

It’s always easy to call a method of a component in Delphi; but we should remember some important things before we call that method.

Here with these two methods above, we need to keep in mind two things before calling them:

We should have enough privileges to do that operation on a database where the table resides.
We need to close the table by either calling the TTable’s close method or setting the Ttable’s active property to false.

Nincsenek megjegyzések:

Megjegyzés küldése