Using the MySQL Client Library to Write Your Own Programs

This chapter is from the book

MySQL

Learn More Buy

This chapter is from the book

This chapter is from the book 

MySQL

Learn More Buy

Processing Queries

Now that we know how to begin and end a conversation with the server, it's time to see how to conduct the conversation while it's going on. This section shows how to communicate with the server to process queries.

Each query you run involves the following steps:

1. Construct the query. The way you do this depends on the contents of the query—in particular, whether or not it contains binary data.

2. Issue the query by sending it to the server for execution.

3. Process the query result. This depends on what type of query you issued. For example, a SELECT statement returns rows of data for you to process. An INSERT statement does not.

One factor to consider in constructing queries is which function to use for sending them to the server. The more general query-issuing routine is mysql_real_query(). With this routine, you provide the query as a counted string (a string plus a length). You must keep track of the length of your query string and pass that to mysql_real_query(), along with the string itself. Because the query is a counted string, its contents may be anything, including binary data or null bytes. The query is not treated as a null-terminated string.

The other query-issuing function, mysql_query(), is more restrictive in what it allows in the query string but often is easier to use. Queries that you pass to mysql_query() should be null-terminated strings, which means they cannot contain null bytes in the text of the query. (The presence of null bytes within the query causes it to be interpreted erroneously as shorter than it really is.) Generally speaking, if your query can contain arbitrary binary data, it might contain null bytes, so you shouldn't use mysql_query(). On the other hand, when you are working with null-terminated strings, you have the luxury of constructing queries using standard C library string functions that you're probably already familiar with, such as strcpy() and sprintf().

Another factor to consider in constructing queries is whether or not you need to perform any character-escaping operations. You do if you want to construct queries using values that contain binary data or other troublesome characters, such as quotes or backslashes. This is discussed in "Encoding Problematic Data in Queries."

A simple outline of query handling looks like this:

if (mysql_query (conn, query) != 0)
{
  /* failure; report error */
}
else
{
  /* success; find out what effect the query had */
}

mysql_query() and mysql_real_query() both return zero for queries that succeed and non-zero for failure. To say that a query "succeeded" means the server accepted it as legal and was able to execute it. It does not indicate anything about the effect of the query. For example, it does not indicate that a SELECT query selected any rows or that a DELETE statement deleted any rows. Checking what effect the query actually had involves additional processing.

A query may fail for a variety of reasons. Some common causes include the following:

• It contains a syntax error.

• It's semantically illegal—for example, a query that refers to a non-existent column of a table.

• You don't have sufficient privileges to access the data referenced by the query.

Queries may be grouped into two broad categories: those that do not return a result and those that do. Queries for statements such as INSERT, DELETE, and UPDATE fall into the "no result returned" category. They don't return any rows, even for queries that modify your database. The only information you get back is a count of the number of rows affected.

Queries for statements such as SELECT and SHOW fall into the "result returned" category; after all, the purpose of issuing those statements is to get something back. The set of rows produced by a query that returns data is called the result set. This is represented in MySQL by the MYSQL_RES data type, a structure that contains the data values for the rows, and also metadata about the values (such as the column names and data value lengths). An empty result set (that is, one that contains zero rows) is distinct from "no result."

Handling Queries That Return No Result Set

To process a query that does not return a result set, issue the query with mysql_query() or mysql_real_query(). If the query succeeds, you can find out how many rows were inserted, deleted, or updated by calling mysql_affected_rows().

The following example shows how to handle a query that returns no result set:

if (mysql_query (conn, "INSERT INTO my_tbl SET name = 'My Name'") != 0)
{
  print_error ("INSERT statement failed");
}
else
{
  printf ("INSERT statement succeeded: %lu rows affected\n",
        (unsigned long) mysql_affected_rows (conn));
}

Note how the result of mysql_affected_rows() is cast to unsigned long for printing. This function returns a value of type my_ulonglong, but attempting to print a value of that type directly does not work on some systems. (For example, I have observed it to work under FreeBSD but to fail under Solaris.) Casting the value to unsigned long and using a print format of '%lu' solves the problem. The same consideration applies to any other functions that return my_ulonglong values, such as mysql_num_rows() and mysql_insert_id(). If you want your client programs to be portable across different systems, keep this in mind.

