Deleting Blob Data
Go Up to Accessing Blob Data with SQL
There are two methods for deleting a Blob. The first is to delete the row containing the Blob. The second is to update the row and set the Blob column to NULL
or to the Blob ID of a different Blob (for example, the new Blob created to update the data of an existing Blob).
The following statement deletes current Blob data in the GUIDEBOOK
column of the TOURISM
table by setting it to NULL
:
EXEC SQL UPDATE TOURISM SET GUIDEBOOK = NULL; WHERE CURRENT OF TC;
Blob data is not immediately deleted when DELETE
is specified. The actual delete operation occurs when InterBase performs version cleanup. The following code fragment illustrates how to recover space after deleting a Blob:
EXEC SQL UPDATE TABLE SET Blob_COLUMN = NULL WHERE ROW = :myrow; EXEC SQL COMMIT; /* wait for all active transactions to finish */ /* force a sweep of the database */
When InterBase performs garbage collection on old versions of a record, it verifies whether or not recent versions of the record reference the Blob ID. If the record does not reference the Blob ID, InterBase cleans up the Blob.
The Blob garbage collection process is as follows: if a record contains a Blob ID, InterBase determines which type of Blob storage has been used. If the Blob is on a page, the line index indicator is released. If the Blob is on a page by itself, that page is marked as free in the page indicator. If the Blob is on a series of pages, InterBase reads the Blob index and frees all the pages. None of this requires retrieving the Blob itself.