Tuesday, January 09, 2007

MySQL Embedded Server ApplicationMySQL is an Open Source Relational Database Management System. Its fast, reliable, secure RDBMS. To add to this it has a unique feature which allows the whole DBMS to be embedded into the application itself, known as the MySQL Embedded Server.

MySQL Embedded ServerWhole MySQL Database server is bundled into a library called MySQL Embedded Server. This means that a single Dynamic linking Library (a .dll on Windows, a .so on Unix) that acts as a server and the client all by itself. Using this library one can run a Full featured Database server within the client application.

Benifits of using Embedded Server The developer doesnt have to worry about database

Installation

Availabilty

Connectivity

Portability

Speed

With embedded Server we dont need MySQL server to be installed on the target machine. Next the developer does not have to think about the availabilty and connectivity to the server. The embedded server resides within the application and uses Interprocess Communication (or pipes) instead of using the network (TCP/IP) in a conventional DBMS.It also the makes the application portable. Portable in the sense data can be moved from one machine to another easily. As the server is running within the client application the speed factor is also taken care of.

Working with the Embedded Server The embedded server resides within the application, so it has to be started and shutdown by the application. Once the server is initialized (i,e started) we can use the regular MySQL API to perform database transactions as in any Database application. To use the MySQL embedded server we have to just link the application with libmysqld library and just add four(two in most cases, unless you are developing a multithreaded application) extra function calls to the existing MySQL client server application. The extra functions to be called beingmysql_server_init() - to start the servermysql_thread_init() - to be called in each new thread that accesses MySQLmysql_thread_end() - to be called before exitingmysql_server_end() - to stop the server

Starting the Embedded Server The Embedded server is initialzed using the mysql_server_init() function with the following function signature

int mysql_server_init(int argc, char **argv, char **groups)Where argc and argv are same as the the main() function's argc and argv. The last argument is used to specify the active groups of the options file.

Options file MySQL server startup can is customized with a list of command line options. If the number of arguments are large then the command line becomes very big. To avoid this problem we can put all the options in a file and instruct MySQL to read this "Options File" while starting up. The same holds good for the embedded server also. In a single options file we can have multiple groups of configuration. So we will have to "activate" a certain group using the third agrument in the mysl_server_init() function. By default MySQL looks at certain places to locate the options file. On Windows it looks at the following locations:

We can override this default behaviour with the "--defaults-file" option in the command line argument(in the argv argument to the mysql_server_init function). The best and easy way of using this in an embedded server application is to ship a options file with the installation and use the --defaults-file argument to point to this options file. Once the embedded server has been started we can proceed further using the database with the regular MySQL C/C++ API.

Bare minimum Options File The bare minimum options file has to contain two groups namely "[server]" and "[client]". Then this can be followed by other custom groups. Under server group we must have atleast the following two enteries:datadir - The location where data has to storedlanguage - Path to the language files.The client group has to have the language entry. In the options file if we are going to have just one set of options then we can skip server and client groups.

Note that the Windows path needs to contain forward slashes (/) and not the backslash (\). To be on the safer side use absolute path. The above example is for Windows, some holds good for UNIX also, just change the path. Copy the language files from the MySQL installation to workspace.

Stopping the server We have to stop the server when we are done with using the database. This is done using the mysql_server_end() function call. This function has no arguments and shutsdown the embedded server.

Example Lets take a look at a simple example for using MySQL Embedded Server in C.

/* * Set connection type to MYSQL_OPT_USE_EMBEDDED_CONNECTION * When we do a real connect, then a embedded server connection is used * instead of a conventional client server connection. */ mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

/* * Establish a connection to the database. By default user authentication * is disabled in a embedded server. If user authentication is required then use * --with-embedded-privilege-control in the server_options. */ if(mysql_real_connect(mysql, NULL, NULL, NULL, "test", 0, NULL, 0) == NULL) { printf("Unable to connect to the database\n"); return 1; }

/* Now that we are connect we can use the C API to proceed further */ mysql_query(mysql, "SELECT UserName FROM USERS"); result = mysql_store_result(mysql); while((row = mysql_fetch_row(result) != NULL) { printf("UserName : %s\n", row[0]); }

/*We are done with the database. Clean up!*/ mysql_free_result(result);