Preparing the UPDATE or INSERT Statement for a Blob

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

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.
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;
```
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));
```
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;
```
Set up the XSQLVAR structure in the XSQLDA for each parameter to be passed. Setting up an XSQLVAR structure involves the following steps:

Specify the item’s data type
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
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
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.

Advance To:

Creating a New Blob and Storing Data