Processing Queries | High Performance MySQL: Optimization, Backups, Replication, and More

The purpose of connecting to the server is to conduct a conversation with it while the connection is open. This section shows how to communicate with the server to process queries. Each query you run involves the following steps:

Construct the query. The way you do this depends on the contents of the query in particular, whether it contains binary data.
Issue the query by sending it to the server. The server will execute the query and generate a result.
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 treated as a counted string rather than as a null-terminated string, it can contain anything, including binary data or null bytes.

The other query-issuing function, mysql_query(), is more restrictive in what it allows in the query string but often is easier to use. Any query passed to mysql_query() should be a null-terminated string, which means it cannot contain null bytes in the text of the query. (The presence of null bytes within the query string will cause 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. This is necessary 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 the "Encoding Problematic Data in Queries" section later in this chapter.

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 a table referred to by the query.

Queries can be grouped into two broad categories those that do not return a result set (a set of rows) and those that do. Queries for statements such as INSERT, DELETE, and UPDATE fall into the "no result set 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 set returned" category; after all, the purpose of issuing those statements is to get something back. In the MySQL C API, the result set returned by such statements is represented by the MYSQL_RES data type. This is 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). Is it legal for a result set to be empty (that is, to contain zero rows).

Handling Queries That Return No Result Set

To process a query that does not return a result set, issue it with mysql_query() or mysql_real_query(). If the query succeeds, you can determine 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 (conn, "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 principle 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 request that behavior when you connect to the server by passing a value of CLIENT_FOUND_ROWS in the flags parameter 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. Statements such as SHOW, DESCRIBE, EXPLAIN, and CHECK TABLE do so as well. For all of these statements, you must perform additional row-handling processing after issuing the query.

Handling a result set involves the following 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 retrieves 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 or NULL when there are no more rows. A MYSQL_ROW value 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.
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 sampdb") != 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);     } }

The example hides the details of result set processing within another function, process_result_set(). We haven't defined that function yet, so we need to do so. Generally, operations that handle a result set are based on a loop that looks something like this:

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

mysql_fetch_row() returns a MYSQL_ROW value, which is a pointer to an array of values. If the return value is assigned to a variable named row, each value within the row can be accessed as 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 a variable of that type as MYSQL_ROW row, not as MYSQL_ROW *row.
Values for all data types, even numeric types, are returned in the MYSQL_ROW array as strings. If you want to treat a value as a number, you must convert the string yourself.
The strings in a MYSQL_ROW array are null-terminated. However, if a column may contain binary data, it can 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. (The "Using Result Set Metadata" section later in this chapter discusses how to determine column lengths.)
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 values for that column are NULL or your program may crash by attempting to dereference a NULL pointer.

What you do with each row will depend on the purpose of your application. For purposes of illustration, let's just print the rows with column values separated by tabs. To do that, it's necessary to know how many columns values rows contain. That information is returned by another client library function, mysql_num_fields().

The following is 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() displays the contents of each row in tab-delimited format (displaying NULL values as the word "NULL"), and then prints 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 you should cast its value to unsigned long and use a %lu format to print it. But note that unlike mysql_affected_rows(), which takes a connection handler argument, mysql_num_rows() takes a result set pointer as its argument.

The code that follows the loop includes an error test. That's just a precautionary measure. 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. Because process_result_set() has no idea whether its caller used mysql_store_result() or mysql_use_result() to generate the result set, the error test allows it to detect errors properly either way.

The version of process_result_set() just shown takes a rather minimalist approach to printing column values one that has certain shortcomings. For example, suppose you execute the following query:

 SELECT last_name, first_name, city, state FROM president  ORDER BY last_name, first_name

You will receive the following output, which is not so easy to read:

 Adams   John    Braintree   MA  Adams   John Quincy Braintree   MA Arthur  Chester A.  Fairfield   VT Buchanan    James   Mercersburg PA Bush    George H.W. Milton  MA Bush    George W.   New Haven   CT Carter  James E.    Plains  GA ...

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 "Using Result Set Metadata" section later in this chapter.

Printing Binary Data

Column values containing binary data that may include null bytes will not print properly using the %s printf() format specifier; printf() expects a null-terminated string and will print the column value only up to the first null byte. For binary data, it's best to use the column length so that you can print the full value. For example, you could use fwrite().

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 may not always know what kind of statement a given query represents. For example, if you execute a query that you read from the keyboard or from a file, it might be anything. You won't know ahead of time whether or not to expect it to return rows, or even whether it's legal. 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 see if the first word is SELECT, because the statement 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, and whether or not it executes successfully. Before writing the code for this handler, let's outline the procedure that it implements:

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() succeeds, the query returned a result set. Process the rows by calling mysql_fetch_row() until it returns NULL, and then free the result set.
If mysql_store_result() fails, it could be that the query does not return a result set, or that it should have but 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 return value:
- If mysql_field_count() returns 0, it means the query returned no columns, and thus no result set. (This indicates the query was a statement such as INSERT, DELETE, or UPDATE).
- If mysql_field_count() returns a non-zero value, it means that an error occurred, because 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 so large that memory allocation failed, or a network outage between the client and the server may have occurred while fetching rows.

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

 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, "Could not execute query");         return;     }     /* the query succeeded; determine whether or not it returns data */     res_set = mysql_store_result (conn);     if (res_set)            /* a result set was returned */     {         /* process rows, then free the result set */         process_result_set (conn, res_set);         mysql_free_result (res_set);     }     else                    /* no result set was returned */     {         /*          * does the lack of a result set mean that the query didn't          * return one, or that it should have but an error occurred?          */         if (mysql_field_count (conn) == 0)         {             /*              * query generated no result set (it was not a SELECT, SHOW,              * DESCRIBE, etc.), so just report number of rows affected              */             printf ("%lu rows affected\n",                         (unsigned long) mysql_affected_rows (conn));         }         else    /* an error occurred */         {             print_error (conn, "Could not retrieve result set");         }     } }

