The C API in Practice (Managing and Using MySQL, 2nd Edition)

12.2. The C API in Practice

What you have seen so far will take you a long way. If you want to understand what is going on in more detail, understand proper error handling, or work with binary data, however, we need to drop down a level and look at the API in a practical example. To these ends, we will look to an API that provides callers with access to delayed stock quotes stored in a MySQL database. The application leverages a single MySQL table, Stock, with the following schema:

CREATE TABLE Stock (
    symbol    CHAR(5) NOT NULL PRIMARY KEY,
    openPrice REAL    NOT NULL,
    currPrice REAL    NOT NULL,
    high52    REAL    NOT NULL,
    low52     REAL    NOT NULL
);

Our library needs two basic functions:

void assign_stock(Stock *stock);: Assigns the values in the Stock structure to the database
Stock *get_stock(char *symbol);: Retrieves the Stock structure for the specified stock

These functions naturally depend on a struct that mirrors the database schema for the stock quotes.

12.2.1. Support Functions

Our example includes three simple helper functions to support its work. The first initializes everything:

MySQL * connection, mysql;
  
int init_api( ) {
    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, "localhost", "orausr",
                                    "orapw", "oradb", 0, 0, 0);
    if( connection == NULL ) {
        return -1;
    }
    else {
        return 0;
    }
}

This function does the MySQL initialization we covered earlier in the chapter. It specifically establishes a connection to the database. If any error occurs while making the connection, it returns -1.

The second support function is the opposite of the initialization function; it closes everything:

void close_api( ) {
    if( connection != NULL ) {
        mysql_close(connection);
        connection = NULL;
    }
}

The last support function will be used many times in the example. It returns a message for the last error.

char *error;
  
char *get_error( ) {
    return error;
}

Any time the API encounters an error condition, it sets the error variable to the appropriate error message. Applications calling this API therefore call this function to retrieve the last error message. You should always copy any error messages into storage managed by your application if you wish to save them for later use.

12.2.2. Quote Retrieval

The get_stock( ) function provides all stock information from MySQL for the stock with the specified symbol. It formulates a query based on the ticker symbol and sticks the results into a Stock structure.

Stock *get_stock(char *symbol) {
    char *query = "SELECT symbol, openPrice, currPrice, high52, low52, \
                  FROM Stock WHERE symbol = '%s'";
    char *sql;
    int state;
  
    error = (char *)NULL;
    sql = (char *)malloc((strlen(query) + strlen(symbol) + 1) * sizeof(char));
    sprintf(sql, query, symbol);
    state = mysql_query(connection, sql);
    free(sql);
    if( state != 0 ) {
        error = mysql_error(connection);
        return (Stock *)NULL;
    }
    else {
        MYSQL_RES *result;
        Stock *stock;
        MYSQL_ROW row;
  
        result = mysql_store_result(connection);
        if( result == (MYSQL_RES *)NULL ) {
            error = mysql_error(connection);
            return (Stock *)NULL;
        }
        stock = (Stock *)malloc(sizeof(Stock));
        row = mysql_fetch_row(result);
        if( !row ) {
            error = "Invalid symbol.";
            return (Stock *)NULL;
        }
        stock->symbol = row[0];
        stock->open_price = atof(row[1]);
        stock->current_price = atof(row[2]);
        stock->high52 = atof(row[3]);
        stock->low52 = atof(row[4]);
        return stock;
    }
}

The first line of the function is where we prepare the query. It includes the entire query except for a %s placeholder for the ticker symbol. We will insert this into the string later using sprintf( ).

The function next clears out the error message used by the get_error( ) helper function. Clearing out the error message is critical since MySQL actually manages the memory allocated to MySQL error messages. If this function failed to clear out the error message, and the client calling this API tried to call get_error( ) when no error occurred, it would be possible for the error message to point to garbage memory.

To make sure the final SQL has enough memory allocated to it, the function allocates enough space for the query with the placeholder, the ticker symbol, and a final null terminator. Using sprintf( ), the query is assembled into its final SQL.

In applications in which the stock symbol may come from data entered by a user, either your application or—perhaps more appropriately—the API should check the ticker symbol for validity. In other words, you should scan it for SQL, especially single quotes. If you fail to check for these things, users may inadvertently cause errors in the database calls or—even worse—exploit the error to corrupt the database.

Immediately after running the query, the API frees the memory allocated for the query and proceeds to error handling. As we illustrated earlier, you check the error on a query by checking its return value. If the return value is nonzero, an error occurred. This API sets the value of the current error message to whatever mysql_error( ) has set and returns NULL to the calling application.

On success, the function retrieves the results of the query. For this query, it is looking for one and only one result. The call to mysql_store_result( ) gets the result set from MySQL. The mysql_store_result( ) function returns NULL under two conditions:

The original SQL was not a query (i.e., it was an INSERT, UPDATE, or DELETE).
The function encountered an error.

Because we know we sent a query to the database, this situation can mean only that an error has occurred. We therefore set the error message and return null.

We are now ready to allocate a Stock struct and assign it values. This example actually does row processing in a different manner from what we did earlier. It specifically calls mysql_fetch_row( ) instead of directly accessing the data through the MYSQL_RES struct. Though both methods are proper, mysql_fetch_row( ) is technically more proper. It enables you to step through a result set row by row and get meta-data for the row. Though we care about neither issue in this case, we still use that function.

The mysql_fetch_row( ) function will return a MYSQL_ROW struct if there are more rows in the result set. For this query, there should be only one row. If the call to mysql_fetch_row( ) returns NULL, the symbol simply is not in the database. We therefore check that it is in the database and begin assigning values to the Stock struct based on the data in the result set.

12.2.3. Adding Symbols

Adding a new symbol to the database is simpler than a query in that you have no results to process, yet more complex in that you have more mucking around with memory allocation for strings in building the query. We take the same approach in building the insert that we took in building SELECT for get_stock( ) except that we do not bother calculating how much memory we need; 255 characters should be more than enough for this example. Nothing about adding the stock is new.

int assign_stock(Stock *stock) {
    char *query = "INSERT INTO Stock ( symbol, openPrice, \
                   currPrice, high52, low52 ) \
                   VALUES ('%s', %f, %f, %f, %f)";
    char *sql;
    int state;
  
    sql = (char *)malloc(255 * sizeof(char));
    error = (char *)NULL;
    sprintf(sql, query,
            stock->symbol, stock->open_price, stock->current_price,
            stock->high52, stock->low52);
    state = mysql_query(connection, sql);
    free(sql);
    if( state != 0 ) {
        error = mysql_error(connection);
        return -1;
    }
    return 0;
}