Accessing Array Data

InterBase supports the following operations on array data:

• Reading from an array or array slice.
• Writing to an array:

• Including a new array in a row to be inserted into a table.
• Replacing the array referenced by an array column of a row with a new array.
• Updating the array referenced by an array column of a row by modifying the array data or a slice of the data.

• Deleting an array.

Dynamic SQL (DSQL) API functions and the XSQLDA data structure are needed to execute SELECT, INSERT, and UPDATE statements required to select, insert, or update relevant array data. The following sections include descriptions of the DSQL programming methods required to execute the sample statements provided.

For more information about DSQL and the XSQLDA, see Working with Dynamic SQL.

Note: The following array operations are not supported:

• Referencing array dimensions dynamically in DSQL.
• Setting individual array elements to NULL.
• Using aggregate functions, such as MIN() and MAX(), with arrays.
• Referencing arrays in the GROUP BY clause of a SELECT.
• Creating views that select from array slices.

Reading Data from an Array

There are seven steps required for reading data from an array or slice of an array:

1. Create a SELECT statement that specifies selection of the array column (and any other columns desired) in the rows of interest.
2. Prepare an output XSQLDA structure to hold the column data for each row that is fetched.
3. Prepare the SELECT statement for execution.
4. Execute the statement.
5. Populate an array descriptor with information describing the array or array slice to be retrieved.
6. Fetch the selected rows one by one.
7. Read and process the array data from each row.

Creating the SELECT Statement

Elicit a statement string from the user or create one that consists of the SQL query that will select rows containing the array data of interest. In your query, specify the array column name and the names of any other columns containing data you are interested in. For example, the following creates a SQL query statement string that selects an array column named QUART_HEAD_CNT and another column named DEPT_NO from the table, PROJ_DEPT_BUDGET:

char *sel_str = "SELECT DEPT_NO, QUART_HEAD_CNT FROM

PROJ_DEPT_BUDGET

WHERE year = 2003 AND PROJ_ID = 'DATABASE'";

Preparing the Output XSQLDA

Most queries return one or more rows of data, referred to as a select-list. An output XSQLDA must be created to store the column data for each row that is fetched. For an array column, the column data is an internal array identifier (array ID) that is needed to access the actual data. To prepare the XSQLDA, follow these steps:

1. Declare a variable to hold the XSQLDA. For example, the following declaration creates an XSQLDA called out_sqlda:

XSQLDA *out_sqlda;

2. Allocate memory for the XSQLDA using the XSQLDA_LENGTH macro. The XSQLDA must contain one XSQLVAR substructure for each column to be fetched. The following statement allocates storage for an output XSQLDA (out_sqlda) with two XSQLVAR substructures:

out_sqlda = (XSQLDA *)malloc(XSQLDA_LENGTH(2));

3. Set the version field of the XSQLDA to SQLDA_CURRENT_VERSION, and set the sqln field of the XSQLDA to indicate the number of XSQLVAR substructures allocated:

out_sqlda->version = SQLDA_CURRENT_VERSION;

out_sqlda->sqln = 2;

Preparing the SELECT Statement for Execution

After an XSQLDA is created for holding the column data for each selected row, the query statement string can be prepared for execution. Follow these steps:

1. Declare and initialize a SQL statement handle, then allocate it with isc_dsql_allocate_statement():

isc_stmt_handle stmt; /* Declare a statement handle. */

stmt = NULL; /* Set handle to NULL before allocation. */

isc_dsql_allocate_statement(status_vector, &db_handle, &stmt);

2. Ready the statement string for execution with isc_dsql_prepare(). This checks the string (sel_str) for syntax errors, parses it into a format that can be efficiently executed, and sets the statement handle (stmt) to refer to this parsed format. The statement handle is used in a later call to isc_dsql_execute(). If isc_dsql_prepare() is passed a pointer to the output XSQLDA, as in the following example, it will fill in most fields of the XSQLDA and all its XSQLVAR substructures with information such as the datatype, length, and name of the corresponding columns in the statement.

A sample call to isc_dsql_prepare() is:

isc_dsql_prepare(status_vector,

&trans, /* Set by previous isc_start_transaction() call. */

&stmt, /* Statement handle set by this function call. */

0, /* Specifies statement string is null-terminated. */

sel_str, /* Statement string. */

1, /* da_version number. */

out_sqlda); /* XSQLDA for storing column data. */

3. Set up an XSQLVAR structure for each column. Setting up an XSQLVAR structure involves the following steps:
   For columns whose types are known at compile time:
   • Specify the column’s datatype (if it was not set by isc_dsql_prepare(), as previously described).
   • Point the sqldata field of the XSQLVAR to an appropriate local variable.
     
   For columns whose types are not known until run time:
   • Coerce the item’s datatype (optional); for example, from SQL_VARYING to SQL_TEXT.
   • Dynamically allocate local storage for the data pointed to by the sqldata field of the XSQLVAR.
     
   For both: Provide a NULL value indicator for the parameter.
   • Data retrieval 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, isc_dsql_prepare() sets each XSQLVAR sqltype field to the appropriate field type, and the data retrieved when a select list row’s data is fetched is placed into the sqldata space allocated for the column. For array columns, the type is set to SQL_ARRAY (or SQL_ARRAY + 1 if the array column is allowed to be NULL). InterBase stores the internal array identifier (array ID), not the array data, in the sqldata space when a row’s data is fetched, so you must point sqldata to an area the size of an array ID. To see how to retrieve the actual array or array slice data once you have an array ID, see Reading and Processing the Array Data.
   • The following code example illustrates the assignments for array and nonarray columns whose types are known at compile time. For more information about DSQL and the XSQLDA, and working with columns whose types are unknown until run time, see Working with Dynamic SQL.

