Handling Errors and Processing Command Options

Our next client, connect2, will be similar to connect1 in the sense that it connects to the MySQL server, disconnects, and exits. However, connect2 is modified in two important ways:

• It provides more information when errors occur. connect1 printed only a brief message if something went wrong. However, we can do a better job of error reporting because the MySQL client library includes functions that return specific information about the causes of errors.

• It allows the user to specify connection parameters as options on the command line or in option files.

Checking for Errors

Let's consider the topic of error-handling first. To start off, I want to emphasize this point: It's important to check for errors whenever you invoke a MySQL function that can fail. It seems to be fairly common in programming texts to say "Error checking is left as an exercise for the reader." I suppose that this is because checking for errors islet's face itsuch a bore. Nevertheless, it is necessary 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: For example, if a function returns a pointer to a data structure or NULL to indicate an error, you'd better check the return value. Attempts to use NULL later in the program when a pointer to a valid data structure is expected will lead to strange results or even crash your program.

Failure to check return values is an unnecessary cause of programming difficulties and is a phenomenon that plays itself out frequently on the MySQL mailing lists. Typical questions are "Why does my program crash when it issues this statement?" or "How come my query doesn't return anything?" In many cases, the program in question didn't check whether the connection was established successfully before issuing the statement or didn't check to make sure the server successfully executed the statement before trying to retrieve the results.

Don't make the mistake of assuming that every client library call succeeds. If you don't check return values, you'll end up trying to track down obscure problems that occur in your programs, or users of your programs will wonder why those programs behave erratically, or both.

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. You should write the test like this:

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

  
  

  Alternatively, write the test 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, three calls in the API are useful for finding out why:

• mysql_error() returns a string containing an error message.

• mysql_errno() returns a MySQL-specific numeric error code.

• mysql_sqlstate() returns an SQLSTATE code. The SQLSTATE value is more vendor neutral because it is based on the ANSI SQL and ODBC standards.

The argument to each function is a pointer to the connection handler. You should call them immediately after an error occurs. If you issue another API call that returns a status, any error information you get from mysql_error(), mysql_errno(), or mysql_sqlstate() will apply to the later call instead.

Generally, the user of a program will find an error message more enlightening than either of the error codes, so if you report only one value, I suggest that it be the message. The examples in this chapter report all three values for completeness. However, it's a lot of work to write three function invocations every place an error might occur. Instead, let's write a utility function, print_error(), that prints an error message supplied by us as well as the error values provided by the MySQL client library routines. In other words, we'll avoid writing out the calls to the mysql_errno() mysql_error(), and mysql_sqlstate() functions like this each time an error test occurs:

if (...some MySQL function fails...)
{
    fprintf (stderr, "...some error message...:\nError %u (%s): %s\n",
        mysql_errno (conn), mysql_sqlstate(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. The print_error() call is simpler than the fprintf() call, so it's easier to write and it 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 callssome to fprintf() and some to print_error().

I can hear someone in the back row objecting: "Well, you don't really have to call every error function each time you want to report an error. 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 respond to them as follows:

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

• If it's easy to report errors, you're more likely to be consistent about checking for them when you should.

• Whether or not you prefer to invoke all 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 need to make the change only 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 that need to check for MySQL-related errors use print_error() to report problems.

The following listing shows the definition of print_error(). It provides the benefits just discussed, and also handles a portability issue: mysql_sqlstate() was not introduced until MySQL 4.1.1, so you cannot use it if you compile your program using an earlier version of the client library. It's possible to check the version of MySQL by testing the value of the MYSQL_VERSION_ID macro and then invoking mysql_sqlstate() only if it's available.

static void
print_error (MYSQL *conn, char *message)
{
    fprintf (stderr, "%s\n", message);
    if (conn != NULL)
    {
#if MYSQL_VERSION_ID >= 40101
        fprintf (stderr, "Error %u (%s): %s\n",
            mysql_errno (conn), mysql_sqlstate(conn), mysql_error (conn));
#else
        fprintf (stderr, "Error %u: %s\n",
            mysql_errno (conn), mysql_error (conn));
#endif
    }
}

The part of connect2.c that will need to check for errors is similar to the corresponding code in connect1.c, and looks like this when we use print_error():

/* initialize connection handler */
conn = mysql_init (NULL);
if (conn == NULL)
{
    print_error (NULL, "mysql_init() failed (probably out of memory)");
    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)
{
    print_error (conn, "mysql_real_connect() failed");
    mysql_close (conn);
    exit (1);
}

The error-checking logic is based on the fact that both mysql_init() and mysql_real_connect() return NULL if they fail. Note that if mysql_init() fails, we pass NULL as the first argument to print_error(). That causes it not to invoke the MySQL error-reporting functions, because the connection handler passed to those functions cannot be assumed to contain any meaningful information. By contrast, if mysql_real_connect() fails, we do pass the connection handler to print_error(). The handler won't contain information that corresponds to a valid connection, but it will contain diagnostic information that can be extracted by the error-reporting functions. The handler also can 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.)

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, "Writing MySQL Programs Using Perl DBI," and Chapter 8, "Writing MySQL Programs Using PHP."

Getting Connection Parameters at Runtime

Now we're ready to tackle the problem of allowing users to specify connection parameters at runtime rather than using hardwired default parameters. The connect1 client program had a significant shortcoming in that the connection parameters were written literally into the source code. To change any of those values, you'd have to edit the source file and recompile it. That's not very convenient, especially if you intend to make your program available for other people to use. One common way to specify connection parameters at runtime is by using command-line options. For example, the programs in the MySQL distribution accept parameters in either of two forms, as shown in the following table.

Parameter	Long Option Form	Short Option Form
Hostname	--host=host_name	-h host_name
Username	--user=user_name	-u user_name
Password	--password or --password=your_pass	-p or -pyour_pass
Port number	--port=port_num	-P port_num
Socket name	--socket=socket_name	-S socket_name

For consistency with the standard MySQL clients, our connect2 client program will accept those same formats. It's easy to do this because the client library includes support for option processing. In addition, connect2 will have the capability to extract information from option files. This allows you to put connection parameters in ~/.my.cnf (that is, the .my.cnf file in your home directory) or in any global option file. Then you don't have to specify the options on the command line each time you invoke the program. The client library makes it easy to check for MySQL option files and pull any relevant values from them. By adding only a few lines of code to your programs, you can make them option file-aware, and you don't have to reinvent the wheel by writing your own code to do it. (Option file syntax is described in the section "Option Files," in Appendix F, "MySQL Program Reference.")

Before showing how option processing works in connect2 itself, we'll develop a couple of programs that illustrate the general principles involved. These show how option handling works fairly simply and without the added complication of connecting to the MySQL server and processing statements.

Note: MySQL 4.1 introduces two more options that relate to connection establishment. --protocol specifies the connection protocol (TCP/IP, Unix socket file, and so on), and --shared-memory-base-name specifies the name of the shared memory to use for shared-memory connections on Windows. This chapter doesn't cover either of these options, but the sampdb distribution contains the source code for a program, protocol, that shows how to use them if you are interested.

Accessing Option File Contents

To read option files for connection parameter values, invoke the load_defaults() function. load_defaults() looks for option files, parses their contents for any option groups in which you're interested, and rewrites your program's argument vector (the argv[] array). It puts information from those option groups in the form of command line options at the beginning of argv[]. That way, the options appear to have been specified on the command line. When you parse the command options, you see the connection parameters in your normal option-processing code. The options are added to argv[] immediately after the command name and before any other arguments (rather than at the end), so that any connection parameters specified on the command line occur later than and thus override any options added by load_defaults().

Here's a little program, show_argv, that demonstrates how to use load_defaults() and illustrates how it modifies your argument vector:

/*
 * show_argv.c - show effect of load_defaults() on argument vector
 */

#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>

static const char *client_groups[] = { "client", NULL };

int
main (int argc, char *argv[])
{
int i;

    printf ("Original argument vector:\n");
    for (i = 0; i < argc; i++)
        printf ("arg %d: %s\n", i, argv[i]);

    MY_INIT (argv[0]);
    load_defaults ("my", client_groups, &argc, &argv);

    printf ("Modified argument vector:\n");
    for (i = 0; i < argc; i++)
        printf ("arg %d: %s\n", i, argv[i]);

    exit (0);
}

The option file-processing code involves several components:

• client_groups[] is an array of character strings indicating the names of the option file groups from which you want to obtain options. Client programs normally include at least "client" in the list (which represents the [client] group), but you can list as many groups as you like. The last element of the array must be NULL to indicate where the list ends.

• MY_INIT() is an initialization macro. It sets a global variable to point to the name of your program (which you pass as its argument), for use in error messages. It also calls my_init() to perform some setup operations required by load_defaults().

• load_defaults() reads the option files. It takes four arguments: the prefix used in the names of your option files (this should always be "my"), the array listing the names of the option groups in which you're interested, and the addresses of your program's argument count and vector. Don't pass the values of the count and vector. Pass their addresses instead because load_defaults() needs to change their values. Note in particular that even though argv is already a pointer, you still pass &argv, that pointer's address.

show_argv prints its arguments twice to show the effect that load_defaults() has on the argument array. First it prints the arguments as they were specified on the command line. Then it calls load_defaults() and prints the argument array again.

To see how load_defaults() works, make sure that you have a .my.cnf file in your home directory with some settings specified for the [client] group. (On Windows, you can use the C:\my.cnf file.) Suppose that the file looks like this:

[client]
user=sampadm
password=secret
host=some_host

If that is the case, executing show_argv should produce output like this:

% ./show_argv a b
Original argument vector:
arg 0: ./show_argv
arg 1: a
arg 2: b
Modified argument vector:
arg 0: ./show_argv
arg 1: --user=sampadm
arg 2: --password=secret
arg 3: --host=some_host
arg 4: a
arg 5: b

When show_argv prints the argument vector the second time, the values in the option file show up as part of the argument list. It's also possible that you'll see some options that were not specified on the command line or in your ~/.my.cnf file. If this occurs, you will likely find that options for the [client] group are listed in a system-wide option file. This can happen because load_defaults() actually looks in several option files. On Unix, it looks in /etc/my.cnf and in the my.cnf file in the MySQL data directory before reading .my.cnf in your home directory. On Windows, load_defaults() reads the my.ini file in your Windows directory and C:\my.cnf.

Client programs that use load_defaults() generally include "client" in the list of option group names (so that they get any general client settings from option files), but you can set up your option file-processing code to obtain options from other groups as well. Suppose that you want show_argv to read options in both the [client] and [show_argv] groups. To accomplish this, find the following line in show_argv.c:

const char *client_groups[] = { "client", NULL };

Change the line to this:

const char *client_groups[] = { "show_argv", "client", NULL };

Then recompile show_argv, and the modified program will read options from both groups. To verify this, add a [show_argv] group to your ~/.my.cnf file:

[client]
user=sampadm
password=secret
host=some_host

[show_argv]
host=other_host

With these changes, invoking show_argv again produces a different result than before:

% ./show_argv a b
Original argument vector:
arg 0: ./show_argv
arg 1: a
arg 2: b
Modified argument vector:
arg 0: ./show_argv
arg 1: --user=sampadm
arg 2: --password=secret
arg 3: --host=some_host
arg 4: --host=other_host
arg 5: a
arg 6: b

The order in which option values appear in the argument array is determined by the order in which they are listed in your option file, not the order in which option group names are listed in the client_groups[] array. This means you'll probably want to specify program-specific groups after the [client] group in your option file. That way, if you specify an option in both groups, the program-specific value takes precedence over the more general [client] group value. You can see this in the example just shown: The host option was specified in both the [client] and [show_argv] groups, but because the [show_argv] group appears last in the option file, its host setting appears later in the argument vector and takes precedence.

load_defaults() does not pick up values from your environment settings. If you want to use the values of environment variables such as MYSQL_TCP_PORT or MYSQL_UNIX_PORT, you must arrange for that yourself by using getenv(). I'm not going to add that capability to our clients, but here's a short code fragment that shows how to check the values of a couple of the standard MySQL-related environment variables:

extern char *getenv();
char *p;
int port_num = 0;
char *socket_name = NULL;

if ((p = getenv ("MYSQL_TCP_PORT")) != NULL)
    port_num = atoi (p);
if ((p = getenv ("MYSQL_UNIX_PORT")) != NULL)
    socket_name = p;

In the standard MySQL clients, environment variable values have lower precedence than values specified in option files or on the command line. If you want to check environment variables in your own programs and want to be consistent with that convention, check the environment before (not after) calling load_defaults() or processing command-line options.

load_defaults() and Security On multiple-user systems, utilities such as the ps program can display argument lists for arbitrary processes, including those being run by other users. Because of this, you may be wondering if there are any process-snooping implications of load_defaults() taking passwords that it finds in option files and putting them in your argument list. This actually is not a problem because ps displays the original argv[] contents. Any password argument created by load_defaults() points to an area of memory that it allocates for itself. That area is not part of the original vector, so ps never sees it. On the other hand, a password that is given on the command line does show up in ps. This is one reason why it's not a good idea to specify passwords that way. One precaution a program can take to help reduce the risk is to remove the password from the argument list as soon as it starts executing. The section "Processing Command-Line Arguments" shows how to do that.

Processing Command-Line Arguments

Using load_defaults(), we can get all the connection parameters into the argument vector, but now we need a way to process the vector. The handle_options() function is designed for this. handle_options() is built into the MySQL client library, so you have access to it whenever you link in that library.

Some of the characteristics of the client library option-processing routines are as follows:

• Precise specification of the option type and range of legal values. For example, you can indicate not only that an option must have integer values, but that it must be positive and a multiple of 1024.

• Integration of help text to make it easy to print a help message by calling a standard library function. There is no need to write your own special code to produce a help message.

• Built-in support for the standard --no-defaults, --print-defaults, --defaults-file, and --defaults-extra-file options. (These options are described in the section "Option Files," in Appendix F.

• Support for a standard set of option prefixes, such as --disable- and --enable-, to make it easier to implement boolean (on/off) options. (This capability is not used in this chapter, but is described in the section "Program Option Conventions," of Appendix F.

To demonstrate how to use MySQL's option-handling facilities, this section describes a show_opt program that invokes load_defaults() to read option files and set up the argument vector, and then processes the result using handle_options().

show_opt allows you to experiment with various ways of specifying connection parameters (whether in option files or on the command line), and to see the result by showing you what values would be used to make a connection to the MySQL server. show_opt is useful for getting a feel for what will happen in our next client program, connect2, which hooks up this option-processing code with code that actually does connect to the server.

show_opt illustrates what happens at each phase of argument processing by performing the following actions:

The following discussion explains how show_opt works, but first take a look at its source file, show_opt.c:

/*
 * show_opt.c - demonstrate option processing with load_defaults()
 * and handle_options()
 */

#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <my_getopt.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 const char *client_groups[] = { "client", NULL };

static struct my_option my_opts[] =     /* option information structures */
{
    {"help", '?', "Display this help and exit",
    NULL, NULL, NULL,
    GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
    {"host", 'h', "Host to connect to",
    (gptr *) &opt_host_name, NULL, NULL,
    GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    {"password", 'p', "Password",
    (gptr *) &opt_password, NULL, NULL,
    GET_STR_ALLOC, OPT_ARG, 0, 0, 0, 0, 0, 0},
    {"port", 'P', "Port number",
    (gptr *) &opt_port_num, NULL, NULL,
    GET_UINT, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    {"socket", 'S', "Socket path",
    (gptr *) &opt_socket_name, NULL, NULL,
    GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    {"user", 'u', "User name",
    (gptr *) &opt_user_name, NULL, NULL,
    GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    { NULL, 0, NULL, NULL, NULL, NULL, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0 }
};

static my_bool
get_one_option (int optid, const struct my_option *opt, char *argument)
{
    switch (optid)
    {
    case '?':
        my_print_help (my_opts);    /* print help message */
        exit (0);
    }
    return (0);
}

int
main (int argc, char *argv[])
{
int i;
int opt_err;

    printf ("Original connection parameters:\n");
    printf ("hostname: %s\n", opt_host_name ? opt_host_name : "(null)");
    printf ("username: %s\n", opt_user_name ? opt_user_name : "(null)");
    printf ("password: %s\n", opt_password ? opt_password : "(null)");
    printf ("port number: %u\n", opt_port_num);
    printf ("socket filename: %s\n",
            opt_socket_name ? opt_socket_name : "(null)");

    printf ("Original argument vector:\n");
    for (i = 0; i < argc; i++)
        printf ("arg %d: %s\n", i, argv[i]);

    MY_INIT (argv[0]);
    load_defaults ("my", client_groups, &argc, &argv);

    printf ("Modified argument vector after load_defaults():\n");
    for (i = 0; i < argc; i++)
        printf ("arg %d: %s\n", i, argv[i]);

    if ((opt_err = handle_options (&argc, &argv, my_opts, get_one_option)))
        exit (opt_err);

    printf ("Connection parameters after handle_options():\n");
    printf ("hostname: %s\n", opt_host_name ? opt_host_name : "(null)");
    printf ("username: %s\n", opt_user_name ? opt_user_name : "(null)");
    printf ("password: %s\n", opt_password ? opt_password : "(null)");
    printf ("port number: %u\n", opt_port_num);
    printf ("socket filename: %s\n",
            opt_socket_name ? opt_socket_name : "(null)");

    printf ("Argument vector after handle_options():\n");
    for (i = 0; i < argc; i++)
        printf ("arg %d: %s\n", i, argv[i]);

    exit (0);
}

The option-processing approach illustrated by show_opt.c involves the following aspects, which are common to any program that uses the MySQL client library to handle command options:

The my_option structure defines the types of information that must be specified for each option that the program understands. It looks like this:

struct my_option
{
  const char *name;               /* option's long name */
  int        id;                  /* option's short name or code */
  const char *comment;            /* option description for help message */
  gptr       *value;              /* pointer to variable to store value in */
  gptr       *u_max_value;        /* The user defined max variable value */
  const char **str_values;        /* array of legal option values (unused) */
  ulong      var_type;            /* option value's type */
  enum get_opt_arg_type arg_type; /* whether option value is required */
  longlong   def_value;           /* option's default value */
  longlong   min_value;           /* option's minimum allowable value */
  longlong   max_value;           /* option's maximum allowable value */
  longlong   sub_size;            /* amount to shift value by */
  long       block_size;          /* option value multiplier */
  int        app_type;            /* reserved for application-specific use */
};

The members of the my_option structure are used as follows:

• name

  The long option name. This is the --name form of the option, without the leading dashes. For example, if the long option is --user, list it as "user" in the my_option structure.

• id

  The short (single-letter) option name, or a code value associated with the option if it has no single-letter name. For example, if the short option is -u, list it as 'u' in the my_option structure. For options that have only a long name and no corresponding single-character name, you should make up a set of option code values to be used internally for the short names. The values must be unique and different from all the single-character names. (To satisfy the latter constraint, make the codes greater than 255, the largest possible single-character value. An example of this technique is shown in "Writing Clients That Include SSL Support.")

• comment

  An explanatory string that describes the purpose of the option. This is the text that you want displayed in a help message.

• value

  This is a gptr (generic pointer) value. If the option takes an argument, value points to the variable where you want the argument to be stored. After the options have been processed, you can check that variable to see what the option has been set to. The data type of the variable that's pointed to must be consistent with the value of the var_type member. If the option takes no argument, value can be NULL.

• u_max_value

  This is another gptr value, but it's used only by the server. For client programs, set u_max_value to NULL.

• str_values

  This member currently is unused. In future MySQL releases, it may be used to allow a list of legal values to be specified, in which case any option value given will be required to match one of these values.

• var_type

  This member indicates what kind of value must follow the option name on the command line. The following table shows these types, their meanings, and the corresponding C type:

  var_type Value	Meaning	C Type
  GET_NO_ARG	No value
  GET_BOOL	Boolean value	my_bool
  GET_INT	Integer value	int
  GET_UINT	Unsigned integer value	unsigned int
  GET_LONG	Long integer value	long
  GET_ULONG	Unsigned long integer value	unsigned long
  GET_LL	Long long integer value	long long
  GET_ULL	Unsigned long long integer value	unsigned long long
  GET_STR	String value	char *
  GET_STR_ALLOC	String value	char *
  GET_DISABLED	Option is disabled

  
  

  The difference between GET_STR and GET_STR_ALLOC is that for GET_STR, the client library sets the option variable to point directly at the value in the argument vector, whereas for GET_STR_ALLOC, it makes a copy of the argument and sets the option variable to point to the copy.

  The GET_DISABLED type can be used to indicate that an option is no longer available, or that it is available only when the program is built a certain way (for example, with debugging support enabled). To see an example, take a look at the mysqldump.c file in a MySQL source distribution. GET_DISABLED was introduced in MySQL 4.1.2.

• arg_type

  The arg_type value indicates whether a value follows the option name, and may be any of the following:

  arg_type Value	Meaning
  NO_ARG	Option takes no following argument
  OPT_ARG	Option may take a following argument
  REQUIRED_ARG	Option requires a following argument

  
  

  If arg_type is NO_ARG, var_type should be set to GET_NO_ARG.

• def_value

  For numeric-valued options, this is the default value to assign to the option if no explicit value is specified in the argument vector.

• min_value

  For numeric-valued options, this is the smallest value that may be specified. Smaller values are bumped up to this value automatically. Use 0 to indicate "no minimum."

• max_value

  For numeric-valued options, this is the largest value that may be specified. Larger values are bumped down to this value automatically. Use 0 to indicate "no maximum."

• sub_size

  For numeric-valued options, sub_size is an offset that is used to convert values from the range as given in the argument vector to the range that is used internally. For example, if values are given on the command line in the range from 1 to 256, but the program wants to use an internal range of 0 to 255, set sub_size to 1.

• block_size

  For numeric-valued options, this value indicates a block size if it is non-zero. Option values given by the user are rounded down to the nearest multiple of this size if necessary. For example, if values must be even, set the block size to 2; handle_options() rounds odd values down to the nearest even number.

• app_type

  This is reserved for application-specific use.

The my_opts array should have a my_option structure for each valid option, followed by a terminating structure that is set up as follows to indicate the end of the array:

{ NULL, 0, NULL, NULL, NULL, NULL, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0 }

When you invoke handle_options() to process the argument vector, it skips over the first argument (the program name), and then processes option argumentsthat is, arguments that begin with a dash. This continues until it reaches the end of the vector or encounters the special two-dash "end of options" argument ('--'). As handle_options() moves through the argument vector, it calls the helper function once per option to allow that function to perform any special processing. handle_options() passes three arguments to the helper function: the short option value, a pointer to the option's my_option structure, and a pointer to the argument that follows the option in the argument vector (which will be NULL if the option is specified without a following value).

When handle_options() returns, the argument count and vector are reset appropriately to represent an argument list containing only the non-option arguments.

Here is a sample invocation of show_opt and the resulting output (assuming that ~/.my.cnf still has the same contents as for the final show_argv example in "Accessing Option File Contents"):

% ./show_opt -h yet_another_host --user=bill x
Original connection parameters:
hostname: (null)
username: (null)
password: (null)
port number: 0
socket filename: (null)
Original argument vector:
arg 0: ./show_opt
arg 1: -h
arg 3: yet_another_host
arg 3: --user=bill
arg 4: x
Modified argument vector after load_defaults():
arg 0: ./show_opt
arg 1: --user=sampadm
arg 2: --password=secret
arg 3: --host=some_host
arg 4: -h
arg 5: yet_another_host
arg 6: --user=bill
arg 7: x
Connection parameters after handle_options():
hostname: yet_another_host
username: bill
password: secret
port number: 0
socket filename: (null)
Argument vector after handle_options():
arg 0: x

The output shows that the hostname is picked up from the command line (overriding the value in the option file), and that the username and password come from the option file. handle_options() correctly parses options whether specified in short-option form (such as -h yet_another_host) or in long-option form (such as --user=bill).

The get_one_option() helper function is used in conjunction with handle_options(). For show_opt, it is fairly minimal and takes no action except for the --help or -? options (for which handle_options() passes an optid value of '?'):

static my_bool
get_one_option (int optid, const struct my_option *opt, char *argument)
{
    switch (optid)
    {
    case '?':
        my_print_help (my_opts);    /* print help message */
        exit (0);
    }
    return (0);
}

my_print_help() is a client library routine that automatically produces a help message for you, based on the option names and comment strings in the my_opts array. To see how it works, try the following command:

% ./show_opt --help

You can add other cases to the switch() statement in get_one_option() as necessary (and we'll do so in connect2 shortly). For example, get_one_option() is useful for handling password options. When you specify such an option, the password value may or may not be given, as indicated by OPT_ARG in the option information structure. That is, you may specify the option as --password or --password=your_pass if you use the long-option form, or as -p or -pyour_pass if you use the short-option form. MySQL clients typically allow you to omit the password value on the command line, and then prompt you for it. This allows you to avoid giving the password on the command line, which keeps people from seeing your password. In later programs, we'll use get_one_option() to check whether a password value was given. We'll save the value if so, and otherwise set a flag to indicate that the program should prompt the user for a password before attempting to connect to the server.

You might find it instructive to modify the option structures in show_opt.c to see how your changes affect the program's behavior. For example, if you set the minimum, maximum, and block size values for the --port option to 100, 1000, and 25, you'll find after recompiling the program that you cannot set the port number to a value outside the range from 100 to 1000, and that values get rounded down automatically to the nearest multiple of 25.

The option processing routines also handle the --no-defaults, --print-defaults, --defaults-file, and --defaults-extra-file options automatically. Try invoking show_opt with each of these options to see what happens.

Incorporating Option-Processing into a MySQL Client Program

Now we're ready to write connect2.c. It has the following characteristics:

• It connects to the MySQL server, disconnects, and exits. This is similar to what connect1.c does, but is modified to use the print_error() function developed earlier for reporting errors.

• It processes options from the command line or in option files. This is done using code similar to that from show_opt.c, but is modified to prompt the user for a password if necessary.

The resulting source file, connect2.c, is as follows:

/*
 * connect2.c - connect to MySQL server, using connection parameters
 * specified in an option file or on the command line
 */

#include <string.h>     /* for strdup() */
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <my_getopt.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 int ask_password = 0;            /* whether to solicit password */

static MYSQL *conn;                     /* pointer to connection handler */

static const char *client_groups[] = { "client", NULL };

static struct my_option my_opts[] =     /* option information structures */
{
    {"help", '?', "Display this help and exit",
    NULL, NULL, NULL,
    GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
    {"host", 'h', "Host to connect to",
    (gptr *) &opt_host_name, NULL, NULL,
    GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    {"password", 'p', "Password",
    (gptr *) &opt_password, NULL, NULL,
    GET_STR_ALLOC, OPT_ARG, 0, 0, 0, 0, 0, 0},
    {"port", 'P', "Port number",
    (gptr *) &opt_port_num, NULL, NULL,
    GET_UINT, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    {"socket", 'S', "Socket path",
    (gptr *) &opt_socket_name, NULL, NULL,
    GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    {"user", 'u', "User name",
    (gptr *) &opt_user_name, NULL, NULL,
    GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
    { NULL, 0, NULL, NULL, NULL, NULL, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0 }
};

static void
print_error (MYSQL *conn, char *message)
{
    fprintf (stderr, "%s\n", message);
    if (conn != NULL)
    {
#if MYSQL_VERSION_ID >= 40101
        fprintf (stderr, "Error %u (%s): %s\n",
            mysql_errno (conn), mysql_sqlstate(conn), mysql_error (conn));
#else
        fprintf (stderr, "Error %u: %s\n",
            mysql_errno (conn), mysql_error (conn));
#endif
    }
}

static my_bool
get_one_option (int optid, const struct my_option *opt, char *argument)
{
    switch (optid)
    {
    case '?':
        my_print_help (my_opts);    /* print help message */
        exit (0);
    case 'p':                       /* password */
        if (!argument)              /* no value given, so solicit it later */
            ask_password = 1;
        else                        /* copy password, wipe out original */
        {
            opt_password = strdup (argument);
            if (opt_password == NULL)
            {
                print_error (NULL, "could not allocate password buffer");
                exit (1);
            }
            while (*argument)
                *argument++ = 'x';
            ask_password = 0;
        }
        break;
    }
    return (0);
}

int
main (int argc, char *argv[])
{
int opt_err;

    MY_INIT (argv[0]);
    load_defaults ("my", client_groups, &argc, &argv);

    if ((opt_err = handle_options (&argc, &argv, my_opts, get_one_option)))
        exit (opt_err);

    /* solicit password if necessary */
    if (ask_password)
        opt_password = get_tty_password (NULL);

    /* get database name if present on command line */
    if (argc > 0)
    {
        opt_db_name = argv[0];
        --argc; ++argv;
    }

    /* initialize connection handler */
    conn = mysql_init (NULL);
    if (conn == NULL)
    {
        print_error (NULL, "mysql_init() failed (probably out of memory)");
        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)
    {
        print_error (conn, "mysql_real_connect() failed");
        mysql_close (conn);
        exit (1);
    }

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

    /* disconnect from server */
    mysql_close (conn);
    exit (0);
}

Compared to the connect1 and show_opt programs that we developed earlier, connect2 does a few new things:

• It allows a database to be selected on the command line; just specify the database after the other arguments. This is consistent with the behavior of the standard clients in the MySQL distribution.

• If a password value is present in the argument vector, get_one_option() makes a copy of it and then overwrites the original. This minimizes the time window during which a password specified on the command line is visible to ps or to other system status programs. (The window is only minimized, not eliminated. Specifying passwords on the command line still is a security risk.)

• If a password option was given without a value, get_one_option() sets a flag to indicate that the program should prompt the user for a password. That's done in main() after all options have been processed, using the get_tty_password() function. This is a utility routine in the client library that prompts for a password without echoing it on the screen. You may ask, "Why not just call getpass()?" The answer is that not all systems have that functionWindows, for example. get_tty_password() is portable across systems because it's configured to adjust to system idiosyncrasies.

Compile and link connect2, and then try running it:

% ./connect2

If connect2 produces no output (as just shown), it connected successfully. On the other hand, you might see something like this:

% ./connect2
mysql_real_connect() failed:
Error 1045 (28000): Access denied for user 'sampadm'@'localhost'
(using password: NO)

This output indicates no connection was established, and it says why. In this case, Access denied means that you need to supply appropriate connection parameters. With connect1, there was no way to do so short of editing and recompiling. connect2 connects to the MySQL server according to the options you specify on the command line or in an option file. Assume that there is no option file to complicate matters. If you invoke connect2 with no arguments, it connects to localhost and passes your Unix login name and no password to the server. If instead you invoke connect2 as shown in the following command, it prompts for a password (because there is no password value immediately following -p), connects to some_host, and passes the username some_user to the server as well as the password you type in:

% ./connect2 -h some_host -p -u some_user some_db

connect2 also passes the database name some_db to mysql_real_connect() to make that the current database. If there is an option file, its contents are processed and used to modify the connection parameters accordingly.

Let's step back for a moment and consider what's been achieved so far. The work that has gone into producing connect2 accomplishes something that's necessary for every MySQL client: connecting to the server using appropriate parameters. It also does a good job of reporting errors if the connection attempt fails. What we have now serves as a framework that can be used as the basis for many different client programs. To write a new client, do this:

And you're done.

All the real action for your application will take place between the mysql_real_connect() and mysql_close() calls, but having a reusable skeleton means that you can concentrate more on what you're really interested inbeing able to access the content of your databases.