Selecting Blob Data

Go Up to Accessing Blob Data with SQL

The following example program selects Blob data from the GUIDEBOOK column of the TOURISM table:

Declare host-language variables to store the Blob ID, the Blob segment data, and the length of the segment data:
```
EXEC SQL
BEGIN DECLARE SECTION;
BASED ON TOURISM.GUIDEBOOK blob_id;
BASED ON TOURISM.GUIDEBOOK.SEGMENT blob_segment_buf;
BASED ON TOURISM.STATE state;
unsigned short blob_seg_len;
EXEC SQL
END DECLARE SECTION;
```
The BASED ON … SEGMENT syntax declares a host-language variable, blob_segment_buf, that is large enough to hold a Blob segment during a FETCH operation. For more information about the BASED ON statement, see the Language Reference Guide.
Declare a table cursor to select the desired Blob column, in this case the GUIDEBOOK column:
```
EXEC SQL
DECLARE TC CURSOR FOR
SELECT STATE, GUIDEBOOK
FROM TOURISM
WHERE STATE = 'CA';
```
Declare a Blob read cursor. A Blob read cursor is a special cursor used for reading Blob segments:
```
EXEC SQL
DECLARE BC CURSOR FOR
READ Blob GUIDEBOOK
FROM TOURISM;
```
The segment length of the GUIDEBOOK Blob column is defined as 60, so Blob cursor, BC, reads a maximum of 60 bytes at a time. To override the segment length specified in the database schema for GUIDEBOOK, use the MAXIMUM_SEGMENT option. For example, the following code restricts each Blob read operation to a maximum of 40 bytes, and SQLCODE is set to 101 to indicate when only a portion of a segment has been read:
```
EXEC SQL
DECLARE BC CURSOR FOR
READ Blob GUIDEBOOK
FROM TOURISM
MAXIMUM_SEGMENT 40;
```
No matter what the segment length setting is, only one segment is read at a time.
Open the table cursor and fetch a row of data containing a Blob:
```
EXEC SQL
OPEN TC;
EXEC SQL
FETCH TC INTO :state, :blob_id;
```
The FETCH statement fetches the STATE and GUIDEBOOK columns into host variables state and blob_id, respectively.
Open the Blob read cursor using the Blob ID stored in the blob_id variable, and fetch the first segment of Blob data:
```
EXEC SQL
OPEN BC USING :blob_id;
EXEC SQL
FETCH BC INTO :blob_segment_buf:blob_seg_len;
```
When the FETCH operation completes, blob_segment_buf contains the first segment of the Blob, and blob_seg_len contains the length of the segment, which is the number of bytes copied into blob_segment_buf.
Fetch the remaining segments in a loop. SQLCODE should be checked each time a fetch is performed. An error code of 100 indicates that all of the Blob data has been fetched. An error code of 101 indicates that the segment contains additional data:
```
while (SQLCODE != 100 || SQLCODE == 101)
{
printf("%*.*s", blob_seg_len, blob_seg_len, blob_segment_buf);
EXEC SQL
FETCH BC INTO :blob_segment_buf:blob_seg_len;
}
```
InterBase produces an error code of 101 when the length of the segment buffer is less than the length of a particular segment.
For example, if the length of the segment buffer is 40 and the length of a particular segment is 60, the first FETCH produces an error code of 101 indicating that data remains in the segment. The second FETCH reads the remaining 20 bytes of data, and produces a SQLCODE of 0, indicating that the next segment is ready to be read, or 100 if this was the last segment in the Blob.
Close the Blob read cursor:
```
EXEC SQL
CLOSE BC;
```
Close the table cursor:
```
EXEC SQL
CLOSE TC;
```

Advance To:

Inserting Blob Data