A query identifies the subset of records in a database that satisfy specific criteria. For example, return all records that have a population value less than 10,000. SQL provides a language to define such queries. For the above example, assume a table of cities, with columns of city_name, area, and population. To obtain all cities with a population less than 10,000:

select city_name, area, population from cities where population < 10000

ArcSDE extends standard SQL queries by supporting spatial relationships and spatial searches. SQL does not have a standard spatial language extension, so ArcSDE provides a method to associate both an attribute-based SQL constraint and a geometric constraint on a query.

Attribute constraints are supplied to the API through an SE_SQL_CONSTRUCT structure. The SE_SQL_CONSTRUCT contains three pieces of information:

• The number of tables in the query
• The list of tables in the query
• The WHERE portion of the query

Taking the city population example, the SE_SQL_CONSTRUCT for the query is defined as:

/* Allocate an SE_SQL_CONSTRUCT */ rc = SE_sql_construct_alloc (1, &sqlc); /* See Error handling section for check_error function code. */ check_error(Connection,NULL,rc, "SE_sql_construct_alloc"); sqlc->where = malloc(20); if (sqlc == NULL) {    printf ("Failed to allocate sqlc\n");    exit(0); } /* Fill in the details of SQL query */ strcpy (sqlc->tables[0], "cities"); strcpy (sqlc->where, "population < 10000"); /*Once the SE_SQL_CONSTRUCT is defined, it can be used as the attribute * constraint on a stream query. The SE_stream_query function defines which * table columns to return and which attribute query constrains the record set:*/ /* Define the number and names of the table columns to be returned */ num_cols = 3; attrs = (CHAR **) malloc (num_cols * sizeof(CHAR *)); attrs[0] = "city_name";  /* Use fully qualified name for column area, since all ArcSDE layers also have a special field named SHAPE.AREA */ attrs[1] = "cities.area"; attrs[2] = "population"; /* Define the stream query */ rc = SE_stream_query (Stream, num_cols, attrs, sqlc); /* See Error handling section for check_error function code. */ check_error(NULL, Stream, rc, "SE_stream_query");

Once a query has been formulated and defined on a particular stream, it can be executed. Executing the query causes the database to evaluate the SQL statement and return a set of records satisfying the constraint. The SE_stream_execute function executes the SQL query on the stream:

/* Execute the SQL constraint on the stream */
rc = SE_stream_execute (Stream);

/* See Error handling section for check_error function code. */
check_error(NULL, Stream, rc, "SE_stream_execute");

When a query is executed, a collection of records is returned. The returned collection is referenced by the stream handle on which the query was executed. The stream maintains a state for the collection. The state indicates the current record being processed and the list of values being returned.

Applications iterate through the returned collection, processing each record individually. The SE_stream_fetch function accesses each record from the record set:

/* Iterate over the results */ while (rc == SE_SUCCESS) {    rc = SE_stream_fetch (Stream);    if (rc == SE_SUCCESS)    {       /* For each record, decode the desired values, and do something */    } }

The stream interface implements a one-way cursor model. The record set is accessed in the sequence established by the server. It is not possible to access records that have been processed already because that requires executing the query again.

The values returned with each record are accessed through a set of functions (see C API section) that manage the different supported data types. Each of these functions takes a stream handle and column number as input and returns the appropriate data type as output.

The previous example is expanded to illustrate how these functions access specific values from a record:

rc = SE_stream_execute (Stream); /* Iterate over the results */ while (rc == SE_SUCCESS){    rc = SE_stream_fetch (Stream);    if (rc == SE_SUCCESS)    {       rc = SE_stream_get_string (Stream,1,name);       /* See Error handling section for check_error function code. */       check_error(NULL, Stream, rc, "SE_stream_get_string");       rc = SE_stream_get_double (Stream,2,&area);       check_error(NULL, Stream, rc, "SE_stream_get_double");       rc = SE_stream_get_integer (Stream,3,&population);       check_error(NULL, Stream, rc, "SE_stream_get_integer");       /* Now do something with the name, area and population */    } }