Team LiB
Previous Section Next Section

Connecting to the Server

Our first MySQL client program is about as simple as can be: It connects to a server, disconnects, and exits. That's not very useful in itself, but you have to know how to do it because you must be connected to a server before you can do anything with a MySQL database. Connecting to a MySQL server is such a common operation that code you develop to establish the connection is code you'll use in every client program you write. Besides, this task gives us something simple to start with. The code can be fleshed out later to do something more useful.

Our first client program, connect1, consists of a single source file, connect1.c:

/*
 * connect1.c - connect to and disconnect from MySQL server
 */

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

static char *opt_host_name = NULL;      /* server host (default=localhost) */
static char *opt_user_name = NULL;      /* username (default=login name) */
static char *opt_password = NULL;       /* password (default=none) */
static unsigned int opt_port_num = 0;   /* port number (use built-in value) */
static char *opt_socket_name = NULL;    /* socket name (use built-in value) */
static char *opt_db_name = NULL;        /* database name (default=none) */
static unsigned int opt_flags = 0;      /* connection flags (none) */

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

int
main (int argc, char *argv[])
{
    /* initialize connection handler */
    conn = mysql_init (NULL);
    if (conn == NULL)
    {
        fprintf (stderr, "mysql_init() failed (probably out of memory)\n");
        exit (1);
    }
    /* connect to server */
    if (mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password,
            opt_db_name, opt_port_num, opt_socket_name, opt_flags) == NULL)
    {
        fprintf (stderr, "mysql_real_connect() failed\n");
        mysql_close (conn);
        exit (1);
    }
    /* disconnect from server */
    mysql_close (conn);
    exit (0);
}

The source file begins by including the header files my_global.h, my_sys.h, and mysql.h. Depending on what a MySQL client program does, it may need to include other header files as well, but these three usually are the bare minimum:

  • my_global.h takes care of including several other header files that are likely to be generally useful, such as stdio.h. It also includes windows.h for Windows compatibility if you're compiling the program on Windows. (You may not intend to build the program under Windows yourself, but if you plan to distribute your code, having that file included will help anyone else who does compile under Windows.)

  • my_sys.h contains various portability macros and definitions for structures and functions used by the client library.

  • mysql.h defines the primary MySQL-related constants and data structures.

The order of inclusion is important; my_global.h is intended to be included before any other MySQL-specific header files.

Next, the program declares a set of variables corresponding to the parameters that need to be specified when connecting to the server. For this client, the parameters are hardwired to have default values. Later, we'll develop a more flexible approach that allows the defaults to be overridden using values specified either in option files or on the command line. (That's why the names all begin with opt_; the intent is that eventually those variables will become settable through command options.) The program also declares a pointer to a MYSQL structure that will serve as a connection handler.

The main() function of the program establishes and terminates the connection to the server. Making a connection is a two-step process:

1.
Call mysql_init() to obtain a connection handler. When you pass NULL to mysql_init(), it automatically allocates a MYSQL structure, initializes it, and returns a pointer to it. The MYSQL data type is a structure containing information about a connection. Variables of this type are called "connection handlers."

Another approach is to pass a pointer to an existing MYSQL structure. In this case, mysql_init() initializes that structure and returns a pointer to it without allocating the structure itself.

2.
Call mysql_real_connect() to establish a connection to the server. mysql_real_connect() takes about a zillion parameters:

  • A pointer to the connection handler. This should be the value returned by mysql_init().

  • The server host. This value is interpreted in a platform-specific way. On Unix, if you specify a string containing a hostname or IP address, the client connects to the given host by using a TCP/IP connection. If you specify NULL or the host "localhost", the client connects to the server running on the local host by using a Unix socket file.

    On Windows, the behavior is similar, except that for "localhost", a shared-memory or TCP/IP connection is used rather than a Unix socket file connection. On Windows NT-based systems, the connection is attempted to the local server using a named pipe if the host is "." or NULL and the server supports named-pipe connections.

  • The username and password for the MySQL account to be used. If the name is NULL, the client library sends your login name to the server (or ODBC, for Windows). If the password is NULL, no password is sent.

  • The name of the database to select as the default database after the connection has been established. If this value is NULL, no database is selected.

  • The port number. This is used for TCP/IP connections. A value of 0 tells the client library to use the default port number.

  • The socket filename. On Unix, the name is used for Unix socket file connections. On Windows, the name is interpreted as the name to use for a pipe connection. A value of NULL tells the client library to use the default socket (or pipe) name.

  • A flags value. The connect1 program passes a value of 0 because it isn't using any special connection options.

You can find more information about mysql_real_connect() in Appendix G, "C API Reference." The description there discusses in more detail issues such as how the hostname parameter interacts with the port number and socket filename parameters, and lists the options that can be specified in the flags parameter. The appendix also describes mysql_options(), which you can use to specify other connection-related options prior to calling mysql_real_connect().

To terminate the connection, invoke mysql_close() and pass it a pointer to the connection handler. If you allocated the handler automatically by passing NULL to mysql_init(), mysql_close() will automatically de-allocate the handler when you terminate the connection. After calling mysql_close(), you should not try to use the handler for further communication with the server.

To try connect1, compile and link it using the instructions given earlier in the chapter for building client programs, and then run it. Under Unix, run the program like this:

% ./connect1

The leading "./" may be necessary on Unix if your shell does not have the current directory (".") in its search path. If the directory is in your search path, or you are using Windows, you can omit the "./" from the command name:

% connect1

If connect1 produces no output, it connected successfully. On the other hand, you might see something like this:

% ./connect1
mysql_real_connect() failed

This output indicates that no connection was established, but it doesn't tell you why. Very likely the reason for the failure is that the default connection parameters (hostname, username, and so on) are unsuitable. Assuming that is so, one way to fix the problem is to recompile the program after editing the initializers for the parameter variables and changing them to values that allow you to access your server. That might be beneficial in the sense that at least you'd be able to make a connection. But the program still would contain hardcoded values, which isn't very flexible if other people are to use it. It's also insecure because it exposes your password. You might think that the password becomes hidden when you compile your program into binary executable form, but it's not hidden at all if someone can run the strings utility on the binary. Also, anyone with read access to the source file can get the password with no work at all.

The preceding paragraph exposes two significant shortcomings of the connect1 program:

  • The error output isn't very informative about specific causes of problems.

  • There isn't a flexible way for the user who runs the program to specify connection parameters. Those parameters are hardwired into the source code. It would be better to give the user the ability to override the parameters by specifying them in an option file or on the command line.

The next section addresses these problems.

    Team LiB
    Previous Section Next Section