- General Procedure for Building Client Programs
- Client 1—Connecting to the Server
- Client 2—Adding Error Checking
- Client 3—Making the Connection Code Modular
- Client 4—Getting Connection Parameters at Runtime
- Processing Queries
- Client 5—An Interactive Query Program
- Miscellaneous Topics
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 before you can really do anything with a MySQL database, you must be connected to a server. This is such a common operation that code you develop to establish a connection is code you'll use in every client program you write. Besides, this task gives us something simple to start with. We can flesh out the client later to do something more useful.
The source for our first client program, client1, consists of a single file, client1.c:
/* client1.c */
#include <stdio.h>
#include <mysql.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_db_name NULL /* database to use (default = none) */
MYSQL *conn; /* pointer to connection handler */
int
main (int argc, char *argv[])
{
conn = mysql_init (NULL);
mysql_real_connect (
conn, /* pointer to connection handler */
def_host_name, /* host to connect to */
def_user_name, /* user name */
def_password, /* password */
def_db_name, /* database to use */
0, /* port (use default) */
NULL, /* socket (use default) */
0); /* flags (none) */
mysql_close (conn);
exit (0);
}
The source file begins by including stdio.h and mysql.h. MySQL clients may include other header files, but generally these two are the bare minimum.
The defaults for the hostname, username, password, and database name are hardwired into the code to keep things simple. Later we'll parameterize these values so you can specify them in option files or on the command line.
The main() function of the program establishes and terminates the connection to the server. Making a connection is a two-step process:
-
Call mysql_init() to obtain a connection handler. The MYSQL data type is a structure containing information about a connection. Variables of this type are called connection handlers. When you pass NULL to mysql_init(), it allocates a MYSQL variable, initializes it, and returns a pointer to it.
-
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 not be NULL; it should be the value returned by mysql_init().
-
The server host. If you specify NULL or the host "localhost", the client connects to the server running on the local host using a UNIX socket. If you specify a hostname or host IP address, the client connects to the named host using a TCP/IP connection.
-
The username and password. 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 port number and socket file. These are specified as 0 and NULL, to tell the client library to use its default values. By leaving the port and socket unspecified, the defaults are determined according to the host you wish to connect to. The details on this are given in the description of mysql_real_connect() in Appendix F.
-
The flags value. This is 0 because we aren't using any special connection options. The options that are available for this parameter are discussed in more detail in the entry for mysql_real_connect() in Appendix F.
On Windows, the behavior is similar, except that TCP/IP connections are used instead of UNIX sockets. (On Windows NT, the connection is attempted using a named pipe before TCP/IP if the host is NULL.)
To terminate the connection, pass a pointer to the connection handler to mysql_close(). A connection handler that is allocated automatically by mysql_init() is de-allocated automatically when you pass it to mysql_close() to terminate the connection.
To try out client1, compile and link it using the instructions given earlier in the chapter for building client programs, then run it:
% client1
The 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 you don't really know whether or not it actually works!
-
The connection parameters (hostname, username, etc.) are hardwired into the source code. It would be better to allow the user to override them by specifying the parameters in an option file or on the command line.
Neither of these problems is difficult to deal with. We'll address them both in the next few sections.
n -The username and password. If the name is NULL, the client library sends your login name to the server. If the password is NULL, no password is sent.
n -The port number and socket file. These are specified as 0 and NULL, to tell the client library to use its default values. By leaving the port and socket unspecified, the defaults are determined according to the host you wish to connect to. The details on this are given in the description of mysql_real_connect() in Appendix F.
n -The flags value. This is 0 because we aren't using any special connection options. The options that are available for this parameter are discussed in more detail in the entry for mysql_real_connect() in Appendix F.
To terminate the connection, pass a pointer to the connection handler to mysql_close(). A connection handler that is allocated automatically by mysql_init() is de-allocated automatically when you pass it to mysql_close() to terminate the connection.
To try out client1, compile and link it using the instructions given earlier in the chapter for building client programs, then run it:
% client1
The 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 you don't really know whether or not it actually works!
The connection parameters (hostname, username, etc.) are hardwired into the source code. It would be better to allow the user to override them by specifying the parameters in an option file or on the command line.
Neither of these problems is difficult to deal with. We'll address them both in the next few sections.