Inserting Blob Data

Go Up to Accessing Blob Data with SQL

The following program inserts Blob data into the GUIDEBOOK column of the TOURISM table:

1. Declare host-language variables to store the Blob ID, 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 directive, see the Language Reference Guide.
2. Declare a Blob insert cursor:

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

3. Open the Blob insert cursor and specify the host variable in which to store the Blob ID:

   EXEC SQL
   OPEN BC INTO :blob_id;
   

4. Store the segment data in the segment buffer, blob_segment_buf, calculate the length of the segment data, and use an INSERT CURSOR statement to write the segment:

   sprintf(blob_segment_buf, 'Exploring Napa County back roads');
   blob_segment_len = strlen(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 Blob segments.
5. Close the Blob insert cursor:

   EXEC SQL
   CLOSE BC;
   

6. Use an INSERT statement to insert a new row containing the Blob into the TOURISM table:

   EXEC SQL
   INSERT INTO TOURISM (STATE,GUIDEBOOK) VALUES ('CA',:blob_id);
   

7. Commit the changes to the database:

   EXEC SQL
   COMMIT;
   

Advance To:

• Updating Blob Data