Reading/Writing BLOB fields

In IB the following commands are allowed for BLOB fields: SELECT, UPDATE, INSERT and DELETE. If you'd like to know the BLOB identifier you can execute a query which gives the BLOB field (Description) as part of the query:
 
SELECT Name, Description
      FROM Products;
 
The commands INSERT and  UPDATE are not usable directly to insert or modify the BLOB data. Embedded SQL is used to manipulate Blob data in these ways. To insert or update Blob data, we must declare insert or read Blob cursors. Blob cursors can only be associated with a single BLOB column. Several host variables are declared to store the BLOB ID, the BLOB segment data, and the length of the segment (CR_ --> for identifying CursoR).
 
EXEC SQL
      BEGIN DECLARE SECTION;
      BASED ON Products.Description CRblobID;
      BASED ON Products.Description.SEGMENT CRBlobSegmentBuffer;
      unsigned short CRBlobSegmentLength;

EXEC SQL
      END DECLARE SECTION;

The following step is declaring and opening a BLOB insert cursor to be able to insert the BLOB data. The BLOB ID is stored in the host variable CRblobID.

EXEC SQL
      DECLARE BlobCursor FOR INSERT BLOB ProductDescription INTO Products;

EXEC SQL
      OPEN BlobCursor INTO :CRblobID;
 
The next INSERT CURSOR statement is used to insert in segments the BLOB data (stored in the buffer called CRblobID_buf) into the BLOB insert cursor.
This commands should be repeated until all segments will be written into the table. For each segment of data stored in the segment buffer you have to calculate in your program the length of the segment (CRBlobSegmentLength).
 
EXEC SQL
      INSERT CURSOR BlobCursor
      VALUES(:CRblobID :CRBlobSegmentBuffer);
 
After succesful written of all segments you can close the BLOB cursor.

EXEC SQL
      CLOSE BlobCursor;

We can now use the INSERT statement to insert a new row into the Products table:

EXEC SQL
      INSERT INTO Products (Name, Description)
            VALUES (’Metall Case’, :CRblobID);

To update a BLOB is an other case. First we have to create a new BLOB then read the earlier data into buffer. When all data are in the buffer you can modify and then insert the changed data into this new BLOB.

In your program you read the old data segments into the CRBlobSegmentBuffer then calculate the CRBlobSegmentLength. You can modify this data segment as needed then you insert this changed segment into the cursor using the insert cursor command:


EXEC SQL
      INSERT CURSOR BlobCursor
            VALUES(:CRBlobSegmentBuffer, :CRBlobSegmentLength);

After you changed all and you witten all segments into the buffer you can close the BlobCursor as above.

EXEC SQL
      CLOSE BlobCursor;

The cursor is created with the changed BLOB data. Now you can update the BLOB by replacing the original BLOB data.

EXEC SQL
      UPDATE Products
            SET Description = :CRblobID;
      WHERE CURRENT OF TableCursor;

The TableCursor points to the target row in the Products table.

You can find more detailled on working with BLOB data in Data Definition Guide Chapter 4, Programmer's Guide Ch. 8 and in Developer’s Guide Ch. 6.

I hope this gives some support. If you don't have them I'll give where you can download from.

wbr Janos