Using the MySQL Client Library to Write Your Own Programs

This chapter is from the book

MySQL

Learn More Buy

This chapter is from the book

This chapter is from the book 

MySQL

Learn More Buy

Client 4—Getting Connection Parameters at Runtime

Okay, now that we have our easily modifiable and bullet-proof-in-case-an-error-occurs connection code, we're ready to figure out how to do something smarter than using NULL connection parameters—like letting the user specify those values at runtime.

The previous client, client3, still has a significant shortcoming in that the connection parameters are hardwired in. To change any of those values, you 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. The programs in the MySQL distribution accept connection parameters in either of two forms, as shown in Table 6.1.

Table 6.1 Standard MySQL Command-Line Options

For consistency with the standard MySQL clients, our client will accept those same formats. It's easy to do this because the client library includes a function to perform option parsing.

In addition, our client will have the ability 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) so that you don't have to specify them on the command line. 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 program, you can make it 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 Appendix E, "MySQL Program Reference."

Accessing Option File Contents

To read option files for connection parameter values, use 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) to put information from those 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. Therefore, when you parse the command options, you get the connection parameters as part of your normal option-parsing loop. The options are added to the beginning of argv[] rather than at the end so that if connection parameters really are specified on the command line, they occur later than (and thus override) any options added by load_defaults().

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

/* show_argv.c */

#include <stdio.h>
#include <mysql.h>

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

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

  my_init ();

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

  load_defaults ("my", 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 the following:

• groups[] is a character string array indicating which option file groups you are interested in. For client programs, you always specify as least "client" (for the [client] group). The last element of the array must be NULL.

• my_init() is an initialization routine that performs some setup operations required by load_defaults().

• load_defaults() takes four arguments: the prefix of your option files (this should always be "my"), the array listing 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 although argv is a pointer, you still pass &argv, that pointer's address.

show_argv prints its arguments twice—first as you specified them on the command line, then as they were modified by load_defaults(). To see the effect of load_defaults(), make sure you have a .my.cnf file in your home directory with some settings specified for the [client] group. Suppose .my.cnf looks like this:

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

If this is the case, then executing show_argv produces 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=paul
arg 2: --password=secret
arg 3: --host=some_host
arg 4: a
arg 5: b

It's possible that you'll see some options in the output from show_argv that were not on the command line or in your ~/.my.cnf file. If so, they were probably specified in a system-wide option file. load_defaults() actually looks for /etc/my.cnf and the my.cnf file in the MySQL data directory before reading .my.cnf in your home directory. (On Windows, load_defaults() searches for C:\my.cnf, C:\mysql\data\my.cnf, and the my.ini file in your Windows system directory.)

Client programs that use load_defaults() almost always specify "client" in the options group list (so that they get any general client settings from option files), but you can also ask for values that are specific to your own program. Just change the following:

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

to this:

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

Then you can add a [show_argv] group to your ~/.my.cnf file:

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

[show_argv]
host=other_host

With these changes, invoking show_argv again has a different result, as follows:

% 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=paul
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 your option groups are listed in the 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 will take precedence. 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 manage that yourself using getenv(). I'm not going to add that capability to our clients, but here's an example showing how to check the values of a couple of the standard MySQL-related environment variables:

extern char *getenv();
char *p;
int port_num;
char *socket_name;

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 variables' values have lower precedence than values specified in option files or on the command line. If you check environment variables and want to be consistent with that convention, check the environment before (not after) calling load_defaults() or processing command line options.

Parsing Command-Line Arguments

We can get all the connection parameters into the argument vector now, but we need a way to parse the vector. The getopt_long() function is designed for this.

getopt_long() is built into the MySQL client library, so you have access to it whenever you link in that library. In your source file, you need to include the getopt.h header file. You can copy this header file from the include directory of the MySQL source distribution into the directory where you're developing your client program.

The following program, show_param, uses load_defaults() to read option files, then calls getopt_long() to parse the argument vector. show_param illustrates what happens at each phase of argument processing by performing the following actions:

1. Sets up default values for the hostname, username, and password.

2. Prints the original connection parameter and argument vector values.

3. Calls load_defaults() to rewrite the argument vector to reflect option file contents, then prints the resulting vector.

4. Calls getopt_long() to process the argument vector, then prints the resulting parameter values and whatever is left in the argument vector.

show_param 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. show_param is useful for getting a feel for what will happen in our next client program, when we actually hook up this parameter-processing code to our connection function, do_connect().

Here's what show_param.c looks like:

/* show_param.c */

#include <stdio.h>
#include <stdlib.h> /* needed for atoi() */
#include "getopt.h"

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

struct option long_options[] =
{
  {"host",   required_argument, NULL, 'h'},
  {"user",   required_argument, NULL, 'u'},
  {"password", optional_argument, NULL, 'p'},
  {"port",   required_argument, NULL, 'P'},
  {"socket",  required_argument, NULL, 'S'},
  { 0, 0, 0, 0 }
};

int
main (int argc, char *argv[])
{
char *host_name = NULL;
char *user_name = NULL;
char *password = NULL;
unsigned int port_num = 0;
char *socket_name = NULL;
int i;
int c, option_index;

  my_init ();

  printf ("Original connection parameters:\n");
  printf ("host name: %s\n", host_name ? host_name : "(null)");
  printf ("user name: %s\n", user_name ? user_name : "(null)");
  printf ("password: %s\n", password ? password : "(null)");
  printf ("port number: %u\n", port_num);
  printf ("socket name: %s\n", socket_name ? socket_name : "(null)");

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

  load_defaults ("my", 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]);

  while ((c = getopt_long (argc, argv, "h:p::u:P:S:", long_options,
          &option_index)) != EOF)
  {
    switch (c)
    {
    case 'h':
      host_name = optarg;
      break;
    case 'u':
      user_name = optarg;
      break;
    case 'p':
      password = optarg;
      break;
    case 'P':
      port_num = (unsigned int) atoi (optarg);
      break;
    case 'S':
      socket_name = optarg;
      break;
    }
  }

  argc -= optind; /* advance past the arguments that were processed */
  argv += optind; /* by getopt_long() */

  printf ("Connection parameters after getopt_long():\n");
  printf ("host name: %s\n", host_name ? host_name : "(null)");
  printf ("user name: %s\n", user_name ? user_name : "(null)");
  printf ("password: %s\n", password ? password : "(null)");
  printf ("port number: %u\n", port_num);
  printf ("socket name: %s\n", socket_name ? socket_name : "(null)");

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

  exit (0);
}

