Running Queries That Return a Resultset

When a program has a live connection to a MySQL database, two functions are available for running a query: mysql_query() and mysql_real_query().

You can use these functions to run any kind of query: those that return a resultset (such as the SQL statements SELECT, DESCRIBE, and SHOW) and those that don't (such as INSERT, UPDATE, and DELETE). Initially we'll concentrate on queries that return a resultset, so you'll see how to run the query and process the data that is returned.

mysql_query() expects a string terminated by a null, whereas mysql_real_query() takes a counted string. For our simple examples, you can use mysql_query(); however, if you need to create a query that contains binary data (which may contain null bytes), you must use mysql_real_query().

The syntax for these commands is

mysql_query (*mysql, *query)

mysql_real_query (*mysql, *query, length)

where *mysql is a database handle, *query is the SQL, and unsigned integer length is the length in bytes of the query. Both return an integer result: zero for success, or non-zero if there was an error. (The result is not an indication of whether a resultset was returned.)

For example, you might run a query like this:

mysql_query (mysql, "SHOW DATABASES")

In a moment, we'll incorporate this line into our program. But first, let's look at the functions for processing a resultset.

Therec are two functions: mysql_store_result() and mysql_use_result(). Their syntax follows:

mysql_store_result (*mysql)

mysql_use_result (*mysql)

where *mysql is a database handle. We'll look at the behavior of these two functions in a moment, but for the time being, we'll assume that we're using mysql_store_result().

If a resultset is fetched (even with no rows of data!), a structure of the datatype MYSQL_RES will be returned. In other words, even if you run a query like SELECT * FROM mytable WHERE 0, a MYSQL_RES structure will be returned.

If a NULL is returned, this can be because the query wasn't meant to return data: It could have been an INSERT or DELETE, for example. Alternatively, it could be NULL because mysql_store_result() failed. So how do you tell?

To find out whether the query should have returned some data, use mysql_field_count(). Its syntax is

mysql_field_count (*mysql)

It returns an integer indicating the number of columns retrieved as a result of the query. If it's non-zero, it indicates that a resultset was expected (so if mysql_store_result() returned NULL and a resultset was expected, something went wrong!). But if it returns zero, it's because no resultset was expected.

Let's put this knowledge together into some code that should help clarify things:

if (mysql_query (mysql, "SHOW DATABASES") == 0) {
    printf ("Query succeeded. About to fetch result.
");
    res_set = mysql_store_result (mysql);
    if (res_set != NULL) {
        /* Result set obtained, so process */
        /* Do something with the result set */

    } else {
        /* No result set, see what happened */
        if (mysql_field_count (mysql) > 0) {
            /* Should have had some fields
            * so an error must have occurred */
            fprintf (stderr,
                "No result set, mysql_store_result() failed: %s
",
                mysql_error (mysql));
            } else {
                /* Field count is 0,
                * so no result was expected from the query */
                printf ("Query was not meant to return data.
");
            }
    }
} else {
    fprintf (stderr,
    "Query failed: %s
", mysql_error(mysql));
}

As you can see, we run the query with mysql_query() and test the result for success or failure of the query itself. If successful, we do mysql_store_result(). If it returns a resultset (not NULL), we process it (you'll see how to do this in a moment). But if there's no resultset, we have to find out why.

So we test mysql_field_count() to see what value it has. A zero value means that the query wasn't meant to return a resultset (this would be the case with a DELETE, INSERT, and so on). But a non-zero result means that some fields were expected and something went wrong with mysql_store_result().

You don't have to write your code in a way that appears ignorant of the SQL it's running! However, it can be useful to use this technique, especially if you're writing a client program that allows the user to write his own SQL. Such a program (such as the mysql monitor) needs to be resilient and handle any result that may be produced.

More About mysql_store_result() and mysql_use_result()

These two result-processing functions work in slightly different ways. mysql_store_result() retrieves the entire resultset at once; in other words, it fetches all the rows of data that result from the query. mysql_use_result(), on the other hand, only initializes the retrieval, which must then be done row-by-row, using the function mysql_fetch_row().

Of the two functions, mysql_store_result() is the most memory-hungry in its demands on the client, because it fetches a potentially large two-dimensional array into memory on the client system. mysql_use_result() is less burdensome on the client; however, it causes the entire resultset to be loaded into the server's memory, and the data will stay there until the last row has been fetched by the client.

mysql_use_result() is therefore a less than ideal choice on a database server that may serve multiple clients. Although mysql_store_result() shifts the memory demands to the client, it is in general a better choice and is the most commonly used as a result.