# Executing eXtremeSQL Queries in C

> Once the SQL database engine structure is initialized with one of the two methods explained in the SQL Engine Initialization page, this structure is used, for all calls to the `mcosql_*` functions. The principal function for performing SQL queries is `mcosql_execute_query()` which returns a result set in the form of structure `data_source_t`. Then the result set data is extracted from the data source using a `cursor` as demonstrated in the following code snippet:

>         /* Return the autoid for the Person record with the specified name */
>         MCO_RET get_person_id( database_t db, char const* name, int64_t* id )
>         {
>             status_t status = SQL_OK;
>             data_source_t data_source;
>             cursor_t cursor;
>             record_t record;
>             status = mcosql_execute_query(db, NULL, &data_source, "select autoid from Person where name=%s",
>                                 name);
>                 if ( SQL_OK == status )
>                 {
>                     status = mcosql_get_cursor(data_source, &cursor);

>                     if ( SQL_OK == status )
>                     {
>                         status = mcosql_cursor_move_next(cursor, &record);
>                         if ( SQL_OK == status )
>                         {
>                             // process record
>                             ...
>                         }
>                         else if ( NO_MORE_ELEMENTS != status )
>                         {
>                             mcosql_error_handler(status);
>                         }
>                     }
>                     mcosql_release_query_result(data_source);

>                 }
>             return status;
>         }

> Note the following in this example code:

> - the function `get_person_id()` is an application-level function intended to return the `autoid` for a Person object with the name specified. The schema definition for the Person record is as follows:

> >         class Person
> >         {
> >             string name;
> >             string address;
> >             string position;
> >             uint4  salary;
> >             float  rating;
> >             autoid_t<Person> manager;
> >             autoid[1000];

> >             tree<name> pk;
> >         };

> - the function `mcosql_execute_query()` executes a SQL query and returns a status code (as defined in header file “`include/sql/sqlc.h`”); it takes a variable length argument list consisting of :
>   - `db` – the database engine
>   - `trans` - a transaction handle or `NULL` if the query is to be executed in its own separate transaction (see section “Managing Transactions” ).
>   - `data_source` – the address of the data source to receive the result set
>   - `statement` – the SQL select statement to be executed
>   - `…` - a list of 0 or more arguments to be inserted into the select statement. (The rules for substitution of values into the SQL statement are elaborated in the SQL Statement Argument Substitution page.)
> - the function `mcosql_get_cursor()`, creates a cursor over the result set and `mcosql_cursor_move_next()` is used to scroll through the result rows.
> - the status code `NO_MORE_ELEMENTS` is returned when the end of the result set is reached
> - the function `mcosql_release_query_result()` must be called when finished with the query to release the memory and other resources allocated for the result set.

> ## Processing Query Results

> Processing the query result set rows will often require extracting values of different types by iterating over the columns of each row. Then each column value must be handled differently depending on its type. Internally, all integer values are stored as 64-bit values. To extract a column value the function `mcosql_get_column_value_as()`is called to convert the internal value to that used by the application. For example the following call extracts the value from the first column of a result set and converts it to a 4-byte integer value:

> >         rc = (MCO_RET)mcosql_get_column_value_as(record, 0, CT_UINT4,
> >                                 &value, sizeof(value), NULL);

> Prior to eXtremeSQL version 6.5, the function `mcosql_get_column_value()` was used to extract column values. This function expects the receiving buffer to be 64-bits even for smaller types. However in version 6.5 and later, the `mcosql_get_column_value()` argument list has been changed and in most cases it should not be used because it returns the “`raw`” value of the internal type (i.e. suppose we have a column of 32-bit integers. Those values are returned as 64-bit integers which may be not what the user desires - especially if a 32-bit buffer is supplied for the data).

> Prior to version 6.5, code like the following snippet was used to process the columns of a result set:

>         /* For each row of the result set, iterate through the columns and display values. */
>         status = mcosql_get_cursor( data_source, &cursor );
>         if ( SQL_OK == status )
>         {
>             for ( n = 0; SQL_OK == ( status = mcosql_cursor_move_next( cursor, &record ) ); n++ )
>             {
>                 for (j = 0; j < i; j++)
>                 {
>                     status = mcosql_get_column_value(record, j, &type, &value );
>                     if ( SQL_OK == status )
>                     {
>                         if ( 0 == j )
>                             printf( "\t" );
>                         else
>                             printf( ", " );
>                         print_value(db, type, value);

>                     }
>                 }
>                 printf("\n");
>             }
>             if ( NO_MORE_ELEMENTS != status )
>             {
>                 mcosql_error_handler(status);
>             }
>         }