To process the argument vector, show_argv uses getopt_long(), which you typically call in a loop:

while ((c = getopt_long (argc, argv, "h:p::u:P:S:", long_options,
        &option_index)) != EOF)
{
  /* process option */
}

The first two arguments to getopt_long() are your program's argument count and vector. The third argument lists the option letters you want to recognize. These are the short-name forms of your program's options. Option letters may be followed by a colon, a double colon, or no colon to indicate that the option must be followed, may be followed, or is not followed by an option value. The fourth argument, long_options, is a pointer to an array of option structures, each of which specifies information for an option you want your program to understand. Its purpose is similar to the options string in the third argument. The four elements of each long_options[] structure are as follows:

• The option's long name.

• A value for the option. The value can be required_argument, optional_argument, or no_argument indicating whether the option must be followed, may be followed, or is not followed by an option value. (These serve the same purpose as the colon, double colon, or no colon in the options string third argument.)

• A flag argument. You can use this to store a pointer to a variable. If the option is found, getopt_long() stores the value specified by the fourth argument into the variable. If the flag is NULL, getopt_long() instead sets the optarg variable to point to any value following the option, and returns the option's short name. Our long_options[] array specifies NULL for all options. That way, getopt_long() returns each argument as it is encountered so that we can process it in the switch statement.

• The option's short (single-character) name. The short names specified in the long_options[] array must match the letters used in the options string that you pass as the third argument to getopt_long() or your program will not process command-line arguments properly.

The long_options[] array must be terminated by a structure with all elements set to 0.

