Client 4?An Interactive Query Program

Let's put together much of what we've developed so far and use it to write a simple interactive client, client4. This program lets you enter queries, executes them using our general purpose query handler process_query(), and displays the results using the process_result_set() display formatter developed in the preceding section.

client4 will be similar in some ways to mysql, although of course not with as many features. There are several restrictions on what client4 will allow as input:

  • Each input line must contain a single complete statement.

  • Statements should not be terminated by a semicolon or by \g.

  • The only non-SQL commands that are recognized are quit and \q, which terminate the program. You can also use Ctrl-D to quit.

It turns out that client4 is almost completely trivial to write (about a dozen lines of new code). Almost everything we need is provided by our client program skeleton (client3.c) and by other functions that we have written already. The only thing we need to add is a loop that collects input lines and executes them.

To construct client4, begin by copying the client skeleton client3.c to client4.c. Then add to that the code for the process_query(), process_result_set(), and print_dashes() functions. Finally, in client4.c, look for the line in main() that says this:

/* ... issue queries and process results here ... */ 

Replace that line with the following while loop:

while (1) 
{
    char    buf[10000];

    fprintf (stderr, "query> ");                    /* print prompt */
    if (fgets (buf, sizeof (buf), stdin) == NULL)   /* read query */
        break;
    if (strcmp (buf, "quit\n") == 0 || strcmp (buf, "\\q\n") == 0)
        break;
    process_query (conn, buf);                      /* execute query */
}

Compile client4.c to produce client4.o, link client4.o with the client library to produce client4, and you're done. You have an interactive MySQL client program that can execute any query and display the results. The following example shows how the program works, both for SELECT and non-SELECT queries, as well as for statements that are erroneous:

% ./client4 
query> USE sampdb
0 rows affected
query> SELECT DATABASE(), USER()
+------------+-------------------+
| DATABASE() | USER()            |
+------------+-------------------+
| sampdb     | sampadm@localhost |
+------------+-------------------+
1 rows returned
query> SELECT COUNT(*) FROM president
+----------+
| COUNT(*) |
+----------+
|       42 |
+----------+
1 rows returned
query> SELECT last_name, first_name FROM president ORDER BY last_name LIMIT 3
+-----------+-------------+
| last_name | first_name  |
+-----------+-------------+
| Adams     | John        |
| Adams     | John Quincy |
| Arthur    | Chester A.  |
+-----------+-------------+
3 rows returned
query> CREATE TABLE t (i INT)
0 rows affected
query> SELECT j FROM t
Could not execute query
Error 1054 (Unknown column 'j' in 'field list')
query> USE mysql
Could not execute query
Error 1044 (Access denied for user: 'sampadm@localhost' to database 'mysql')