Home > Articles > Data > MySQL

This chapter is from the book

This chapter is from the book

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."

Printing Binary Data

Column values that contain 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. You could use fwrite() or putc(), for example.

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().1

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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020