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