Reading Data from a Blob

There are six steps required for reading data from an existing Blob:

1. Create a SELECT statement query that specifies selection of the Blob column (and any other columns desired) in the rows of interest.]]
2. Prepare an output XSQLDA structure to hold the column data for each row that is fetched.
3. Prepare the SELECT statement for execution.
4. Execute the statement.
5. Fetch the selected rows one by one.
6. Read and processing the Blob data from each row.

Creating the SELECT Statement

Elicit a statement string from the user or create one that consists of the SQL query that will select rows containing the Blob data of interest. For example, the following creates a SQL query statement string that selects three columns from various rows in the PROJECT table:

char *str =

"SELECT PROJ_NAME, PROJ_DESC, PRODUCT FROM PROJECT WHERE \

PRODUCT IN ('software', 'hardware', 'other') ORDER BY PROJ_NAME";

Preparing the Output XSQLDA

Most queries return one or more rows of data, referred to as a select-list. An output XSQLDA must be created to store the column data for each row that is fetched. For a Blob column, the column data is an internal Blob identifier (Blob ID) that is needed to access the actual data. To prepare the XSQLDA, follow these steps:

1. Declare a variable to hold the XSQLDA. For example, the following declaration creates an XSQLDA called out_sqlda:

XSQLDA *out_sqlda;

2. Allocate memory for the XSQLDA using the XSQLDA_LENGTH macro. The XSQLDA must contain one XSQLVAR substructure for each column to be fetched. The following statement allocates storage for an output XSQLDA (out_sqlda) with three XSQLVAR substructures:

   out_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(3));

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

   out_sqlda->version = SQLDA_CURRENT_VERSION;

   out_sqlda->sqln = 3;

Preparing the SELECT Statement for Execution

After an XSQLDA is created for holding the column data for each selected row, the query statement string can be prepared for execution. Follow these steps:

1. Declare and initialize a SQL statement handle, then allocate it with isc_dsql_allocate_statement():

isc_stmt_handle stmt; /* Declare a statement handle. */

stmt = NULL; /* Set handle to NULL before allocation. */

isc_dsql_allocate_statement(status_vector, &db_handle, &stmt);

2. Ready the statement string for execution with isc_dsql_prepare(). This checks the string (str) for syntax errors, parses it into a format that can be efficiently executed, and sets the statement handle (stmt) to refer to this parsed format. The statement handle is used in a later call to isc_dsql_execute().

   If isc_dsql_prepare() is passed a pointer to the output XSQLDA, as in the following example, it will fill in most fields of the XSQLDA and all its XSQLVAR substructures with information such as the datatype, length, and name of the corresponding columns in the statement.

   A sample call to isc_dsql_prepare() is:

   isc_dsql_prepare(status_vector,

   &trans, /* Set by previous isc_start_transaction() call. */

   &stmt, /* Statement handle set by this function call. */

   0, /* Specifies statement string is null-terminated. */

   str, /* Statement string. */

   1, /* da_version number. */

   out_sqlda); /* XSQLDA for storing column data. */

3. Set up an XSQLVAR structure for each column. Setting up an XSQLVAR structure involves the following steps:
   For columns whose types are known at compile time
   a. Specify the column’s datatype (if it was not set by isc_dsql_prepare(), as previously described).
   b. Point the sqldata field of the XSQLVAR to an appropriate local variable.
   
   For columns whose types are not known until run time
   c. Coerce the item’s datatype (optional), for example, from SQL_VARYING to :SQL_TEXT.
   d. Dynamically allocate local storage for the data pointed to by the sqldata field of the XSQLVAR.
   
   For both
   e. Specify the number of bytes of data to be retrieved into sqldata.
   f. Provide a NULL value indicator for the parameter.
   
   Data retrieval for Blob (and array) columns is different from other types of columns, so the XSQLVAR fields must be set differently. For non-Blob (and nonarray)columns, isc_dsql_prepare() sets each XSQLVAR sqltype field to the appropriate field type, and the data retrieved when a select-list row’s data is fetched is placed into the sqldata space allocated for the column. For Blob columns, the type must be set to SQL_Blob (or SQL_Blob + 1 if a NULL indicator is desired). InterBase stores the internal Blob identifier (Blob ID), not the Blob data, in the sqldata space when a row’s data is fetched, so you must point sqldata to an area the size of a Blob ID. To see how to retrieve the actual Blob data once you have a Blob ID, see Reading Data from a Blob. The following code example illustrates the assignments for Blob and non-Blob columns whose 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 20

   #define TYPELEN 12

   ISC_QUAD blob_id;

   char proj_name[PROJLEN + 1];

   char prod_type[TYPELEN + 1];

   short flag0, flag1, flag2;

   out_sqlda->sqlvar[0].sqldata = proj_name;

   out_sqlda->sqlvar[0].sqltype = SQL_TEXT + 1;

   out_sqlda->sqlvar[0].sqllen = PROJLEN;

   out_sqlda->sqlvar[0].sqlind = &flag0;

   out_sqlda->sqlvar[1].sqldata = (char *) &blob_id;

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

   out_sqlda->sqlvar[1].sqllen = sizeof(ISC_QUAD);

   out_sqlda->sqlvar[1].sqlind = &flag1;

   out_sqlda->sqlvar[2].sqldata = prod_type;

   out_sqlda->sqlvar[2].sqltype = SQL_TEXT + 1;

   out_sqlda->sqlvar[2].sqllen = TYPELEN;

   out_sqlda->sqlvar[2].sqlind = &flag2;

