Handling Resultsets

Although the program in Listing 13.4 retrieves and tests a resultset from a query, it doesn't do anything with it. We need some functions for processing the data in a resultset.

You saw that you can use mysql_store_result() or mysql_use_result() to fetch the result of your query. Whichever you use, you can access one row of that data at a time using the function mysql_fetch_row().

The syntax for mysql_fetch_row() is as follows:

mysql_fetch_row (*result)

It returns the next row of data in the datatype MYSQL_ROW. Each time the function is called, it returns the next row, until there are no more rows, whereupon it returns NULL.

If you use mysql_store_result(), you have the advantage of some extra functions that let you move backward and forward within the resultset. These commands and their syntax are

mysql_data_seek (*result, offset)

mysql_row_seek (*result, offset)

in which *result is a resultset of the datatype MYSQL_RES, and offset is an unsigned long integer indicating the number of the row in the resultset to be retrieved, between 0 and mysql_num_rows(result)-1.

mysql_num_rows() is used with this syntax:

mysql_num_rows (*result)

and it returns the number of rows in a resultset (in datatype my_ulonglong). If you use mysql_store_result(), this number is available immediately; but if you use mysql_use_result(), it returns the correct value only when all rows have been retrieved with mysql_fetch_row().

Modifying the Script to Process a Resultset

In Listing 13.4, we fetched a resultset but didn't do anything with it. Now we'll do some processing to go through the resultset and print it to the screen.

The program will now define a new function, print_result_set(). This requires a prototype in the shared header file, so the following lines need to be appended to common.h:

void print_result_set (
    MYSQL *mysql,
    MYSQL_RES *res_set);

In Listing 13.5, look out for the addition of the print_result_set() function.

Listing 13.5. main.c

 1: /* main.c */
 2: /* Program to connect to database, run a query,  */
 3: /* fetch and display the result set */
 4:
 5: #include <stdio.h>
 6: #include <mysql.h>
 7: #include "common.h"
 8:
 9: #define def_db_host     NULL
10: #define def_db_user     NULL
11: #define def_db_name     NULL
12: #define def_db_port     0
13: #define def_unix_socket NULL
14:
15: MYSQL   *mysql;
16:
17: int main (int argc, char *argv[])
18: {
19: MYSQL_RES   *res_set;
20: char        *db_pass;
21:
22:     db_pass = get_tty_password (NULL);
23:
24:     mysql = db_connect (
25:         def_db_host,
26:         def_db_user,
27:         db_pass,
28:         def_db_name,
29:         def_db_port,
30:         def_unix_socket,
31:         0);
32:     if (mysql == NULL) {
33:         exit (1);
34:     } else {
35:         fprintf (stdout, "Connected.
");
36:
37:         if (mysql_query (mysql, "SHOW DATABASES") == 0) {
38:             printf ("Query succeeded. About to fetch result.
");
39:             res_set = mysql_store_result (mysql);
40:
41:             if (res_set != NULL) {
42:                 /* Result set obtained, so process */
43:
44:                 print_result_set (mysql, res_set);
45:                 mysql_free_result (res_set);
46:
47:             } else {
48:                 /* No result set, see what happened */
49:                 if (mysql_field_count (mysql) > 0) {
50:                     /* Should have had some fields
51:                     * so an error must have occurred */
52:                     fprintf (stderr,
53:                         "No result set, mysql_store_result() failed: %s
",
54:                         mysql_error (mysql));
55:                 } else {
56:                     /* Field count is 0,
57:                     * so no result was expected from the query */
58:                     printf ("Query was not meant to return data.
");
59:                 }
60:             }
61:         } else {
62:             fprintf (stderr,
63:                 "Query failed: %s
", mysql_error (mysql));
64:         }
65:
66:     }
67:
68:     db_disconnect (mysql);
69:     exit (0);
70: }
71:
72: void print_result_set (MYSQL *mysql, MYSQL_RES *res_set)
73: {
74: unsigned int    f;
75: MYSQL_ROW       row;
76:
77:     while ((row = mysql_fetch_row (res_set)) != NULL) {
78:         f = 0;
79:         while (f<mysql_num_fields (res_set)) {
80:             if (f>0) fputc ('	', stdout);
81:             fprintf (stdout, "%s", row[f]);
82:             f++;
83:         }
84:         fputc ('
', stdout);
85:     }
86:
87:     if (mysql_errno (mysql) == 0)
88:         fprintf (stdout, "Number of rows returned: %lu.
",
89:             (unsigned long) mysql_num_rows (res_set));
90:     else
91:         fprintf (stderr, "mysql_fetch_row() failed.
");
92: }
						

In line 44 of main.c we call print_result_set(), which is defined in lines 72 through 92 of the code. Let's analyze what this code does.

When we call the function in line 44, we pass it the connection handle pointer and the resultset pointer. The function calls mysql_fetch_row() repeatedly (line 77) to get each row of data, each time placing it in the array row. The inner while loop (line 79) goes through each of the elements of row until it reaches the last one (corresponding to the last column), the last element number being referenced by mysql_num_fields() less one.

When the outer while loop finishes (line 85), the result of mysql_fetch_row() will be NULL. However, after exiting the loop, we don't know whether the NULL resulted from an error occurring or simply from reaching the last row. If an error occurred, we should handle it, so line 87 tests the value of mysql_errno(). If it's zero, there was no error, and lines 88–89 report the number of rows returned, from mysql_num_rows(). Otherwise, line 91 reports the error.

After compiling your program with make, run your program. You may get a dialog like this:

$ ./myapp
Enter password:
Connected.
Query succeeded. About to fetch result.
store
contacts
mysql
test
Number of rows returned: 4.

As you can see, the program asked for my password and then ran the query to display the names of all the MySQL databases on my system. You can start experimenting with this script—for example, by changing the database name in line 11 (for example, put the database name "store" rather than NULL) and changing the SQL in line 37 to run some SELECT statements that return multiple columns.