mysql_affected_rows() returns the number of rows affected by the query, but the meaning of "rows affected" depends on the type of query. For INSERT, REPLACE, or DELETE, it is the number of rows inserted, replaced, or deleted. For UPDATE, it is the number of rows updated, which means the number of rows that MySQL actually modified. MySQL does not update a row if its contents are the same as what you're updating it to. This means that although a row might be selected for updating (by the WHERE clause of the UPDATE statement), it might not actually be changed.

This meaning of "rows affected" for UPDATE actually is something of a controversial point because some people want it to mean "rows matched"—that is, the number of rows selected for updating, even if the update operation doesn't actually change their values. If your application requires such a meaning, you can get this behavior by asking for it when you connect to the server. Pass a flags value of CLIENT_FOUND_ROWS to mysql_real_connect(). You can pass CLIENT_FOUND_ROWS as the flags argument to do_connect(), too; it will pass along the value to mysql_real_connect().

Handling Queries That Return a Result Set

Queries that return data do so in the form of a result set that you deal with after issuing the query by calling mysql_query() or mysql_real_query(). It's important to realize that in MySQL, SELECT is not the only statement that returns rows. SHOW, DESCRIBE, and EXPLAIN do so as well. For all of these statements, you must perform additional row-handling processing after you issue the query.

Handling a result set involves these steps:

• Generate the result set by calling mysql_store_result() or mysql_use_result(). These functions return a MYSQL_RES pointer for success or NULL for failure. Later, we'll go over the differences between mysql_store_result() and mysql_use_result(), as well as the conditions under which you would choose one over the other. For now, our examples use mysql_store_result(), which returns the rows from the server immediately and stores them in the client.

• Call mysql_fetch_row() for each row of the result set. This function returns a MYSQL_ROW value, which is a pointer to an array of strings representing the values for each column in the row. What you do with the row depends on your application. You might simply print the column values, perform some statistical calculation on them, or do something else altogether. mysql_fetch_row() returns NULL when there are no more rows left in the result set.

