Using DBI in Web Applications

Using the Embedded Server Library

MySQL 4.0 introduced an embedded server library, libmysqld, that contains the MySQL server in a form that can be linked (embedded) into applications. This allows you to produce MySQL-based standalone applications, as opposed to applications that connect as a client over a network to a separate server program.

To write an embedded server application, two requirements must be satisfied. First, the embedded server library must be installed:

• If you're building from source, enable the library by using the --with-embedded-server option when you run configure.

• For binary distributions, use a Max distribution if the non-Max distribution doesn't include libmysqld.

• For RPM installs, make sure to install the embedded server RPM.

Second, you'll need to include a small amount of code in your application to start up and shut down the server.

After making sure that both requirements are met, it's necessary only to compile the application and link in the embedded server library (-lmysqld) rather than the regular client library (-lmysqlclient). In fact, the design of the server library is such that if you write an application to use it, you can easily produce either an embedded or a client/server version of the application simply by linking in the appropriate library. This works because the regular client library contains interface functions that have the same calling sequences as the embedded server calls but are stubs (dummy routines) that do nothing.

Writing an Embedded Server Application

Writing an application that uses the embedded server is little different from writing one that operates in a client/server context. In fact, if you begin with a program that is written as a client/server application, you can easily convert it to use the embedded server instead. The following procedure describes how to produce an embedded application named embapp, beginning with stmt_exec.c:

1.	Copy stmt_exec.c to embapp.c. The remaining steps apply to embapp.c. (The reason we begin with stmt_exec.c rather than stmt_exec_ssl.c is that there is no need to use SSL for connections that are set up internally within a single application.)
2.	Add mysql_embed.h to the set of MySQL header files used by the program: #include <my_global.h> #include <my_sys.h> #include <mysql.h> #include <mysql_embed.h> #include <my_getopt.h>
3.	An embedded application includes both a client side and a server side, so it can process one group of options for the client, and another group for the server. For example, an application named embapp might read the [client] and [embapp] groups from option files for the client part. To set that up, modify the definition of the client_groups array to look like this: static const char *client_groups[] = { "client", "embapp", NULL }; Options in these groups can be processed by load_defaults() and handle_options() in the usual fashion. Then define another list of option groups for the server side to use. By convention, this list should include the [server] and [embedded] groups, and also the [appname_server] group, where appname is the name of your application. For a program named embapp, the application-specific group will be [embapp_server], so you declare the list of group names as follows: static const char *server_groups[] = { "server", "embedded", "embapp_server", NULL };
4.	Call mysql_server_init() before initiating communication with the server. A good place to do this is before you call mysql_init().
5.	Call mysql_server_end() after you're done using the server. A good place to do this is after you call mysql_close().

After making these changes, the main() function in embapp.c looks like this:

 int main (int argc, char *argv[]) { int opt_err;     MY_INIT (argv[0]);     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 embedded server */     mysql_server_init (0, NULL, (char **) server_groups);     /* 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);     }     while (1)     {         char    buf[10000];         fprintf (stderr, "query> ");                    /* print prompt */         if (fgets (buf, sizeof (buf), stdin) == NULL)   /* read statement */             break;         if (strcmp (buf, "quit\n") == 0 || strcmp (buf, "\\q\n") == 0)             break;         process_statement (conn, buf);                  /* execute it */     }     /* disconnect from server */     mysql_close (conn);     /* shut down embedded server */     mysql_server_end ();     exit (0); } 

Producing the Application Executable Binary

To produce the embedded-server executable binary for embapp, link in the -lmysqld library rather than the -lmysqlclient library. The mysql_config utility is useful here.

Just as it can show you the flags to use for linking in the regular client library, it also can display the flags necessary for the embedded server:

 % mysql_config --libmysqld-libs  -L'/usr/local/mysql/lib/mysql' -lmysqld -lz -lm 

Thus, to produce an embedded version of embapp, use commands like these:

 % gcc -c `mysql_config --cflags` embapp.c % gcc -o embapp embapp.o `mysql_config --libmysqld-libs` 

Note: In these commands, you might find it necessary to use a C++ compiler such as g++ rather than a C compiler.

At this point, you have an embedded application that contains everything you need to access your MySQL databases. However, be sure when you execute embapp that it does not attempt to use the same data directory as any standalone servers that may already be running on the same machine.

Also, under Unix, the application must run with privileges that give it access to the data directory. You can either run embapp while logged in as the user that owns the data directory, or you can make it a setuid program that changes its user ID to that user when it starts. For example, to set embapp to run with the privileges of a user named mysql, issue the following commands as root:

 # chown mysql embapp # chmod 4755 embapp 

Should you decide that you want to produce a non-embedded version of the application that operates in a client/server context, link it against the regular client library. You can do so by building it like this:

 % gcc -c `mysql_config --cflags` embapp.c % gcc -o embapp embapp.o `mysql_config --libs` 

The regular client library includes dummy versions of mysql_server_init() and mysql_server_end() that do nothing, so no link errors will occur.