Preparing the UPDATE or INSERT Statement for an Array
Go Up to Writing Data to an Array
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 new row or updating the row(s) containing the array column(s) of interest. For example, the following statement is for updating the array column namedQUART_HEAD_CNT
in the specified row of the table,PROJ_DEPT_BUDGET
. The department number and quarterly headcounts are assumed to be supplied at run time:char *upd_str = "UPDATE PROJ_DEPT_BUDGET SET QUART_HEAD_CNT = ? WHERE YEAR = 1994 AND PROJ_ID = "MKTPR" AND DEPT_NO = ?";
As an example of an
INSERT
statement, the following is for inserting a new row into thePROJ_DEPT_BUDGET
table, with column data supplied at run time:char *upd_str = "INSERT INTO PROJ_DEPT_BUDGET (YEAR, PROJ_ID, DEPT_NO, QUART_HEAD_CNT) VALUES (?, ?, ?, ?)";
The remaining steps refer only to
UPDATE
statements, but the actions apply toINSERT
statements as well. - 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 the
XSQLDA
toSQLDA_CURRENT_VERSION
, and set the Sqln 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 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 array (and Blob) columns is different from other types of columns, so the
XSQLVAR
fields must be set differently. For non-array (and non-Blob) columns, input parameter data comes from the space pointed to by Sqldata. For array columns, set the type toSQL_ARRAY
(orSQL_ARRAY
+ 1 if the array column is allowed to beNULL
). The application must store space for the internal array identifier, not the array data, in the Sqldata space. See the following sections to create or modify an array, store its array ID in the Sqldata space, and then associate the actual array data with the column.The following code example illustrates the assignments for one
TEXT
column and one array column, where the column types are known at compile time.#define NUMLEN 4 char dept_no[NUMLEN + 1]; ISC_QUAD array_id; in_sqlda->sqlvar[0].sqldata = &array_id; in_sqlda->sqlvar[0].sqltype = SQL_ARRAY + 1; in_sqlda->sqlvar[0].sqllen = sizeof(ISC_QUAD); in_sqlda->sqlvar[1].sqldata = dept_no; in_sqlda->sqlvar[1].sqltype = SQL_TEXT; in_sqlda->sqlvar[1].sqllen = 4;
The
dept_no
variable should be assigned a value at run time (unless the value is known at compile time). Thearray_id
variable should be set to refer to the newly created array, as described in the following sections.
For examples of handling data whose types are not known until run time, see Working with Dynamic SQL.