Preparing the SELECT Statement to Read Data from a Blob for Execution

Go Up to Reading Data from a Blob

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:

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);

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 data type, 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. */
```
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 data type of a column (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 data type of the item (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 non-array) columns, isc_dsql_prepare() sets each XSQLVAR sqltype field to the appropriate field type, and the data retrieved when a data of a select-list row 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 data of a row 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;
```

Advance To:

Executing the Statement to Read Data from a Blob