Updating Blob Data

Go Up to Accessing Blob Data with SQL

You cannot update a Blob directly. You must create a new Blob, read the old Blob data into a buffer where you can edit or modify it, then write the modified data to the new Blob.

Create a new Blob by following these steps:

Declare a Blob insert cursor:

EXEC SQL
DECLARE BC CURSOR FOR INSERT BLOB GUIDEBOOK INTO TOURISM;

Open the Blob insert cursor and specify the host variable in which to store the Blob ID:
```
EXEC SQL
OPEN BC INTO :blob_id;
```
Store the old Blob segment data in the segment buffer blob_segment_buf, calculate the length of the segment data, perform any modifications to the data, and use an INSERT CURSOR statement to write the segment:
```
/* Programmatically read the first/next segment of the old Blob
* segment data into blob_segment_buf; */
EXEC SQL
INSERT CURSOR BC VALUES (:blob_segment_buf:blob_segment_len);
```
Repeat these steps in a loop until you have written all the Blob segments.
Close the Blob insert cursor:
```
EXEC SQL
CLOSE BC;
```
When you have completed the creation of the new Blob, issue an UPDATE statement to replace the old Blob in the table with the new one, as in the following example:
```
EXEC SQL UPDATE TOURISM
SET
GUIDEBOOK = :blob_id;
WHERE CURRENT OF TC;
```
Note:
The TC table cursor points to a target row established by declaring the cursor and then fetching the row to update.

To modify a Blob text using this technique, you might read an existing Blob field into a host-language buffer, modify the data, then write the modified buffer over the existing field data with an UPDATE statement.

Advance To:

Deleting Blob Data