Client 1Connecting to the Server

Client 1 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 the code you develop to establish the connection is code you'll use in every client program you write. Additionally, this task gives us something simple to start with. The client can be fleshed out later to do something more useful.

The code for our first client program, client1, consists of a single source file, client1.c:

 /* client1.c - connect to and disconnect from MySQL server */  #include <my_global.h> #include <mysql.h> static char *opt_host_name = NULL;      /* server host (default=localhost) */ static char *opt_user_name = NULL;      /* username (default=login name) */ static char *opt_password = NULL;       /* password (default=none) */ static unsigned int opt_port_num = 0;   /* port number (use built-in value) */ static char *opt_socket_name = NULL;    /* socket name (use built-in value) */ static char *opt_db_name = NULL;        /* database name (default=none) */ static unsigned int opt_flags = 0;      /* connection flags (none) */ static MYSQL *conn;                     /* pointer to connection handler */ int main (int argc, char *argv[]) {     /* initialize connection handler */     conn = mysql_init (NULL);     /* connect to server */     mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password,                 opt_db_name, opt_port_num, opt_socket_name, opt_flags);     /* disconnect from server */     mysql_close (conn);     exit (0); } 

The source file begins by including the header files my_global.h and mysql.h. Depending on what a MySQL client does, it may need include other header files as well, but usually these two 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.)

• 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.

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. If you specify a string containing a hostname or IP address on UNIX, the client connects to the given host using a TCP/IP connection. If you specify NULL or the host "localhost", the client connects to the server running on the local host using a UNIX socket.

     On Windows, the behavior is similar, except that TCP/IP connections are used instead of UNIX sockets. Also, on Windows NT-based systems, the connection is attempted to the local server using a named pipe if the host is "." or NULL.

   • 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. 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 and socket file The port number is used for TCP/IP connections. The socket name is used for UNIX socket connections (on UNIX) or named pipe connections (on Windows). The values 0 and NULL for the parameters tell the client library to use the default port number or socket (or pipe) name.

   • A flags value The 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 F. For example, the description there discusses in more detail how the hostname parameter interacts with the port number and socket name 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.

To try out client1, 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 as follows:

 % ./client1  

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:

 % client1  

The client1 program connects to the server, disconnects, and exits. Not very exciting, but it's a start. However, it's just a start, because there are two significant shortcomings:

• The client does no error checking, so we don't really know whether or not it actually works!

• The connection parameters (hostname, username, and so forth) 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.

Neither of these problems is difficult to deal with. The next few sections address them both.