Client 3 Getting Connection Parameters at RuntimeNow we're ready to figure out how to do something smarter than using hardwired default connection parameters such as letting the user specify those values at runtime. The previous client programs have a significant shortcoming in that the connection parameters are written literally into the source code. 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. For example, the programs in the MySQL distribution accept parameters in either of two forms, as shown in the following table.
For consistency with the standard MySQL clients, our next client program, client3, will accept those same formats. It's easy to do this because the client library includes support for option processing. In addition, our client will have the ability to extract information from option files, which allows you to put connection parameters in ~/.my.cnf (that is, the .my.cnf file in your home directory) or in any of the global option files. 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 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.") Before writing client3 itself, we'll develop a couple programs that illustrate how MySQL's option-processing support works. These show how option handling works fairly simply and without the added complication of connecting to the MySQL server and processing queries. Accessing Option File ContentsTo 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 so that when you parse the command options, you get the connection parameters as part of 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(). The following is 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 <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 (); 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:
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, and then it calls load_defaults() and prints the argument array again. To see how load_defaults() works, make sure 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 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 system directory, C:\my.cnf, and the my.cnf file in the MySQL data directory. Client programs that use load_defaults() almost always specify "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 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 will produce 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 will take 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 what follows is 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 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.
Processing Command-Line ArgumentsUsing 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. The option-processing methods described here were introduced in MySQL 4.0.2. Before that, the client library included option-handling code that was based on the getopt_long() function. If you're writing MySQL-based programs using the client library from a version of MySQL earlier than 4.0.2, you can use the version of this chapter from the first edition of this book, which describes how to process command options using getopt_long(). The first-edition chapter is available online in PDF format at the book's companion Web site at http://www.kitebird.com/mysql-book/. The getopt_long()-based code has now been replaced with a new interface based on handle_options(). Some of the improvements offered by the new option-processing routines are:
Note: The new option-processing routines appeared in MySQL 4.0.2, but it's best to use 4.0.5 or later. Several problems were identified and fixed during the initial shaking-out period from 4.0.2 to 4.0.5. 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, client3, 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 <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 } }; 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 ("host name: %s\n", opt_host_name ? opt_host_name : "(null)"); printf ("user name: %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 name: %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 (); 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 ("host name: %s\n", opt_host_name ? opt_host_name : "(null)"); printf ("user name: %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 name: %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 will be 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) */ enum get_opt_var_type 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:
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 arguments that is, arguments that begin with a dash. This continues until it reaches the end of the vector or encounters the special "end of options" argument ('--' by itself). As it moves through the argument vector, handle_options() 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 will have been reset appropriately to represent an argument list containing only the non-option arguments. The following 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 the "Accessing Option File Contents" section earlier in this chapter): % ./show_opt -h yet_another_host --user=bill x Original connection parameters: host name: (null) user name: (null) password: (null) port number: 0 socket name: (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(): host name: yet_another_host user name: bill password: secret port number: 0 socket name: (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 '?'): 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; the final part of the output will be the help message: % ./show_opt --help You can add other cases to get_one_option() as necessary. For example, this function 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 can 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 or not 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 may 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 ProgramNow let's strip out from show_opt.c 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, client3.c, is as follows: /* * client3.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 <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 } }; 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)); } } 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'; } break; } return (0); } int main (int argc, char *argv[]) { int opt_err; my_init (); 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 queries and process results here ... */ /* disconnect from server */ mysql_close (conn); exit (0); } Compared to the client1, client2, and show_opt programs that we developed earlier, client3 does a few new things:
client3 connects to the MySQL server according to the options you specify. Assume there is no option file to complicate matters. If you invoke client3 with no arguments, it connects to localhost and passes your UNIX login name and no password to the server. If instead you invoke client3 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: % ./client3 -h some_host -p -u some_user some_db client3 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. The work we've done so far to produce client3 accomplishes something that's necessary for every MySQL client connecting to the server using appropriate parameters. The process is implemented by the client skeleton, client3.c, which you can use as the basis for other programs. Copy it and add to it any application-specific details. That means you can concentrate more 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 mysql_real_connect() and mysql_close() 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 the following:
And you're done. |