• When you are done with the result set, call mysql_free_result() to de-allocate the memory it uses. If you neglect to do this, your application will leak memory. (It's especially important to dispose of result sets properly for long-running applications; otherwise, you will notice your system slowly being taken over by processes that consume ever-increasing amounts of system resources.)

The following example outlines how to process a query that returns a result set:

MYSQL_RES *res_set;

if (mysql_query (conn, "SHOW TABLES FROM mysql") != 0)
  print_error (conn, "mysql_query() failed");
else
{
  res_set = mysql_store_result (conn);  /* generate result set */
  if (res_set == NULL)
      print_error (conn, "mysql_store_result() failed");
  else
  {
    /* process result set, then deallocate it */
    process_result_set (conn, res_set);
    mysql_free_result (res_set);
  }
}

We cheated a little here by calling a function process_result_set() to handle each row. We haven't defined that function yet, so we need to do so. Generally, result set-handling functions are based on a loop that looks like this:

MYSQL_ROW row;

while ((row = mysql_fetch_row (res_set)) != NULL)
{
  /* do something with row contents */
}

The MYSQL_ROW return value from mysql_fetch_row() is a pointer to an array of values, so accessing each value is simply a matter of accessing row[i], where i ranges from 0 to the number of columns in the row minus one.

There are several important points about the MYSQL_ROW data type to note:

• MYSQL_ROW is a pointer type, so you declare variables of that type as MYSQL_ROW row, not as MYSQL_ROW *row.

• The strings in a MYSQL_ROW array are null-terminated. However, if a column may contain binary data, it may contain null bytes, so you should not treat the value as a null-terminated string. Get the column length to find out how long the column value is.

• Values for all data types, even numeric types, are returned as strings. If you want to treat a value as a number, you must convert the string yourself.

• NULL values are represented by NULL pointers in the MYSQL_ROW array. Unless you have declared a column NOT NULL, you should always check whether or not values for that column are NULL pointers.

Your applications can do whatever they like with the contents of each row. For purposes of illustration, let's just print the rows with column values separated by tabs. To do that, we need an additional function, mysql_num_fields(), from the client library; this function tells us how many values (columns) the row contains.

Here's the code for process_result_set():

void
process_result_set (MYSQL *conn, MYSQL_RES *res_set)
{
MYSQL_ROW    row;
unsigned int  i;

  while ((row = mysql_fetch_row (res_set)) != NULL)
  {
    for (i = 0; i < mysql_num_fields (res_set); i++)
    {
      if (i > 0)
        fputc ('\t', stdout);
      printf ("%s", row[i] != NULL ? row[i] : "NULL");
    }
    fputc ('\n', stdout);
  }
  if (mysql_errno (conn) != 0)
    print_error (conn, "mysql_fetch_row() failed");
  else
    printf ("%lu rows returned\n", (unsigned long) mysql_num_rows (res_set));
}

process_result_set() prints each row in tab-delimited format (displaying NULL values as the word "NULL"), followed by a count of the number of rows retrieved. That count is available by calling mysql_num_rows(). Like mysql_affected_rows(), mysql_num_rows() returns a my_ulonglong value, so cast that value to unsigned long and use a '%lu' format to print it.

The row-fetching loop is followed by an error test. If you create the result set with mysql_store_result(), a NULL return value from mysql_fetch_row() always means "no more rows." However, if you create the result set with mysql_use_result(), a NULL return value from mysql_fetch_row() can mean "no more rows" or that an error occurred. The test simply allows process_result_set() to detect errors, no matter how you create your result set.

This version of process_result_set() takes a rather minimalist approach to printing column values, an approach that has certain shortcomings. For example, suppose you execute this query:

SELECT last_name, first_name, city, state FROM president

You will receive the following output:

Adams  John  Braintree  MA
Adams  John Quincy Braintree  MA
Arthur Chester A. Fairfield  VT
Buchanan  James  Mercersburg PA
Bush  George W.  Milton MA
Carter James E. Jr Plains GA
Cleveland  Grover Caldwell  NJ
...

We could make the output prettier by providing information such as column labels and making the values line up vertically. To do that, we need the labels, and we need to know the widest value in each column. That information is available, but not as part of the column data values—it's part of the result set's metadata (data about the data). After we generalize our query handler a bit, we'll write a nicer display formatter in the section "Using Result Set Metadata."

A General Purpose Query Handler

The preceding query-handling examples were written using knowledge of whether or not the statement should return any data. That was possible because the queries were hardwired into the code: We used an INSERT statement, which does not return a result set, and a SHOW TABLES statement, which does.

However, you don't always know what kind of statement the query represents. For example, if you execute a query that you read from the keyboard or from a file, it might be any arbitrary statement. You won't know ahead of time whether or not to expect it to return rows. What then? You certainly don't want to try to parse the query to determine what kind of statement it is. That's not as simple as it might seem, anyway. It's not sufficient to look at the first word because the query might begin with a comment, as follows:

/* comment */ SELECT ...

Fortunately, you don't have to know the query type in advance to be able to handle it properly. The MySQL C API makes it possible to write a general purpose query handler that correctly processes any kind of statement, whether or not it returns a result set.

Before writing the code for the query handler, let's outline how this works:

• Issue the query. If it fails, we're done.

• If the query succeeds, call mysql_store_result() to retrieve the rows from the server and create a result set.

• If mysql_store_result() fails, it could be that the query does not return a result set, or that an error occurred while trying to retrieve the set. You can distinguish between these outcomes by passing the connection handler to mysql_field_count() and checking its value, as follows:

• If mysql_field_count() is non-zero, it indicates an error: The query should have returned a result set but didn't. This can happen for various reasons. For example, the result set may have been too large and memory allocation failed, or a network outage between the client and the server may have occurred while fetching rows.

A slight complication to this procedure is that mysql_field_count() doesn't exist prior to MySQL 3.22.24. In earlier versions, you use mysql_num_fields() instead. To write programs that work with any version of MySQL, include the following code fragment in any file that calls mysql_field_count():

#if !defined(MYSQL_VERSION_ID) || MYSQL_VERSION_ID<32224
#define mysql_field_count mysql_num_fields
#endif

This causes any calls to mysql_field_count() to be treated as calls to mysql_num_fields() for versions of MySQL earlier than 3.22.24.

• If mysql_field_count() returns 0, it means the query returned no result set. (This indicates the query was a statement such as INSERT, DELETE, or UPDATE).

• If mysql_store_result() succeeds, the query returned a result set. Process the rows by calling mysql_fetch_row() until it returns NULL.

The following listing shows a function that processes any query, given a connection handler and a null-terminated query string:

#if !defined(MYSQL_VERSION_ID) || MYSQL_VERSION_ID<32224
#define mysql_field_count mysql_num_fields
#endif

void
process_query (MYSQL *conn, char *query)
{
MYSQL_RES *res_set;
unsigned int field_count;

  if (mysql_query (conn, query) != 0) /* the query failed */
  {
    print_error (conn, "process_query() failed");
    return;
  }

  /* the query succeeded; determine whether or not it returns data */

  res_set = mysql_store_result (conn);
  if (res_set == NULL)  /* no result set was returned */
  {
    /*
     * does the lack of a result set mean that an error
     * occurred or that no result set was returned?
     */
    if (mysql_field_count (conn) > 0)
    {
      /*
       * a result set was expected, but mysql_store_result()
       * did not return one; this means an error occurred
       */
      print_error (conn, "Problem processing result set");
    }
    else
    {
      /*
       * no result set was returned; query returned no data
       * (it was not a SELECT, SHOW, DESCRIBE, or EXPLAIN),
       * so just report number of rows affected by query
       */
      printf ("%lu rows affected\n",
            (unsigned long) mysql_affected_rows (conn));
    }
  }
  else  /* a result set was returned */
  {
    /* process rows, then free the result set */
    process_result_set (conn, res_set);
    mysql_free_result (res_set);
  }
}

Alternative Approaches to Query Processing

The version of process_query() just shown has these three properties:

• It uses mysql_query() to issue the query.

• It uses mysql_store_query() to retrieve the result set.

• When no result set is obtained, it uses mysql_field_count() to distinguish occurrence of an error from a result set not being expected.

Alternative approaches are possible for all three of these aspects of query handling:

• You can use a counted query string and mysql_real_query() rather than a null-terminated query string and mysql_query().

• You can create the result set by calling mysql_use_result() rather than mysql_store_result().

• You can call mysql_error() rather than mysql_field_count() to determine whether result set retrieval failed or whether there was simply no set to retrieve.

Any or all of these approaches can be used instead of those used in process_query(). Here is a process_real_query() function that is analogous to process_query() but that uses all three alternatives:

void
process_real_query (MYSQL *conn, char *query, unsigned int len)
{
MYSQL_RES *res_set;
unsigned int field_count;

  if (mysql_real_query (conn, query, len) != 0)  /* the query failed */
  {
    print_error (conn, "process_real_query () failed");
    return;
  }

  /* the query succeeded; determine whether or not it returns data */

  res_set = mysql_use_result (conn);
  if (res_set == NULL)  /* no result set was returned */
  {
    /*
     * does the lack of a result set mean that an error
     * occurred or that no result set was returned?
     */
    if (mysql_errno (conn) != 0) /* an error occurred */
      print_error (conn, "Problem processing result set");
    else
    {
      /*
       * no result set was returned; query returned no data
       * (it was not a SELECT, SHOW, DESCRIBE, or EXPLAIN),
       * so just report number of rows affected by query
       */
      printf ("%lu rows affected\n",
            (unsigned long) mysql_affected_rows (conn));
    }
  }
  else  /* a result set was returned */
  {
    /* process rows, then free the result set */
    process_result_set (conn, res_set);
    mysql_free_result (res_set);
  }
}

A Comparison of mysql_store_result() and mysql_use_result()

The mysql_store_result() and mysql_use_result() functions are similar in that both take a connection handler argument and return a result set. However, the differences between them actually are quite extensive. The primary difference between the two functions lies in the way rows of the result set are retrieved from the server. mysql_store_result() retrieves all the rows immediately when you call it. mysql_use_result() initiates the retrieval but doesn't actually get any of the rows. Instead, it assumes you will call mysql_fetch_row() to retrieve the records later. These differing approaches to row retrieval give rise to all other differences between the two functions. This section compares them so you'll know how to choose the one that's most appropriate for a given application.

When mysql_store_result() retrieves a result set from the server, it fetches the rows, allocates memory for them, and stores them in the client. Subsequent calls to mysql_fetch_row() never return an error because they simply pull a row out of a data structure that already holds the result set. A NULL return from mysql_fetch_row() always means you've reached the end of the result set.

By contrast, mysql_use_result() doesn't retrieve any rows itself. Instead, it simply initiates a row-by-row retrieval, which you must complete yourself by calling mysql_fetch_row() for each row. In this case, although a NULL return from mysql_fetch_row() normally still means the end of the result set has been reached, it's also possible that an error occurred while communicating with the server. You can distinguish the two outcomes by calling mysql_errno() or mysql_error().

mysql_store_result() has higher memory and processing requirements than does mysql_use_result() because the entire result set is maintained in the client. The overhead for memory allocation and data structure setup is greater, and a client that retrieves large result sets runs the risk of running out of memory. If you're going to retrieve a lot of rows at once, you may want to use mysql_use_result() instead.

mysql_use_result() has lower memory requirements because only enough space to handle a single row at a time need be allocated. This can be faster because you're not setting up as complex a data structure for the result set. On the other hand, mysql_use_result() places a greater burden on the server, which must hold rows of the result set until the client sees fit to retrieve all of them. This makes mysql_use_result() a poor choice for certain types of clients:

• Interactive clients that advance from row to row at the request of the user. (You don't want the server having to wait to send the next row just because the user decides to take a coffee break.)

• Clients that do a lot of processing between row retrievals.

In both of these types of situations, the client fails to retrieve all rows in the result set quickly. This ties up the server and can have a negative impact on other clients because tables from which you retrieve data are read-locked for the duration of the query. Any clients that are trying to update those tables or insert rows into them are blocked.

Offsetting the additional memory requirements incurred by mysql_store_result() are certain benefits of having access to the entire result set at once. All rows of the set are available, so you have random access into them: The mysql_data_seek(), mysql_row_seek(), and mysql_row_tell() functions allow you to access rows in any order you want. With mysql_use_result(), you can access rows only in the order in which they are retrieved by mysql_fetch_row(). If you intend to process rows in any order other than sequentially as they are returned from the server, you must use mysql_store_result() instead. For example, if you have an application that allows the user to browse back and forth among the rows selected by a query, you'd be best served by using mysql_store_result().

With mysql_store_result(), you can obtain certain types of column information that are unavailable when you use mysql_use_result(). The number of rows in the result set is obtained by calling mysql_num_rows(). The maximum widths of the values in each column are stored in the max_width member of the MYSQL_FIELD column information structures. With mysql_use_result(), mysql_num_rows() doesn't return the correct value until you've fetched all the rows, and max_width is unavailable because it can be calculated only after every row's data have been seen.

Because mysql_use_result() does less work than mysql_store_result(), it imposes a requirement that mysql_store_result() does not: The client must call mysql_fetch_row() for every row in the result set. Otherwise, any remaining records in the set become part of the next query's result set and an "out of sync" error occurs. This does not happen with mysql_store_result() because when that function returns, all rows have already been fetched. In fact, with mysql_store_result(), you need not call mysql_fetch_row() yourself at all. This can be useful for queries for which all that you're interested in is whether you got a non-empty result, not what the result contains. For example, to find out whether or not a table my_tbl exists, you can execute this query:

SHOW TABLES LIKE "my_tbl"

If, after calling mysql_store_result(), the value of mysql_num_rows() is non-zero, the table exists. mysql_fetch_row() need not be called. (You still need to call mysql_free_result(), of course.)

If you want to provide maximum flexibility, give users the option of selecting either result set processing method. mysql and mysqldump are two programs that do this. They use mysql_store_result() by default but switch to mysql_use_result() if you specify the --quick option.

Using Result Set Metadata

Result sets contain not only the column values for data rows but also information about the data. This information is called the result set metadata, which includes:

• The number of rows and columns in the result set, available by calling mysql_num_rows() and mysql_num_fields().

• The length of each column value in a row, available by calling mysql_fetch_lengths().

• Information about each column, such as the column name and type, the maximum width of each column's values, and the table the column comes from. This information is stored in MYSQL_FIELD structures, which typically are obtained by calling mysql_fetch_field(). Appendix F describes the MYSQL_FIELD structure in detail and lists all functions that provide access to column information.

Metadata availability is partially dependent on your result set processing method. As indicated in the previous section, if you want to use the row count or maximum column length values, you must create the result set with mysql_store_result(), not with mysql_use_result().

Result set metadata is helpful for making decisions about how to process result set data:

• The column name and width information is useful for producing nicely formatted output that has column titles and lines up vertically.

• You use the column count to determine how many times to iterate through a loop that processes successive column values for data rows. You can use the row or column counts if you need to allocate data structures that depend on knowing the number of rows or columns in the result set.

• You can determine the data type of a column. This allows you to tell whether a column represents a number, whether it may contain binary data, and so forth.

Earlier, in the section "Handling Queries That Return Data," we wrote a version of process_result_set() that printed columns from result set rows in tab-delimited format. That's good for certain purposes (such as when you want to import the data into a spreadsheet), but it's not a nice display format for visual inspection or for printouts. Recall that our earlier version of process_result_set() produced output like this:

Adams  John  Braintree  MA
Adams  John Quincy Braintree  MA
Arthur Chester A. Fairfield  VT
Buchanan  James  Mercersburg PA
Bush  George W.  Milton MA
Carter James E. Jr Plains GA
Cleveland  Grover Caldwell  NJ
...

Let's make some changes to process_result_set() to produce tabular output by titling and "boxing" each column. The revised version will display those same results in a format that's easier to look at:

+------------+---------------+-------------+-------+
| last_name  |  first_name   |    city     | state |
+------------+---------------+-------------+-------+
| Adams      | John          | Braintree   | MA    |
| Adams      | John Quincy   | Braintree   | MA    |
| Arthur     | Chester A.    | Fairfield   | VT    |
| Buchanan   | James         | Mercersburg | PA    |
| Bush       | George W.     | Milton      | MA    |
| Carter     | James E., Jr. | Plains      | GA    |
| Cleveland  | Grover        | Caldwell    | NJ    |
| ...        | ...           | ...         | ...   |
+------------+---------------+-------------+-------+

The general outline of the display algorithm is as follows:

1. Determine the display width of each column.

2. Print a row of boxed column labels (delimited by vertical bars and preceded and followed by rows of dashes).

3. Print the values in each row of the result set, with each column boxed (delimited by vertical bars) and lined up vertically. In addition, print numbers right justified and print the word "NULL" for NULL values.

4. At the end, print a count of the number of rows retrieved.

This exercise provides a good demonstration of the use of result set metadata. To display output as just described, we need to know quite a number of things about the result set other than just the values of the data contained in the rows.

You may be thinking to yourself, "Hmm, that description sounds suspiciously similar to the way mysql displays its output." Yes, it does, and you're welcome to compare the source for mysql to the code we end up with for the revised process_result_set(). They're not the same, and you may find it instructive to compare two approaches to the same problem.

First, we need to determine the display width of each column. The following listing shows how to do this. Observe that the calculations are based entirely on the result set metadata, and they make no reference whatsoever to the row values:

MYSQL_FIELD   *field;
unsigned int  i, col_len;

/* determine column display widths */
mysql_field_seek (res_set, 0);
for (i = 0; i < mysql_num_fields (res_set); i++)
{
  field = mysql_fetch_field (res_set);
  col_len = strlen (field->name);
  if (col_len < field->max_length)
    col_len = field->max_length;
  if (col_len < 4 && !IS_NOT_NULL (field->flags))
    col_len = 4;  /* 4 = length of the word "NULL" */
  field->max_length = col_len;  /* reset column info */
}

Column widths are calculated by iterating through the MYSQL_FIELD structures for the columns in the result set. We position to the first structure by calling mysql_fetch_seek(). Subsequent calls to mysql_fetch_field() return pointers to the structures for successive columns. The width of a column for display purposes is the maximum of three values, each of which depends on metadata in the column information structure:

• The length of field->name, the column title.

• field->max_length, the length of the longest data value in the column.

• The length of the string "NULL" if the column can contain NULL values. field->flags indicates whether or not the column can contain NULL.

Notice that after the display width for a column is known, we assign that value to max_length, which is a member of a structure that we obtain from the client library. Is that allowable, or should the contents of the MYSQL_FIELD structure be considered read-only? Normally, I would say "read-only," but some of the client programs in the MySQL distribution change the max_length value in a similar way, so I assume it's okay. (If you prefer an alternative approach that doesn't modify max_length, allocate an array of unsigned int values and store the calculated widths in that array.)

The display width calculations involve one caveat. Recall that max_length has no meaning when you create a result set using mysql_use_result(). Because we need max_length to determine the display width of the column values, proper operation of the algorithm requires that the result set be generated using mysql_store_result().¹

Once we know the column widths, we're ready to print. Titles are easy to handle; for a given column, we simply use the column information structure pointed to by field and print the name member, using the width calculated earlier:

printf (" %-*s |", field->max_length, field->name);

For the data, we loop through the rows in the result set, printing column values for the current row during each iteration. Printing column values from the row is a bit tricky because a value might be NULL, or it might represent a number (in which case we print it right justified). Column values are printed as follows, where row[i] holds the data value and field points to the column information:

if (row[i] == NULL)
  printf (" %-*s |", field->max_length, "NULL");
else if (IS_NUM (field->type))
  printf (" %*s |", field->max_length, row[i]);
else
  printf (" %-*s |", field->max_length, row[i]);

The value of the IS_NUM() macro is true if the column type indicated by field->type is a numeric type such as INT, FLOAT, or DECIMAL.

The final code to display the result set looks like this. Note that because we're printing lines of dashes multiple times, code to do that is encapsulated into its own function, print_dashes():

void
print_dashes (MYSQL_RES *res_set)
{
MYSQL_FIELD   *field;
unsigned int  i, j;

  mysql_field_seek (res_set, 0);
  fputc ('+', stdout);
  for (i = 0; i < mysql_num_fields (res_set); i++)
  {
    field = mysql_fetch_field (res_set);
    for (j = 0; j < field->max_length + 2; j++)
      fputc ('-', stdout);
    fputc ('+', stdout);
  }
  fputc ('\n', stdout);
}

void
process_result_set (MYSQL *conn, MYSQL_RES *res_set)
{
MYSQL_FIELD   *field;
MYSQL_ROW    row;
unsigned int  i, col_len;

  /* determine column display widths */
  mysql_field_seek (res_set, 0);
  for (i = 0; i < mysql_num_fields (res_set); i++)
  {
    field = mysql_fetch_field (res_set);
    col_len = strlen (field->name);
    if (col_len < field->max_length)
      col_len = field->max_length;
    if (col_len < 4 && !IS_NOT_NULL (field->flags))
      col_len = 4;  /* 4 = length of the word "NULL" */
    field->max_length = col_len;  /* reset column info */
  }

  print_dashes (res_set);
  fputc ('|', stdout);
  mysql_field_seek (res_set, 0);
  for (i = 0; i < mysql_num_fields (res_set); i++)
  {
    field = mysql_fetch_field (res_set);
    printf (" %-*s |", field->max_length, field->name);
  }
  fputc ('\n', stdout);
  print_dashes (res_set);

  while ((row = mysql_fetch_row (res_set)) != NULL)
  {
    mysql_field_seek (res_set, 0);
    fputc ('|', stdout);
    for (i = 0; i < mysql_num_fields (res_set); i++)
    {
      field = mysql_fetch_field (res_set);
      if (row[i] == NULL)
        printf (" %-*s |", field->max_length, "NULL");
      else if (IS_NUM (field->type))
        printf (" %*s |", field->max_length, row[i]);
      else
        printf (" %-*s |", field->max_length, row[i]);
    }
    fputc ('\n', stdout);
  }
  print_dashes (res_set);
  printf ("%lu rows returned\n", (unsigned long) mysql_num_rows (res_set));
}

The MySQL client library provides several ways of accessing the column information structures. For example, the code in the preceding example accesses these structures several times using loops of the following general form:

mysql_field_seek (res_set, 0);
for (i = 0; i < mysql_num_fields (res_set); i++)
{
  field = mysql_fetch_field (res_set);
  ...
}

However, the mysql_field_seek() / mysql_fetch_field() combination is only one way of getting MYSQL_FIELD structures. See the entries for the mysql_fetch_fields() and mysql_fetch_field_direct() functions in Appendix F for other ways of getting column information structures.