A slight complication to this procedure is that mysql_field_count() doesn't exist prior to MySQL 3.22.24. The workaround for earlier versions is to call mysql_num_fields() instead. To write programs that work with any version of MySQL, include the following code fragment in your source file after including mysql.h and before invoking mysql_field_count():

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

The #define converts calls to mysql_field_count() into invocations of mysql_num_fields() for versions of MySQL earlier than 3.22.24.

Alternative Approaches to Query Processing

The version of process_query() just shown has the following 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() or mysql_errno() 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(). The following 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, "Could not execute query");         return;     }     /* the query succeeded; determine whether or not it returns data */     res_set = mysql_use_result (conn);     if (res_set)            /* a result set was returned */     {         /* process rows, then free the result set */         process_result_set (conn, res_set);         mysql_free_result (res_set);     }     else                    /* no result set was returned */     {         /*          * does the lack of a result set mean that the query didn't          * return one, or that it should have but an error occurred?          */         if (mysql_errno (conn) == 0)         {             /*              * query generated no result set (it was not a SELECT, SHOW,              * DESCRIBE, etc.), so just report number of rows affected              */             printf ("%lu rows affected\n",                         (unsigned long) mysql_affected_rows (conn));         }         else    /* an error occurred */         {             print_error (conn, "Could not retrieve result set");         }     } }

mysql_store_result() and mysql_use_result() Compared

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. 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. Consequently, 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 may mean instead 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 in a single result set, 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 will be 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 have access to 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; similarly, 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. If you fail to do this before issuing another query, any remaining records in the current result set become part of the next query's result set and an "out of sync" error occurs. (You can avoid this by calling mysql_free_result() before issuing the second query. mysql_free_result() will fetch and discard any pending rows for you.) One implication of this processing model is that with mysql_use_result() you can work only with a single result set at a time.

Sync errors do not happen with mysql_store_result() because when that function returns, there are no rows yet to be fetched from the server. In fact, with mysql_store_result(), you need not call mysql_fetch_row() explicitly at all. This can sometimes be useful if all that you're interested in is whether you got a non-empty result rather than what the result contains. For example, to find out whether a table mytbl exists, you can execute the following query:

 SHOW TABLES LIKE 'mytbl'

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.

Result sets generated with mysql_store_result() should be freed with mysql_free_result() at some point, but this need not necessarily be done before issuing another query. This means that you can generate multiple result sets and work with them simultaneously, in contrast to the "one result set at a time" constraint imposed when you're working with mysql_use_result().

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 the current 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:

Column names and widths are useful for producing nicely formatted output that has column titles and that 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 dimensions of 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 contains binary data, and so forth.

Earlier, in the "Handling Queries That Return Data" section, 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 this output:

 Adams   John    Braintree   MA  Adams   John Quincy Braintree   MA Arthur  Chester A.  Fairfield   VT Buchanan    James   Mercersburg PA Bush    George H.W. Milton  MA Bush    George W.   New Haven   CT Carter  James E.    Plains  GA ...

Let's write a different version of process_result_set() that produces tabular output instead by titling and "boxing" each column. This 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 H.W.   | Milton              | MA    | | Bush       | George W.     | New Haven           | CT    | | Carter     | James E.      | Plains              | GA    | ... +------------+---------------+---------------------+-------+

The general outline of the display algorithm is as follows:

Determine the display width of each column.
Print a row of boxed column labels (delimited by vertical bars and preceded and followed by rows of dashes).
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.
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 because it requires knowledge of quite a number of things about the result set other than just the values of the data contained in its 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 process_result_set(). They're not the same, and you may find it instructive to compare the two approaches to the same problem.

First, it's necessary 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 make no reference whatsoever to the row values:

 MYSQL_FIELD     *field;  unsigned long   col_len; unsigned int    i; /* determine column display widths -- requires result set to be */ /* generated with mysql_store_result(), not mysql_use_result() */ 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 */ }

This code calculates column widths by iterating through the MYSQL_FIELD structures for the columns in the result set. We position to the first structure by calling mysql_field_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 long 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(). In programs that use mysql_use_result() rather than mysql_store_result(), one possible workaround is to use the length member of the MYSQL_FIELD structure, which tells you the maximum length that column values can be.

When 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 |", (int) 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)             /* print the word "NULL" */      printf (" %-*s |", (int) field->max_length, "NULL"); else if (IS_NUM (field->type))  /* print value right-justified */     printf (" %*s |", (int) field->max_length, row[i]); else                            /* print value left-justified */     printf (" %-*s |", (int) field->max_length, row[i]);

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

The final code to display the result set is as follows. Note that because we're printing lines of dashes multiple times, it's easier to write a print_dashes() function to do so rather than to repeat the dash-generation code several places:

 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_ROW       row; MYSQL_FIELD     *field; unsigned long   col_len; unsigned int    i;     /* determine column display widths -- requires result set to be */     /* generated with mysql_store_result(), not mysql_use_result() */     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 |", (int) 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)             /* print the word "NULL" */                 printf (" %-*s |", (int) field->max_length, "NULL");             else if (IS_NUM (field->type))  /* print value right-justified */                 printf (" %*s |", (int) field->max_length, row[i]);             else                            /* print value left-justified */                 printf (" %-*s |", (int) 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.