Writing Data to a Blob

Before you can create a new Blob and write data to it, you must do at least one of the following:

• Include Blob data in a row to be inserted into a table.
• Replace the data referenced by a Blob column of a row.
• Update the data referenced by a Blob column of a row.

The entry in a Blob column of a row does not actually contain Blob data. Rather, it has a Blob ID referring to the data, which is stored elsewhere. So, to set or modify a Blob column, you need to set (or reset) the Blob ID stored in it. If a blob column contains a Blob ID, and you modify the column to refer to a different Blob (or to contain NULL), the Blob referenced by the previously stored Blob ID will be deleted during the next garbage collection.

These operations all require the following steps:

1. Prepare an appropriate DSQL statement. This will be an INSERT statement if you are inserting a new row into a table, or an UPDATE statement for modifying a row. Each of these statements will need a corresponding input XSQLDA structure for supplying parameter values to the statement at run time. The Blob ID of a new Blob will be one of the values passed.
2. Create a new Blob, and write data into it.
3. Associate the Blob ID of the new Blob with the Blob column of the table row by executing the UPDATE or INSERT statement.

Note that you cannot update Blob data directly. If you want to modify Blob data, you must:

4. Create a new Blob.
5. Read the old Blob data into a buffer where you can edit or modify it.
6. Write the modified data to the new Blob.
7. Prepare and execute an UPDATE statement that will modify the Blob column to contain the Blob ID of the new Blob, replacing the old Blob’s Blob ID.

The sections below describe the steps required to insert, replace, or update Blob data.

• Preparing the UPDATE or INSERT Statement
• Creating a New Blob and Storing Data
• Associating the New Blob with the Blob Column

Preparing the UPDATE or INSERT Statement

To prepare an UPDATE or INSERT statement for execution, follow these steps:

1. Elicit an UPDATE or INSERT statement string from the user or create one for inserting a row or updating the row containing the Blob column of interest. For example, the following statement is for updating the Blob column named PROJ_DESC in the row of the table, PROJECT, whose PROJ_ID field contains a value specified at run time:

char *upd_str = "UPDATE PROJECT SET PROJ_DESC = ? WHERE PROJ_ID = ?";";

As an example of an INSERT statement, the following inserts a new row containing values in four columns:

char *in_str = "INSERT INTO PROJECT (PROJ_NAME, PROJ_DESC, PRODUCT,

PROJ_ID) VALUES (?, ?, ?, ?)";

The remaining steps refer only to UPDATE statements, but the actions apply to INSERT statements as well.

2. Declare a variable to hold the input XSQLDA needed to supply parameter values to the UPDATE statement at run time. For example, the following declaration creates an XSQLDA called in_sqlda:

   XSQLDA *in_sqlda;

3. Allocate memory for the input XSQLDA using the XSQLDA_LENGTH macro. The XSQLDA must contain one XSQLVAR substructure for each parameter to be passed to the UPDATE statement. The following statement allocates storage for an input XSQLDA (in_sqlda) with two XSQLVAR substructures:

   in_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(2));

4. Set the version field of the XSQLDA to SQLDA_CURRENT_VERSION, and set the sqln field to indicate the number of XSQLVAR structures allocated:

   in_sqlda->version = SQLDA_CURRENT_VERSION;

   in_sqlda->sqln = 2;

5. Set up the XSQLVAR structure in the XSQLDA for each parameter to be passed. Setting up an XSQLVAR structure involves the following steps:
   1. Specify the item’s datatype
   2. For parameters whose types are known at compile time: Point the sqldata field of the XSQLVAR to an appropriate local variable that will contain the data to be passed
   3. For parameters whose types are not known until run time: Allocate local storage for the data pointed to by the sqldata field of the XSQLVAR
   4. Specify the number of bytes of data
   Data storage for Blob (and array) columns is different from other types of columns, so the XSQLVAR fields must be set differently. For non-Blob and non-array columns, input parameter data comes from the space pointed to by sqldata. For Blob columns, you must set the type to SQL_Blob (or SQL_Blob + 1 if you want a NULL indicator). Your application must store space for the internal Blob identifier, not the Blob data, in the sqldata space. For more information about creating a Blob,storing its ID in the sqldata space, and associating the Blob with a column, see Creating a New Blob and Storing Data. The following code example illustrates the assignments for one text column and one Blob column, where the column types are known at compile time. For examples of handling data types that are unknown until run time, see Working with Dynamic SQL.

   #define PROJLEN 5

   char proj_id[PROJLEN + 1];

   ISC_QUAD blob_id;

   in_sqlda->sqlvar[0].sqldata = (char *) &blob_id;

   in_sqlda->sqlvar[0].sqltype = SQL_Blob + 1;

   in_sqlda->sqlvar[0].sqllen = sizeof(ISC_QUAD);

   in_sqlda->sqlvar[1].sqldata = proj_id;

   in_sqlda->sqlvar[1].sqltype = SQL_TEXT;

   in_sqlda->sqlvar[1].sqllen = 5;

   The proj_id variable should be assigned a value at run time (unless the value is known at compile time). The blob_id variable should be set to refer to the newly created Blob, as described in the following sections. The proj_id variable should be assigned a value at run time (unless the value is known at compile time). The blob_id variable should be set to refer to the newly created Blob, as described in the following sections.

Creating a New Blob and Storing Data

To create a new Blob containing the data to be written:

1. Declare and initialize a Blob handle:

   isc_blob_handle blob_handle; /* Declare a Blob handle. */

   blob_handle = NULL;/* Set handle to NULL before using it */

2. Declare and initialize a Blob ID:

   ISC_QUAD blob_id; /* Declare a Blob ID. */

   blob_id = NULL; /* Set handle to NULL before using it */

3. Create a new Blob by calling isc_create_blob2():

   isc_create_blob2(status_vector, &db_handle, &trans,

   &blob_handle, /* set by this function to refer to the new Blob */

   &blob_id, /* Blob ID set by this function */

   0, /* Blob Parameter Buffer length = 0; no filter will be used */

   NULL); /* NULL Blob Parameter Buffer, since no filter will be used */

   This function creates a new Blob, opens it for write access, and sets blob_handle to point to the new Blob. isc_create_blob2() also assigns the Blob a Blob ID, and sets blob_id to point to the Blob ID. Note that blob_id is the variable pointed to by the sqldata field of the UPDATE statement input parameter that specifies the Blob column to be updated. Thus, when the UPDATE statement is executed, this new Blob will be used to update the Blob column.

4. Write all the data to be written to the Blob by making a series of calls to isc_put_segment(). The following example reads lines of data, and concatenates each to the Blob referenced by blob_handle. (get_line() reads the next line of data to be written.)

   char *line;

   unsigned short len;

   . . .

   line = get_line();

   while (line) {

   len = strlen(line);

   isc_put_segment(status_vector,

   &blob_handle, /* set by previous isc_create_blob2() */

   len, /* length of buffer containing data to write */

   line); /* buffer containing data to write into Blob */

   if (status_vector[0] == 1 && status_vector[1]) {

   isc_print_status(status_vector);

   return(1);

   };

   line = get_line();

   };

5. Close the Blob:

   isc_close_blob(status_vector, &blob_handle);

Associating the New Blob with the Blob Column

Execute the UPDATE statement to associate the new Blob with the Blob column in the row selected by the statement:

isc_dsql_execute_immediate(status_vector, &db_handle, &trans,

0, /* indicates string to execute is null-terminated */

upd_str, /* UPDATE statement string to be executed */

1, /* da_version number */

in_sqlda); /* XSQLDA supplying parameters to UPDATE statement */

Advance to Next Section

• Deleting a Blob