Embedding the MySQL Server Library in a C Program

By Russell Dyer

By embedding the MySQL server library in MySQL applications, MySQL doesn't have to be installed on the end user's computer. It's particularly useful with small devices and public kiosks. The embedded MySQL server also has the advantage of running faster with MySQL applications since there isn't a network connection or any passing of data between the application and MySQL. It's able to avoid these factors because the server library is embedded in the compiled program. Best of all, the embedded server library is part of the MySQL distribution.

MySQL can be embedded with C, Python, Visual Basic, and other programming languages. This article, though, will explain how to develop and compile a C program to work with the embedded MySQL server library. Some aspects are specific to C, but almost all of the concepts apply to all APIs. I will assume that you know the basics of C programming and skip over aspects that are strictly C. Additionally, I will assume that you have the GNU C Compiler (gcc) installed on your development computer and you are using Linux (although Linux is not required). This is basically all you will need. The embedded server library is contained in the libmysqld.a file, which is located in the lib sub-directory of the standard MySQL installation. To make this tutorial easier to follow, I will work through the development of a very simple C program which will retrieve data from a MySQL database using the embedded server library.

Initial Preparations

To start the example C program properly, and to make it simpler, we'll use a few header files. Using a text editor like vi, we will open a new filed called mysql_test.c and enter the following opening lines:

#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include "mysql.h"

If you've worked with C before, the first three lines here are probably very familiar to you: they are header files that contain functions that are commonly used in a C program. The fourth line calls for a header file which contains functions and structures that will be needed to interface with MySQL databases and tables. These files are located in a directory which we will specify later when we compile the program. Next we will declare some variables based on structures provided by the mysql.h header file. This is done by adding the following lines to the program:

   MYSQL *mysql;
   MYSQL_RES *results;
   MYSQL_ROW record;

The first line above sets up a pointer called mysql based on the structure MYSQL as defined in mysql.h. This will create an object for accessing the databases. The next line declares a complex data structure for the results set of a SELECT statement that will be executed later in the program. This is followed by a declaration of an array for temporarily holding a record or row that will be read from the results set later.

Even though the MySQL server daemon is not used, we still need to pass server options to the embedded server library. Server options are given in an array as an arguments to the mysql_server_init(), which initializes the server. So, in preparation we will set up two arrays related to server options like so:

   static char *server_options[] = {"mysql_test", "--defaults-file=my.cnf"};
   int num_elements = sizeof(server_options) / sizeof(char *);

   static char *server_groups[] = {"libmysqd_server", "libmysqd_client"};

The first array above contains the server options. The first element is a label which will be ignored by the mysql_server_init() function when deployed. This function is just for the embedded server, by the way. The server options follow the first element. I've only given one here. However, you can give as many as you would like, each within double-quotes, separated by commas. See the on-line documentation for a list of server options available. To minimize code, you can put the options that are common to your C programs in an options file. Then you would only have to give options that are needed by a particular program in the mysql_server_init() function, along with the --defaults-file option. This option is used to specify an options file other than the usual default one (i.e., /etc/my.cnf). In the example above, it's pointing to a file called my.cnf in the current directory since no path is given. This is handy for shipping the option file with compiled, embedded programs. The options file might look like this:

# my.cnf

[libmysqd_server]
datadir = ./data
language = ./english
skip-innodb

[libmysqld_client]
language = ./english

For the server group here, the --datadir option is given. This data directory is a sub-directory of the current directory--relative paths are allowed. Again, these settings and file locations are useful for shipping compiled programs with related files. Another option shown above is the --language option. It identifies the location of the character set and error message files. I've copied the directory english from /usr/local/mysql/share/mysql/english on my system and put it in the current directory for easy packaging. Although it may seem redundant, there has to be a separate program group for the client with at least the --language option. You can put any server or client options that you need in the options file.

Going back to the program code shown before this option file excerpt, the next line calculates the number of elements in the server_options[] array and stores that number in an integer variable called num_elements. This will be needed for the mysql_server_init() function later on. The last array (server_groups[]) above contains a list of server groups in the options file that the program is to use. It will ignore all other groups. This means that you can have different groups for different types of programs, all using the same options file.

Now that the header files have been called in, the initial variables and the like declared, and the options file created, we can begin the main() function, which will interface with the MySQL data.

Connecting to a Database

To keep this program simple, we will just have it connect to one database and execute a basic SELECT statement. For the example program, we will use a database for a fictitious bookstore called bookstore. The first tasks after starting the standard C main() function will be to initialize the embedded server and the mysql object that was declared earlier:

int main(void)
{
   mysql_server_init(num_elements, server_options, server_groups);
   mysql = mysql_init(NULL);

   mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
   mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

The first line of code within the main() function initializes the MySQL server, given the server options and server groups from the arrays set up in the previous section. The next line begins the client session: it initializes the MYSQL object, which will be referenced by mysql. Any name would do, by the way. Using the C API function mysql_options() with the MYSQL_READ_DEFAULT_GROUP option, the next line of code instructs the program, acting as a client, to use the options in my.cnf under the group heading [libmysqld_client] (shown before) as the default group. The mysql_options() function is used again above to specify explicitly that the client is to use the embedded server and not a local or remote MySQL server daemon.

Now that the server and client are initialized, the next task is to connect to the bookstore database so that queries can be executed. Using the standard C API function, mysql_real_connect(), the database is opened like so:

   mysql_real_connect(mysql, NULL,NULL,NULL, "bookstore", 0,NULL,0);

Since the database is accessed directly and not through the network, the second, third, fourth, sixth, and seventh parameters are set to NULL (except for the sixty; it's set to 0). These are respectively the host, user name, password, TCP/IP port, and socket file. Again, these parameters aren't given because the program is not interfacing through the network or a daemon. With the exception of the data, it will all be contained within the compiled program. The only parameters that need to be given are the first and fifth, and possibly the last one. The first is the MYSQL object; the fifth is the name of the database to use; the last is for any special client flags.

By default, the embedded server doesn't require user authentication. However, you could require the user to authenticate if you'd like. You would do this by adding the --with-embedded-privilege-control to the server options (in server_options[]) for mysql_server_init(). You would also need to give the host address, user name, and password with the mysql_real_connect() function above. You would probably use variables and modify your program to collect the user name and password from the user. As for host, in addition to providing it for the client connection, it should be declared first with the mysql_options() function like so:

 mysql_options(mysql, MYSQL_SET_CLIENT_IP, "10.1.1.2");

This will set the client IP to the address given. This means that you will need to add the user to the grants table and provide privileges for the user from this IP address.

At this point in the program, we can proceed as we would normally with a C API program that interfaces with MySQL. We just need to add a line with the mysql_server_end() function to close the embedded server when we're finished. So, as you can see, if you want to modify one of your C programs to use the embedded server, you would only need to modify a few lines of code. All of the C API functions will work with the embedded MySQL server library as they would with the regular MySQL server. You would have to compile the program differently, though. This is covered in a later section. For good measure, let's finish the example program.

Querying a Database

To query the MySQL database named in the last section, the function mysql_query() or mysql_real_query() can be used. We'll use the former to issue a SELECT statement to get a list of books from a table called books. Using the mysql_store_results() function, the results of the query are then stored in the results array, as shown below:

   mysql_query(mysql, "SELECT book_id, title FROM books");

   results = mysql_store_result(mysql);

   while((record = mysql_fetch_row(results))) {
      printf("%s - %s \n", record[0], record[1]);
   }

Using a while statement and the mysql_fetch_row() function, the results are looped through, one record at a time, and printed. After all of the records have been displayed, everything that was opened needs to be closed, including the main() function. This is done with these few lines:

   mysql_free_result(results);
   mysql_close(mysql);
   mysql_server_end();

   return 0;
}

The mysql_free_result() function is used to free the memory where the results set from the query is stored. The mysql_close() function will close the mysql object. You wouldn't do this if you want to perform more queries. The mysql_server_end() function is used to close the embedded server. The rest of the code above (including the closing curly-bracket) is used to close out the main() function. This completes the example program. A complete copy of it is presented at the end of this article if you would like to see it without comments in between. All that remains is to save the program and to compile it with the required libraries and files.

Compiling

To compile the C program to include the necessary files to embed the MySQL server library into the compiled version of the program, we will use the GNU C compiler (gcc). The compiler will need to know where to find various files and need instructions on how to compile the program. Below is an example of how the program outlined in this article could be compiled from the command-line:

gcc mysql_test.c -o mysql_test -lz \
   `/usr/local/mysql/bin/mysql_config --include --libmysqld-libs` 

Immediately following the gcc command is the name of the C program file we just went through in text form, uncompiled. After it, the -o option is given to indicate that the file name that follows is the name that the compiler is to give to the output file, the compiled program. The back-slash indicates to the shell that more text follows on the next line. The next line of code basically says for the compiler to obtain the location of the include files and libraries and other settings for your particular system. Because of a problem with mysql_config, I've manually added -lz, which is used for compression. This setting should be given by it, but it doesn't. If you'd like to see the settings passed to the compiler, you can enter the text contained within the back-ticks separately. By the way, those are back-ticks, not single-quotes.

Shipping It

If a program you've developed with the C API compiles without errors, you can copy it, the options file, the data directory, and the language directory to a CD and ship it. Before doing this, you should familiarize yourself with MySQL's licensing requirements. You should also consider what data you're copying and whether you want the users to have unrestricted access to it. You may want to give users only the databases and data that they need. For instance, you probably won't need to copy the mysql database directory unless you require authentication. If you have the time zone tables in the mysql database, though, you could copy just those table files and not the tables related to user privileges. If authentication is enabled, you may want to remove all users that aren't needed in the packaged application. If everything is set up properly, your compiled program should be able to run without MySQL installed and without a network connection.

Conclusion

The program shown in this article is missing any error checking code and other methods normally used with C code. I presented a very simple program for tutorial purposes. As a result, hopefully it has helped you to understand how to develop a C API program with the embedded MySQL server library. The embedded server library can allow you to create powerful database applications and to simplify user installation greatly. It has excellent possibilities.

Sample Script

For your convenience, below is a copy of the example program described in this article. This code has been reviewed and tested. However, the code is not guaranteed to be bug-free or to work properly with all versions or platforms of MySQL. You may use it or modify it at your own risk. The author or MySQL AB accept no responsibility for any problems that occur from using it. It's intended for educational purposes only.

#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include "mysql.h"

MYSQL *mysql;
MYSQL_RES *results;
MYSQL_ROW record;

static char *server_options[] = { "mysql_test", "--defaults-file=my.cnf" };
int num_elements = sizeof(server_options)/ sizeof(char *);

static char *server_groups[] = { "libmysqld_server", "libmysqld_client" };

int main(void)
{
   mysql_server_init(num_elements, server_options, server_groups);
   mysql = mysql_init(NULL);
   mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
   mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

   mysql_real_connect(mysql, NULL,NULL,NULL, "tester", 0,NULL,0);

   mysql_query(mysql, "SELECT book_id, title FROM books");

   results = mysql_store_result(mysql);

   while((record = mysql_fetch_row(results))) {
      printf("%s - %s \n", record[0], record[1]);
   }

   mysql_free_result(results);
   mysql_close(mysql);
   mysql_server_end();

   return 0;
}