> Note also that the argument list for `mcosql_get_column_value()` in version 6.5 and later contains six arguments as opposed to the previous implementation which had 4 arguments.

> Now the function signature is as follows

> >         status_t mcosql_get_column_value(record_t record, size_t columnNo, type_t* type, void* buf,
> >                             size_t buf_size, size_t* value_size);

> where `buf` receives the value stored in the specified column and the data type is returned in argument type. Also note that the function `mcosql_get_record_id()` formerly used to retrieve the `mcocomp` generated record identifier has been eliminated from version 6.5 and later, and the previous function `mcosql_release_memory()` is no longer available as all memory is managed by the static block allocator.

> The application level function `print_value()` (highlighted in the snippet above) is copied below to demonstrate how different value types might be displayed to an output device:

> >         /* print the value contained in this database field depending on its type */
> >         void print_value(database_t db, int type, void* value)
> >         {
> >             char buf[MAX_NAME_LENGTH];
> >             switch (type)
> >             {
> >                 case CT_NULL:
> >                     printf("null");
> >                     break;
> >                 case CT_BOOL:
> >                     printf(*(char*)value ? "true" : "false");
> >                     break;
> >                 case CT_UINT1:
> >                     printf("%u", *(uint1*)value);
> >                     break;
> >                 case CT_UINT2:
> >                     printf("%u", *(uint2*)value);
> >                     break;
> >                 case CT_UINT4:
> >                     printf("%u", *(uint4*)value);
> >                     break;
> >                 case CT_UINT8:
> >                     printf("%" INT8_FORMAT "d", *(int64_t*)value);
> >                     break;
> >                 case CT_INT1:
> >                     printf("%d", *(int1*)value);
> >                     break;
> >                 case CT_INT2:
> >                     printf("%d", *(int2*)value);
> >                     break;
> >                 case CT_INT4:
> >                     printf("%d", *(int4*)value);
> >                     break;
> >                 case CT_INT8:
> >                     printf("%" INT8_FORMAT "d", *(int64_t*)value);
> >                     break;
> >                 case CT_REAL4:
> >                     printf("%.1f", *(float*)value);
> >                     break;
> >                 case CT_REAL8:
> >                     printf("%.1f", *(double*)value);
> >                     break;
> >                 case CT_STRING:
> >                     printf("%s", (char*)value);
> >                     break;
> >                 case CT_REFERENCE:
> >                     {
> >                         /* lookup the referenced record and extract its name field */
> >                         int64_t id;
> >                         mcosql_get_record_id(value, &id);
> >                         get_person_name(db, buf, sizeof(buf), &id);
> >                         printf("%s", buf);
> >                         break;
> >                     }
> >                 default:
> >                     printf("???");
> >             }
> >         }

> Note the following in this example code:

> - The column type constants `CT_*` are defined in the header file “`include/sql/sqlc.h`”.
> - The constant `INT8_FORMAT` inserted into the `printf()` format string for types `CT_INT8` and `CT_UINT8` is defined in the header file “`include/sql/stdtp.h`” as the string constant “`l`”, “`ll`” or “`I64`” depending on the development system platform.
> - In this example the type `CT_REFERENCE` is treated as a reference to a different Person object with the `autoid` contained in the function’s value argument. So an additional database lookup is performed to navigate to the specified Person record and extract then display its name field. Clearly this is application logic specific to this particular database schema and data model.

> ### Extracting Data from Result Set Rows

> The example above extracts a single column value from each result set row. But often a result set row consists of an entire database object, as for example in the query “`select * from Person`”. In this case a function like the following might be written to process the result set:

>         /* Demonstrate use of the function mcosql_extract_struct() */
>         MCO_RET get_persons( database_t db, char const* name_like )
>         {
>             data_source_t data_source;
>             cursor_t cursor;
>             status_t status;
>             record_t record;
>             int64_t id;
>             type_t type;
>             void* value;
>             int n;

>             /* Define a structure with exactly the same components as the Person database class */
>             struct
>             {
>                 char * name;
>                 char * address;
>                 char * position;
>                 uint4 salary;
>                 float rating;
>                 autoid_t manager;
>             } rec;

>             /* Define a character array to hold possible null indicators if any fields are null */
>             char null_indicators[10];
>             printf("\n\tGet Person records with name like '%s':\n", name_like );
>             status = mcosql_execute_query(db, NULL, &data_source,
>                                 "select * from Person where name like %s", name_like );
>             if ( SQL_OK == status )
>             {
>                 status = mcosql_get_cursor( data_source, &cursor );
>                 if ( SQL_OK == status )
>                 {
>                     for (n = 0; SQL_OK == ( status = mcosql_cursor_move_next( cursor, &record ) ); n++)
>                     {
>                         /* Extract the structure "rec" from the result set record */
>                         status = mcosql_extract_struct(data_source, record, &rec, sizeof(rec), null_indicators);
>                         printf("\t\t%d. %s: salary=%d, rating = %f\n", n + 1, rec.name, rec.salary, rec.rating);
>                     }
>                     if ( NO_MORE_ELEMENTS != status )
>                     {
>                         mcosql_error_handler(status);
>                     }
>                 }
>                 else
>                  {
>                     fprintf(stderr, "No person found with name like '%s'\n", name_like);
>                 }
>                 mcosql_release_query_result( data_source );
>             }
>             return rc;
>         }

> Note the following in this example code:

> - the structure `record_t` `record` is used to receive the result set row retrieved by the call to function `mcosql_cursor_move_next()`.
> - the structure `rec` is defined to correspond exactly to the database object Person as defined in the `schema` file. The rules for defining this C `struct` are as follows:

> > > o The `struct` must have the same number of components in the same order as the database class.

> > > o eXtremeSQL assumes the default alignment of all structure elements; i.e. the alignment used by the compiler without special align `pragmas`.

> > > o `Array` type components are represented by a pointer to `Array` value.

> > > o `String` components are represented as null-terminated ANSI strings.

> > > o Nested structures should be represented by the same C `struct`.

> > > o If the column does not belong to a database class but is rather the result of some select statement expression or calculation, then its type is determined by the following:

> > > - Integer types (`char`, `short`, `unsigned short`, `int`, …) are represented by type `int64_t`
> > > - floating point types (`float`, `double`) are represented by type `double`
> > > - other types are represented as they are (not converted to any other type)

> - for each row in the result set, the row contents (in `record_t` record) are extracted into structure rec by calling function `mcosql_extract_struct()`.
> - the `null_indicators[]` array indicates if any of the columns are null

> In addition to the functions demonstrated above, the following functions are provided for processing result set rows:

> >         /* Get the current transaction within which the specified data source was produced */
> >         mcosql_get_current_transaction(data_source_t data_source, transaction_t* trans);

> >         /* Get the number of columns in the specified data source */
> >         mcosql_get_number_of_columns(data_source_t data_source, int* n_columns);

> >         /* Get an iterator to iterate over the data source columns */
> >         mcosql_get_column_iterator(data_source_t data_source, column_iterator_t* iterator);

> >         /* Get column information. Move the column iterator to the next position and return name
> >         and type information for the current column */
> >         mcosql_get_column_info(column_iterator_t iterator, type_t* type, char** name);

> >         /* Get column value converted to the specified type. In case of string types,
> >         only a pointer to the zero terminated string will be placed in the buffer
> >         and it remains valid only until mcosql_release_query_result is executed. */
> >         mcosql_get_column_value_as(record_t record, int columnNo, type_t type, void* buffer);

> >         /* Get the value of a structure or array field of the current record for update of
> >         its components. This value remains valid only until mcosql_release_query_result
> >         is executed. This value can be used only as a SQL statement parameter (used
> >         with the '%v' format placeholder). */
> >         mcosql_get_column_value_for_update(record_t record, int columnNo, type_t* type, void* value);

> >         /* Set the value of a specified column */
> >         mcosql_set_column_value(record_t record, int columnNo, type_t type, void* value);

