- General Procedure for Building Client Programs
- Client 1—Connecting to the Server
- Client 2—Adding Error Checking
- Client 3—Making the Connection Code Modular
- Client 4—Getting Connection Parameters at Runtime
- Processing Queries
- Client 5—An Interactive Query Program
- Miscellaneous Topics
This chapter is from the book
This chapter is from the book
This chapter is from the book
Client 2—Adding Error Checking
Our second client will be like the first one, but it will be modified to take into account the possibility of errors occurring. It seems to be fairly common in programming texts to say "Error checking is left as an exercise for the reader," probably because checking for errors is—let's face it—such a bore. Nevertheless, I prefer to promote the view that MySQL client programs should test for error conditions and respond to them appropriately. The client library calls that return status values do so for a reason, and you ignore them at your peril. You end up trying to track down obscure problems that occur in your programs due to failure to check for errors, users of your programs wonder why those programs behave erratically, or both.
Consider our program, client1. How do you know whether or not it really connected to the server? You could find out by looking in the server log for Connect and Quit events corresponding to the time at which you ran the program:
990516 21:52:14 20 Connect paul@localhost on
20 Quit
Alternatively, you might see an Access denied message instead:
990516 22:01:47 21 Connect Access denied for user: 'paul@localhost'
(Using password: NO)
This message indicates that no connection was established at all. Unfortunately, client1 doesn't tell us which of these outcomes occurred. In fact, it can't. It doesn't perform any error checking, so it doesn't even know itself what happened. In any case, you certainly shouldn't have to look in the log to find out whether or not you were able to connect to the server! Let's fix that right away.
Routines in the MySQL client library that return a value generally indicate success or failure in one of two ways:
-
Pointer-valued functions return a non-NULL pointer for success and NULL for failure. (NULL in this context means "a C NULL pointer," not "a MySQL NULL column value.")
-
Integer-valued functions commonly return 0 for success and non-zero for failure. It's important not to test for specific non-zero values, such as -1. There is no guarantee that a client library function returns any particular value when it fails. On occasion, you may see older code that tests a return value incorrectly like this:
Of the client library routines we've used so far, mysql_init() and mysql_real_connect() both return a pointer to the connection handler to indicate success and NULL to indicate failure.
if (mysql_XXX() == -1) /* this test is incorrect */ fprintf (stderr, "something bad happened\n");
This test might work, and it might not. The MySQL API doesn't specify that any non-zero error return will be a particular value, other than that it (obviously) isn't zero. The test should be written either like this:
if (mysql_XXX()) /* this test is correct */ fprintf (stderr, "something bad happened\n");
or like this:
if (mysql_XXX() != 0) /* this test is correct */ fprintf (stderr, "something bad happened\n");
The two tests are equivalent. If you look through the source code for MySQL itself, you'll find that generally it uses the first form of the test, which is shorter to write.
Not every API call returns a value. The other client routine we've used, mysql_close(), is one that does not. (How could it fail? And if it did, so what? You were done with the connection, anyway.)
When a client library call fails and you need more information about the failure, two calls in the API are useful. mysql_error() returns a string containing an error message, and mysql_errno() returns a numeric error code. You should call them right after an error occurs because if you issue another API call that returns a status, any error information you get from mysql_error() or mysql_errno() will apply to the later call instead.
Generally, the user of a program will find the error string more enlightening than the error code. If you report only one of the two, I suggest it be the string. For completeness, the examples in this chapter report both values.
Taking the preceding discussion into account, we'll write our second client, client2. It is similar to client1, but with proper error-checking code added. The source file, client2.c, looks like this:
/* client2.c */
#include <stdio.h>
#include <mysql.h>
#define def_host_name NULL /* host to connect to (default = localhost) */
#define def_user_name NULL /* user name (default = your login name) */
#define def_password NULL /* password (default = none) */
#define def_db_name NULL /* database to use (default = none) */
MYSQL *conn; /* pointer to connection handler */
int
main (int argc, char *argv[])
{
conn = mysql_init (NULL);
if (conn == NULL)
{
fprintf (stderr, "mysql_init() failed (probably out of memory)\n");
exit (1);
}
if (mysql_real_connect (
conn, /* pointer to connection handler */
def_host_name, /* host to connect to */
def_user_name, /* user name */
def_password, /* password */
def_db_name, /* database to use */
0, /* port (use default) */
NULL, /* socket (use default) */
0) /* flags (none) */
== NULL)
{
fprintf (stderr, "mysql_real_connect() failed:\nError %u (%s)\n",
mysql_errno (conn), mysql_error (conn));
exit (1);
}
mysql_close (conn);
exit (0);
}
The error-checking logic is based on the fact that both mysql_init() and mysql_real_connect() return NULL if they fail. Note that although the program checks the return value of mysql_init(), no error-reporting function is called if it fails. That's because the connection handler cannot be assumed to contain any meaningful information when mysql_init() fails. By contrast, if mysql_real_connect() fails, the connection handler doesn't reflect a valid connection, but does contain error information that can be passed to the error-reporting functions. (Don't pass the handler to any other client routines, though! Because they generally assume a valid connection, your program may crash.)
Compile and link client2, and then try running it:
% client2
If client2 produces no output (as just shown), it connected successfully. On the other hand, you might see something like this:
% client2 mysql_real_connect() failed: Error 1045 (Access denied for user: 'paul@localhost' (Using password: NO))
This output indicates no connection was established, and lets you know why. It also means that our first program, client1, never successfully connected to the server, either! (After all, client1 used the same connection parameters.) We didn't know it then because client1 didn't bother to check for errors. client2 does check, so it can tell us when something goes wrong. That's why you should always test API function return values.
Answers to questions on the MySQL mailing list often have to do with error checking. Typical questions are "Why does my program crash when it issues this query?" or "How come my query doesn't return anything?" In many cases, the program in question didn't check whether or not the connection was established successfully before issuing the query or didn't check to make sure the server successfully executed the query before trying to retrieve the results. Don't make the mistake of assuming that every client library call succeeds.
The rest of the examples in this chapter perform error checking, and you should, too. It might seem like more work, but in the long run it's really less because you spend less time tracking down subtle problems. I'll also take this approach of checking for errors in Chapters 7, "The Perl DBI API," and 8, "The PHP API."
Now, suppose you did see an Access denied message when you ran the client2 program. How can you fix the problem? One possibility is to change the #define lines for the hostname, username, and password to values that allow you to access your server. That might be beneficial, in the sense that at least you'd be able to make a connection. But the values would still be hardcoded into your program. I recommend against that approach, especially for the password value. You might think that the password becomes hidden when you compile your program into binary form, but it's not hidden at all if someone can run strings on the program. (Not to mention the fact that anyone with read access to your source file can get the password with no work at all.)
We'll deal with the access problem in the section "Client 4—Getting Connection Parameters at Runtime." First, I want to show some other ways of writing your connection code.