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 or INSERT 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 named QUART_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 the PROJ_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 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:
1. Specify the item’s data type.
2. 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.
3. 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.
4. 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 to SQL_ARRAY (or SQL_ARRAY + 1 if the array column is allowed to be NULL). 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). The array_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.

Advance To:

Calling isc_array_put_slice2()