Preparing the UPDATE or INSERT Statement for a Blob
Go Up to Writing Data to a Blob
To prepare an UPDATE
or INSERT
statement for execution, follow these steps:
- Elicit an
UPDATE
orINSERT
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 namedPROJ_DESC
in the row of the table,PROJECT
, whosePROJ_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:
The remaining steps refer only tochar *in_str = "INSERT INTO PROJECT (PROJ_NAME, PROJ_DESC, PRODUCT, PROJ_ID) VALUES (?, ?, ?, ?)";
UPDATE
statements, but the actions apply toINSERT
statements as well. - As an example of an
- Declare a variable to hold the input
XSQLDA
needed to supply parameter values to theUPDATE
statement at run time. For example, the following declaration creates anXSQLDA
calledin_sqlda
:XSQLDA *in_sqlda;
- Allocate memory for the input
XSQLDA
using theXSQLDA_LENGTH
macro. TheXSQLDA
must contain oneXSQLVAR
substructure for each parameter to be passed to theUPDATE
statement. The following statement allocates storage for an inputXSQLDA
(in_sqlda
) with twoXSQLVAR
substructures:in_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(2));
- Set the
version
field of theXSQLDA
toSQLDA_CURRENT_VERSION
, and set thesqln
field to indicate the number ofXSQLVAR
structures allocated:in_sqlda->version = SQLDA_CURRENT_VERSION; in_sqlda->sqln = 2;
- Set up the
XSQLVAR
structure in theXSQLDA
for each parameter to be passed. Setting up anXSQLVAR
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 theXSQLVAR
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 theXSQLVAR
- 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.