The fifth argument to getopt_long() is a pointer to an int variable. getopt_long() stores into this variable the index of the long_options[] structure that corresponds to the option last encountered. (show_param doesn't do anything with this value.)

Note that the password option (specified as --password or as -p) may take an optional value. That is, you may specify it 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. The optional nature of the password value is indicated by the double colon after the "p" in the options string, and by optional_argument in the long_options[] array. MySQL clients typically allow you to omit the password value on the command line, then prompt you for it. This allows you to avoid giving the password on the command line, which keeps people from seeing your password via process snooping. When we write our next client, client4, we'll add this password-checking behavior to it.

Here is a sample invocation of show_param and the resulting output (assuming that ~/.my.cnf still has the same contents as for the show_argv example):

% show_param -h yet_another_host x
Original connection parameters:
host name: (null)
user name: (null)
password: (null)
port number: 0
socket name: (null)
Original argument vector:
arg 0: show_param
arg 1: -h
arg 2: yet_another_host
arg 3: x
Modified argument vector after load_defaults():
arg 0: show_param
arg 1: --user=paul
arg 2: --password=secret
arg 3: --host=some_host
arg 4: -h
arg 5: yet_another_host
arg 6: x
Connection parameters after getopt_long():
host name: yet_another_host
user name: paul
password: secret
port number: 0
socket name: (null)
Argument vector after getopt_long():
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. getopt_long() correctly parses options whether specified in short-option form (-h host_name) or in long-option form (--user=paul, --password=secret).

Now let's strip out the stuff that's purely illustrative of how the option-handling routines work and use the remainder as a basis for a client that connects to a server according to any options that are provided in an option file or on the command line. The resulting source file, client4.c, looks like this:

/* client4.c */

#include <stdio.h>
#include <stdlib.h> /* for atoi() */
#include <mysql.h>
#include "common.h"
#include "getopt.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_port_num    0    /* use default port */
#define def_socket_name NULL /* use default socket name */
#define def_db_name     NULL /* database to use (default = none) */

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

struct option long_options[] =
{
  {"host",   required_argument, NULL, 'h'},
  {"user",   required_argument, NULL, 'u'},
  {"password", optional_argument, NULL, 'p'},
  {"port",   required_argument, NULL, 'P'},
  {"socket",  required_argument, NULL, 'S'},
  { 0, 0, 0, 0 }
};

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

int
main (int argc, char *argv[])
{
char *host_name = def_host_name;
char *user_name = def_user_name;
char *password = def_password;
unsigned int port_num = def_port_num;
char *socket_name = def_socket_name;
char *db_name = def_db_name;
char  passbuf[100];
int ask_password = 0;
int c, option_index=0;
int i;

  my_init ();
  load_defaults ("my", groups, &argc, &argv);

  while ((c = getopt_long (argc, argv, "h:p::u:P:S:", long_options,
          &option_index)) != EOF)
  {
    switch (c)
    {
    case 'h':
      host_name = optarg;
      break;
    case 'u':
      user_name = optarg;
      break;
    case 'p':
      if (!optarg)    /* no value given */
        ask_password = 1;
      else        /* copy password, wipe out original */
      {
          (void) strncpy (passbuf, optarg, sizeof(passbuf)-1);
          passbuf[sizeof(passbuf)-1] = '\0';
          password = passbuf;
          while (*optarg)
            *optarg++ = ' ';
      }
      break;
    case 'P':
      port_num = (unsigned int) atoi (optarg);
      break;
    case 'S':
      socket_name = optarg;
      break;
    }
  }

  argc -= optind; /* advance past the arguments that were processed */
  argv += optind; /* by getopt_long() */

  if (argc > 0)
  {
    db_name = argv[0];
    --argc; ++argv;
  }

  if (ask_password)
    password = get_tty_password (NULL);

  conn = do_connect (host_name, user_name, password, db_name,
                  port_num, socket_name, 0);
  if (conn == NULL)
    exit (1);

  /* do the real work here */

  do_disconnect (conn);
  exit (0);
}

Compared to the programs client1, client2, and client3 that we developed earlier, client4 does a few things we haven't seen before:

• It allows the database name to be specified on the command line, following the options that are parsed by getopt_long(). This is consistent with the behavior of the standard clients in the MySQL distribution.

• It wipes out any password value in the argument vector after making a copy of it. This is to minimize 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 minimized, not eliminated. Specifying passwords on the command line still is a security risk.)

• If a password option was given without a value, the client prompts the user for a password using get_tty_password(). This is a utility routine in the client library that prompts for a password without echoing it on the screen. (The client library is full of goodies like this. It's instructive to read through the source of the MySQL client programs because you find out about these routines and how to use them.) You may ask, "Why not just call getpass()?" The answer is that not all systems have that function – Windows, for example. get_tty_password() is portable across systems because it's configured to adjust to system idiosyncrasies.

client4 responds according to the options you specify. Assume there is no option file to complicate matters. If you invoke client4 with no arguments, it connects to localhost and passes your UNIX login name and no password to the server. If instead you invoke client4, as shown here, then it prompts for a password (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:

% client4 -h some_host -u some_user -p some_db

client4 also passes the database name some_db to do_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.

Earlier, we went on a code-encapsulation binge, creating wrapper functions for disconnecting to and disconnecting from the server. It's reasonable to ask whether or not to put option-parsing stuff in a wrapper function, too. That's possible, I suppose, but I'm not going to do it. Option-parsing code isn't as consistent across programs as connection code: Programs often support other options in addition to the standard ones we've just looked for, and different programs are likely to support different sets of additional options. That makes it difficult to write a function that standardizes the option-processing loop. Also, unlike connection establishment, which a program may wish to do multiple times during the course of its execution (and thus is a good candidate for encapsulation), option parsing is typically done just once at the beginning of the program.

The work we've done so far accomplishes something that's necessary for every MySQL client: connecting to the server using appropriate parameters. You need to know how to connect, of course. But now you do know how, and the details of that process are implemented by the client skeleton (client4.c), so you no longer need to think about them. That means you can concentrate on what you're really interested in—being able to access the content of your databases. All the real action for your application will take place between the do_connect() and do_disconnect() calls, but what we have now serves as a basic framework that you can use for many different clients. To write a new program, just do this:

1. Make a copy of client4.c.

2. Modify the option-processing loop, if you accept additional options other than the standard ones that client4.c knows about.

3. Add your own application-specific code between the connect and disconnect calls.

And you're done.

The point of going through the discipline of constructing the client program skeleton was to come up with something that you can use easily to set up and tear down a connection so that you could focus on what you really want to do. Now you're free to do that, demonstrating the principle that from discipline comes freedom.