Writing Data to An Array

isc_array_put_slice2() is called to write data to an array or array slice. Use it to:

• include a new array in a row to be inserted into a table.
• Replace the current contents of an array column of a row with a new array.
• Update the array referenced by an array column of a row by modifying the array data or a slice of the data.

The entry in an array column of a row does not actually contain array data. Rather, it has an array ID referring to the data, which is stored elsewhere. So, to set or modify an array column, you need to set or change the array ID stored in it. If an array column contains an array ID, and you modify the column to refer to a different array (or to contain NULL), the array referenced by the previously stored array ID will be deleted during the next garbage collection.

The following steps are required to insert, replace, or update array data:

1. Prepare an array descriptor with information describing the array (or slice) to be written to.
2. Prepare an array buffer with the data to be written.
3. Prepare an appropriate DSQL statement. This will be an INSERT statement if you are inserting a new row into a table, or an UPDATE statement for modifying an existing row.
4. Call isc_array_put_slice2() to create a new array (possibly copying an existing one), and to write the data from the array buffer into the array or array slice.
5. Associate the new array with an array column of the table row being modified or inserted by executing the UPDATE or INSERT statement. This sets the array column to contain the array ID of the new array.

Preparing the Array Buffer with Data

Create an array buffer to hold the data to be written to the array. Make it large enough to hold all the elements in the slice to be written (which could be the entire array). For example, the following declares an array buffer large enough to hold 4 long elements:

long hcnt[4];

1. Create a variable specifying the length of the array buffer:

short len;

len = sizeof(hcnt);

2. Fill the array buffer with the data to be written.

If you are creating a new array, then fill the buffer with data. For example,

hcnt[0] = 4;

hcnt[1] = 5;

hcnt[2] = 6;

hcnt[3] = 6;

To modify existing array data instead of creating a new one, then perform all the steps listed in Reading Data from an Arrayto read the existing array data into the array buffer. Modify the data in the buffer.

Preparing the UPDATE or INSERT Statement

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

1. 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.

2. 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;

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

4. 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;

5. Set up the XSQLVAR structure in the XSQLDA for each parameter to be passed. Setting up an XSQLVAR structure involves the following steps:

a. Specify the item’s datatype.

b. 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.

c. 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.

d. 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.

Calling isc_array_put_slice2()

The following steps are required to store the data into an array or array slice:

1. Declare an array ID:

ISC_QUAD array_id; /* Declare an array ID. */

2. Initialize the array ID. If you are creating a new array to be inserted into a new row, or to replace an existing array, then simply initialize the array ID to NULL:

array_id = NULL;/* Set handle to NULL before using it */

If you are modifying an existing array, then follow the steps listed under Reading Data from an Array to read the existing array ID into array_id.

3. Call isc_array_put_slice2(). In your call you pass the array ID (either the array ID of an existing array, or NULL for a new array) in the array_id variable. You also pass the buffer of data to be written and a descriptor specifying the array slice to which the data belongs. When isc_array_put_slice2() is called with an array ID of an existing array, it creates a new array with the same characteristics as the specified array, and copies the existing array data to the new array. Then isc_array_put_slice2() writes the data from the array buffer to the new array (or slice of the array), per the bounds specified in the array descriptor, and returns in the same array_id variable the array ID of the new array. When isc_array_put_slice2() is called with a NULL array ID, it creates a new empty array with characteristics as declared for the array column whose name and table name are specified in the array descriptor passed to isc_array_put_slice2(). It then writes the data from the array buffer to the new array (or slice of the array), and returns in the array_id variable the array ID of the new array.

   Note: In both cases, a new array is created, and its array ID is returned in the array_id variable. The array is temporary until an UPDATE or INSERT statement is executed to associate the array with a particular column of a particular row.

   You can make a single call to isc_array_put_slice2() to write all the data to the array. Or, you may call isc_array_put_slice2() multiple times to store data into various slices of the array. In this case, each call to isc_array_put_slice2() after the first call should pass the array ID of the temporary array. When isc_array_put_slice2() is called with the array ID of a temporary array, it copies the specified data to the specified slice of the temporary array, but does not create a new array. The following is a sample call to isc_array_put_slice2(): isc_array_put_slice2(status_vector, &db_handle, &trans, &array_id, /* array ID (NULL, or existing array's array ID) */ &desc, /* array descriptor describing where to write data */ hcnt, /* array buffer containing data to write to array */ &len); /* length of array buffer */ This call creates a new array, copies the data in hcnt to the new array (or slice of the array), assigns the array an array ID, and sets array_id to point to the array ID.

Important: array_id should be the variable pointed to by the Sqldata field of the UPDATE (or INSERT) statement input parameter that specifies the array column to be updated. Thus, when the INSERT or UPDATE statement is executed, this new array’s array ID will be used to set or update the array column to refer to the new array.

Associating the New Array with the Array Column

Execute the UPDATE statement to associate the new array with the array column in the row selected by the statement:

isc_dsql_execute_immediate(status_vector, &db_handle, &trans,

0, /* indicates string to execute is null-terminated */

upd_str, /* UPDATE statement string to be executed */

1, /* da_version number */

in_sqlda); /* XSQLDA supplying parameters to UPDATE statement

• /

This sets the array column in the row specified in the UPDATE statement to contain the array ID of the new array. The array ID comes from the array_id variable pointed to by the in_sqlda parameter corresponding to the array column.

If the array column in the specified row contains the array ID of a different array before the UPDATE statement is executed, then the column is modified to contain the new array ID, and the array referenced by the previously stored array ID will be deleted during the next garbage collection.

Advance to Next Topic

• Deleting an Array