Executing the Statement

Once the query statement string is prepared, it can be executed:

isc_dsql_execute(status_vector,

&trans, /* set by previous isc_start_transaction() call */

&stmt, /* allocated above by isc_dsql_allocate_statement() */

1, /* da_version number */

NULL); /* NULL since stmt doesn't have input values */

This statement creates a select list, the rows returned by execution of the statement.

Fetching Selected Rows

A looping construct is used to fetch (into the output XSQLDA) the column data for a single row at a time from the select-list and to process each row before the next row is fetched. Each execution of isc_dsql_fetch() fetches the column data into the corresponding XSQLVAR substructures of out_sqlda. For the Blob column, the Blob ID, not the actual Blob data, is fetched.

ISC_STATUS fetch_stat;

long SQLCODE;

. . .

while ((fetch_stat = isc_dsql_fetch(status_vector, &stmt, 1, out_sqlda))

== 0)

{

proj_name[PROJLEN] = '\0';

prod_type[TYPELEN] = '\0';

printf("\nPROJECT: %–20s TYPE: %–15s\n\n", proj_name, prod_type);

/* Read and process the Blob data (see next section) */

}

if (fetch_stat != 100L) {

/* isc_dsql_fetch returns 100 if no more rows remain to be retrieved */

SQLCODE = isc_sqlcode(status_vector);

isc_print_sqlerror(SQLCODE, status_vector);

return(1);

}

Reading and Processing the Blob Data

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. Create a buffer for holding each Blob segment as it is read. Its size should be the maximum size segment your program expects to be read from the Blob.

   char blob_segment[80];

3. Declare an unsigned short variable into which InterBase will store the actual length of each segment read:

   unsigned short actual_seg_len;

4. Open the Blob with the fetched blob_id:

   isc_open_blob2(status_vector, &db_handle, &trans,

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

   &blob_id, /* Blob ID put into out_sqlda by isc_dsql_fetch() */

   0, /* BPB length = 0; no filter will be used */

   NULL ); /* NULL BPB, since no filter will be used */

5. Read all the Blob data by calling isc_get_segment() repeatedly to get each Blob segment and its length. Process each segment read. In the following example, “processing” consists of printing each Blob as it is read:

   blob_stat = isc_get_segment(status_vector,

   &blob_handle, /* set by isc_open_blob2()*/

   &actual_seg_len, /* length of segment read */

   sizeof(blob_segment), /* length of segment buffer */

   blob_segment); /* segment buffer */

   while (blob_stat == 0 || status_vector[1] == isc_segment) {

   /* isc_get_segment returns 0 if a segment was successfully read. */

   * status_vector[1] is set to isc_segment if only part of a */

   * segment was read due to the buffer (blob_segment) not being */

   * large enough. In that case, the following calls to */

   * isc_get_segment() read the rest of the buffer. */

   printf("%*.*s", actual_seg_len, actual_seg_len, blob_segment);

   blob_stat = isc_get_segment(status_vector, &blob_handle,

   &actual_seg_len, sizeof(blob_segment), blob_segment);

   printf("\n");

   };

   printf("\n");

6. Close the Blob:

   isc_close_blob(status_vector, &blob_handle);

Advance to the Next Topic

• Writing Data to a Blob