Client 2Adding Error Checking

Client 2 Adding Error Checking

Our second client will be like the first one, but it will be modified to take into account the fact that errors may occur. 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, it is much better for MySQL client programs to test for error conditions and respond to them appropriately. The client library functions that return status values do so for a reason, and you ignore them at your peril; you'll end up trying to track down obscure problems that occur in your programs due to failure to check for errors, or users of your programs will wonder why those programs behave erratically, or both.

Consider our first program, client1. How do you know whether 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:

 020816 21:52:14      20 Connect    sampadm@localhost on                      20 Quit 

Alternatively, you might see an Access denied message instead, which indicates that no connection was established at all:

 020816 22:01:47      21 Connect    Access denied for user: 'sampadm@localhost'                                    (Using password: NO) 

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. That is unacceptable. You certainly shouldn't have to look in the server's log to find out whether you were able to connect to it! Let's fix this problem right away by adding some error checking.

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

  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.

• 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 code that tests a return value from a C API function mysql_XXX() incorrectly, like this:

   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() != 0)        /* this test is correct */      fprintf (stderr, "something bad happened\n"); 

  or like this, which is equivalent, and slightly simpler to write:

   if (mysql_XXX())            /* this test is correct */      fprintf (stderr, "something bad happened\n"); 

If you look through the source code for MySQL itself, you'll find that generally it uses the second form of the test.

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 does fail, two calls in the API are useful for finding out why. mysql_error() returns a string containing an error message, and mysql_errno() returns a numeric error code. The argument to both functions is a pointer to the connection handler. You should call them right after an error occurs; 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, so if you report only one of the two values, I suggest it be the string. For completeness, the examples in this chapter report both values.

Taking the preceding discussion into account, we can write our second client program, client2, which is similar to client1 but has proper error-checking code added. The source file, client2.c, is as follows:

 /*   * client2.c - connect to and disconnect from MySQL server,  * with error-checking  */ #include <my_global.h> #include <mysql.h> static char *opt_host_name = NULL;      /* server host (default=localhost) */ static char *opt_user_name = NULL;      /* username (default=login name) */ static char *opt_password = NULL;       /* password (default=none) */ static unsigned int opt_port_num = 0;   /* port number (use built-in value) */ static char *opt_socket_name = NULL;    /* socket name (use built-in value) */ static char *opt_db_name = NULL;        /* database name (default=none) */ static unsigned int opt_flags = 0;      /* connection flags (none) */ static MYSQL *conn;                     /* pointer to connection handler */ int main (int argc, char *argv[]) {     /* initialize connection handler */     conn = mysql_init (NULL);     if (conn == NULL)     {         fprintf (stderr, "mysql_init() failed (probably out of memory)\n");         exit (1);     }     /* connect to server */     if (mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password,               opt_db_name, opt_port_num, opt_socket_name, opt_flags) == NULL)     {         fprintf (stderr, "mysql_real_connect() failed:\nError %u (%s)\n",                             mysql_errno (conn), mysql_error (conn));         mysql_close (conn);         exit (1);     }     /* disconnect from server */     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 still won't contain information that corresponds to a valid connection, but it will contain diagnostic information that can be passed to the error-reporting functions. The handler can also be passed to mysql_close() to release any memory that may have been allocated automatically for it by mysql_init(). (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: 'sampadm@localhost' (Using password: NO)) 

This output indicates no connection was established, and it lets you know why. It also means that the 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.

Knowing about problems is better than not knowing, which is why you should test API function return values. Failure to do so is an unnecessary cause of programming difficulties. This phenomenon plays itself out frequently on the MySQL mailing list. 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. And when a program doesn't check for errors, the programmer ends up confused. Don't make the mistake of assuming that every client library call succeeds.

The rest of the programs in this chapter perform error checking, and your own programs 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 Chapter 7, "The Perl DBI API," and Chapter 8, "The PHP API."

Now, suppose you do see an Access denied message when you run the client2 program. How can you fix the problem? One possibility is to recompile the program after modifying the source code to change the initializers for the connection parameters 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 hard-coded 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 executable form, but it's not hidden at all if someone can run the strings utility on the binary. Not to mention the fact that anyone with read access to the source file can get the password with no work at all.)

In the next section, we'll develop more flexible methods of indicating how to connect to the server. But first I want to develop a simpler method for reporting errors, because that's something we'll need to be ready to do often. I will continue to use the style of reporting both the MySQL numeric error code and the descriptive error string when errors occur, but I prefer not to write out the calls to the error functions mysql_errno() and mysql_error() like this each time:

 if (...some MySQL function fails...)  {     fprintf (stderr, "...some error message...:\nError %u (%s)\n",                         mysql_errno (conn), mysql_error (conn)); } 

It's easier to report errors by using a utility function that can be called like this instead:

 if (...some MySQL function fails...)  {     print_error (conn, "...some error message..."); } 

print_error() prints the error message and calls the MySQL error functions automatically. It's easier to write out the print_error() call than a long fprintf() call, and it also makes the program easier to read. Also, if print_error() is written to do something sensible even when conn is NULL, we can use it under circumstances such as when mysql_init() call fails. Then we won't have a mix of error-reporting calls some to fprintf() and some to print_error(). A version of print_error() that satisfies this description can be written as follows:

 void  print_error (MYSQL *conn, char *message) {     fprintf (stderr, "%s\n", message);     if (conn != NULL)     {         fprintf (stderr, "Error %u (%s)\n",                 mysql_errno (conn), mysql_error (conn));     } } 

I can hear someone in the back row objecting, "Well, you don't really have to call both error functions every time you want to report an error, so you're deliberately overstating the tedium of reporting errors that way just so your utility function looks more useful. And you wouldn't really write out all that error-printing code a bunch of times anyway; you'd write it once, and then use copy and paste when you need it again." Those are reasonable objections, but I would address them as follows:

• Even if you use copy and paste, it's easier to do so with shorter sections of code.

• Whether or not you prefer to invoke both error functions each time you report an error, writing out all the error-reporting code the long way leads to the temptation to take shortcuts and be inconsistent when you do report errors. Wrapping the error-reporting code in a utility function that's easy to invoke lessens this temptation and improves coding consistency.

• If you ever do decide to modify the format of your error messages, it's a lot easier if you only need to make the change one place rather than throughout your program. Or, if you decide to write error messages to a log file instead of (or in addition to) writing them to stderr, it's easier if you only have to change print_error(). This approach is less error prone and, again, lessens the temptation to do the job halfway and be inconsistent.

• If you use a debugger when testing your programs, putting a breakpoint in the error-reporting function is a convenient way to have the program break to the debugger when it detects an error condition.

For these reasons, programs in the rest of this chapter will use print_error() to report MySQL-related problems.