A Simple Connection Script

Now it's time to put the code together into a suite of small programs that we can use. As a reminder, the three programs we will create are as follows:

• main.c— The main program

• common.c— In which we will define common functions

• common.h— A header file that contains prototypes for the common functions

The code examples you saw just now came from common.c. Listing 13.1 shows the full program.

Listing 13.1. common.c

 1: /* common.c */
 2:
 3: #include <stdio.h>
 4: #include <mysql.h>
 5: #include "common.h"
 6:
 7: MYSQL * db_connect (
 8:     char *db_host,
 9:     char *db_user,
10:     char *db_pass,
11:     char *db_name,
12:     unsigned int db_port,
13:     char *unix_socket,
14:     unsigned int db_flags )
15: {
16: MYSQL   *mysql;
17:
18:     if ((mysql = mysql_init (NULL)) == NULL) {
19:         fprintf (stderr, "mysql_init() failed.
");
20:         return (NULL);
21:     }
22:     if (mysql_real_connect (
23:         mysql,
24:         db_host,
25:         db_user,
26:         db_pass,
27:         db_name,
28:         db_port,
29:         unix_socket,
30:         db_flags) == NULL ) {
31:         fprintf (stderr,
32:         "mysql_real_connect() failed!
Error %u: %s
",
33:         mysql_errno (mysql),
34:         mysql_error (mysql));
35:         return (NULL);
36:     }
37:     return (mysql);
38: }
39:
40: void db_disconnect (MYSQL *mysql)
41: {
42:     mysql_close (mysql);
43: }
					

This program defines the subroutines for connecting to (db_connect(), lines 7–38) and disconnecting from (db_disconnect(), lines 40–43) the database. These wrapper functions are held in common.c so that they may be shared by any number of application programs.

Because db_disconnect() holds a one-line routine only, it is arguable that you don't need it; you could just call mysql_close() directly each time you want to disconnect. However, this is just good program structure because at some time in the future, if you want to add other features to the disconnection routine, you would only have to change code in this one program.

Now let's look at the main application, main.c, which is shown in Listing 13.2.

Listing 13.2. main.c

 1: /* main.c */
 2:
 3: #include <stdio.h>
 4: #include <mysql.h>
 5: #include "common.h"
 6:
 7: #define def_db_host     NULL
 8: #define def_db_user     NULL
 9: #define def_db_name     NULL
10: #define def_db_port     0
11: #define def_unix_socket NULL
12:
13: MYSQL   *mysql;
14:
15: int main (int argc, char *argv[])
16: {
17: MYSQL_RES   *res_set;
18: char        *db_pass;
19:
20:     db_pass = get_tty_password (NULL);
21:
22:     mysql = db_connect (
23:         def_db_host,
24:         def_db_user,
25:         db_pass,
26:         def_db_name,
27:         def_db_port,
28:         def_unix_socket,
29:         0);
30:     if (mysql == NULL) {
31:         exit (1);
32:     } else {
33:         fprintf (stdout, "Connected.
");
34:
35:         /* Do the work here */
36:
37:     }
38:
39:     db_disconnect (mysql);
40:     exit (0);
41: }
					

Lines 3 through 5 include standard I/O and MySQL routines, and the common.h header file, which we'll look at in a moment.

Lines 7 through 11 define values for the MySQL server, the database user, the database name, the port to use, and the Unix socket. You will immediately notice that all these values are NULL or 0. This means that the server localhost will be used, and the MySQL username specified will be the same as the Unix login name of the user running the script.

Why do we do this? On a Web application, such as you saw with PHP and Perl, this would not make sense. In a Web application, you would typically put your database connection parameters into a common file that is locked away from view as much as possible. But here we're putting those parameters into the main application program.

We're doing this because a C program is more likely to be used as a standalone application than a Web application, and the user who runs the program may well be logged in at the console of the client machine where the program is running. Our script is simple! It assumes that he has a username that will be a MySQL username and, for even greater simplicity, that the MySQL server is running on that same machine.

Naturally the script would have some shortcomings in the real world: We'd want to be able to specify a different username, connect to databases on other machines, and specify which database to use. (The mysql monitor is a good example of a similar program that can do these things.) But the objective here isn't to get so clever, so let's concentrate on how to use the API.

The connection routine needs a password, so we declare the string variable db_pass on line 18 of main.c and in line 20 use the get_tty_password() function to ask the user to enter a password. This routine gets user input without echoing what he types to the screen. get_tty_password() and many other useful routines are defined in the MySQL client library, which you may want to study if you are building C applications in earnest.

Starting at line 22, the db_connect() function defined in common.c is invoked, with the values we established for the connection being passed to it. The function should return mysql, the database connection handle. The program tests for NULL in line 30, indicating failure to connect, but if successful, it executes the code in the body of the program at line 35. This is where we will soon insert some more interesting code.

To complete the three files, Listing 13.3 contains the code for common.h.

Listing 13.3. common.h

 1: MYSQL * db_connect (
 2:     char *db_host,
 3:     char *db_user,
 4:     char *db_pass,
 5:     char *db_name,
 6:     unsigned int db_port,
 7:     char *db_socket,
 8:     unsigned int flags );
 9:
10: void db_disconnect (
11:     MYSQL *mysql);
					

This header file contains prototypes for the db_connect() and db_disconnect() functions, which we defined in common.c.

To compile and link your programs, type the following:

$ make
					

If this proceeds without errors, it creates object files main.o and common.o, and the executable myapp. If it fails, go back and look for where the error occurred, following guidance from the compiler's error report.

When compiled and linked, run your application like this:

$ ./myapp
					

If you enter a valid password and the connection succeeds, you see this:

Enter password:
Connected.

Output looks like this if the connection fails:

Enter password:
mysql_real_connect() failed!
Error 1045: Access denied for user: 'tonyb@localhost' (Using password: YES)

Provided that your program compiled and ran correctly, you have now successfully built a simple database connection program. We will now enhance this program to make it run queries on a database.