> > It is important to note that the internal SQL engine representation of the supported C data types can lead to some questions such as: Why for a field declared as “`int4`“ (`signed<4>`) does `mcosql_get_column()` return an `int8` value? The answer is that inside the sql engine all integer types are represented by `int8`, and all floating point values by `double`. So even if nothing is done with the field (for example `select * from foo`), the value is still converted to `int8`. Because this could be inconvenient, the function `mcosql_get_column_as()` is provided, which converts the column value to the specified type.

> ### Extracting Arrays, Vectors, Structs, Blobs and References

> Result set values of type `array` (or `vector`), `ref`, `struct` and `blob` require more complicated processing. The following functions are provided for these types of fields.

> For result set columns corresponding to database fields of type `array` (or `vector`):

> >         /* Get array length */
> >         mcosql_get_array_length(void* array, int* length);

> >         /* Get array element value */
> >         mcosql_get_array_element_value(void* array, int index, type_t* type, void* value);

> >         /* Get array element value for update of its components */
> >         mcosql_get_array_element_value_for_update(void* array, int index, type_t* type, void* value);

> >         /* Get array body: copy the specified number of elements from the specified offset
> >         to the buffer. This method can be used only for arrays of scalar types. */
> >         mcosql_get_array_body(void* array, void* dst, int offs, int len);

> >         /* Set array length */
> >         mcosql_set_array_length(void* array, int length);

> >         /* Set array element value */
> >         mcosql_set_array_element_value(void* array, int index, type_t type, void* value);

> >         /* Set array body: copy the specified number of elements from buffer to the array
> >         using the specified offset. This method can be used only for arrays of scalar types. */
> >         mcosql_set_array_body(void* array, void* src, int offs, int len);

> The following snippet is an example of code that uses these functions to process an array (or `vector`)field:

> >         void process_array(void * value)
> >         {
> >             unsigned int i, len = 0;
> >             mcosql_get_array_length(value, &len);
> >             for (i = 0; i < len; i++)
> >             {
> >                 type_t inner_type;
> >                 void* inner_value;
> >                 mcosql_get_array_element_value(value, i, &inner_type, &inner_value);
> >                 /* Do what is needed with the array element */
> >             }
> >         }

> For result set columns corresponding to database fields of type `ref` (reference to another database object):

> >         /* Get referenced record (by actual memory address ‘ref’) */
> >         mcosql_get_referenced_record(void* ref, record_t* record);

> For result set columns corresponding to database fields of type `struct`:

> >         /* Get number of components in the structure */
> >         mcosql_get_struct_size(void* s, int* size);
> >         /* Get structure component value */
> >         mcosql_get_struct_component_value(void* s, int index, type_t* type, void* value);
> >         /* Get structure or array component of a structure for updates to its components or elements. */
> >         mcosql_get_struct_component_value_for_update(void* s, int index, type_t* type, void* value);
> >         /* Set structure component value */
> >         mcosql_set_struct_component_value(void* s, int index, type_t* type, void* value);

> > The following snippet is an example of code that uses these functions to process a `struct` field:

> > >         void process_struct(void * value)
> > >         {
> > >             unsigned int i, len = 0;
> > >             mcosql_get_struct_size(value, &len);
> > >             for (i = 0; i < len; i++)
> > >             {
> > >                 type_t inner_type;
> > >                 void* inner_value;
> > >                 mcosql_get_struct_component_value(value, i, &inner_type, &inner_value);
> > >                 /* Do what is needed with the struct field */
> > >             }
> > >         }

> For result set columns corresponding to database fields of type `blob` (Binary Large Object):

> >         /* Return the number of bytes available to be extracted.
> >         NB: This is not the total size of the BLOB, it can be smaller than BLOB size.
> >         For example, if the BLOB consists of several segments, it can be the segment size. */
> >         mcosql_blob_available_size(void* blob, int* size);

> >         /* Copy BLOB data to the specified buffer. This method copies up to buffer_size bytes
> >         from the current position in the BLOB to the specified buffer. Then, the current
> >         position is advanced by the number of bytes fetched. */
> >         mcosql_blob_get_data(void* blob, void* buffer, int buffer_size, int* n_bytes);

> >         /* Append new data to the BLOB. NB: Append is always performed at the end of the
> >         BLOB and doesn't change the current position for the GET method. */
> >         mcosql_blob_append_data(void const* blob, void* buffer, int size);

> >         /* Reset the current position to the beginning of the BLOB */
> >         mcosql_blob_reset(void* blob);