ISC_QUAD array_id = 0L;

char dept_no[6];

short flag0, flag1;

out_sqlda->sqlvar[0].sqldata = (char *) dept_no;

out_sqlda->sqlvar[0].sqltype = SQL_TEXT + 1;

out_sqlda->sqlvar[0].sqlind = &flag0;

out_sqlda->sqlvar[1].sqldata = (char *) &array_id;

out_sqlda->sqlvar[1].sqltype = SQL_ARRAY + 1;

out_sqlda->sqlvar[1].sqlind = &flag1;

Executing the Statement

Once the query statement string is prepared, it can be executed:

isc_dsql_execute(status_vector,

&trans, /* set by previous isc_start_transaction() call */

&stmt, /* set above by isc_dsql_prepare() */

1, /* da_version number */

NULL); /* NULL since stmt doesn't have input values */

This statement creates a select-list, the rows returned by execution of the statement.

Populating the Array Descriptor

To prepare an array descriptor that describes the array or array slice to be read, follow these steps:

1. Create the array descriptor:

ISC_ARRAY_DESC_V2 desc;

2. Fill in the descriptor with information regarding the array column from which data will be read. Do this either by calling one of the functions isc_array_lookup_bounds2(), isc_array_lookup_desc2(), or isc_array_set_desc2(), or by directly filling in the descriptor. For information on the contents of array descriptors and the use of these functions, see Array Descriptors. Ensure the descriptor boundaries are set to those of the slice to be read. If you want to retrieve all the array data (that is, not just a smaller slice), set the boundaries to the full boundaries as initially declared for the array column. This is guaranteed to be the case if you fill in the descriptor by calling isc_array_lookup_bounds2(), as in:

   ISC_ARRAY_DESC_V2 desc;

   isc_array_lookup_bounds2(status_vector, &db_handle, &trans,

   "PROJ_DEPT_BUDGET",/* table name */

   "QUART_HEAD_CNT", /* array column name */

   &desc);

   Suppose the array column, QUART_HEAD_CNT, is a one-dimensional array consisting of four elements, and was declared to have a lower subscript bound of 1 and an upper bound of 4 when it was created. Then after the above call to isc_array_lookup_bounds2(), the array descriptor fields for the boundaries contain the following information:

   desc.array_desc_bounds[0].array_bound_lower == 1

   desc.array_desc_bounds[0].array_bound_upper == 4

   If you want to read just a slice of the array, then modify the upper and/or lower bounds appropriately. For example, if you just want to read the first two elements of the array, then modify the upper bound to the value 2, as in:

   desc.array_desc_bounds[0].array_bound_upper = 2

Fetching Selected Rows

A looping construct is used to fetch (into the output XSQLDA) the column data for a single row at a time from the select-list and to process each row before the next row is fetched. Each execution of isc_dsql_fetch() fetches the column data for the next row into the corresponding XSQLVAR structures of out_sqlda. For the array column, the array ID, not the actual array data, is fetched.

ISC_STATUS fetch_stat;

long SQLCODE;

. . .

while ((fetch_stat = j

isc_dsql_fetch(status_vector, &stmt, 1, out_sqlda)) == 0) {

/* Read and process the array data */

}

if (fetch_stat != 100L) {

/* isc_dsql_fetch returns 100 if no more rows remain to be retrieved */

SQLCODE = isc_sqlcode(status_vector);

isc_print_sqlerror(SQLCODE, status_vector);

return(1);

}

Reading and Processing the Array Data

To read and process the array or array slice data:

1. Create a buffer for holding the array data to be read. Make it large enough to hold all the elements in the slice to be read (which could be the entire array). For example, the following declares an array buffer large enough to hold four long elements:

long hcnt[4];

2. Declare a short variable for specifying the size of the array buffer:

   short len;

3. Set the variable to the buffer length:

   len = sizeof(hcnt);

4. Read the array or array slice data into the buffer by calling isc_array_get_slice2(). Process the data read. In the following example, the array is read into the hcnt array buffer, and “processing” consists of printing the data:

isc_array_get_slice2(status_vector,

&db_handle, /* set by isc_attach_database()*/

&trans, /* set by isc_start_transaction() */

&array_id, /* array ID put into out_sqlda by isc_dsql_fetch()*/

&desc, /* array descriptor specifying slice to be read */

(void *) hcnt, /* buffer into which data will be read */

(long *) &len); /* length of buffer */

if (status_vector[0] == 1 && status_vector[1]) {

isc_print_status(status_vector);

return(1);

}

/* Make dept_no a null-terminated string */

dept_no[out_sqlda->sqlvar[0].sqllen] = '\0';

printf("Department #: %s\n\n", dept_no);

printf("\tCurrent head counts: %ld %ld %ld %ld\n",

hcnt[0], hcnt[1], hcnt[2], hcnt[3]);

Advance to Next Subject

• Writing Data to An Array